Professional Web Applications Themes

Can't use parameters in OPENDATASOURCE - Microsoft SQL / MS SQL Server

Hi! I can't seem to be able to use parameters in OPENDATASOURCE. ----------------------------------------------- A non-working example: ----------------------------------------------- SELECT external_datasource = 'THESERVER' INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=' + external_datasource + ';User ID=sa;Password=mypwd').TheDB.dbo.tblTrenddata SELECT * FROM tblTransport ----------------------------------------------- A working example: ----------------------------------------------- INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=THESERVER;User ID=sa;Password=mypwd').TheDB.dbo.tblTrenddata SELECT * FROM tblTransport Does anyone know how this can be done? -- Best regards, Alexander Ris°y IT Team Nebb Engineering...

  1. #1

    Default Can't use parameters in OPENDATASOURCE

    Hi!
    I can't seem to be able to use parameters in OPENDATASOURCE.

    -----------------------------------------------
    A non-working example:
    -----------------------------------------------
    SELECT external_datasource = 'THESERVER'
    INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=' + external_datasource
    + ';User ID=sa;Password=mypwd').TheDB.dbo.tblTrenddata
    SELECT * FROM tblTransport

    -----------------------------------------------
    A working example:
    -----------------------------------------------
    INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=THESERVER;User
    ID=sa;Password=mypwd').TheDB.dbo.tblTrenddata
    SELECT * FROM tblTransport


    Does anyone know how this can be done?

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering


    Alexander Guest

  2. #2

    Default Re: Can't use parameters in OPENDATASOURCE

    Use dynamic sql
    Ex:
    (untested)
    SELECT external_datasource = 'THESERVER'
    exec('INSERT INTO OPENDATASOURCE(''SQLOLEDB'',''Data Source=''' +
    external_datasource+ ';User
    ID=sa;Password=mypwd'').TheDB.dbo.tblTrenddata')
    SELECT * FROM tblTransport

    dynamically create the above string and execute is using EXEC.

    --
    -Vishal
    "Alexander Ris°y" <no> wrote in message
    news:KbRQa.13275$e.nsc.no... 
    external_datasource 


    Vishal Guest

  3. #3

    Default Re: Can't use parameters in OPENDATASOURCE

    Thanks, that worked better!

    But I'm getting the following error message for a similar situation:

    -----------------------------------------------
    Error message:
    -----------------------------------------------
    Msg 170, Level 15, State 1, Server PC2002-03, Line 1
    Line 1: Incorrect syntax near '='.
    -----------------------------------------------

    It is the very last equals it is referring to. The on in 'WHERE ID ='. The
    funny thing is that if I remove 'WHERE ID = etc.' the external tblCurrent
    updates with all data, but it does not accept a WHERE clause?

    -----------------------------------------------
    Procedure copy_current
    -----------------------------------------------
    SELECT external_datasource = Val FROM tblSysvars WHERE ID = 'SQL_MAIN'

    -- Copy all rows in local tblCurrent to the external table tblCurrent on
    external_datasource
    EXEC('INSERT INTO OPENDATASOURCE(''SQLOLEDB'',''Data Source=' +
    external_datasource
    + ';User ID=sa;Password=mypwd'').OvnDB.dbo.tblCurrent ' + 'SELECT * FROM
    tblCurrent WHERE ID = ' + currentID)
    -----------------------------------------------

    -----------------------------------------------
    Purpose
    -----------------------------------------------
    This is eventually intended in a trigger on INSERT of the local tblCurrent
    table to append the new data (the inserted row) to the external tblCurrent,
    to keep it updated.
    -----------------------------------------------


    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering
    "Vishal Parkar" <com> skrev i melding
    news:phx.gbl... 
    > >
    > >[/ref]
    >
    >[/ref]


    Alexander Guest

  4. #4

    Default Re: Can't use parameters in OPENDATASOURCE

    After a lot of trial an error I found out that it did not like the ID in
    "WHERE ID = etc." Even if no Query Debugging tools complained, Access nor
    EntprMangr. I've now changed it to CurrentID instead and it works fine.

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering

    "Alexander Ris°y" <no> skrev i melding
    news:W8WQa.13365$e.nsc.no... 
    tblCurrent, [/ref][/ref]
    returned: 
    > >
    > >[/ref]
    >
    >[/ref]


    Alexander Guest

  5. #5

    Default Re: Can't use parameters in OPENDATASOURCE

    As a matter of fact id did not work at all.

    But if I write "WHERE currentID = 5" instead of "WHERE currentID = ' +
    currentID" it works perfectly(!) Any suggestions?

    --
    Best regards,

    Alexander Ris°y
    IT Team
    Nebb Engineering

    "Alexander Ris°y" <no> skrev i melding
    news:GC7Ra.6485$e.nsc.no... [/ref]
    The [/ref]
    tblCurrent [/ref]
    tblCurrent 
    > tblCurrent, [/ref]
    > returned: [/ref][/ref]
    Source=THESERVER;User 
    > >
    > >[/ref]
    >
    >[/ref]


    Alexander Guest

Similar Threads

  1. create parameters without creating parameters
    By dan marks in forum ASP Components
    Replies: 0
    Last Post: October 30th, 01:51 AM
  2. parameters
    By Kieran in forum PHP Development
    Replies: 2
    Last Post: September 4th, 02:53 PM
  3. Replies: 0
    Last Post: August 17th, 08:24 PM
  4. #24962 [Opn]: Incompatible variant type in call to MailMerge.OpenDataSource
    By David dot Richards at Tools dot co dot uk in forum PHP Development
    Replies: 0
    Last Post: August 12th, 12:57 PM
  5. #24962 [Com]: Incompatible variant type in call to MailMerge.OpenDataSource
    By richard dot quadling at carval dot co dot uk in forum PHP Development
    Replies: 0
    Last Post: August 12th, 12:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139