Results 1 to 5 of 5

Thread: Opendatasource

  1. #1
    Join Date
    Oct 2005
    Posts
    58

    Unanswered: Opendatasource

    What is the syntax for using parameters in an opendatasource query? I tried using the below:

    SELECT top 10 *
    FROM OPENDATASOURCE(
    'SQLOLEDB',
    'Data Source=10.22.10.78;User ID='+@user+';Password='+@pword
    ).Northwind.dbo.Categories

  2. #2
    Join Date
    May 2002
    Posts
    299
    ad-hoc (i.e. openquery/openrowset/opendatasource) only accepts string literals. You have to formulate your entire opendatasource query and execute it.

    e.g.
    set @sql='select *
    from opendatasource(....)'

    exec(@sql)
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Oct 2005
    Posts
    58
    Nothing really happens when I try to exec the following, Is my syntax correct?:

    Declare @user as varchar(25)
    Declare @pword as varchar(25)
    Declare @Cstring as varchar(500)

    Set @cString = 'SELECT top 10 SOPNUMBE, TRXDATE, LNITMSEQ, [DB_ID], AccountId, XTNDPRCE
    FROM OPENDATASOURCE(
    ' + 'SQLOLEDB' + ',' +
    'Data Source=server1;User ID=' + @user + ';Password=' + @pword + '
    ).dw1.dbo.Table1'

    exec (@cstring)

  4. #4
    Join Date
    Oct 2005
    Posts
    58
    Wasn't entering the username and password. Is there a way to make this an Insert statement?

  5. #5
    Join Date
    May 2002
    Posts
    299
    insert into what? like this?

    --set @sql='your select query'

    insert tb
    exec(@sql)
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

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