Storing SP results to Temp Table

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/

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.

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/

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.

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.

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.

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources