How can I get this cursor to work ?

[Tison]

CREATE PROCEDURE dbo.test
AS
BEGIN
declare stt_crsr cursor
exec sp_remotesql SYBASE_REMOTE,"select * from database..tablename"
open stt_crsr
fetch stt_crsr
close stt_crsr
deallocate cursor stt_crsr
END

 

[JeanNiBee]

Hi

What't the error you are getting?

The cursor SHOULD be working, but you aren't doing anything with it. I.E. you are 'fetching' but not putting what's being fetched into variables.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[Tison]

When I run this it gives a syntax error at the "exec" statement.
If I take out exec, it gives a syntax error at the "sp_remotesql" statement.

 

[JeanNiBee]

Hi

The declare Cursor syntax is

DECLARE stt_crsr cursor FOR "select statement"

You are missing the FOR clause.

I'm not sure if you can use the results of a sp_remotesql in a cursor. The documentation leads me to believe you can (as it states: " sp_remoteSql - Establishes a connection to a remote server, passes a query buffer to the remote server from the client, and relays the results back to the client.&quot, but I have never tied it.

Just make sure the server you are trying to connect to has been added via 'sp_addserver'

Hope this helps.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects