InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 16:36:37 GMT20http://www.sqlservercentral.com/Forums/Topic633198-145-1.aspxHello Jeff. I realize that you posted this reply over a year ago, but I was having the same problem and went digging through Forums to see what I could find. I always SET NOCOUNT ON in my procedures, so it did not even cross my mind that another coder over on another server that I was linked to did not include that. Such a simple solution - that took me hours to find.. I just had to say Thank You.Tue, 30 Mar 2010 13:31:30 GMTSophie Gravierhttp://www.sqlservercentral.com/Forums/Topic633198-145-1.aspxIf I read you right, you want to use the output of a given stored procedure as input to another table. There are several ways to accomplish that... one way, of course, is to make the temp table in the outer query and then use Insert/Exec to populate the table using the proc as the source of rows...... but, you have the requirement of...[quote]I need a query that [highlight][b]creates[/b][/highlight] and inserts the records executed by the proc into a table #TEMP.[/quote]Can do. First, let's see what we can do with a built in stored procedure like "sp_who"...[code]--===== Create and populate the temp table using the result set of a stored procedure -- as if it were a table. SELECT * INTO #Temp FROM OPENROWSET('SQLOLEDB','Server=[color="RED"]server\instance[/color];Trusted_Connection=Yes;Database=Master', 'Set FmtOnly OFF; EXEC dbo.sp_Who')--===== Display the content of the new temp table to show it worked -- and then drop the temp table for reruns. SELECT * FROM #Temp DROP TABLE #Temp[/code]Notice that for this to work, you have to change "server\instance" to the instance name of your server as shown in SMS. Try it.Now, we've proven that the method works, so why won't this work with your stored procedure (again, change the name of the server\instance AND the name of the database) ?[code]--===== Create and populate the temp table using the result set of a stored procedure -- as if it were a table. SELECT * INTO #Temp FROM OPENROWSET('SQLOLEDB','Server=[color="RED"]server\instance[/color];Trusted_Connection=Yes;Database=[color="RED"]nameofdatabasehere[/color]', 'Set FmtOnly OFF; EXEC [b]dbo.usp_temp_InsSample'[/b])--===== Display the content of the new temp table to show it worked. SELECT * FROM #Temp DROP TABLE #Temp[/code][b][color="RED"]Msg 7357, Level 16, State 1, Line 3Cannot process the object "Set FmtOnly OFF; EXEC dbo.usp_temp_InsSample". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.[/color][/b]The answer is... the "rowcount" of building the table in the stored proc is treated like an empty result set! You MUST add SET NOCOUNT ON to your example proc so it looks like this...[code]CREATE PROC usp_temp_InsSampleASBEGIN[font="Arial Black"]SET NOCOUNT ON[/font]CREATE TABLE #MyTable(UID INT,NAME VARCHAR(100))INSERT INTO #MyTable SELECT 1,'Name1' INSERT INTO #MyTable SELECT 2,'Name2' INSERT INTO #MyTable SELECT 3,'Name3' SELECT * FROM #MyTableDROP TABLE #MyTableEND[/code]... then, the code will work just fine.There's also a way to make it so the server can be (LOCAL) for everything, but I forget how to do that... I think you have to make a "loop back" linked server called (Local), but I just don't remember. My appologies.Last but not least... you don't need the "DataBase=" part of the code if you use a 3 part naming convention on the name of the stored procedure to be executed.Lemme know if you have any questions on this.Fri, 09 Jan 2009 12:22:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic633198-145-1.aspxI'm not sure I follow you 100% here. Can you give an example of how you want to call the SP and what you expect to be able to do with the returned values?Fri, 09 Jan 2009 10:36:09 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic633198-145-1.aspxHi,Look into the query below.[i][b]CREATE TABLE #MyTable(UID INT,NAME VARCHAR(100))INSERT INTO #MyTable SELECT 1,'Name1' INSERT INTO #MyTable SELECT 2,'Name2' INSERT INTO #MyTable SELECT 3,'Name3' SELECT * INTO #TEMP FROM #MyTableDROP TABLE #TEMP DROP TABLE #MyTable[/b][/i]Using the above query i can create and insert a table #TEMP in a single statement.Now check procedure below:[i][b]CREATE PROC usp_temp_InsSampleASBEGINCREATE TABLE #MyTable(UID INT,NAME VARCHAR(100))INSERT INTO #MyTable SELECT 1,'Name1' INSERT INTO #MyTable SELECT 2,'Name2' INSERT INTO #MyTable SELECT 3,'Name3' SELECT * FROM #MyTableDROP TABLE #MyTableEND[/b][/i]I need a query that creates and inserts the records executed by the proc into a table #TEMP.In Simple Words, When i execute a proc i should get the result in a table as #Temp. This helps me to have a table with the number of columns the proc executes.Thanks.Fri, 09 Jan 2009 02:54:44 GMTjchandramouli