Dynamically create table when executing a stored procedure

Question

Post reply

Dynamically create table when executing a stored procedure

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

CJohnson-232084 Default port Points: 1492 More actions · Answer 1

>>>>

Select * into #T1

from Openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=YOURSERVER','exec sp_who') dt

select * from #T1

drop table #T1

<<<<<

Noeld, I must be missing something with this code. When I run the query it executes sp_who on the server to which QA is connected, regardless of the server name I specify in SOURCE=

I took a look in BOL but don't see why this doesn't work. Any thoughts?

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Do you have access to those servers?

Did you try using the sa account instead of trusted?

Noeld I'm getting close... I was only missing the data source this time .

noeld SSC Guru Points: 96590 More actions · Answer 3

That is correct!! (The code was using Windows integrated security) The problem is that if you want to be able to switch "servers" then you need to:

1. Setup account delegation with Kerberos (which not everyone can)

2. Use SQL authentication

so following the second suggestion you can try:

Openrowset('SQLOLEDB','Server=SERVERNAME;UID=xx;PWD=nn;Database=dd','exec sp_who')

* Noel

CJohnson-232084 Default port Points: 1492 More actions · Answer 4

>>>

Openrowset('SQLOLEDB','Server=SERVERNAME;UID=xx;PWD=nn;Database=dd','exec sp_who')

<<<

That did it. Thanks.

noeld SSC Guru Points: 96590 More actions · Answer 5

noeld

SSC Guru

Points: 96590

August 5, 2005 at 8:55 am

#580056

You are welcome!

* Noel

raj-247939 SSCertifiable Points: 5999 More actions · Answer 6

Hi ,

Thanx to neold it worked exactly what i needed.

Thanx to Remi,johnson and everybody on this post.

from

killer

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

August 5, 2005 at 9:59 am

#580096

HTH.

Tim Cullen Ten Centuries Points: 1394 More actions · Answer 8

Have you tried a "select into" statement? Seems to work well. All you have to give is a table name. For subsequent running of a select into statement I would first run a t-sql statement that looks in the sysobjects table of the db you're going to store the temp table into and drop the table if it exists.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

That's select into with a stored proc... not select .

Select *

into #T1

from Openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=YOURSERVER','exec sp_who') dt

select * from #T1

drop table #T1

raj-247939 SSCertifiable Points: 5999 More actions · Answer 10

Hi Tim,

Select * into work with table

i needed with stored procedure . This help me in keeping old data copied from dbcc and extended stored procedure.

Again thanx to neol and Remi

from

Killer

SkAudi SSC-Addicted Points: 410 More actions · Answer 11

Hello,

i have a question on this. Maybe you can help...

Following SQL works fine:

Select

*

from

Openquery(siebeldb, 'Exec sp_help')

But when i try it on sp_helpdb:

Select

*

from

Openquery(siebeldb, 'Exec sp_helpdb')

i get following error:

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "Exec sp_helpdb". The OLE DB provider "SQLNCLI" for linked server "siebeldb" indicates that either the object has no columns or the current user does not have permissions on that object.

Why this? I have the same permission on this object?

any idea on that?

regards

andreas