question

Champak avatar image
Champak asked

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.
stored-procedurestsqlopenrowset
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
`Trusted_Connection=yes;` indicates windows/domain authentication instead of that you should supply your SQL Server login and password `User ID=username;Pwd=password;`
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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".
0 Likes 0 ·
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')
0 Likes 0 ·
salum avatar image
salum answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Champak avatar image
Champak answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.