Post reply

OpenQuery help

  • May 14, 2007 at 5:07 am

    #116805

    Hi

     

    How can i schedule Open Query? I am using SQL SERVER 2000 and running my open query on AS400. i need to store query result in a table on sql server and need to schedule this query ...

    Any help please

     

  • May 14, 2007 at 6:27 am

    #706191

    Have you got your linked server running for an interactive query that you can run?  If so, then you can use the same code to make a job for SQL Server Agent.  Look in Enterprise Manager in SQL 2000 under the "Management" section on the left.  Make a job with a single step that executes T-SQL code - paste your code in there.  Schedule the job as appropriate.  Remember that you may need to set the user under which the job executes appropriately to ensure that it has access to the linked server.

  • May 14, 2007 at 6:54 am

    #706203

    Thanks Ian Yates It's working

    I am working on OpenQuery First time so could you please let me know how to pass parameter to OpenQuery?

    Regards

     

  • May 20, 2007 at 7:21 pm

    #707712

    OpenQuery does not accept parameters/variables.  You'll need to do something ugly with dynamic SQL for it to work.

    EG

    set @query = 'select * from myTable where myColumn=''xyz'' '    (note the double single quotes)
set @fullQuery = 'select * from openQuery(myLinkedServer, ''' + replace(@query, '''', '''''') + ''')'
    print @query
print @fullQuery
    exec(@fullQuery)

    Looks awful

     

  • May 22, 2007 at 7:03 am

    #708047

    Thanks Ian

     

    Realy Helpful..........

  • July 21, 2008 at 7:22 am

    #845747

    Afternoon Ian

    I am trying to do much the same thing .In your query you have

    set @fullQuery = 'select * from openQuery(myLinkedServer, ''' + replace(@query, '''', '''''') + ''')'

    but would I would like to use a variable instead of substituting 'mylinkedsever' for a fixed value ,

    e.g.

    set @fullQuery = 'select * from openQuery(@server , ''' + replace(@query, '''', '''''') + ''')'

    Tried various combinations but to no avail.

    Thanks

    Geoff

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

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