Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Storing SP results to Temp Table

Author  Topic 

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2014-04-09 : 12:26:26
I have a db2 linked server. I have figured out how to see the information I need but I want to store it in a Temp Table to work with it. I have tried a few things but none seems to work.

Here is what I have tried:
SELECT * INTO #tab FROM (sp_columns_ex 'DB2','tblName')

I get incorrect syntax (also tried it with EXEC on the sp, still no good).

and

SELECT * INTO #tab
FROM
OPENQUERY([DB2], 'SET FMTONLY OFF; EXEC sp_columns_ex
DB2, tblName')

This one I get the error returned message "An unexpected token "SET FMTONLY OFF "

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-09 : 12:45:36
What do you use to "see the information"? Does this work:?

SELECT *
FROM
OPENQUERY([DB2], 'SET FMTONLY OFF; EXEC sp_columns_ex
DB2, tblName')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2014-04-09 : 12:58:58
I get the following Errors when i try that:

OLE DB provider "DB2OLEDB" for linked server "DB2" returned message "An unexpected token "SET FMTONLY OFF " was found following " EXEC". Expected tokens may include: "BEGIN-OF-STATEMENT". SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SET FMTONLY OFF; EXEC sp_columns_ex
DB2, tblName" for execution against OLE DB provider "DB2OLEDB" for linked server "DB2".

I am trying to get data from that table in a linked server and put it into a temp table for some comparison to my SQL table.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-09 : 12:59:31
What query are you using to see the information? You said you can see it. We need to see that query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2014-04-10 : 10:51:04
I use this to get the column information of a table from the linked db2 server:

EXEC sp_columns_ex 'DB2_OLE','tablename'

The information that return I need to work with which is why I want to toss it into a temp table.
Go to Top of Page

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-10 : 11:11:57
A best way would be to create a table structure as that of the output of the stored procedure and use
INSERT INTO tbl_nm EXEC proc_name
assuming it's not a dynamic column building procedure.

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2014-04-10 : 11:35:57
What would be the benefit between creating a table and just using a temp table? Not being sarcastic, just curious.

It is bombing on the EXEC part of it is the issue. Has to be something to do with it being a linked server or db2, but not 100% on that.
Go to Top of Page

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2014-04-14 : 10:24:26
Not sure why a temp table wouldn't work, but a physical table did. Thanks for the input.
Go to Top of Page
   

- Advertisement -