Post reply

Distributed Query: using machine name in a variable in SQL query

  • January 13, 2005 at 11:13 pm

    #102351

    Distributed Linked Query: Passing and using machine name in a variable in a Stored Procedure
     

    What is the correct syntax to use the machine name in a stored procedure that is passed to it as a parameter?
     
    Example:

    I have 3 machines, Alpha, Beta, and Gamma. SQL server 2000 are properly installed, linked,

    and tested in this 3-machine based distributed db, distributed query system.

    Following Test1 stored procedure works just fine when executed remotely (from any of the 3 linked server).

     
    CREATE PROCEDURE Test1

    AS

    select * from Alpha.Northwind.dbo.employees

    GO

     
    So far so goo.
     
    My application doesn't know ahead of time which machine to use in the above SELECT query.

    Therefore, my application dynamically, during run time, figures out what machine need to be used.

     
    Hence:
     
    CREATE PROCEDURE Test2

    @ThisMachine varchar(10)

    AS

    select * from @ThisMachine.Northwind.dbo.employees

    GO

     
    Test2 is giving me compilation error.
     
    I also tried putting square bracket around @ThisMachine but it did not help.
     
    I will greatly appreciate if you could tell me the correct syntax for using machine name in a variable.
     
    Similarly, I would also like to know how can one use database name, table name,

    column name in a variable in a SQL statement.

     
    Thanks.
     
    Munzer Haque

    mhaque4@comcast.net

  • January 14, 2005 at 5:37 am

    #537444

    Try:

    CREATE PROCEDURE Test2

    @ThisMachine varchar(10)

    AS

    EXEC ('select * from ' + @ThisMachine+ '.Northwind.dbo.employees')

    GO

  • January 14, 2005 at 1:14 pm

    #537533

    It gave me the following run time error. I also tried SET ANSI_NULLS ON, SET ANSI_WARNINGS On inside the stored procedure. Still it did not work.

    Now the question is where do I set these two options ON and what is the proper syntax.

    Server: Msg 7405, Level 16, State 1, Line 1

    Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

  • January 14, 2005 at 1:18 pm

    #537534

    Are the linked servers SQL Servers?

  • January 14, 2005 at 1:36 pm

    #537535

    Yes. All Lined servers are SQL server 2000 and tested works fine in distributed quiery enviornment, except that when try to pass parameter.

  • January 14, 2005 at 1:43 pm

    #537537

    Now try this:

    CREATE PROCEDURE Test2

    @ThisMachine varchar(10)

    AS

    DECLARE @qry AS nvarchar(200)

    SELECT @qry = 'select * from ' + @ThisMachine+ '.master.dbo.sysdatabases'

    EXEC sp_executeSQL @qry

    GO

     

  • January 14, 2005 at 1:56 pm

    #537539

    same error message.

    I check in SQL server book online about ANSI settings. It says for all distributed query ANSI_NULLS and ASNI_WARNINGS must be set to ON. Default setting is OFF.

    What I do not know is where to set this setting and what is the proper syntax.

     

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

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