CURSOR Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CURSOR Query

Author

Topic

majnoon
Starting Member

26 Posts

Posted - 2003-01-23 : 06:59:45

Hi:

I have 2 sets of the same tables sitting on 2 databases on 2 different servers. For reasons I cannot go into I cannot use a publisher / subscriber relationship to synchronise the 2 sets of tables. In which case I created a batch job that synchronises them over night. I want to run a query that gives me a rowcount for each table and its corresponding table on the linked server.

I want to run a query using a cursor such as:

DECLARE @TABLE_Name VARCHAR(50)
DECLARE @WT45SQL2_RowCount INT
DECLARE @WT47SQL1_RowCount INT

DECLARE @QUERY VARCHAR(1000)

DECLARE ORACLE_Table_CURSOR CURSOR FOR

SELECT Process FROM ORACLE_IMPORT_Process
(where Process is the table name)

OPEN ORACLE_Table_CURSOR

FETCH NEXT FROM ORACLE_Table_CURSOR
INTO @TABLE_Name

SET @QUERY = 'SELECT COUNT(*) FROM ' + @TABLE_Name

SELECT @WT45SQL2_RowCount = EXEC (@QUERY)

CLOSE ORACLE_Table_CURSOR
DEALLOCATE ORACLE_Table_CURSOR

The query fails and reports that there is a syntactical error near the word "EXEC" on the line "SELECT @WT45SQL2_RowCount = EXEC (@QUERY)". How do I re-write the query so it will work??

Any suggestions would be greatly appreciated!!

Wishing you a peaceful journey

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-23 : 07:06:14

If you want to return a value from a dynamic SQL statement, you should use the sp_executesql system stored procedure rather than the EXEC() function.

Jay White
{0}

Edited by - Page47 on 01/23/2003 07:06:35

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-23 : 07:21:36

how about

select @WT45SQL2_RowCount = SELECT rows FROM sysindexes where id = object_id(@TABLE_Name) and indid in (0,1)

otherwise
DECLARE @QUERY NVARCHAR(1000)
SET @QUERY = 'SELECT @WT45SQL2_RowCount = COUNT(*) FROM ' + @TABLE_Name
exec sp_executesql @QUERY, N'@WT45SQL2_RowCount int output', @WT45SQL2_RowCount output

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

majnoon
Starting Member

26 Posts

Posted - 2003-01-23 : 08:59:07

Thank you, Thank you, Thank you

Wishing you a peaceful journey

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-23 : 09:15:18