July 17, 2002 at 1:42 pm
Help me please!
I need inside the procedure run such query
using query from string:
declare @sql varchar(100)
select @sql = 'SELECT * FROM T1'
SELECT * FROM OPENQUERY(MYSQL_LINK, @sql)
it works just this way
SELECT * FROM OPENQUERY(MYSQL_LINK, 'SELECT * FROM T1')
But it doesn't work.
July 17, 2002 at 2:29 pm
Try this;
declare
@sql varchar(100)
, @sqlstr varchar(200)
select @sql = 'SELECT * FROM T1'
select @sqlstr = 'SELECT * FROM OPENQUERY(MYSQL_LINK, '''+@sql+''')'
exec(@sqlstr)
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 17, 2002 at 2:36 pm
IT DOESN'T HELP BECAUSE I WANT TO USE OPENQUERY LIKE A TABLE IN THE QUERY
July 17, 2002 at 3:35 pm
I beleive David is going for the dynamic SQL version of this which is common to do when you want to change items in the Query to the linked server.
Doing
declare @sql varchar(100)
select @sql = 'SELECT * FROM T1'
exec ('SELECT * FROM OPENQUERY(MYSQL_LINK,''' + @sql + ''')')
Will run as
SELECT * FROM OPENQUERY(MYSQL_LINK, 'SELECT * FROM T1')
Now you say you want like a table so I am a little lost unless you are wanting to join to another table. If so then create a temp table with CREATE TABLE #tmpTbl that has the same number of fields as the return. Then you run the code for the open query in a Stored Procedure which will allow you to pull the data into a local temp table (in truth a temp table is most likely built anyway in memory to work with the data locally).
So this would work
CREATE TABLE #tmpTbl (
columnsandwhat
)
INSERT INTO #tmpTbl (columnlist) EXECUTE sp_WithOpenQuery @params=params
This should do the job.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply