sp_executesql and OpenQuery - 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)

sp_executesql and OpenQuery

Author

Topic

cmadison2426
Starting Member

10 Posts

Posted - 2006-10-18 : 16:13:07

Hi everyone,
I'm trying to update a SQL table with data from an Oracle table but I've hit a dead end. I have a linked server to the Oracle database and I've used openquery successfully to populate a table on theSQL side with data from the Oracle side. Now I want to update the SQL table with the changes that were made on the Oracle side. To do that I've added a where clause to check for records on the Oracle side that have a greater ID than the last record on the SQL side. I can manually type the ID in and the query runs great but that means I would have to retrieve the last ID and manually input it into the query everytime I want to update the table. So I thought I would pass the value of the last ID to the openquery using sp_executesql. After much reading and trial and error I ended up with the below.
USE TRIO
GO
DECLARE @sql nvarchar(500)
DECLARE @ParmDefinition nvarchar(30)
DECLARE @LastRec bigint

SET @sql = N'INSERT [7 Diary] SELECT * FROM OPENQUERY([APX.WORLD], "SELECT * FROM AXENT.A010_DIARY WHERE Diary_ID > '+@LastRec1+'")'
SET @ParmDefinition = N'@LastRec1 bigint'
SET @LastRec = (SELECT TOP 1 Diary_ID FROM [7 Diary] ORDER BY Diary_ID DESC)

EXEC sp_executesql @sql, @ParmDefinition, @LastRec1 = @LastRec
GO

But I get the following error:
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@LastRec1".

If anynoe has any ideas I would greatly appreciate it.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 16:25:45

I think this should do it. There's no real need to use a parameter with sp_executesql in this case because it's being passed off to Oracle anyway.


USE TRIO
GO
DECLARE @sql nvarchar(500)
DECLARE @LastRec bigint

SET @LastRec = (SELECT TOP 1 Diary_ID FROM [7 Diary] ORDER BY Diary_ID DESC)
SET @sql = N'INSERT [7 Diary] SELECT * FROM OPENQUERY([APX.WORLD], ''SELECT * FROM AXENT.A010_DIARY WHERE Diary_ID > ' + CAST(@LastRec AS varchar(18)) + ''')'

EXEC sp_executesql @sql
GO

Edit - added the cast around @LastRec

cmadison2426
Starting Member

10 Posts

Posted - 2006-10-18 : 16:56:44

snSQL, that worked!!! You're awsome! that's the last time I use the help files. Ok maybe not the last time but I won't be so quick to take their word for it.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 17:44:24

No! Always use the help files - the help isn't wrong your query is just an exception.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources