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