Bind Variable in CURSOR

SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.

I have a stored procedure which is not working the way I think it
should be.

I have a CURSOR which has a variable in the WHERE clause:

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename

It won't return anything, even when I verify that @.dbname has a value
and if I run the query in Query Analyzer with the value, it returns
rows:

SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = 'Archive'
ORDER BY tablename

DB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-29
09:52:11.823
DtsAdtStdArchive_DataSourceType5175768822006-03-29
09:52:11.8702006-03-29 09:52:11.887
DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-29
09:52:12.103

I've taken out most of the guts for simplicity, but here's what I've
got:

--CREATE TABLE dbo.db_ind
--(
--db_ind_tkintIDENTITY,
-- id int NULL,
-- tablename sysname NOT NULL,
-- indid int NULL,
-- indexname sysname NOT NULL,
-- shcontig1dt datetime NULL,
-- defragdt datetime NULL,
-- shcontig2dt datetime NULL,
-- reindexdt datetime NULL
--)

ALTER PROCEDURE IDR
(@.hours int
)
AS

--SET NOCOUNT ON
--SET ANSI_WARNINGS OFF

DECLARE @.tabname varchar(100),
@.indname varchar(100),
@.dbname varchar(50),
@.vsql varchar(1000),
@.v_hours varchar(4),
@.shcontig1dtdatetime,
@.shcontig2dtdatetime,
@.defragdtdatetime,
@.reindexdtdatetime,
@.idint,
@.indidint,
@.rundbcursorint,
@.runtabcursorint,
@.runindcursorint

DECLARE get_dbs CURSOR local fast_forward FOR
SELECT dbname
FROM db_jobs
WHERE idrdate < getdate() - 4
or idrdate is null
ORDER BY dbname

DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename

DECLARE get_inds CURSOR local fast_forward FOR
SELECT indid, indexname, defragdt, reindexdt
FROM db_ind
WHERE dbname = @.dbname
AND tablename = @.tabname
ORDER BY indexname

OPEN get_dbs
FETCH NEXT FROM get_dbs
INTO @.dbname

IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0

SELECT @.v_hours = CONVERT(varchar,@.hours)

--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====

WHILE @.rundbcursor = 1
BEGIN -- db while

PRINT '============================='
PRINT @.dbname
PRINT '============================='

--================================================== ================================================== =====
--================================================== ================================================== =====

OPEN get_tabs

FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt

IF @.@.FETCH_STATUS = 0
BEGIN
PRINT 'table: ' + @.tabname
SELECT @.runtabcursor = 1
end
ELSE
BEGIN
PRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITS
SELECT @.runtabcursor = 0
end

WHILE @.runtabcursor = 1
BEGIN
PRINT @.dbname
PRINT @.tabname

--================================================== ================================================== =====

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

WHILE @.runindcursor = 1
BEGIN
PRINT 'Index:' + @.dbname + '.' + @.tabname + '.' + @.indname

FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

END-- 1st loop through indexes
CLOSE get_inds

--================================================== ================================================== =====

--==========
PRINT 'db.tab: ' + @.dbname + '.' + @.tabname

--==========

--================================================== ================================================== =====

OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

WHILE @.runindcursor = 1
BEGIN

PRINT 'dbname: ' + @.dbname
PRINT 'tabname: ' + @.tabname
PRINT 'indname: ' + @.indname

FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt

IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0

END -- 2nd loop through indexes
CLOSE get_inds

--================================================== ================================================== =====

FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt

IF @.@.FETCH_STATUS = 0
SELECT @.runtabcursor = 1
ELSE
SELECT @.runtabcursor = 0

END-- loop through tables
CLOSE get_tabs

--================================================== ================================================== =====
--================================================== ================================================== =====

PRINT 'Index Maintenence complete. Job report in
[DB_Rpt_Fragmentation]'
PRINT ''

FETCH NEXT FROM get_dbs
INTO @.dbname

IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0

END -- loop through databases
CLOSE get_dbs
deallocate get_dbs
deallocate get_tabs
deallocate get_inds

--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====

GO

And this is what I'm getting:

=============================
Archive
=============================

(0 row(s) affected)

not getting any tables!
Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]

..
..
..
etc.

Am I missing something obvious?

Thank you for any help you can provide!!One of my fellow emps got it - apparently the CURSOR needed to be
declare w/in the loop right before I opened it.

I moved the get_tabs and get_inds cursor declarations and all is well .
.. .sql

Bind a variable to a SQL data source for labels, textboxes, etc.

I am trying to set a variable to be passed to my asp page but the value is not carrying over. I don't even know if this is supposed to work or not below:

<tdcolspan="3"style="font-size: 10pt; vertical-align: top; color:<%=Div7fontcolor%>; background-color:<%=Div7bgcolor%>; text-align: center; width: 0px;"id="TopBox"runat="server">

The above Div7fontcolor and Div7bgcolor are variables that I set in the VB file as shown below:

Dim obConnectionAs SqlConnection =New SqlConnection("Data Source=localhost\sqlexpress;Initial Catalog=OrgBoard;Integrated Security=True")Dim obCommandAs SqlCommand =New SqlCommand("SELECT Divisions.*, Dept19.*, Dept20.*, Dept21.*, ConfigDisplay.* FROM Divisions CROSS JOIN Dept19 CROSS JOIN Dept20 CROSS JOIN Dept21 CROSS JOIN ConfigDisplay", obConnection)

obConnection.Open()

Dim drAs SqlDataReader = obCommand.ExecuteReader()

dr.Read()

Dim Div7bgcolorAsString = dr("Div7color").ToString().Trim()

Dim Div7fontcolorAsString = dr("Div7textcolor").ToString().Trim()

dr.Close()

obConnection.Close()

The web page runs fine with no errors but the value does not carry over and change the property correctly.

I am able to set the bgColor value of the TopBox within the VB file if I use "TopBox.BgColor = Div7bgcolor" but I can't set other values on my web page like the font color. If I can do this from the VB file then please correct me. Using variables within the page will allow me to set almost any value but I don't even know if what I want is possible. Any help is greatly apprectiated.

Thank you,

Kris

The easiest thing for this is to use Literal controls:

<tdcolspan="3"style="font-size: 10pt; vertical-align: top; color:<asp:Literal ID="Div7fontcolor" runat="server" />; background-color:<asp:Literal ID="Div7bgcolor" runat="server" />; text-align: center; width: 0px;"id="TopBox"runat="server">

....

dr.Read()

Div7bgcolor.Text = dr("Div7color").ToString().Trim()

Div7fontcolor.Text = dr("Div7textcolor").ToString().Trim()

dr.Close()

Binary fields efficiency

Hi there,
I have a record where associated with it are upto 1000 floating point
values, would it be more efficient to have a variable sized binary field to
hold an array of floating point values, or is it fine to create another tabl
e
to hold these floating points required of the records. I'll have thousands
of the records, although they wont be accessed often - ie, once during an
import.
Thanks
K.Create the table. You are using a Relational database, which works
really well when the data is structured relationally. Use the
relational approach and you probably won't have to ask for help here
very often. Use the non-relational approach and you will generally
get a lot of heartburn when SQL Server doesn't have the tools to work
with the data. And when you end up asking for help here, you will
mostly get criticism of your database design!
So create the table. SQL Server handles thousands of rows very
efficiently, and the SQL you will need to manipulate them will be
clear and straightforward.
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 02:45:28 -0800, "Katie Viola"
<KatieViola@.discussions.microsoft.com> wrote:

>Hi there,
>I have a record where associated with it are upto 1000 floating point
>values, would it be more efficient to have a variable sized binary field t
o
>hold an array of floating point values, or is it fine to create another tab
le
>to hold these floating points required of the records. I'll have thousands
>of the records, although they wont be accessed often - ie, once during an
>import.
>Thanks
>K.

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
--
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+Isql

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
Mat
Convert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith
|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat
|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I
|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

BIDS Variable Window Disappeared

I am experiencing a weird problem with SSIS development in BIDS...
When I click "View --> Other Windows --> Variables", the window doesn't show
up.
Has anyone else had this issue? If so did you solve it, and how?
Thanks!Hello Dan,
The actual window itself or the ability to edit variables?
Are you sure the window isn't already open just tabbed with the toolbox or
something like that?
What build are you running?
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> I am experiencing a weird problem with SSIS development in BIDS...
> When I click "View --> Other Windows --> Variables", the window
> doesn't show up.
> Has anyone else had this issue? If so did you solve it, and how?
> Thanks!
>|||Allan,
Thanks for the reply.
The window itself doesn't show up. I have the window in auto-hide on the
left (with the toolbox on the left). When I traverse the menu (as stated) a
blank box appears as if it is trying to show me what I've asked.
I can get to (and edit) the variables by using the package explorer tab -
just not from the variables window.
Here are the builds of the different products I'm using:
-Microsoft Visual Studio Team Edition for Database Professionals Version
2.0.50727.251
-Microsoft Visual Studio 2005 Team Explorer
Version 8.0.50727.762
-Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Thanks again for the help...
"Allan Mitchell" wrote:

> Hello Dan,
> The actual window itself or the ability to edit variables?
> Are you sure the window isn't already open just tabbed with the toolbox or
> something like that?
> What build are you running?
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
>

BIDS Variable Window Disappeared

I am experiencing a weird problem with SSIS development in BIDS...
When I click "View --> Other Windows --> Variables", the window doesn't show
up.
Has anyone else had this issue? If so did you solve it, and how?
Thanks!
Hello Dan,
The actual window itself or the ability to edit variables?
Are you sure the window isn't already open just tabbed with the toolbox or
something like that?
What build are you running?

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> I am experiencing a weird problem with SSIS development in BIDS...
> When I click "View --> Other Windows --> Variables", the window
> doesn't show up.
> Has anyone else had this issue? If so did you solve it, and how?
> Thanks!
>
|||Allan,
Thanks for the reply.
The window itself doesn't show up. I have the window in auto-hide on the
left (with the toolbox on the left). When I traverse the menu (as stated) a
blank box appears as if it is trying to show me what I've asked.
I can get to (and edit) the variables by using the package explorer tab -
just not from the variables window.
Here are the builds of the different products I'm using:
-Microsoft Visual Studio Team Edition for Database Professionals Version
2.0.50727.251
-Microsoft Visual Studio 2005 Team Explorer
Version 8.0.50727.762
-Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Thanks again for the help...
"Allan Mitchell" wrote:

> Hello Dan,
> The actual window itself or the ability to edit variables?
> Are you sure the window isn't already open just tabbed with the toolbox or
> something like that?
> What build are you running?
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>

Best way to send DB results to variable

Hello all

I have a database query which will return a single value

SELECT COUNT(DISTINCT(Level_id)) FROM RoleSkill WHERE Route_ID = 1

obviously this will return only a single value and not an array of data like most database queries, therefore i would like it to be sent directly to an integer variable.

I tried doing this directly with a sqlcommand but get error that it cannot be converted to integer. Is there a good way of solving this problem?

thanks for your help

TomExecuteScalar|||Aha right you are!

thanks very much i expected the process to be more complex, i had forgotten there are 3 options for a command object.

cheers ...Tom|||Output parameters