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.
`Trusted_Connection=yes;` indicates windows/domain authentication instead of that you should supply your SQL Server login and password `User ID=username;Pwd=password;`
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".
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
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.
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.
