How to insert output of SP to temp table.

My question is: I have a stored procedure with temptable and dyanmic SQL. When I execute SP it will execute dynamic SQL and give output. It will generate N no of Columns. I want to store output of SP to temptable. How will I implement? Another thing I have only SQL authentication. So what can I do now?
Do I need to set up linkserver with localserver? When I execute below query it give me this error:

SELECT * INTO #SomeNewTable  
FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=yes;','EXEC sp_who')  
Error: Access to the remote server is denied because no login-mapping exists.
What I need to do now? Any idea suggestion. Please help to resolve this issue.
Thanks in advance.
more ▼

asked Dec 08, 2011 at 08:25 AM in Default

Champak gravatar image

2 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

Finally I got an answer. SELECT * INTO #SomeNewTable FROM OPENROWSET('SQLNCLI','Server=.;Database=databasename;Uid=username;pwd=password','EXEC sp_who')

This will work only for SQL authentication.
more ▼

answered Dec 09, 2011 at 03:00 PM

Champak gravatar image

2 1 1 1

(comments are locked)
10|1200 characters needed characters left

I guess it would be hard for you to sort out this. So try this alternative

CREATE TABLE #sp_who_output
     spid SMALLINT
    ,ecid SMALLINT
    ,[status] NVARCHAR(30)
    ,loginname NVARCHAR(128)
    ,hostname NVARCHAR(128)
    ,blk NVARCHAR(10)
    ,dbname NVARCHAR(128)
    ,cmd NVARCHAR(16)
    ,request_id INT

INSERT [#sp_who_output] 
EXEC sp_who

SELECT * FROM [#sp_who_output] AS SWO

DROP TABLE [#sp_who_output]
more ▼

answered Dec 08, 2011 at 11:47 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thanks for your response but I do not need to store only output of sp_who sp but in that I have some other stored procs which contains dynamic SQL with N no of columns in output. sp_who is just for an example.
Dec 09, 2011 at 06:54 AM Champak
(comments are locked)
10|1200 characters needed characters left
Check the Username which you mentioned should exist on server and you are using correct password' if this is not a case then. As per your error message: Username tends to put your login name which you had used to connect sql server and password for the same
more ▼

answered Dec 08, 2011 at 12:48 PM

salum gravatar image

213 1

Username and login name both are same. username is mapped to login name.Password is also correct. Do I Need to have a link server for this query? I am running locally.
Dec 08, 2011 at 02:13 PM Champak
(comments are locked)
10|1200 characters needed characters left

Trusted_Connection=yes; indicates windows/domain authentication

instead of that you should supply your SQL Server login and password

User ID=username;Pwd=password;
more ▼

answered Dec 08, 2011 at 10:17 AM

KenJ gravatar image

20.3k 1 4 12

I tried but now it throws me below error message.

SELECT * INTO #SomeNewTable FROM OPENROWSET('SQLNCLI10','Server=.;Uid=USERNAME;Password=password','EXEC sp_who')

Now I got below error message Login failed for user 'username'. OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid connection string attribute".
Dec 08, 2011 at 12:36 PM Champak

Password should have been Pwd. I've updated my answer. Yours should read as follows...

SELECT * INTO #SomeNewTable FROM OPENROWSET 'SQLNCLI10','Server=.;Uid=USERNAME;Pwd=password','EXEC sp_who')
Dec 09, 2011 at 11:16 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 08, 2011 at 08:25 AM

Seen: 3141 times

Last Updated: Dec 09, 2011 at 07:59 AM