Dynamically create table when executing a stored procedure

  • >>>>

    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

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

  • 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

  • >>>

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

    <<<

    That did it.  Thanks.

  • You are welcome!


    * Noel

  • Hi ,

    Thanx  to neold it worked exactly what i needed.

    Thanx to Remi,johnson and everybody on this post.

     

    from

    killer

  • HTH.

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

  • 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

  • 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

  • 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

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

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