Check database access (SQL Server 2005 / 2008)

0
votes

What is the best way to check database access from remote machine.

Here is the situation:

  • SQL Server 2005 (or 2008) is running on MACHINE1 (Windows 2003 or 2008). There is a database called ENTERPRISEDB.

  • Another server, called MACHINE2 (Windows 2003 or 2008). It has ODBC connection to MACHINE1.

Now, what is the best and simplest SQL query to check if MACHINE2 can connect and access the ENTERPRISEDB in MACHINE1?

Thanks.

2
sql-serverdatabase-connectionconnectivity
It's important to accept answers!. You have asked 5 questions and cast no votes and accepted no answers. Make sure you accept the correct answer to your question so others know what works. – JNK
Silly me, I did not know how to accept answer! I tried to click the make useful, but got warning. Thanks! I have accepted the answer. – Kawaii-Hachii

2 Answers

0
votes

If your ODBC connection is to database ENTERPRISEDB then you could run

SELECT top 1 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME='ENTERPRISEDB'

which will return "1" if the database exists or nothing if it doesn't.

If you need a boolean/int return then try

SELECT case when COUNT(*) >0 then 1 else 0 end 
FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME='ENTERPRISEDB'

which will return "1" if it exists, "0" if it doesn't.

0
votes

I always use 

Select @@servername, @@SERVICENAME

Returns Server Name, Instance Name to check basic server connection. To check database connectivity itself is correct you can't beat sys.objects. 

select DB_NAME(), * from sys.objects

Returns a table with current database name as the first column and a list of object names as the general data list.