How to insert output of SP to temp table.

Question

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.

Answer 1

0

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.

answered Dec 09 '11 at 03:00 PM

Champak
2 ● 1 ● 1 ● 1

(comments are locked)

Answer 2

0

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]

Cheers.

answered Dec 08 '11 at 11:47 PM

Usman Butt
13.8k ● 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 '11 at 06:54 AM Champak

(comments are locked)

Answer 3

0

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

answered Dec 08 '11 at 12:48 PM

salum
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 '11 at 02:13 PM Champak

(comments are locked)

Answer 4

0

Trusted_Connection=yes; indicates windows/domain authentication

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

User ID=username;Pwd=password;

answered Dec 08 '11 at 10:17 AM

KenJ
12.4k ● 2 ● 10

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 '11 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 '11 at 11:16 AM KenJ

(comments are locked)

How to insert output of SP to temp table.

Follow this question

Related Questions