Post reply

Query to get Instance names in SQL 2000

  • April 6, 2011 at 8:32 am

    #234403

    Hi Any Query to get instances running on a machine for SQL 2000

    I was using the following Script but its not working for SQL 2000 can some one guide me here thanks in advance

    CREATE TABLE #GetInstances

    (Value VARCHAR(30), InstanceNames VARCHAR(20), Data VARCHAR(30))

    INSERT INTO #GetInstances

    EXECUTE xp_regread

    @rootkey = 'HKEY_LOCAL_MACHINE',

    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

    @value_name = 'InstalledInstances'

    SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(20)) AS ServerName,

    CASE

    WHEN InstanceNames = 'MSSQLSERVER'

    THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(20))

    ELSE CAST(CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(20))+'\'+ InstanceNames AS VARCHAR(20))

    END AS InstanceName from #GetInstances

  • April 6, 2011 at 10:23 am

    #1308108

    RamSteve (4/6/2011)

    Hi Any Query to get instances running on a machine for SQL 2000

    I was using the following Script but its not working for SQL 2000 can some one guide me here thanks in advance

    SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(20)) AS ServerName,

    CASE

    WHEN InstanceNames = 'MSSQLSERVER'

    THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(20))

    ELSE CAST(CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(20))+'\'+ InstanceNames AS VARCHAR(20))

    END AS InstanceName from #GetInstances

    'ComputerNamePhysicalNetBIOS' is not supported in SQL 2000.

    Try using machinename instead.

    http://msdn.microsoft.com/en-us/library/aa259183(v=sql.80).aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • April 6, 2011 at 12:23 pm

    #1308199

    Thank you its working 🙂

  • April 6, 2011 at 12:27 pm

    #1308202

    Excellent

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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