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.
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.