September 22, 2009 at 7:25 am
I'm pulling what's left of my hair out. Here is the problem:
I need to retrieve data from mainframe tables using OpenQuery() with a dynamic SQL string, since I only want a subset of data. I know this can only be done by building the SQL string, and then calling Exec(@mySQLstring), since OpenQuery() does not accept parameters.
Ex:
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT @VAR = 'CA'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''' + @VAR + ''''')'
EXEC(@TSQL)
But in an SQL Function, you cannot call EXEC.
I really need a table function, and not a stored proc, because the table function will be used in other queries, such as:
Select * from tableA
Where tableA.B = fnMainFrameData(5).B
Can anyone suggest a workaround? My company wants up to the minute data from the mainframe, so pulling data down from the mainframe into an SQL table at predetermined times is not acceptable.
September 23, 2009 at 12:59 pm
This method is borrowed from a blog post here: http://consultingblogs.emc.com/jamespipe/archive/2007/06/28/SQL-Server-2005_3A00_-Passing-variables-into-an-OPENQUERY-argument.aspx
This should do the trick:
DECLARE @VAR char(2)
DECLARE @TSQL varchar(8000)
set @VAR = 'CA'
set @TSQL = 'SELECT * FROM pubs.dbo.authors WHERE state = ''' + @VAR + ''''
set @TSQL = 'SELECT * from openquery(MyLinkedServer, ''' + REPLACE(@TSQL, '''', '''''') + ''')'
print @TSQL
exec (@TSQL)
- Jeff
September 23, 2009 at 3:14 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply