December 3, 2007 at 2:39 pm
Hi experts,
Is there any way to write a function such that it can dynamically return a string that can be used in the openquery() function?
I have data in a Pervasive db (a linked_server) that I am synchronizing into a SQL Server db. There is one table that is insert only and by using the reference_no of the last record that was synchronized during the last synchronization ('max(reference_no) as MaxRef' on the SQL Server table), I would like to query the Pervasive db for records where reference_no >= MaxRef
Maybe you have a different way of doing this. The crux of the issue is that there are 500,000+ records in the Pervasive table and only 8,000 or so added on a daily basis. The reference_no is unique and always increases so I want to just grab just the ones that have been added since the last time the synchronization ran.
Thanks very much
December 3, 2007 at 5:10 pm
Openquery and Link server both does not work for function.
Function only works within its own database.
July 8, 2010 at 1:00 am
A late reply but I have done this recently. The problem is that you can only use static strings in openquery, not one that has been dynamically built up.
I got around this by creating an extended stored procedure through which you pass the linked server name and your dynamically built up SQL string. The extended stored procedure calls a small .NET assembly I created which runs the openquery statement using the current security context. It then pipes back the results to SQL server.
I can post the code later if anyone is stil interested. I notice that the original post was 2007 so sorry if this doesn't help the original poster.
July 8, 2010 at 1:16 am
The method I used for a similar issue to the OP about a year ago was to build up the OPENQUERY() call inside a dynamic statement. Something along the lines of (from memory, not tested):
DECLARE @sql varchar(max), @DateModified datetime
SET @DateModified = GETDATE()
SET @sql = 'SELECT * FROM OPENQUERY(''servername'',
''SELECT * FROM DBName WHERE DateModified = ''''' + @DateModified + ''''' ')
EXEC(@sql)
It made for a confusing amount of single quotes, but worked.
July 8, 2010 at 1:22 am
If that works, thats even simpler than my solution. I'm sure I tried similar before I went down the extended stored proc route but failed miserably. Will give it a try when I get to work.:-)
July 8, 2010 at 2:24 am
Nice one. That approach worked well against my linked server. I'll create a function to do the same but will probably escape out those single quotes with another character. They can get rather confusing.
July 8, 2010 at 4:54 am
Heres the stored proc I created to do generic dynamic openquery selects...
CREATE PROCEDURE [dbo].[LinkedServerQuery]
(
@LinkedServerName varchar(50),
@SQL varchar(max)
)
AS
BEGIN
DECLARE @LinkedSQL varchar(max)
SET @LinkedSQL = 'SELECT * FROM OPENQUERY(' + @LinkedServerName + ','''+ REPLACE(@SQL, '@', '''''') + ''')'
EXEC (@LinkedSQL)
END
Heres an example of how to call it and store the results in a temporary table. Notice that I replace all quotes with @ in the query I run, this reduces confusion with single quotes everywhere.
DECLARE @testtemp TABLE
(
a varchar(50),
b varchar(50)
)
declare @SQL = 'select a, b from table where code=@0000020@'
insert @testtemp exec [dbo].[LinkedServerQuery] 'linkedservername', @SQL
select * from @testtemp
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply