This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 23%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBL%3C/text%3E%3C/svg%3E)
I need to query a linked server using OpenQuery () and then store the results into a temp table. I don't have any problems doing this when the query is under the 8000 character limit, but now I have one that exceeds the limit as it is 17,559 characters. So, I have employed the solution shown below and the query runs fine. Now, I need help with how to adjust the script to store the results into a temp table, preferably using Select * Into so I can further manipulate the data.
Declare @strSQL varchar(max)
set @strSQL = N'<your query here>'
exec @strSQL at <YourLinkedServerName>
Thanks
Lorjust
You can do:
CREATE TABLE #temp (...)
INSERT TABLE #temp
EXEC (@strSQL) at LinkedServer
Yes, you need to create the table beforehand.
So, is this the proper sequence?
CREATE TABLE #temp (...)
Declare @strSQL varchar(max)
set @strSQL = N'<your query here>'
INSERT TABLE #temp
exec @strSQL at <YourLinkedServerName>
Yes, but you need parentheses around the @strSQL variable.
I'm getting the error shown below after running the code. Since I need to communicate this with the DBA, where would I need to tell him to make the necessary updates?
OLE DB provider "MSDASQL" for linked server "RIDE_REPORTING2" returned message "XARMcreate error code=8004d026:Please enable XA transaction in MSDTC security configuration".
Msg 7391, Level 16, State 2, Line 184
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "RIDE_REPORTING2" was unable to begin a distributed transaction.
By default a local transaction will promote to a distributed transaction. In this case, you have a transaction defined by the INSERT statement.
You can disable this with:
EXEC sp_serveroption RIDE_REPORTING2, 'remote proc transaction promotion', false
I assume that since you are using MSDASQL that remote data source is not another SQL Server instance?
You are correct. It is a PostGreSQL database. I placed the code before the dynamic query code but it says that I don't have the permissions to perform the action. I suppose I will have to talk to the DBA tomorrow.
I will mark this answered. Muchisimas gracias!!
Yes, this is administrative operation which you (ot your DBA) perform once,
Just curious, should I also be able to load this data into local table?
Once you have got the configuration of the server in place there should be no issues. The table you insert to can be a temp table or a permanent table.
ok, thanks .
Hi there,
If you would like to store dynamic SQL results into # temporary table or a a table variable, you have to declare the DDL first.
The below article has a sample script that might help you out.
https://learn.microsoft.com/en-us/answers/questions/257458/how-can-i-insert-dynamic-sql-data-into-temp-table.html
------------------------------------------------------------------------------------------------------------------------------
--If the reply is helpful, please Upvote and Accept it as an answer–