Subject: (1) New Update

From: View Membership Settings <[email-masked]>
Date: Tue, 24 Jun 2014 07:57:12 -0700

Dear Atompub-archive,

It is my pleasure to inform you that you qualify for 2014 membership to the Who's Who Network of Executives and Professionals, the largest professional association for business executives and professionals in the United States.

Inclusion in the Who's Who Network is a privilege shared by thousands of executives and professionals throughout the world each year.

Confirm Your Bio Here -> http://www.humusbytrack.com/bio/created/june24.index

On behalf of the Who's Who Network of Executives and Professionals, welcome to the membership.

Sincerely,
JT Richards
Vice President
Who's Who Network, Research Division

To stop further alerts. Write to Current Update Alerts at  1644 Doral Drive_Brookings, SD 5 7 0 0 6 or visit 
http://www.humusbytrack.com/keke/fk3kf.eo4old

This is the right way to do it. OPENROWSET is pretty much the only way to treat the results of a stored procedure as a table expression.   Rob Farley Aug 5 '09 at 13:24
12	  	
This seems a bit cumbersome just to insert into a table. A lot of configuring to do. Also when I tried it I got "Msg 7357, Level 16, State 2, Line 1 Cannot process the object "EXEC GetPartyAnalysisData 146". 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." So you need to set a linked server...   Ferdeen Aug 10 '09 at 12:18
5	  	
You don't need a linked server, but you would need to get the connection string right...and also, specify the full path to the stored procedure including the database name and the sp's owner.   CodeByMoonlight Aug 11 '09 at 12:30
  	  	
Indeed, that error you're getting when you try this is what I get when I try to access an object that isn't in the same database as the default db for the OPENROWSET connection (which is probably 'master').   CodeByMoonlight Aug 11 '09 at 12:37
  	  	
Thanks. I'll give another try. Looks like this is the bounty winner. Thanks for all your help.   Ferdeen Aug 11 '09 at 13:08
  	  	
On 2nd thought, this solution worked better for me because setting server level settings with sp_serveroption is verboten around here.   jcollum Dec 23 '09 at 18:09I gotta say this solution is brilliant, and works really well. 
  	
 A table-valued function is an appropriate solution. We should make note of the minor drawbacks: the table-valued function is an extra database object, and it may be necessary to grant privileges on it.   spencer7593 Aug 11 '10 at 18:29
  	  	
Love the solution. One minor snag I hit, is that my table cant have order by where as it could have it in the stored procedure. Owh well, i'll sort it out     	One more snag - "Cannot access temporary tables from within a function"  he original question is how do we create a temp table w/ the results of the sp. This is a good pattern, but doesnt address this question   greg Apr 16 '13 at 20:16
1	  	
greg, the first line in my answer states "This is an answer to a slightly modified version of your question." Your comment is redundant.   Christian Loris May 28 '13 at 12:51
  	  	
eeeeew! a reference to the same server? nasty. definitely more of a hack than having to manually create the temp table 
  	  	
-it is cool, but it does not work if the stored procedure declare a temp table inside, I've tried with lots of stored procedure and everything is ok, but if I use a #tablename inside the stored I got a invalid object s not work for me, but I liked the spirit of it so I modified a few things : made it a stored procedure changed the way the foreign keys are populated and recreated the original script truncates all referenced tables, this can be wrong when the referenced table cannot be truncated because it also has foreign key references. In this version only the table specified as parameter will be truncated, all referenced table should be truncated manually befo
What happens here if the SP returns more than one resultset?   Alpha Mar 13 '12 at 20:57
2	  	
@Alkampfer put SET FMTONLY OFF; in your OPENQUERY call before the actual SP execution, that will make it work with temp tables. Just be aware that the SP will be run twice.   ErikE Jul 30 '12 at 18:26
6	  	
i agree that this is a hack and should probably be avoided unless your back is against the wall. Changing the sp to a function is probably a better angle to take. IMHO.   greg Apr 16 '13 at 20:20