|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Checking for database existence on a SQL server using ADOWhat is the best way to check for whether a database exists on a server
using ADO. I am thinking that if one connects to the server, and the connection fails, the Errors object should have some sort of information to point out to me that the database does not exist. Does anybody have information about this or any other way to check if a database exists on a SQL server using ADO ? May be the db exists but you do not have access to it.
AMB Show quote "Edward Diener" wrote: > What is the best way to check for whether a database exists on a server > using ADO. I am thinking that if one connects to the server, and the > connection fails, the Errors object should have some sort of information > to point out to me that the database does not exist. Does anybody have > information about this or any other way to check if a database exists on > a SQL server using ADO ? > Try executing sp_databases and checking if the db is there.
AMB Show quote "Alejandro Mesa" wrote: > May be the db exists but you do not have access to it. > > > AMB > > "Edward Diener" wrote: > > > What is the best way to check for whether a database exists on a server > > using ADO. I am thinking that if one connects to the server, and the > > connection fails, the Errors object should have some sort of information > > to point out to me that the database does not exist. Does anybody have > > information about this or any other way to check if a database exists on > > a SQL server using ADO ? > > In ASP:
assume Application("strCon") contains a database connection string Function CheckDBConnection Dim oConn on error resume next Set oConn = Server.CreateObject("ADODB.Connection") oConn.ConnectionString = Application("strCon") oConn.Open if err.number = 0 then CheckDBConnection = "success" else CheckDBConnection = "failure. Error " & err.number & ": " & err.description end if oConn.Close Set oConn = Nothing err.Clear End Function This is fast too, especially if database is there, or if it is not. Enjoy Alex Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:DA5526FF-EB8D-45D8-B6E0-00489CAFBE15@microsoft.com... > Try executing sp_databases and checking if the db is there. > > > AMB > > "Alejandro Mesa" wrote: > > > May be the db exists but you do not have access to it. > > > > > > AMB > > > > "Edward Diener" wrote: > > > > > What is the best way to check for whether a database exists on a server > > > using ADO. I am thinking that if one connects to the server, and the > > > connection fails, the Errors object should have some sort of information > > > to point out to me that the database does not exist. Does anybody have > > > information about this or any other way to check if a database exists on > > > a SQL server using ADO ? > > > A_X_L_X wrote:
Show quote > In ASP: This tells me that the connection fails, not that the database does not > assume Application("strCon") contains a database connection string > > > Function CheckDBConnection > Dim oConn > on error resume next > Set oConn = Server.CreateObject("ADODB.Connection") > oConn.ConnectionString = Application("strCon") > oConn.Open > if err.number = 0 then > CheckDBConnection = "success" > else > CheckDBConnection = "failure. Error " & err.number & ": " & > err.description > end if > oConn.Close > Set oConn = Nothing > err.Clear > End Function > > This is fast too, especially if database is there, or if it is not. exist. The connection may fail for other reasons, such as an invalid user ID or password. I need to track the failure to the fact that the database itself does not exist on the server. So far I have not found any information in the SQL Server docs about the error number returned which says that a database to which I am trying to connect does not exist. If you have any idea where such information exists in the documentation, I would be glad to know about it. also, from the code I posted, you can decrease a connection timeout for it
to fail faster if DB is not there. Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:DA5526FF-EB8D-45D8-B6E0-00489CAFBE15@microsoft.com... > Try executing sp_databases and checking if the db is there. > > > AMB > > "Alejandro Mesa" wrote: > > > May be the db exists but you do not have access to it. > > > > > > AMB > > > > "Edward Diener" wrote: > > > > > What is the best way to check for whether a database exists on a server > > > using ADO. I am thinking that if one connects to the server, and the > > > connection fails, the Errors object should have some sort of information > > > to point out to me that the database does not exist. Does anybody have > > > information about this or any other way to check if a database exists on > > > a SQL server using ADO ? > > > Alejandro Mesa wrote:
> Try executing sp_databases and checking if the db is there. Thanks, I will look at this stored procedure. I assume it returns the databases on a server. Show quote > > > AMB > > "Alejandro Mesa" wrote: > > >>May be the db exists but you do not have access to it. >> >> >>AMB >> >>"Edward Diener" wrote: >> >> >>>What is the best way to check for whether a database exists on a server >>>using ADO. I am thinking that if one connects to the server, and the >>>connection fails, the Errors object should have some sort of information >>>to point out to me that the database does not exist. Does anybody have >>>information about this or any other way to check if a database exists on >>>a SQL server using ADO ? >>> Alejandro Mesa wrote:
> Try executing sp_databases and checking if the db is there. Yes, this should work fine, thank you ! The related problem is if I can connect to the server without specifying a database. Is this possible ? In the case of running the stored procedure you specify, sp_databases, what database do I connect to, or can I just leave it out of my connection string ?  |
|||||||||||||||||||||||
Other interesting topics