Post reply

SELECT into from linked server stored procedure

  • March 29, 2011 at 2:48 pm

    #233934

    Hi,

    I'd like to do a SELECT INTO from the result of a StoredProcedure located on a secured remote server on a VPN network (no Trusted Connection).

    The following query works:

    EXEC [192.168.192.3].Database.dbo.StoredProc_sp '1313-21897'

    I tried this query:

    SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=[192.168.192.3];User Id=Username;Password=Password', 'EXEC StoredProc_sp ''1313-21897''')

    it gives the error:

    OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid authorization specification".

    OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Invalid connection string attribute".

    Msg 7399, Level 16, State 1, Line 16

    The OLE DB provider "SQLNCLI10" for linked server "(null)" reported an error. Authentication failed.

    Msg 7303, Level 16, State 1, Line 16

    Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "(null)".

    Linked server is registered with the same Username/Password stated in this query.

    Any help would be appreciate.

    thanks a lot for your time and help

  • March 29, 2011 at 2:53 pm

    #1305155

    If it is a linked server then you should be able to use the Four part naming convention

    select * from [server] . [database] . [schema] . [object]

    sorry, i just read that it was from a stored proc not a table...

  • March 29, 2011 at 5:16 pm

    #1305206

    Your connection string was slightly flawed. Try this:

    IF OBJECT_ID(N'tempdb..#MyTable') > 0

    DROP TABLE #MyTable ;

    /* notice:

    1. use SQLNCLI10 as the provider if you're accessing SQL 2008

    2. no square brackets around the server name

    3. Specify DB name in connection string (optional depending on user's default DB)

    4. different connection string property names for user and password

    */

    SELECT *

    INTO #MyTable

    FROM OPENROWSET('SQLNCLI',

    'Server=192.168.192.3;Database=DATABASE_NAME;Uid=LOGIN_NAME;Pwd=PASSWORD;',

    'EXEC StoredProc_sp ''1313-21897''') ;

    SELECT *

    FROM #MyTable ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 29, 2011 at 6:14 pm

    #1305216

    Ah, the old quotes instead of doubled apostrophes... nice catch opc.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 30, 2011 at 9:46 am

    #1305519

    thanks a lot, it works perfectly!!!

Viewing 5 posts - 1 through 5 (of 5 total)

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