none
Check if Server , DB and SP exists RRS feed

  • Question

  •  

    Hai
    i am using VB.NET 2003 windows Application... In that i want to connect to SQL Server DataBase dependng on the Server Name AND DataBase name thats i enter in the textbox ... But i want to check if this server and database exists ... Also i want to execute a stored procedure.. That SP Name is also entered in the text box .. before executing the SP i want to check if that SP exists ...How can i check these using VB.net code ? How shud i do the coding ?
    If anybody knows plz do help me .. Any Help Appreciated ..

    Tuesday, February 26, 2008 11:21 AM

All replies

  • In your connection string you can try to connect to the server and database, you will get an error if that server or database does not exist.  To check if the sproc exists you can just run a query like this:

     

    Code Snippet

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'SPROC_NAME_HERE') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
        -- Do something like return true here
    GO

     

     

    Tuesday, February 26, 2008 12:01 PM
  • You can use the following query to check database existance:
    Code Snippet

    if exists (select * from sys.databases where name = 'Testing')


    This code checks stored procedure existance

    Code Snippet

    if exists (select * from sys.objects
    where [object_id] = object_id('Your SP Name') )


    Tuesday, February 26, 2008 5:12 PM
  •  

    The query "select name from sysobjects where xtype='p' "
    will select the names of all procedures in a particular database
    The query "select name from sysobjects where xtype='U' "
    will select the names of all user tables in a particular database

    "select name from sysdtspackages" giv all packages names
    from msdb db
    Thursday, February 28, 2008 7:12 AM
  • In a case of SQL Server 2005 you could use .NET Managed SQL SMO library (which is part of SQL Server SDK). SMO provides set of classes to access objects in SQL Server database and provides collection of databases, stored procedures and other objects. To see if SQL Server is available you could try to instantiate SMO class and if it fails than server does not exists. If it was successful instantiation then you will get all other information from there. Here is link to the reference for the Server class of SMO

     

    http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx

     

    Thursday, February 28, 2008 10:44 AM
    Moderator