sql rowcount: cursor

Showing posts with label cursor. Show all posts

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterprise ma

Hi,
every time I save after a change in any table through enterprise nmanager
I receive this error:
[Microsoft][ODBC SQL Server Driver] Invalid cursor state
If I generate a script instead-of saving directly
and if I execute this script through the query analyzer all works fine!
My windows 2003 is english
my SQL Server is french
my default language is french
I've found the same error on another server which is in the same config.
any idea?
thanks.
Jerome.> every time I save after a change in any table through enterprise nmanager
> I receive this error:
> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
So why not use scripts in Query Analyzer? IMHO, there are only a few cases
where Enterprise Manager should be used...
http://www.aspfaq.com/2515
http://www.aspfaq.com/2455
--
http://www.aspfaq.com/
(Reverse address to reply.)|||enterprise manager is easy to use.
When I update or add a column, I can do this very quickly in enterprise
manager
using query analyzer is an overhead for me :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> every time I save after a change in any table through enterprise nmanager
>> I receive this error:
>> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
> So why not use scripts in Query Analyzer? IMHO, there are only a few
> cases
> where Enterprise Manager should be used...
> http://www.aspfaq.com/2515
> http://www.aspfaq.com/2455
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||This is over your head?
ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> enterprise manager is easy to use.
> When I update or add a column, I can do this very quickly in enterprise
> manager
> using query analyzer is an overhead for me :-)
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> >> every time I save after a change in any table through enterprise
nmanager
> >> I receive this error:
> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
> >
> > So why not use scripts in Query Analyzer? IMHO, there are only a few
> > cases
> > where Enterprise Manager should be used...
> >
> > http://www.aspfaq.com/2515
> > http://www.aspfaq.com/2455
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
>|||yep
specially when you have a lot of columns to change/add/remove :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is over your head?
> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>|||> yep
> specially when you have a lot of columns to change/add/remove :-)
Okay, so is there something especially painful about learning them? It
certainly can't HURT you to have this knowledge; surely someday you will
have to connect to SQL Server without using a GUI as a crutch...|||There are different combinations of service packs that lend
themselves to having this error more often - e.g between
8.00.0859 and 8.00.0875 you can get the error more often,
version 8.00.0927 or no SP3 version 8.00.0194 you don't,
etc.
The bottom line is what Aaron keeps telling you - you should
be using Query Analyzer for this type of thing. It was
designed more for this type of activity.
-Sue
On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
<willgart_A_@.hotmail_A_.com> wrote:
>yep
>specially when you have a lot of columns to change/add/remove :-)
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>|||I've the version 859
what is the 927 version? SP4? intermediate release?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
> There are different combinations of service packs that lend
> themselves to having this error more often - e.g between
> 8.00.0859 and 8.00.0875 you can get the error more often,
> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
> etc.
> The bottom line is what Aaron keeps telling you - you should
> be using Query Analyzer for this type of thing. It was
> designed more for this type of activity.
> -Sue
> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
> <willgart_A_@.hotmail_A_.com> wrote:
>>yep
>>specially when you have a lot of columns to change/add/remove :-)
>>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>|||> what is the 927 version? SP4? intermediate release?
Hotfix. Only certain ones are available without contacting PSS directly.
http://www.aspfaq.com/2160
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hot fix for the following:
http://support.microsoft.com/?kbid=839688
But then you will be managing hot fixes based upon not
wanting to use Query Analyzer for writing T-SQL to maintain
your databases. Doesn't seem like a good path to go down.
So if another slammer comes out, would your company want you
to patch it and use T-SQL for table changes or would they
want you to leave the box vulnerable so you could use
Enterprise Manager to make all your table changes?
-Sue
On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
<willgart_A_@.hotmail_A_.com> wrote:
>I've the version 859
>what is the 927 version? SP4? intermediate release?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
>> There are different combinations of service packs that lend
>> themselves to having this error more often - e.g between
>> 8.00.0859 and 8.00.0875 you can get the error more often,
>> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
>> etc.
>> The bottom line is what Aaron keeps telling you - you should
>> be using Query Analyzer for this type of thing. It was
>> designed more for this type of activity.
>> -Sue
>> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
>> <willgart_A_@.hotmail_A_.com> wrote:
>>yep
>>specially when you have a lot of columns to change/add/remove :-)
>>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>>
>|||ok ok...
I'll try to change my working method ;-)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:9tuhp0p5ab2leg6t7527kfe6r68n014ia8@.4ax.com...
> Hot fix for the following:
> http://support.microsoft.com/?kbid=839688
> But then you will be managing hot fixes based upon not
> wanting to use Query Analyzer for writing T-SQL to maintain
> your databases. Doesn't seem like a good path to go down.
> So if another slammer comes out, would your company want you
> to patch it and use T-SQL for table changes or would they
> want you to leave the box vulnerable so you could use
> Enterprise Manager to make all your table changes?
> -Sue
> On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
> <willgart_A_@.hotmail_A_.com> wrote:
>>I've the version 859
>>what is the 927 version? SP4? intermediate release?
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
>> There are different combinations of service packs that lend
>> themselves to having this error more often - e.g between
>> 8.00.0859 and 8.00.0875 you can get the error more often,
>> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
>> etc.
>> The bottom line is what Aaron keeps telling you - you should
>> be using Query Analyzer for this type of thing. It was
>> designed more for this type of activity.
>> -Sue
>> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
>> <willgart_A_@.hotmail_A_.com> wrote:
>>yep
>>specially when you have a lot of columns to change/add/remove :-)
>>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> This is over your head?
>> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> enterprise manager is easy to use.
>> When I update or add a column, I can do this very quickly in
>> enterprise
>> manager
>> using query analyzer is an overhead for me :-)
>>
>> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> every time I save after a change in any table through enterprise
>> nmanager
>> >> I receive this error:
>> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >
>> > So why not use scripts in Query Analyzer? IMHO, there are only a
>> > few
>> > cases
>> > where Enterprise Manager should be used...
>> >
>> > http://www.aspfaq.com/2515
>> > http://www.aspfaq.com/2455
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>>
>>
>>
>|||Related question:
Because of this same problem I have started using the query analyzer for my
query changes. I cannot find a reference for how to use ALTER to
rearrange(MOVE) columns within a table. Can someone show me an example
syntax of how to do this in TSQL?
"JÃ©jÃ©" wrote:
> ok ok...
> I'll try to change my working method ;-)
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:9tuhp0p5ab2leg6t7527kfe6r68n014ia8@.4ax.com...
> > Hot fix for the following:
> > http://support.microsoft.com/?kbid=839688
> >
> > But then you will be managing hot fixes based upon not
> > wanting to use Query Analyzer for writing T-SQL to maintain
> > your databases. Doesn't seem like a good path to go down.
> > So if another slammer comes out, would your company want you
> > to patch it and use T-SQL for table changes or would they
> > want you to leave the box vulnerable so you could use
> > Enterprise Manager to make all your table changes?
> >
> > -Sue
> >
> > On Mon, 15 Nov 2004 13:40:43 -0500, "JÃ©jÃ©"
> > <willgart_A_@.hotmail_A_.com> wrote:
> >
> >>I've the version 859
> >>what is the 927 version? SP4? intermediate release?
> >>
> >>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
> >> There are different combinations of service packs that lend
> >> themselves to having this error more often - e.g between
> >> 8.00.0859 and 8.00.0875 you can get the error more often,
> >> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
> >> etc.
> >> The bottom line is what Aaron keeps telling you - you should
> >> be using Query Analyzer for this type of thing. It was
> >> designed more for this type of activity.
> >>
> >> -Sue
> >>
> >> On Mon, 15 Nov 2004 11:17:34 -0500, "JÃ©jÃ©"
> >> <willgart_A_@.hotmail_A_.com> wrote:
> >>
> >>yep
> >>specially when you have a lot of columns to change/add/remove :-)
> >>
> >>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> >>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
> >> This is over your head?
> >>
> >> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "JÃ©jÃ©" <willgart_A_@.hotmail_A_.com> wrote in message
> >> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
> >> enterprise manager is easy to use.
> >> When I update or add a column, I can do this very quickly in
> >> enterprise
> >> manager
> >>
> >> using query analyzer is an overhead for me :-)
> >>
> >>
> >> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> >> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> >> >> every time I save after a change in any table through enterprise
> >> nmanager
> >> >> I receive this error:
> >> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
> >> >
> >> > So why not use scripts in Query Analyzer? IMHO, there are only a
> >> > few
> >> > cases
> >> > where Enterprise Manager should be used...
> >> >
> >> > http://www.aspfaq.com/2515
> >> > http://www.aspfaq.com/2455
> >> >
> >> > --
> >> > http://www.aspfaq.com/
> >> > (Reverse address to reply.)
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>|||you have to copy your table in a new one which has the right order
then delete the first table and rename the temporary table to the right name
(all in a transaction)
or you can use enterprise manager to change the order and ask to generate
the SQL statement instead-of saving the change. then execute this syntax.
"Don" <Don@.discussions.microsoft.com> wrote in message
news:140DD312-365C-45C3-9280-8082FAE8F62C@.microsoft.com...
> Related question:
> Because of this same problem I have started using the query analyzer for
> my
> query changes. I cannot find a reference for how to use ALTER to
> rearrange(MOVE) columns within a table. Can someone show me an example
> syntax of how to do this in TSQL?
> "Jéjé" wrote:
>> ok ok...
>> I'll try to change my working method ;-)
>>
>> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> news:9tuhp0p5ab2leg6t7527kfe6r68n014ia8@.4ax.com...
>> > Hot fix for the following:
>> > http://support.microsoft.com/?kbid=839688
>> >
>> > But then you will be managing hot fixes based upon not
>> > wanting to use Query Analyzer for writing T-SQL to maintain
>> > your databases. Doesn't seem like a good path to go down.
>> > So if another slammer comes out, would your company want you
>> > to patch it and use T-SQL for table changes or would they
>> > want you to leave the box vulnerable so you could use
>> > Enterprise Manager to make all your table changes?
>> >
>> > -Sue
>> >
>> > On Mon, 15 Nov 2004 13:40:43 -0500, "Jéjé"
>> > <willgart_A_@.hotmail_A_.com> wrote:
>> >
>> >>I've the version 859
>> >>what is the 927 version? SP4? intermediate release?
>> >>
>> >>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >>news:isshp0916fff78e8q6i0rn7n1lh5a0meen@.4ax.com...
>> >> There are different combinations of service packs that lend
>> >> themselves to having this error more often - e.g between
>> >> 8.00.0859 and 8.00.0875 you can get the error more often,
>> >> version 8.00.0927 or no SP3 version 8.00.0194 you don't,
>> >> etc.
>> >> The bottom line is what Aaron keeps telling you - you should
>> >> be using Query Analyzer for this type of thing. It was
>> >> designed more for this type of activity.
>> >>
>> >> -Sue
>> >>
>> >> On Mon, 15 Nov 2004 11:17:34 -0500, "Jéjé"
>> >> <willgart_A_@.hotmail_A_.com> wrote:
>> >>
>> >>yep
>> >>specially when you have a lot of columns to change/add/remove :-)
>> >>
>> >>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> >>news:ebp1VjyyEHA.2540@.TK2MSFTNGP09.phx.gbl...
>> >> This is over your head?
>> >>
>> >> ALTER TABLE <tablename> ADD <columnname> <DATATYPE>
>> >>
>> >> --
>> >> http://www.aspfaq.com/
>> >> (Reverse address to reply.)
>> >>
>> >>
>> >>
>> >>
>> >> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> >> news:eat5rhyyEHA.4028@.TK2MSFTNGP15.phx.gbl...
>> >> enterprise manager is easy to use.
>> >> When I update or add a column, I can do this very quickly in
>> >> enterprise
>> >> manager
>> >>
>> >> using query analyzer is an overhead for me :-)
>> >>
>> >>
>> >> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>> >> news:%23hCrneyyEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> >> >> every time I save after a change in any table through
>> >> >> enterprise
>> >> nmanager
>> >> >> I receive this error:
>> >> >> [Microsoft][ODBC SQL Server Driver] Invalid cursor state
>> >> >
>> >> > So why not use scripts in Query Analyzer? IMHO, there are only
>> >> > a
>> >> > few
>> >> > cases
>> >> > where Enterprise Manager should be used...
>> >> >
>> >> > http://www.aspfaq.com/2515
>> >> > http://www.aspfaq.com/2455
>> >> >
>> >> > --
>> >> > http://www.aspfaq.com/
>> >> > (Reverse address to reply.)
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>>

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterpris

Saturday, February 11, 2012

@local variable (newbie)

I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJOriginally posted by LittleJonny
I am trying to run a query.

Declare ColNames_Csr Cursor
-- Open Cursor that contains all column names
for
Select Column_Name from Information_Schema.columns where Table_Name = @.TableName

Open ColNames_csr

Fetch Next From ColNames_csr into @.FieldName

While ...

Begin
.
.
.
Select @.FieldValue = (Select @.FieldName from Contacts)
Print @.FieldValue
.
.
.
end

@.SomeValue always returns the FieldName (EMail) not @.FieldName (name@.somewhere.com).

I am trying to roll through all the columns and see what the value is for @.fieldname

What am i missing?

Thanks
LJ

Query below returns value of @.FieldName - no field in table. Think about dynamic query ...

(Select @.FieldName from Contacts)|||Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ|||Originally posted by LittleJonny
Dynamic Query? Im not sure I understand.

@.FieldName = 'Name'

Select @.FieldValue = (Select @.FieldName from Contacts where contactid = 32)

Print @.FieldValue

Always Prinst "Name"

How do I get the data in Contacts.@.FieldName?

Thanks
LJ

Dynamic query is something like this:

create table test(id int, code varchar(10))
go
insert test values(1,'A')
insert test values(2,'B')
insert test values(3,'C')
go
create proc retvalue(@.sql varchar(8000),@.result varchar(50) output)
as
declare @.res varchar(50)
create table #tmp(res varchar(50))
insert #tmp exec(@.sql)
select @.result=res from #tmp
return
go
declare @.res varchar(50),@.sql varchar(8000),@.field varchar(50)
set @.field='code'
set @.sql='select '+@.field+' from test'
exec retvalue @.sql,@.res output
select @.res

Thursday, February 9, 2012

@@RowCount problem

Hi I'm opening a RecordSet using the following code :

declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode
FROM TCertResults TSample
WHERE (TSample.ISmpShortCode ='24/12359')

Open trcl

What I want to be able to get a count of this recordset. Been thru the TSQL help and it's pointing me toward the @.@.RowCount command but I can't seem to get this working.

Any help would be apprieciated@.@.ROWCOUNT returns the number of rows affected by the last statement. Use the @.@.CURSOR_ROWS function to receive the number of qualifying rows in the last opened cursor.

BTW - do you really need cursor? It heavily hits DB performance.|||I agree,

Wont SELECT COUNT(*) FROM TCertResults TSample
WHERE TSample.ISmpShortCode ='24/12359' do the trick?|||No cursrors...No cursors...

There is precious little you can do without them...

Describe what your trying to do...|||The cursor is a lot more complicated than posted but the basic gist is that the results of the cursor will be 'fetched' into variables which will then be used to update other tables, etc. Then the variables will be repopulated using the fetch next command. When updating the other tables with the results of the original cursor, one of the values that I need to write is total amount of records from the original cursor.
So I need the recordcount as soon as the cursor is open. But as I'm sure you've gathered I haven't got a clue how to get this!|||Makes no sense to me...(being sober sucks...)

So you want to write the count of the entire result set, for every row in the cursor?

That seems to be a derivation of a thing...

Anyway...do this before you open the cursor and just use the local variable..

DECLARE @.x int
SELECT @.x=COUNT(*)
FROM TCertResults TSample
WHERE ISmpShortCode ='24/12359'

Still, you could do all of this without a cursor.

Good luck|||Looks for me you can use temp tables instead of cursors...

But if you are decided to stay with them - didn't the @.@.CURSOR_ROWS suits you? Why - any reason? error? sth other?|||Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax

declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')

Open tcrl

PRINT @.@.CURSOR_ROWS|||You don't my count solution?|||Your solution works great thank you.
I was just continuing the thread in the hope that someone could tell me what the correct syntax @.@.Cursor_Rows function was.

Cheers|||Look in BOL (Books Online):

@.@.CURSOR_ROWS
Returns the number of qualifying rows currently in the last cursor opened on the connection. To improve performance, Microsoft SQL Server can populate large keyset and static cursors asynchronously. @.@.CURSOR_ROWS can be called to determine that the number of the rows that qualify for a cursor are retrieved at the time @.@.CURSOR_ROWS is called.

Return value Description
-m The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset.
-1 The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved.
0 No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed or deallocated.
n The cursor is fully populated. The value returned (n) is the total number of rows in the cursor.

Don't use Cursors...|||Originally posted by SexualChocolate
Using the Cursor_Row function always gave me a answer of -1. To be fair though I'm new to TSQL so my syntax could be totally wrong. And to prove it here's my syntax

declare tcrl cursor FAST_FORWARD for SELECT dbo.TSample.ISmpCode
FROM dbo.TSample INNER JOIN
dbo.TCertResults ON dbo.TSample.ISmpN = dbo.TCertResults.ISmpN
WHERE (dbo.TSample.ISmpShortCode = '24/12359')

Open tcrl

PRINT @.@.CURSOR_ROWS

The Great Holy Online Book says:
"If you receive '-1' as return from @.@.CURSOR_ROWS that it means:
The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the cursor is constantly changing. It can never be definitely stated that all qualified rows have been retrieved. You can try with Brett Kaiser's count solution. So says The Great Holy Online Book"

Again:
<ghost_voice>Beware of cursors... uuuuaahahahahahaaa....</ghost_voice>|||like I say I'm fairly new to all this. I'm using a cursor because I don't know of another was to open a recordset and step thru it. If there is an alternative then please point me at it|||Count - to determine no of records,
temp tables or derived tables to help recordset operations.|||People that normally write code in client tools tend to use cursors when writing code in SQLServer..thats perfectly understood since thats the way its done in vb, c, c# etc.

BUT SqlServer is about batch updates (most of the time); lots and lots and lots of rows being updated/inserted/deleted at once, a cursor just isnt efficient enough..

My role is:If you are forced to use cursors or other types of loops in more than 1% of your sql code you have problaly done something wrong..|||Fair enough. So is there another way you can open a RecordSet populate variables based upon the first row returned from the recordset, perform an update on another table based upon the values held within these variables, step to the next row, repopulate the variables, perform the update again, etc. Without using a cursor?

Everybody tells me that there evil and everything I've read instructs me not to use them but is there another way?

TSQL novice|||You need to get your mindset away from "stepping to the next row", and think about updating ALL the rows SIMULTANEOUSLY. That is what set-based transactions are all about, and that is the power of a database management engine.

If you want to know how to do it, you will first need to read the Books Online entries for SELECT statements and UPDATE statements. Then, if you are still confused, post again and give more details about what you are trying to do. We can assist you in writing the code, but we need a base to build upon.|||Can you give us an example of an update and the cursor itself...also, tying the # of records to the "batch" that result set came from is sort of meangless...because data is fluid by nature , that number is wrong the instant you set it on the row...

Maybe a brief business description of what's going will help make things more clear to us.|||Okey dokey, heres the cursor:

declare tcrl cursor FAST_FORWARD for SELECT TSample.ISmpCode, TAnalyses.IAnalysesN, TSample.ISmpShortCode, TCertResults.ICertResultsN, TCertResults.LabNo,
TCertResults.LabNoDateTime, TJob.IJobCode, TAnalyses.IAnCode, TAnalyses.TemperatureID + 48 AS MyTempAscii,
TMedia.SpreadOrPour, TAnalyses.MediaID + 48 AS MyMediaAscii, TAnalyses.IAnShortCertDesc, TCertResultSpecDilution.Dilution,
TCertResultSpecDilution.SpecDilN, TAnalyses.KiestraTest, TAnalyses.LabelRule, TAnalyses.IAnWorkType, TAnalyses.Pathogen,
TAnalyses.TemperatureID,TAnalyses.CameraSortID,TAn alysesIncubation.IncubationLength,TAnalysesIncubat ionTolerance.IncubationTolerance
FROM TCertResults INNER JOIN
TSample ON TCertResults.ISmpN = TSample.ISmpN INNER JOIN
TJob ON TSample.IJobN = TJob.IJobN INNER JOIN
TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN INNER JOIN
TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN INNER JOIN
TAnalysesIncubationTolerance ON
TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID LEFT OUTER JOIN
TMedia ON TAnalyses.MediaID = TMedia.MediaID LEFT OUTER JOIN
TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE (TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,','))) AND (TCertResults.LabNo IS NOT NULL) AND (TCertResults.LabNoDateTime IS NOT NULL) AND
(TAnalyses.KiestraTest = 1) AND (TAnalyses.LabelRule = 'Shared') AND (TAnalyses.IAnWorkType = 'M') AND
(TAnalyses.Pathogen = 0)

Open tcrl
Fetch tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance
While @.@.Fetch_status=0

Based upon the results of the this cursor variables will be populated and tested for certain criteria and other variables populated as a result of the results. Something like:

IF @.MySpreadOrPour = 'Pour'
BEGIN
Set @.MyMediaID = '0'
END
ELSE
BEGIN
Set @.myMediaID = @.MyMediaAscii - 48
END

Then finally another table will be populated with the values held within the variables:

INSERT INTO TBarCodAFile ( Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Dilution, LabelRule, DateDue)
VALUES (@.MyBarCode,@.MyLongLSN,@.MyJobCode,@.MyShortTestDesc ,@.MyDatePlateToBeRead,@.MyMediaID,@.MyStacker,@.MyWhi teLabels,@.MyAnalystPosition,@.MyEmpID,@.MyFormatting ,@.MyDilution,@.MyLabelRule,@.MyWholeDatePlateToBeRea d).

I'll then move to the next record in the recordset (Fetch next from tcrl into @.MyLongLsn,@.MyAnalysisID,@.MyShortCode,@.MyCertResul tsN,@.MyLabNo,@.MyLabNoDateTime,@.MyJobCode,@.MyIanCod e,@.MyTempAscii,@.MySpreadOrPour,@.MyMediaAscii,@.MySh ortTestDesc,@.MyDilution,@.MySpecDilN,@.MyKiestraTest ,@.MyLabelRule,@.MyIanWorkType,@.MyPathogen,@.MyTemper atureID,@.MyCameraSortID,@.MyIncubationLength,@.MyInc ubationTolerance).

Then do all my tests again and perform the INSERT statement again. And continue unless the end of the recordset.

No doubt this is a serious pain in the arse but I can find no other way of doing it.|||FIRST...lose the fully qualified table names...

Second, THAT'S ONE HELL OF A JOIN...ARE THE KEYS ALL IDENTITY?

Third, if it takes a long time to run, you could save the results to a temp table and do all of your work as a join to that

Fourth (and I'm liking the temp table thing more and more0 you could do the insert like:

INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)|||Obviously the columns do not match...there's more in the select than the INSERT, you need to match them up...

How long does that monster take to run as a stand alone select?|||I've got to be honest and at this point say you've lost me. Maybe I just can't get my head around this temp table idea but as I see it if I populated a temp table with the results of a straight select statement surely I'd then have to read all the rows from this table to perform the criteria testing and wouldn't I still a cursor to do this. Or more likely I'm being a cretin.

Most of the linking fields are identity,yes.

Oh yeah its a proper beast, it used to be coded in Access VBA and it could take up to ten minutes to complete depending on the amount of rows affected.

I've been asked to re-code it server side and its the first time I've ventured into server side programming, so please excuse my naivety.|||Where did I lose you?

The INSERT?

The Table Aliases?

The Temp Table?

I would do...

SELECT b.ISmpCode
, d.IAnalysesN
, b.ISmpShortCode
, a.ICertResultsN
, a.LabNo
, a.LabNoDateTime
, c.IJobCode
, d.IAnCode
, d.TemperatureID + 48 AS MyTempAscii
, g.SpreadOrPour
, d.MediaID + 48 AS MyMediaAscii
, d.IAnShortCertDesc
, e.Dilution
, e.SpecDilN
, d.KiestraTest
, d.LabelRule
, d.IAnWorkType
, d.Pathogen
, d.TemperatureID
, d.CameraSortID
, h.IncubationLength
, f.IncubationTolerance
INTO WorkTable
FROM TCertResults a
INNER JOIN TSample b
ON a.ISmpN = b.ISmpN
INNER JOIN TJob c
ON b.IJobN = c.IJobN
INNER JOIN TAnalyses d
ON a.IAnalysesN = d.IAnalysesN
INNER JOIN TCertResultSpecDilution e
ON a.ICertResultsN = e.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance f
ON d.IncubationToleranceID = f.IncubationToleranceID
LEFT JOIN TMedia g
ON d.MediaID = g.MediaID
LEFT JOIN TAnalysesIncubation h
ON d.IncubationID = h.IncubationID
WHERE (TSample.ISmpShortCode IN
(SELECT *
FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')
)
)
AND (TCertResults.LabNo IS NOT NULL)
AND (TCertResults.LabNoDateTime IS NOT NULL)
AND (TAnalyses.KiestraTest = 1)
AND (TAnalyses.LabelRule = 'Shared')
AND (TAnalyses.IAnWorkType = 'M')
AND (TAnalyses.Pathogen = 0)

INSERT INTO TBarCodAFile(
Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, Field9
, Field10
, Field11
, Dilution
, LabelRule
, DateDue)
SELECT ColList
FROM WorkTable

DROP WorkTable|||This is not all that bad. This should get you started:

INSERT INTO TBarCodAFile
(--Field1,
Field2,
Field3,
Field4,
--Field5,
Field6,
--Field7,
--Field8,
--Field9,
--Field10,
--Field11,
Dilution,
LabelRule
--DateDue
)
select --MyBarCode function,
TSample.ISmpCode,
TJob.IJobCode,
TAnalyses.IAnShortCertDesc,
--MyDatePlateToBeRead function
Case when TMedia.SpreadOrPour = 'Pour' then 0 else TAnalyses.MediaID end,
--MyStacker function,
--MyWhiteLabels function,
--MyAnalystPosition function,
--MyEmpID function,
--MyFormatting function,
TCertResultSpecDilution.Dilution,
TAnalyses.LabelRule
--MyWholeDatePlateToBeRead function
FROM TCertResults
INNER JOIN TSample ON TCertResults.ISmpN = TSample.ISmpN
INNER JOIN TJob ON TSample.IJobN = TJob.IJobN
INNER JOIN TAnalyses ON TCertResults.IAnalysesN = TAnalyses.IAnalysesN
INNER JOIN TCertResultSpecDilution ON TCertResults.ICertResultsN = TCertResultSpecDilution.ICertResultsN
INNER JOIN TAnalysesIncubationTolerance ON TAnalyses.IncubationToleranceID = TAnalysesIncubationTolerance.IncubationToleranceID
LEFT OUTER JOIN TMedia ON TAnalyses.MediaID = TMedia.MediaID
LEFT OUTER JOIN TAnalysesIncubation ON TAnalyses.IncubationID = TAnalysesIncubation.IncubationID
WHERE TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@.MYLSN,',')) --This should be changed to a join...
AND TCertResults.LabNo IS NOT NULL
AND TCertResults.LabNoDateTime IS NOT NULL
AND TAnalyses.KiestraTest = 1
AND TAnalyses.LabelRule = 'Shared'
AND TAnalyses.IAnWorkType = 'M'
AND TAnalyses.Pathogen = 0

I've commented out elements where the calculation function was not available. It should run MUCH faster if you convert it to this format.

...and I like using qualified column names, Brett! To each his own...|||Yeah to each his/her own...

But it's so verbose...

Sounds like they need to do many actions against many different tables based on the result.

What do you think of the temp table idea?

Just 1 pass versus over and over...|||Temp table (or table variable) is better than a cursor, no doubt.|||Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!

Thanks again for your time and patience|||Originally posted by SexualChocolate
Ok I'll give those idea's a blast. Doubtless I'll be back in touch in about 15 minutes!!

Thanks again for your time and patience

OK...I gotta know...2:44?

Where in the world are you?|||Birmingham, England

How do I call a function from within a select statement? Told you I'd be back!|||You're better off starting a new thread for a new question but...

SELECT dbo.udf_myFunction99(Col1)....|||Thanks to everyone who posted.

Ta

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterprise ma

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterprise ma

[Microsoft][ODBC SQL Server Driver] Invalid cursor stateevery time I save in enterpris

Saturday, February 11, 2012

@local variable (newbie)

Thursday, February 9, 2012

@@RowCount problem

sql rowcount

Blog Archive

About Me