CURSOR to delete from multiple table

I'm trying to declare a cursor that gets a list of tables in a databse and
then deletes all the data from them. My problem is that SQL will not
recognize
the variable as a table name. Is there another way to do this or perhaps
some different syntax use?
Here are my statements
declare @.table_name nvarchar(384)
DECLARE DEL_WO_DATA CURSOR FOR
select [name] As table_name from sysobjects where [name] like 'work_%'
OPEN DEL_WO_DATA
FETCH NEXT FROM DEL_WO_DATA
INTO @.table_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
delete from [@.table_name] /* <--Does not recognize the @.table_name
variable at this point
Receive this error while debugging:
Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'@.table_Name'. */
FETCH NEXT FROM DEL_WO_DATA
INTO @.table_name
end
close DEL_WO_DATA
deallocate DEL_WO_DATAchange delete from [@.table_name] /* <--Does not recognize the
table_name variable at this point
to
exec('delete from ' + @.table_name)
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to declare a cursor that gets a list of tables in a databse and
> then deletes all the data from them. My problem is that SQL will not
> recognize
> the variable as a table name. Is there another way to do this or perhaps
> some different syntax use?
> Here are my statements
> declare @.table_name nvarchar(384)
> DECLARE DEL_WO_DATA CURSOR FOR
> select [name] As table_name from sysobjects where [name] like 'work_%'
>
> OPEN DEL_WO_DATA
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> delete from [@.table_name] /* <--Does not recognize the @.table_name
> variable at this point
> Receive this error while debugging:
> Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '@.table_Name'. */
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> end
> close DEL_WO_DATA
> deallocate DEL_WO_DATA
>|||EXEC('delete from ' + @.table_name)
HH, Jens Suessmeyer.
http://sqlserver2005.de
--
"Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to declare a cursor that gets a list of tables in a databse and
> then deletes all the data from them. My problem is that SQL will not
> recognize
> the variable as a table name. Is there another way to do this or perhaps
> some different syntax use?
> Here are my statements
> declare @.table_name nvarchar(384)
> DECLARE DEL_WO_DATA CURSOR FOR
> select [name] As table_name from sysobjects where [name] like 'work_%'
>
> OPEN DEL_WO_DATA
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> delete from [@.table_name] /* <--Does not recognize the @.table_name
> variable at this point
> Receive this error while debugging:
> Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '@.table_Name'. */
> FETCH NEXT FROM DEL_WO_DATA
> INTO @.table_name
> end
> close DEL_WO_DATA
> deallocate DEL_WO_DATA
>|||Try the script below. However this will most likely fail if you have
foreign key constraints declared on tables. If this is just a one-off
then you could also try:
/* Are you SURE' EXEC sp_msforeachtable 'DELETE ?' */
Again, you may have to execute it more than once if it fails on FK
constraints. Don't use this in persistent code as it's undocumented.
You may alternatively find it more efficient just to drop the database
and then re-create it from a script.
/* DELETE FROM every table !! */
DECLARE @.TableName SYSNAME
DECLARE TableList INSENSITIVE CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE
table_type='BASE TABLE'
OPEN TableList
FETCH TableList INTO @.TableName
WHILE @.@.fetch_status=0
BEGIN
PRINT @.TableName
/* Are you SURE' EXEC ('DELETE FROM ['+@.TableName+']') */
FETCH TableList INTO @.TableName
END
CLOSE TableList
DEALLOCATE TableList
David Portas
SQL Server MVP
--|||That worked! Many thanks... to both of you.
-Troy
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uEbLVlQRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> EXEC('delete from ' + @.table_name)
> HH, Jens Suessmeyer.
> --
> http://sqlserver2005.de
> --
> "Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
> news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>

cursor select and variables

I have problems to place my variable into the select statement.

DECLARE @.DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @.DB_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @.DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @.DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @.DB_Name in my select ?

the object against which you run a query cannot be a variable. You need use dynamic SQL by first constructing your SQL query as a string and then using the EXEC command or the sp_execute_sql system stored procedure. e.g.

declare @.sql VARCHAR(4000)

set @.sql = 'Select NAME, FILEDIRECTORY FROM ' + @.DB_NAME + '.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE ''%\\%'');'

EXEC (@.sql)

Cursor question

I've recently began studying cursors. The following code is from my server but in don't understand the purpose of the highlighted part.

declare c_cursor cursor for
select distinct suniq from Stustat where trkuniq in (select trkuniq from track where schoolc = @.schoolc) and
graden >= @.beginningGradeForCalc

open c_cursor
fetch next from c_cursor into @.variable
while (@.@.fetch_status= 0)
begin
-- CHECK GPA (FIRES OFF SINGLE STUDENT DYNAMIC GPA CALCULATION FOR EACH SUNIQ IN STUGRP_ACTIVE FOR A SPECIFIED SCHOOL)
-- exec siSp_dyngpa @.suniq, @.schyear, @.graden example: exec sisp_dyngpa @.variable, 2007, 9, 1
exec sisp_dyngpa @.variable, @.beginningYrForCalc, @.beginningGradeForCalc, '1'
fetch next from c_cursor into @.variable

end
fetch next from c_cursor into @.variable
deallocate c_cursor

Is there an obvious reason for the FETCH after teh END statment?

I don't think it should be there as its outside the while loop. Doesn't serve any purpose as you are deallocating the cursor straight afterwards anyway and so no processing is done with the results.

HTH!

|||

No, there is no obvious reason. also, remember to close the cursor before deallocating it.

AMB

|||What are the ramification of not closing the cursor before deallocating it? Or it is more a matter of procedure?|||

I am not familiar with the internals, but I think that the description in BOL is clear.

AMB

sql

cursor problem

Actually My proble is

DECLARE @.SNO INT
DECLARE CURS_FOR_SNO CURSOR FOR SELECT * FROM MACB where cntrl_no='DC000429' and isnull(listed,'')='U' AND ISNULL(SNO,'')='' ORDER BY SNO
OPEN CURS_FOR_SNO
FETCH NEXT FROM CURS_FOR_SNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.I=@.I + 1
SET @.SNO=@.X + @.I
UPDATE MACB SET SNO=@.SNO where CURRENT OF CURS_FOR_SNO
FETCH NEXT FROM CURS_FOR_SNO
END
CLOSE CURS_FOR_SNO
DEALLOCATE CURS_FOR_SNO

it gives read only column but i want to update that so plz, solve my problem

YOu will have to pout the UPDATE syntax after the Select statement, for more information and samples, look in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||plz give any example qry for update syntax|||

Hi,

you have defined a "Order by" which declares the cursor as readonly. Leave the Orderby and put a FOR UPDATE after your cursor declaration:

DECLARE CURS_FOR_SNO CURSOR FOR SELECT * FROM MACB where cntrl_no='DC000429' and isnull(listed,'')='U' AND ISNULL(SNO,'')='' FOR UPDATE

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||You don't need to a cursor to perform these DML operations. You can do this with a single DML statement that uses a correlated sub-query for example to count the rows and assign the SNO value.|||Sure thats one thing I forgot to mention, set based statements are in most cases faster than cursor based ones. Cursors are only not avoidable in cases you can′t use a setbased command like executing a stored procedure for n-rows (like sending mails)

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

Cursor is the only choice?

If I want to do a process in a sequence order, is Cursor my only choice?
for example,
----
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from table
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoSomething @.ID
end
close MyCursor
deallocate MyCursor
----
In fact, I have found a way which does NOT work as followed,
select @.Null=dbo.DoSomething(id)
from table
where created>thedate
order by created
As I just metioned, it does not work. There are too many restrictions in the
function declaration. No newid(), no xxxxxx, no xxxxxx, too many
restrictions! But the code is short and clear. I like.
So I would like to know are there another choices for me in this simple
example?Hi, Frank
You can do something this:
declare @.ID int
set @.ID=(
select top 1 id from table
where created>thedate
order by created, id
)
while @.ID is not null begin
exec DoSomething(@.ID)
set @.ID=(
select top 1 id from table
where created>thedate
and id>@.ID
order by created, id
)
end
But this method would not have a better performance than a cursor. The
real solution is to rewrite the DoSomething procedure to process all
rows at once. That's what SQL is for: set-based operations. Please tell
us what DoSomething is supposed to do, or post the actual procedure,
along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
...), so we can really help.
Razvan|||Frank Lee wrote:

> If I want to do a process in a sequence order, is Cursor my only choice?
That depends what the process is. What is the thing represented by
"DoSomething" in your example code? If it is just more data
manipulation then there's a good chance that it can be done without
using a cursor.
David Portas
SQL Server MVP
--|||Go to http://www.sciencecartoonsplus.com/gallery.htm. That is what
your "exec DoSomething @.ID" is like!
I doubt you really need a cursor. In my career (`20 years of SQL
coding, 35 years total) I have written five cursors in production SQL
code; I know that if I had the CASE expression back in the old days, I
know I could have avoided three of them.
We will need more details and probably have to re-write your DDL (good
SQL programmers do not use uniqueidentifier, and names like "id" or
"@.null" even in pseudo-code).|||The Scenario for me is an (I Called) asynchornous Request-and-Process model.
--DDL
Create table Request (
ID uniqueidentifier primary key default newid(),
types int not null, --RequestType, depend on use case, not
important here.
data varchar(255) not null, --pseudo code, maybe many columns,
depend.
created datetime not null default getdate(),
done bit not null default 0
)
go
create trigger xxxxxxxxxxx on Request for insert as
begin
--Do nothing or DoSomethings which will complete in short time
--And then activate a job which process the Request
exec sp_start job xxxxxxxxxxxxxxxxxxxx
end
go
Client-Side (C/S or asp.net) insert something by using
insert into Request (types, data)
The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
or activate by Request_Insert_Trigger. There is nothing to do for the job
but just exec MyProcessSP. In MyProcessSP, there is a loop to call
DoMyProcessSP @.ID as descripted in last post.
create procedure DoMyProcessSP (@.ID uniqueidentifier) as
begin
declare @.types int
declare @.data varchar(255)
--pseudo code
select @.types=types, @.data=data
from Request
where id=@.id
if @.types=0
begin
insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else if @.type=1
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
update request
set Done=1
where id=@.id
end
go
create procedure MyProcessSP as --A caller in sequent order
begin
--pseudo code
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from request
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoMyProcessSP @.ID
end
close MyCursor
deallocate MyCursor
end
go
"Razvan Socol" <rsocol@.gmail.com>
'?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
> Hi, Frank
> You can do something this:
> declare @.ID int
> set @.ID=(
> select top 1 id from table
> where created>thedate
> order by created, id
> )
> while @.ID is not null begin
> exec DoSomething(@.ID)
> set @.ID=(
> select top 1 id from table
> where created>thedate
> and id>@.ID
> order by created, id
> )
> end
> But this method would not have a better performance than a cursor. The
> real solution is to rewrite the DoSomething procedure to process all
> rows at once. That's what SQL is for: set-based operations. Please tell
> us what DoSomething is supposed to do, or post the actual procedure,
> along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
> ...), so we can really help.
> Razvan
>|||There is a more detail example code in the post I reply to Razvan.
If you do have a good suggestion, please read that post. thx.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136020809.428204.141060@.o13g2000cwo.googlegroups.com...
> Frank Lee wrote:
>
> That depends what the process is. What is the thing represented by
> "DoSomething" in your example code? If it is just more data
> manipulation then there's a good chance that it can be done without
> using a cursor.
> --
> David Portas
> SQL Server MVP
> --
>|||Frank Lee (Reply@.to.newsgroup) writes:
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
>...
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
>...
So we still can't tell whether you can rewrite this into set-based code.
However, it cannot be denied that there is a trade-off. Even in T-SQL
it is easier to express logic scalarly, handling one row at a time. For
starters, if you want to split code between stored procedures, it's
easy to pass parameters, but you can only pass scalar parameters, not
tables. (Procedures can still share data over tables, see my article
http://www.sommarskog.se/share_data.html for some techniques.)
Rewriting existing code that uses iterative processesing into set-based
can for complex cases be quite an effort. I rewrote a central procedure
of our system in October/November, and it spent over 80 hours on that,
including testing. We have more that we need to rewrite, and the total
estimate is over 500 hours.
Obviously, there is a trade-off. As long as performance is acceptable
for the task, it can be difficult to justify a rewrite, but be prepared
that in some point in the life-time of the system, the situation may
become untenable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Frank Lee wrote:
> The Scenario for me is an (I Called) asynchornous Request-and-Process mode
l.
> --DDL
> Create table Request (
> ID uniqueidentifier primary key default newid(),
> types int not null, --RequestType, depend on use case, not
> important here.
> data varchar(255) not null, --pseudo code, maybe many columns,
> depend.
> created datetime not null default getdate(),
> done bit not null default 0
> )
> go
> create trigger xxxxxxxxxxx on Request for insert as
> begin
> --Do nothing or DoSomethings which will complete in short time
> --And then activate a job which process the Request
> exec sp_start job xxxxxxxxxxxxxxxxxxxx
> end
> go
> Client-Side (C/S or asp.net) insert something by using
> insert into Request (types, data)
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
> create procedure DoMyProcessSP (@.ID uniqueidentifier) as
> begin
> declare @.types int
> declare @.data varchar(255)
> --pseudo code
> select @.types=types, @.data=data
> from Request
> where id=@.id
> if @.types=0
> begin
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else if @.type=1
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> update request
> set Done=1
> where id=@.id
> end
> go
> create procedure MyProcessSP as --A caller in sequent order
> begin
> --pseudo code
> declare @.ID uniqueidentifier
> declare MyCursor cursor local for
> select id
> from request
> where created>thedate
> order by created
> for read only
> open MyCursor
> while (1=1)
> begin
> fetch next from MyCursor into @.ID
> if @.@.fetch_status<>0
> break
> exec DoMyProcessSP @.ID
> end
> close MyCursor
> deallocate MyCursor
> end
> go
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
Here's a slightly different approach that has worked for me in the
past. Use your scheduled job or some other scheduled prgram to pull
processes from your request table one at a time. That way you can scale
it by spawning new threads, each of which will take the next pending
request. Also it's probably easier to handle and debug errors if each
job execution is associated with only a single request at a time. You
can use TOP 1 to retrieve the next request. If your processes need to
be serialized then you can use an extra attribute to group those so
that they are taken as a sequence.
I am assuming that these requests are some unrelated and inherently
procedural tasks rather than straight data-manipulation, otherwise
there may be better solutions without cursors or procedural code.
In SQL Server 2005 we have the Service Broker architecture to take care
of messaging and queueing functionality. Take a look at Service Broker
if you haven't already.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136119382.878745.243360@.f14g2000cwb.googlegroups.com...
> Here's a slightly different approach that has worked for me in the
> past. Use your scheduled job or some other scheduled prgram to pull
> processes from your request table one at a time.
Thanks, I use SQL Agent Job.

> That way you can scale
> it by spawning new threads, each of which will take the next pending
> request. Also it's probably easier to handle and debug errors if each
> job execution is associated with only a single request at a time.
Yes, I agree.

> You
> can use TOP 1 to retrieve the next request. If your processes need to
> be serialized then you can use an extra attribute to group those so
> that they are taken as a sequence.
>
Good point. Thx.

> I am assuming that these requests are some unrelated and inherently
> procedural tasks rather than straight data-manipulation, otherwise
> there may be better solutions without cursors or procedural code.
>
Yes, they are unrelated.

>
> In SQL Server 2005 we have the Service Broker architecture to take care
> of messaging and queueing functionality. Take a look at Service Broker
> if you haven't already.
>
Yes, I know. I have test it, and try to rewrite one implementation which
used to use SQL Agent job to implement. However, I find Service Broker is
too BIG for me. I would like, and am planning, to use it to do another big
things. Thanks anyway.

> --
> David Portas
> SQL Server MVP
> --
>sql

CURSOR INSENSITIVE

Hi,

DECLARE MyCursor CURSOR READ_ONLY FOR Select statement

it seems I could not use INSENSITIVE in this cursor declaration, how can I accomplish that?

this works

DECLARE MyCursor2 INSENSITIVE CURSOR FOR Select * From sys.sysobjects FOR READ ONLY

and here is the syntax for the DECLARE CURSOR via BOL for 2005, you can use either syntax:

SQL 92 Syntax

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

[;]

Transact-SQL Extended Syntax

DECLARE cursor_name CURSOR[ LOCAL | GLOBAL ][ FORWARD_ONLY | SCROLL ][ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ][ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ][ TYPE_WARNING ]FOR select_statement[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]|||

INSENSITIVE in the declaration is a SQL-92 syntax, and READ_ONLY is not. So they are part of two different versions of cursor syntax (confusingly smashed together.

Check books online in the DECLARE CURSOR topic and it shows the two different syntaxes together.

Why do you think you need both? INSENSITIVE implies read only: INSENSITIVE: "modifications made to base tables are not reflected in the data returned by fetches made to this cursor"

And to answer your other question here. Cursors are pretty horrible for you SQL performance if you can get around their use. If you can't think of a way to avoid using them, post here and someone will help you :)

|||Please update thread Jim.sql

Cursor from stored procedure?

I have a situation where I need to create a cursor based on a stored
procedure, so basically something like this...
DECLARE @.proc varchar(250)
Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
Cast(@.LastUpdateDate As Varchar(7))
DECLARE the_cursor CURSOR FOR @.proc
SQL Server expects a 'Select' statement instead of an SP name and therefore
is giving me an error. I am building the stored procedure name to include
the server name because I am linking with the @.DBServerName server before
this statement (with sp_addlinkedserver).
I tried just issuing a built 'Select' statement against the linked server,
but again, the 'Select' statement had to be built as a string to include the
linked server name. Is there a Transact-SQL version of eval() or something
like that to tell it to treat the string version of the 'Select' statement a
s
if it were a 'Select' statement that was just typed in (not built as a
string)? Or, is there a way to build a cursor by defining it to be a stored
procedure instead of a 'Select' statement? Seems as though I'm in a catch-2
2
unless I'm missing something.
Thanks,
ToddI dont think you can define based directly on a SP.
Instead, store the results in a Temp table and base your cursor on the temp
table.
Gopi
"Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>I have a situation where I need to create a cursor based on a stored
> procedure, so basically something like this...
> DECLARE @.proc varchar(250)
> Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
> Cast(@.LastUpdateDate As Varchar(7))
> DECLARE the_cursor CURSOR FOR @.proc
> SQL Server expects a 'Select' statement instead of an SP name and
> therefore
> is giving me an error. I am building the stored procedure name to include
> the server name because I am linking with the @.DBServerName server before
> this statement (with sp_addlinkedserver).
> I tried just issuing a built 'Select' statement against the linked server,
> but again, the 'Select' statement had to be built as a string to include
> the
> linked server name. Is there a Transact-SQL version of eval() or
> something
> like that to tell it to treat the string version of the 'Select' statement
> as
> if it were a 'Select' statement that was just typed in (not built as a
> string)? Or, is there a way to build a cursor by defining it to be a
> stored
> procedure instead of a 'Select' statement? Seems as though I'm in a
> catch-22
> unless I'm missing something.
> Thanks,
> Todd|||The problem is that I have to build a string that represents the remote
stored procedure or string that represents a 'Select' statement. I must
build the string representations in order to dynamically include the remotel
y
linked server name into the equation. If I build a string representation of
an SP SQL Server will run it, but I have no way of seeing the records it
returns that I know of (can't build a cursor from an SP). If I build a
string representation of a 'Select' statement that includes the remote serve
r
name, how the heck do you tell SQL Server to run the 'Select'? It's just a
string value to SQL Server.
I guess my other options are to have a separate SP for each server I'm going
to link to or to put the code on each SQL Server machine and link back up
with the main server. That way the linked server name will be constant. I
didn't want to do either of these unless, of course, the other way just won'
t
work.
"rgn" wrote:

> I dont think you can define based directly on a SP.
> Instead, store the results in a Temp table and base your cursor on the tem
p
> table.
> Gopi
> "Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
> news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>
>sql

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I need to process some data from a read-only MSAccess table named "Padron"
>> that has 350000+ records.
>> I declare the linked server:
>> EXEC sp_addlinkedserver
>> @.server = 'PADRONELECTORAL',
>> @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> @.srvproduct = 'OLE DB Provider for Jet',
>> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
>> The problem is that when I open the cursor, the operation takes too long:
>> DECLARE tabla CURSOR LOCAL FOR
>> SELECT * FROM
>> PADRONELECTORAL...Padron
>> OPEN tabla
>> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
>> solve the problem.
>> Note: If I try to open the cursor with a table that has a few of records,
>> the operation executes immediately, so I guess the problem is related with
>> record count.
>> Thanks!
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!
This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>
|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data th
en
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Cursor for dynamic sql

is it possible to declare cursor for dynamic sql ?
thanksCould you show give us some more detail on what is it that you are trying to
do?
I tend to avoid cursors and dynamic SQL where possible, but you are asking
for a combination of both :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%237MeBX52FHA.3588@.TK2MSFTNGP15.phx.gbl...
is it possible to declare cursor for dynamic sql ?
thanks|||Refer to this.
http://www.sommarskog.se/dynamic_sql.html#cursor
But remember, you are digging your own grave.
--
Regards
R.D
--Knowledge gets doubled when shared
"prefect" wrote:

> is it possible to declare cursor for dynamic sql ?
> thanks
>
>|||he he :) , i was expecting that kind of response.
i am producing loginnames from name + surname according to some logic.
when a loginname repeats , i have to change the logic for each repeating
loginname therefore i have to use cursor inside a cursor but only once and
bla bla......anyway i solved my problem, thanks for your interest...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23lkb7o52FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Could you show give us some more detail on what is it that you are trying
> to
> do?
> I tend to avoid cursors and dynamic SQL where possible, but you are asking
> for a combination of both :-)
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%237MeBX52FHA.3588@.TK2MSFTNGP15.phx.gbl...
> is it possible to declare cursor for dynamic sql ?
> thanks
>
>

Cursor for

Hi,

Declare wh_ctry_id CURSOR FOR

Is "cursor for" is a function or datatype or what is this?

Regards

Abdul

According to the MSDN documentation...

Microsoft SQL Server statements produce a complete result set, butthere are times when the results are best processed one row at a time.Opening a cursor on a result set allows processing the result set onerow at a time. You can assign a cursor to a variable or parameter withacursor data type.

... a cursor is sort of result set within SQL Server.

|||

To know about the Cursors, check out the links given below:

http://www.databasejournal.com/features/mssql/article.php/1439731

http://www.teratrax.com/articles/sql_server_cursor.html

http://www.sqlteam.com/article/cursors-an-overview

|||

Hello Abdul,

The CURSOR FOR declaration is part of T-SQL. See:http://msdn2.microsoft.com/en-us/library/aa258831(SQL.80).aspx

You can use a cursor for looping over a number of records to perform certain actions in a stored procedure.

Jeroen Molenaar.

Cursor Error...

...I can't seem to work it out, maybe you can help. I get a
Server: Msg 137, Level 15, State 2, Line 22
Must declare the variable '@.tbl_name'.
when I run...
declare @.counter int, @.tbl_name varchar(40)
select @.counter = 1
declare loop_cursor cursor for SELECT distinct name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
UPDATE @.tbl_name
SET marcA = substring(marcA,1,2)
WHERE marc = 1
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
END
FETCH loop_cursor INTO @.tbl_name
CLOSE loop_cursor
DEALLOCATE loop_cursor
am i missing something blatently obvious, it's friday so probably so arrrgh
The creates are as follows for anyone wishing to try it
drop table marc, marc1
create table marc (marc int, marcA varchar(5))
create table marc1 (marc int, marcA varchar(5))
insert into marc values(1, 'ABC')
insert into marc values(2, 'ABC')
insert into marc1 values(1, 'ABC')
insert into marc1 values(2, 'ABC')The SQL Server parser doesn't resolve variables as table/column names.
You'll need to use dynamic SQL to do this in Transact-SQL.
See http://www.sommarskog.se/dynamic_sql.html for an article on dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:38C354EE-43DC-42D8-B4B9-E503B72D34EA@.microsoft.com...
> ...I can't seem to work it out, maybe you can help. I get a
> Server: Msg 137, Level 15, State 2, Line 22
> Must declare the variable '@.tbl_name'.
> when I run...
> declare @.counter int, @.tbl_name varchar(40)
> select @.counter = 1
> declare loop_cursor cursor for SELECT distinct name FROM
> sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> UPDATE @.tbl_name
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> END
> FETCH loop_cursor INTO @.tbl_name
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> am i missing something blatently obvious, it's friday so probably so
> arrrgh
>
> The creates are as follows for anyone wishing to try it
> drop table marc, marc1
> create table marc (marc int, marcA varchar(5))
> create table marc1 (marc int, marcA varchar(5))
> insert into marc values(1, 'ABC')
> insert into marc values(2, 'ABC')
> insert into marc1 values(1, 'ABC')
> insert into marc1 values(2, 'ABC')|||thanks Dan, I replaced
/*
UPDATE @.tbl_name1
SET marcA = substring(marcA,1,2)
WHERE marc = 1
*/
to dynamic as follows but it only updates the first row of the first
table...Any ideas
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command|||> but it only updates the first row of the first
> table...Any ideas
It appears you need a FETCH within the WHILE loop. Try:
declare @.counter int, @.tbl_name varchar(40)
declare @.command nvarchar(4000)
select @.counter = 1
declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
FETCH loop_cursor INTO @.tbl_name
END
CLOSE loop_cursor
DEALLOCATE loop_cursor
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
> thanks Dan, I replaced
> /*
> UPDATE @.tbl_name1
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> */
> to dynamic as follows but it only updates the first row of the first
> table...Any ideas
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
>|||thankyou Dan, appreciate it.
"Dan Guzman" wrote:

> It appears you need a FETCH within the WHILE loop. Try:
>
> declare @.counter int, @.tbl_name varchar(40)
> declare @.command nvarchar(4000)
> select @.counter = 1
> declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> FETCH loop_cursor INTO @.tbl_name
> END
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
>
>sql

cursor error must declare scalar variable

when the following SELECT is executed via A cursor to SQL 2005 it works

SELECT * FROM LCTRY
WHERE LANGUAGE = 'EN' AND CTRY_CODE = :CTRY

BUT

SELECT * FROM LCTRY
WHERE CTRY_CODE = :CTRY AND LANGUAGE = 'EN'

PRODUCES AN ERROR 000137 MUST DECALRE SCALAR VARIABLE
'@.P1AND'

The application works with MySQL.Hi,

It could be that you are missing single quotes around one of your parameters.

Cheers
C

Cursor Declaration

Hello All!
I am trying to declare a cursor and I keep on gettin the following error
when I try and debug: Incorrect syntax near the keyword 'declare'. I am
stumped and becuase I have even tried using the numerous exmaples
listed on the internet and modified the select clause.
PLEASE HELP!
Here is my stored PRoc code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FXSecondPart
AS
BEGIN
--SET NOCOUNT ON;
declare @.1m as float
declare @.2m as float
declare @.spot as float
declare @.@.ValDt as datetime
declare @.@.PayAmt as money
declare @.@.RcvAmt as money
declare @.BS as char(1)
declare @.CCY as char(3)
declare @.Sell
declare pubcrsr cursor
for select ISIN
from AllTrades
FOR READ ONLY
if i parse this I get a error!READ_ONLY not READ ONLY
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
and also
declare @.Sell needs a type maybe int?
declare @.Sell int
http://sqlservercode.blogspot.com/|||umm.. what type exactly is the @.Sell variable?
:)
Peter
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||based on what you've posted
1)there should be a END associated with the BEGIN
2) add a datatype to declare @.Sell
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris Allison" <ChrisAllison@.discussions.microsoft.com> wrote in message
news:63674EDA-96BF-41B0-A30C-19F6D254A5C1@.microsoft.com...
> Hello All!
> I am trying to declare a cursor and I keep on gettin the following error
> when I try and debug: Incorrect syntax near the keyword 'declare'. I am
> stumped and becuase I have even tried using the numerous exmaples
> listed on the internet and modified the select clause.
> PLEASE HELP!
> Here is my stored PRoc code:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE FXSecondPart
> AS
> BEGIN
> --SET NOCOUNT ON;
> declare @.1m as float
> declare @.2m as float
> declare @.spot as float
> declare @.@.ValDt as datetime
> declare @.@.PayAmt as money
> declare @.@.RcvAmt as money
> declare @.BS as char(1)
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||Hi SQL
That doesnt work! If I parse this I get a incorrect syntax near declare, the
same error!!
It cant be a permissions problem as I have SA privilidges. coulld it be
another system setting? or perhaps im declaring it in the wrong 'section' of
my stored procedure. Im using SQL Express 2005
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
OPEN pubcrsr
WHILE @.@.Fetch_Status <>-1
Begin
FETCH NEXT from pubcrsr
INTO @.@.ValDt, @.@.PayAmt, @.@.RcvAmt
--SELECT @.spot = (SELECT LastTrade FROM HistoricalFX WHERE (CCY = @.CCY) AND
--(Date = @.p1))
--SELECT @.1m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '1M') AND (Date = @.p1) )
--SELECT @.2m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '2M') AND (Date = @.p1) )
-- pass @.spot, @.1m, @.2m, @.valdt, to calculating procedure
-- with the returned value calculate the PnL and put this in a 'counter'
FETCH NEXT from pubcrsr
End
Close pubcrsr
Deallocate pubcrsr
"SQL" wrote:

> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>|||THATS IT!!! I know it was a simple problem Thanks Guys it was the @.sell
variable which ive deleted!!
Great Stuff!
"SQL" wrote:

> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>

Cursor and UDf

Hello:

I am trying to define a cursor as follows:

DECLARE EmployeeList
CURSOR FOR dbo.GetRecord(@.EmployeeID,@.CurrentDate)
Can't I use a UDF in the CURSOR FOR ?
Help please.
thank you.

I think it is the other way your UDF can use local Cursors, try the links below for more info; the second link is a UDF expert. I am assuming you know Cursors can be avoided because the query processor is not created to perform such tasks. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/1442221

http://www.novicksoftware.com/

|||SomeNewTricks2, what does your GetRecord UDF return?
|||What I am trying to do is that, I have an input parameter for my stored procedure, if it is set to 1, I want to get all records else one specific record, I want to build the select statement dynamically, then put it in the CURSOR FOR statement.
Is that possible Terri?
Thank you|||

Also, can I use something like:
CURSOR FOR
SELECT * FROM dbo.GetRecord(@.EmployeeID,@.CurrentDate)
?
thanks a lot.

|||

Hello tmorton: I was able to do so:
CREATE FUNCTION [dbo].[GetEmployeeRecord]
(
@.EmployeeID INT
)
RETURNS @.EmployeeRecord TABLE
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
AS
BEGIN
DECLARE @.strQuery NVARCHAR(1000)
DECLARE @.parameterList NVARCHAR(1000)
SELECT @.strQuery =
N'
-- Create Global Table (Temporarily)
Create Table ##TempEmployeeData
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
-- Insert the needed records to the above table
INSERT INTO ##TempEmployeeData SELECT * FROM Employees WHERE (1=1)'

-- If employee id = -1, means get all records else, get specifi record
IF (@.EmployeeID != -1)
SELECT @.strQuery = @.strQuery + N' AND (EmployeeID = @.EmployeeID)'

-- Add parameter list, only one parameter present
SET @.parameterList =N'@.EmployeeID INT'

-- Execute dynamic query
EXECUTE SP_EXECUTESQL @.strQuery, @.parameterList, @.EmployeeID

-- Fill our returned table
INSERT INTO
@.EmployeeRecord
SELECT * FROM ##TempEmployeeData

-- Drop the Temp table
DROP TABLE ##TempEmployeeData

RETURN
END
I get this error:
Server: Msg 2772, Level 16, State 1, Procedure GetEmployeeRecord, Line 72
Cannot access temporary tables from within a function.
Server: Msg 2772, Level 16, State 1, Procedure GetEmployeeRecord, Line 74
Cannot access temporary tables from within a function.
Once I finish this udf, I will use
CURSOR FOR
SELECT * FROM dbo.GetEmployeeRecord.
Can you help please. thanks you.

|||Whoa. It seems to be you are making this much more difficult than it has to be.

SomeNewTricks2 wrote:

Also, can I use something like:
CURSOR FOR
SELECT * FROM dbo.GetRecord(@.EmployeeID,@.CurrentDate)

Yes, that would be the correct way to access data from a tabel-valuedUDF. Your function returns a table, so use need to SELECT fromit. You can't just put the name of the UDF into a command byitself and expect SQL to know what to do.
You are trying to use global temp tables (##) which is usually not agood idea for a web application. When user #2 hits the site andtries to run the page you are going to start running into problems.
Maybe I am not really following what you are trying to do exactly, but why aren't you just doing something like this (untested)?

CREATE FUNCTION [dbo].[GetEmployeeRecord]
(
@.EmployeeID INT
)
RETURNS @.EmployeeRecord TABLE
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
AS
BEGIN
INSERT INTO
@.EmployeeRecord
(
EmployeeID,
UserName,
FirstName,
LastName
)

SELECT
EmployeeID,
UserName,
FirstName,
LastName
FROM
Employees
WHERE
@.EmployeeID = -1 OR @.EmployeeID = EmployeeID
RETURN

END

|||thank you Terri, that solved the problem even without temp tables.
The reason I used ## is that to be able to access it outside the context of the dynamically executed query. I mean when I run a dynamic query, in which I create a temp table, I need to access it outside the context of its execution, that is why i used ##.
One more thing, I am trying to get all records when EmlpoyeeId = -1 and get specific record when there is a valid employee id
my question, why did u write EmployeeID = -1 OR EmployeeID = @.EmployeeID ?
if EmployeeID = -1, then I will go into the table search for EmployeeID = -1, cannot find any record, however, it is returning all records, what is the logic behind it?
thank you
|||

SomeNewTricks2 wrote:

why did u write EmployeeID = -1 OR EmployeeID = @.EmployeeID ?

Actually, I wrote@.EmployeeID= -1 OREmployeeID = @.EmployeeID. That's a huge difference and is the keyto why the statement works. You have 2 conditions in your WHEREclause, with an OR between them, so only one of the conditions mustbe true in order for the WHERE criteria to be met and for a row to bereturned.
Here's a chart representing representing the above explanation:
@.EmployeeID = 5
UserName EmployeeID Condition1 (@.EmployeeID=-1) Condition 2(@.EmplyeeID=EmployeeID) Row Returned?
jones 123 False False No
smith 5 False True Yes
brown 99 False False No

@.EmployeeID = -1
UserName EmployeeID Condition1 (@.EmployeeID=-1) Condition 2(@.EmplyeeID=EmployeeID) Row Returned?
jones 123 True False Yes
smith 5 True False Yes
brown 99 True False Yes
|||

Fantastic Terri, you are really fantastic.

I once read that Books Online are good, but are there are resources for SQL Server that you usally trust?

Thanks a lot.

|||Terri, what I am doing is kind of a complicated thing.
It is a stored procedure, where I am using like 7 UDFs inside it, each has its own responsibility.
I have one more question, in a stored procedure, can I return a table?
I mean, inside the SP I will have several SELECT statements, I have like 8 fields, I want to have data for them for each employee, sometimes I might return 1 row, other times many rows, so what I am doing is, go through each row in the employees table, using the CURSOR, and then process eac employee alone, either I process one employee or many.
during procesing I will get a record of 8 fields for the employee,I want to insert that into a table, because I might have several employees, how to do that in SP ?
thanks a lot really, you are saving me.|||I'm glad to help :-)

SomeNewTricks2 wrote:

I once read that Books Online are good,but are there are resources for SQL Server that you usallytrust?

I recently changed jobs, and currently Books Online is the onlyresource for SQL Server that I have. I use it *continually*.
For websites, I most often use Google. I've also gotten a lot of help fromhttp://www.sql-server-performance.com.
On the shelf at my last job we had Ken Henderson'sThe Guru's Guide to Transact-SQL, which is excellent. Also, I have not used this book myself, but it comes highly recommended: Rob Veiera'sProfessional SQL Server 2000 Programming. I still have lots more to learn. But I have learned what I know so farwith both practical on-the-job experience, and reading and replying toposts here and on the lists athttp://www.sqladvice.com. I've certainly made mistakes in some of the advice I've given, and whenothers have come along and made a different/better suggestion this iswhere I've learned the most, I think.


|||

SomeNewTricks2 wrote:

Terri, what I am doing is kind of a complicated thing.
It is a stored procedure, where I am using like 7 UDFs inside it, each has its own responsibility.
I have one more question, in a stored procedure, can I return a table?
Imean, inside the SP I will have several SELECT statements, I have like8 fields, I want to have data for them for each employee, sometimes Imight return 1 row, other times many rows, so what I am doing is, gothrough each row in the employees table, using the CURSOR, and thenprocess eac employee alone, either I process one employee or many.
duringprocesing I will get a record of 8 fields for the employee,I want toinsert that into a table, because I might have several employees, howto do that in SP ?
thanks a lot really, you are saving me.

Are you certain you need to use cursors? You need to break freeof the procedural mindset and start thinking about sets of data. When you say you need to process each employee, what is it that you aredoing?
Explaining it further, rather than creating a loop with a cursor toinsert one record at a time into a table, you can do it in one fellswoop with a statement like this:

INSERT INTO
someTable
(
column1,
column2
)
SELECT
column1,
column2
FROM
someOtherTable
WHERE
@.someID = -1 OR @.someID = someID

|||

Hi Terri:

What do you mean by "sets of data", what is the difference between that and Cursors?
What I am doing is the following:
1- I am getting a list of all employees
2- for each employee I do the following:
2.1 Get attendance sheet (timein, timeout, absence, remarks)
3- if employee is scehduled
3.1 Get the schedule of the employee
3.2 get new values (required time in, required time out, required work time)
3.3 get difference in time in
3.4 get difference in time out
4- If not scheduled
4.1 I get normal schedule for all employees
4.2 I set the values for (required time in, ... as above)
I am doing that in one sp, it is in a project I have employees and I want to keep track of their attendance.
so, I am lopping through each employee, generated one record as output:
EmployeeName, timeIn, Requiredtimein, timeout, requiredtimeout, worktime, requried work time, break. diffin, diffout, absence, remarks
That is the record that should be returned for each employee.
So what are your ideas?
Thanks a lot and good luck in your work.

|||You should really be able to do all of that in one SQL statement. Below is a very rough idea of what I think you are going after(understanding that I am not sure of how your tables relate exactly,nor how you might determine the "normal schedule", and that theWorkTime, DiffIn, and DiffOut calculations probably should use theDATEDIFF function):

SELECT
E.EmployeeName,
A.TimeIn,
ISNULL(S.RequiredTimeIn, NS.RequiredTimeIn) AS RequiredTimeIn,
A.TimeOut,
ISNULL(S.RequiredTimeOut, NS.RequiredTimeOut) ASRequiredTimeOut,
A.TimeOut - A.TimeIn AS WorkTime,
ISNULL(S.RequiredWorkTime, NS.RequiredWorkTime) ASRequiredWorkTime,
A.Break,
A.TimeIn - ISNULL(S.RequiredTimeIn, NS.RequiredTimeIn) AS DiffIn,
A.TimeOut - ISNULL(S.RequiredTimeOut, NS.RequiredTimeOut) AS Diffout,
A.Absence,
A.Remarks
FROM
Employee E
LEFT OUTER JOIN
Attendance A ON E.EmployeeID = A.EmployeeID
LEFT OUTER JOIN
EmployeeSchedule S ON S.employeeID = E.employeeID
LEFT OUTER JOIN
NormalSchedule NS ON NS.employeeID = E.employeeID

I looked up a few resources to help you with the concept of set-based logic (instead of procedural logic):

SQL Cheat Sheet: Query By Example
Thinking SQL: Set-based logic can improve query performance
Procedural Versus Declarative Languages

Cursor alternative

Does anybody have a way to re-write this without using a cursor? This takes a
long time to finish. The goal is to cut the processing time.
DECLARE @.the_emp_id nvarchar(50)
Begin
Declare EmpCursor Cursor For
SELECT Employees.dbo.tbl_emplist.emp_id
FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
FOR READ ONLY
end
Open EmpCursor
While(0=0) Begin
Fetch Next From EmpCursor Into @.the_emp_id
If(@.@.Fetch_Status <> 0) Break
--execute this Stored Procedures
EXEC usp_calculate_retirement @.the_emp_id
EXEC usp_calculate_benefits @.the_emp_id
EXEC usp_calculate_vacation @.the_emp_id
EXEC usp_calculate_bonuses @.the_emp_id
EXEC usp_calculate_promotion @.the_emp_id
End
Close EmpCursor
Deallocate EmpCursor
Help highly appreciated.What you have to do is look at the functionality of each of the procs that
you exec. By the looks of it, you're doing work for just one employee at a
time in those procs. You need to fuse that functionality with the SELECT on
which you declared your cursor. You can do an UPDATE with a JOIN, for
example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92F8D983-F31D-41DB-A2AE-DBB28F545C1C@.microsoft.com...
Does anybody have a way to re-write this without using a cursor? This takes
a
long time to finish. The goal is to cut the processing time.
DECLARE @.the_emp_id nvarchar(50)
Begin
Declare EmpCursor Cursor For
SELECT Employees.dbo.tbl_emplist.emp_id
FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
FOR READ ONLY
end
Open EmpCursor
While(0=0) Begin
Fetch Next From EmpCursor Into @.the_emp_id
If(@.@.Fetch_Status <> 0) Break
--execute this Stored Procedures
EXEC usp_calculate_retirement @.the_emp_id
EXEC usp_calculate_benefits @.the_emp_id
EXEC usp_calculate_vacation @.the_emp_id
EXEC usp_calculate_bonuses @.the_emp_id
EXEC usp_calculate_promotion @.the_emp_id
End
Close EmpCursor
Deallocate EmpCursor
Help highly appreciated.|||Rewrite each of the sprocs to do their work using set-based logic rather
than processing one employee at a time. Since you call each of them with a
cursor, I bet you prolly have some cursors inside the sprocs too. Classic
mistake when developing database applications. You are not alone! :-)
The problem is that shifting your mindset to set-based from row-based can be
a VERY difficult undertaking. The two are not even in the same solar
system.
Read some beginning Transact SQL books. Hire a consultant/mentor to fix
your stuff and learn from him/her at the same time.
--
TheSQLGuru
President
Indicium Resources, Inc.
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92F8D983-F31D-41DB-A2AE-DBB28F545C1C@.microsoft.com...
> Does anybody have a way to re-write this without using a cursor? This
> takes a
> long time to finish. The goal is to cut the processing time.
> DECLARE @.the_emp_id nvarchar(50)
> Begin
> Declare EmpCursor Cursor For
> SELECT Employees.dbo.tbl_emplist.emp_id
> FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
> FOR READ ONLY
> end
> Open EmpCursor
> While(0=0) Begin
> Fetch Next From EmpCursor Into @.the_emp_id
> If(@.@.Fetch_Status <> 0) Break
> --execute this Stored Procedures
> EXEC usp_calculate_retirement @.the_emp_id
> EXEC usp_calculate_benefits @.the_emp_id
> EXEC usp_calculate_vacation @.the_emp_id
> EXEC usp_calculate_bonuses @.the_emp_id
> EXEC usp_calculate_promotion @.the_emp_id
> End
> Close EmpCursor
> Deallocate EmpCursor
> Help highly appreciated.
>|||On Aug 31, 7:32 pm, morphius <morph...@.discussions.microsoft.com>
wrote:
> Does anybody have a way to re-write this without using a cursor? This takes a
> long time to finish. The goal is to cut the processing time.
> DECLARE @.the_emp_id nvarchar(50)
> Begin
> Declare EmpCursor Cursor For
> SELECT Employees.dbo.tbl_emplist.emp_id
> FROM Employees.dbo.tbl_emplist WHERE emp_age = 55
> FOR READ ONLY
> end
> Open EmpCursor
> While(0=0) Begin
> Fetch Next From EmpCursor Into @.the_emp_id
> If(@.@.Fetch_Status <> 0) Break
> --execute this Stored Procedures
> EXEC usp_calculate_retirement @.the_emp_id
> EXEC usp_calculate_benefits @.the_emp_id
> EXEC usp_calculate_vacation @.the_emp_id
> EXEC usp_calculate_bonuses @.the_emp_id
> EXEC usp_calculate_promotion @.the_emp_id
> End
> Close EmpCursor
> Deallocate EmpCursor
> Help highly appreciated.
create procedure calculate as
begin
exec usp_calculate_retirement
exec usp_calculate_benefits
exec usp_calculate_vacation
exec usp_calculate_bonuses
exec usp_calculate_promotion
end
for each process you now have to handle all employees.

Cursor already ...

Hi! Ive got a little problem with the trigger bellow:

CREATE TRIGGER GSVD_PARAMETRO
ON TSVD_PARAMETROS
FOR INSERT
AS
DECLARE @.cod_dado int,@.cod_grupo int, @.cod_plano int, @.empresa int, @.contrato int,
@.termo int, @.pagto char(2), @.grupo int, @.cota_grupo numeric, @.cod_per int
DECLARE inserts CURSOR FOR
SELECT COD_DADO, COD_GRUPO, COD_PLANO
FROM INSERTED
DECLARE empresas CURSOR FOR
SELECT T.COD_TERMO,T.TIP_PAGTO
FROM VSVD_EMPRESA E, TSVD_CONTRATO C, TSVD_TERMO T
WHERE C.COD_EMPRESA = E.COD_EMPRESA
AND T.COD_CONTRATO = C.COD_CONTRATO
AND DATEDIFF(MONTH,T.DAT_FIM_USO,GETDATE()) <= 12

OPEN inserts

FETCH NEXT FROM inserts INTO @.cod_dado, @.cod_grupo, @.cod_plano

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(
SELECT 1
FROM TSVD_PLANO PL
WHERE NOT EXISTS (
SELECT P.COD_PLANO
FROM TSVD_PARAMETROS P
WHERE P.COD_PLANO = PL.COD_PLANO
AND P.COD_DADO = @.cod_dado)
)
BEGIN

OPEN empresas
FETCH NEXT FROM empresas INTO @.termo, @.pagto
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.pagto = 'V'
BEGIN
SELECT DISTINCT @.grupo=G.COD_GRUPO,@.cota_grupo=G.QTD_COTA_GRUPO
FROM TSVD_GRUPO G, TSVD_PARAMETROS P, TSVD_TERMO T
WHERE T.COD_TERMO = @.termo
AND P.COD_GRUPO = G.COD_GRUPO
AND P.COD_PLANO = T.COD_PLANO
AND P.COD_DADO = @.cod_dado

SELECT @.cod_per = P.COD_PER
FROM TSVD_PERIODO P
WHERE P.COD_TERMO = @.termo

IF NOT EXISTS(
SELECT 1
FROM TSVD_SALDO S
WHERE S.COD_GRUPO = @.grupo
AND S.COD_PER = @.cod_per)
BEGIN
INSERT INTO TSVD_SALDO
(COD_GRUPO,COD_PER,QTD_COTA)
VALUES
(@.grupo,@.cod_per,(@.cota_grupo * 12))
END
END
ELSE
BEGIN
SELECT DISTINCT @.grupo=G.COD_GRUPO,@.cota_grupo=G.QTD_COTA_GRUPO
FROM TSVD_GRUPO G, TSVD_PARAMETROS P, TSVD_TERMO T
WHERE T.COD_TERMO = @.termo
AND P.COD_GRUPO = G.COD_GRUPO
AND P.COD_PLANO = T.COD_PLANO
AND P.COD_DADO = @.cod_dado

DECLARE periodos CURSOR FOR
SELECT P.COD_PER
FROM TSVD_PERIODO P
WHERE P.COD_TERMO = @.termo

OPEN periodos

FETCH NEXT FROM periodos INTO @.cod_per

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(
SELECT 1
FROM TSVD_SALDO S
WHERE S.COD_GRUPO = @.grupo
AND S.COD_PER = @.cod_per)
BEGIN
INSERT INTO TSVD_SALDO
(COD_GRUPO,COD_PER,QTD_COTA)
VALUES
(@.grupo,@.cod_per,@.cota_grupo)
END

FETCH NEXT FROM periodos INTO @.cod_per
END

CLOSE periodos

DEALLOCATE periodos

END

FETCH NEXT FROM empresas INTO @.termo,@.pagto
END
CLOSE empresas
DEALLOCATE empresas
END
FETCH NEXT FROM inserts INTO @.cod_dado, @.cod_grupo, @.cod_plano
END
CLOSE inserts
DEALLOCATE inserts

And its returning:

Server: Msg 16915, Level 16, State 1, Procedure GSVD_PARAMETRO, Line 25
A cursor with the name 'empresas' already exists.
The statement has been terminated.

I cant figure it out! Could someone please help?

Thank you all!A cursor...bad enough...In a TRIGGER?

Wow...you're in for a world of hurt...

If the trigger fires before another trigger firing you'll already have the cursor declared...

Also, if it failed it will leave the cursor open...I don't see any error handling...

I would rethink your strategy...|||How about those conditions in if statement are not "YES" .. where are you closing the cursor ? Like Brett mentioned , check errors !!|||Sorry guys. Im new in sqlserver (came from oracle), and things are really different here, also Im developing using a wordpad (thats a long story...). So I dont quite know how to handle errors within a trigger (also its hard to find documentation on microsofts library), but Ill answer as I can:
Its like this

OPEN INSERTS CURSOR -FOR MULTI-ROWS(SO I WAS TOLD...)
WHILE INSERTS CURSOR
IF -SOMETHING
DECLARE CURSOR
OPEN CURSOR
WHILE CURSOR
DO STUFF
END LOOP
CLOSE CURSOR
DEALLOCATE CURSOR - HERES WHERE IT SHOULD PREVENT
THE TRIGGER FINDING THE CURSOR
DECLARED IN THE NEXT TIME
(OR SO I WAS TOLD...)
END IF
END LOOP

Hope that helps.

Cursor

hello; I have an error on the level of declaration of cursor for update:

Sqlstate 37000: the clause update is authorized only for DECLARE CUSOR. which is the solution?

can you post in English? what is the declare statement that caused this error ?|||I don't know French, but did you declare your cursor FOR UPDATE? If you try to update columns that are not allowed, you will get an error. I can tell you error says something about clause UPDATE. Also make sure that your syntax is correct on the cursor. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp|||

I have an error in declaration of Cursor.

sql state : 37000 :The Clause Update is autorized only for Declare Cursor.

|||Can you post the SQL? Sounds like you are doing an update on a non-updatable cursor or the syntax is incorrect.|||the problem is in ODBC for SqlServer 2005?

Cursor

Hi
I am creating a cursor which runs through a table of database names and
execute a script using xp_cmdshell on every database in that table.
Declare @.DataBaseName varchar(255)
Declare DBcursor CURSOR FOR
Select dbname From master..upgradedb
Declare @.command varchar(255)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @.DataBaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Upgrading.... ' + @.DataBaseName
set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
exec master..xp_cmdshell @.command
FETCH NEXT FROM DBCursor INTO @.DataBaseName
END
CLOSE DBCursor
DEALLOCATE DBCursor
I get the followinf results - Can anyone guide me in the right direction
please.
Upgrading.... QFM161R23AMEYAA
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)
Upgrading.... QFM161R23GLSCHOOLS
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
If you had printed out the command before trying to execute it for the first
time, you'd have noticed that there's a syntax error in it. A space is
missing before the -i switch.
But I'm just guessing here.
ML|||Check if sp_MSforeachdb System Stored Procedure can be used.
This will simplify the code. U may not require cursor at all.
Rakesh
"Jaco" wrote:

> Hi
> I am creating a cursor which runs through a table of database names and
> execute a script using xp_cmdshell on every database in that table.
> Declare @.DataBaseName varchar(255)
> Declare DBcursor CURSOR FOR
> Select dbname From master..upgradedb
> Declare @.command varchar(255)
>
> OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Upgrading.... ' + @.DataBaseName
> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
> c:\temp\Complete(7-9).sql'
> exec master..xp_cmdshell @.command
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> END
> CLOSE DBCursor
> DEALLOCATE DBCursor
> I get the followinf results - Can anyone guide me in the right direction
> please.
> Upgrading.... QFM161R23AMEYAA
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
> Upgrading.... QFM161R23GLSCHOOLS
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
>sql