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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy