Does SQL Server Exist

Hi All,

I am trying to write an application to install a product that my company has developed. As part of the install I want to perform some validation ie is IIS running is SQL 2000 running etc before installation. The application is in VB .net can anyone tell me if it is possible to check if SQL Server 2000 exists and is running?

Thanks in advance for your help.

Cheers,

SamBefore using SQL DMO or something to check the service state of SQL Server, why not issuing a simple command like "Select 1" to check whether the server is up or not. Of you want to check for the presence of a database you might issue the command:
Select 1 from master..sysdatabases Where name = 'YourDBNametoCheckfor'

HTH, Jens Suessmeyer|||i would first check if network is available, after check if the server (not sql database server) is on-line... and then check if database server is installed... i'm using this for now till i find something better
i open connection to the server for default database and if its not there (exception) then the sql server is either not runing or it's service is stoped...
hope this helps a bit.... its not perfect but it helps for now...
Private Function CheckServer() As Boolean
Try
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
MyCnn.Close()
Return True
Catch ex As SqlException
Return False
Catch ex As Exception
Return False
End Try
End Function

and here i check if database with that name exists
Private Function CheckDb() As Boolean
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
With MyCmd
.Connection = MyCnn
'this line of code well sql query i found somewhere on this forums
.CommandText = "Select 1 from master..sysdatabases Where name = '" & m_dbName & "'"
.CommandType = CommandType.Text
End With
Try
Select Case (MyCmd.ExecuteScalar).ToString
Case 1
Return True
MyCnn.Close()
Exit Function
Case Else
Return False
MyCnn.Close()
Exit Function
End Select
Catch ex As NullReferenceException
Return False
MyCnn.Close()
Exit Function
End Try
MyCnn.Close()
End Function

Does SQL Server Exist

Hi All,

I am trying to write an application to install a product that my company has developed. As part of the install I want to perform some validation ie is IIS running is SQL 2000 running etc before installation. The application is in VB .net can anyone tell me if it is possible to check if SQL Server 2000 exists and is running?

Thanks in advance for your help.

Cheers,

SamBefore using SQL DMO or something to check the service state of SQL Server, why not issuing a simple command like "Select 1" to check whether the server is up or not. Of you want to check for the presence of a database you might issue the command:
Select 1 from master..sysdatabases Where name = 'YourDBNametoCheckfor'

HTH, Jens Suessmeyer|||i would first check if network is available, after check if the server (not sql database server) is on-line... and then check if database server is installed... i'm using this for now till i find something better
i open connection to the server for default database and if its not there (exception) then the sql server is either not runing or it's service is stoped...
hope this helps a bit.... its not perfect but it helps for now...
Private Function CheckServer() As Boolean
Try
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
MyCnn.Close()
Return True
Catch ex As SqlException
Return False
Catch ex As Exception
Return False
End Try
End Function

and here i check if database with that name exists
Private Function CheckDb() As Boolean
Dim MyCnn As New SqlClient.SqlConnection(m_CnnString)
Dim MyCmd As New SqlClient.SqlCommand
MyCnn.Open()
With MyCmd
.Connection = MyCnn
'this line of code well sql query i found somewhere on this forums
.CommandText = "Select 1 from master..sysdatabases Where name = '" & m_dbName & "'"
.CommandType = CommandType.Text
End With
Try
Select Case (MyCmd.ExecuteScalar).ToString
Case 1
Return True
MyCnn.Close()
Exit Function
Case Else
Return False
MyCnn.Close()
Exit Function
End Select
Catch ex As NullReferenceException
Return False
MyCnn.Close()
Exit Function
End Try
MyCnn.Close()
End Function

Does RAISERROR store values for @@ERROR?

I tried to write some test code that looks like:
DECLARE @.ErrorValue int
sp_addmessage 50001,10,'This is a test error message'
RAISERROR (50001,10,1)
SELECT @.ErrorValue = @.@.ERROR
If @.ErrorValue <> 0 ...etc.
I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
variable immediately after the line that raised the error. Instead, a zero
value was returned. Why?
If I try to replace that line with:
RAISERROR (-100,10,1)
...which generates a system error 2758 ("RAISERROR could not locate entry
for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
the code is working.
Does RAISERROR not set the value of @.@.ERROR?
Thanks!> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
The behavior is described completely in the documentation for raiserror.
You can use a different severity or override the behavior with an option.|||from BOL
"When an error is raised, the error number is placed in the @.@.ERROR
function, which stores the most recently generated error number. @.@.ERROR
is set to 0 by default for messages with a severity from 1 through 10."
Your second RAISERROR itself raised an error, which is what was returned
in @.@.ERROR, so that's not really a valid test.
Joel wrote:
> I tried to write some test code that looks like:
> DECLARE @.ErrorValue int
> sp_addmessage 50001,10,'This is a test error message'
> RAISERROR (50001,10,1)
> SELECT @.ErrorValue = @.@.ERROR
> If @.ErrorValue <> 0 ...etc.
> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
> If I try to replace that line with:
> RAISERROR (-100,10,1)
> ...which generates a system error 2758 ("RAISERROR could not locate entry
> for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
> the code is working.
> Does RAISERROR not set the value of @.@.ERROR?
> Thanks!|||Ahh... that worked. I just tried the same code with an error level of 12, an
d
it works fine. Thanks for the help!
"Trey Walpole" wrote:

> from BOL
> "When an error is raised, the error number is placed in the @.@.ERROR
> function, which stores the most recently generated error number. @.@.ERROR
> is set to 0 by default for messages with a severity from 1 through 10."
> Your second RAISERROR itself raised an error, which is what was returned
> in @.@.ERROR, so that's not really a valid test.
> Joel wrote:
>sql

Does anyone know how often the sysjobschedules table is updated?

Hi,
I'm trynig to write some simple code to check that all of my scheduled jobs have completed (successfully or not) by checking that the next run date and time for the active schedules is in the future. However when doing this the sysjobschedules tables
does not seem to update with the next information immediately. Does anynoe know of the frequency of this update or what controls it?
Cheers,
John
You might want to take a look at sp_help_job instead.
Andrew J. Kelly SQL MVP
"John" <j.edwards@.ausbulk.com.au> wrote in message
news:413AE3B6-4491-42AE-9412-4025D74764A6@.microsoft.com...
> Hi,
> I'm trynig to write some simple code to check that all of my scheduled
jobs have completed (successfully or not) by checking that the next run date
and time for the active schedules is in the future. However when doing this
the sysjobschedules tables does not seem to update with the next information
immediately. Does anynoe know of the frequency of this update or what
controls it?
> Cheers,
> John

Does anyone know how often the sysjobschedules table is updated?

Hi,
I'm trynig to write some simple code to check that all of my scheduled jobs
have completed (successfully or not) by checking that the next run date and
time for the active schedules is in the future. However when doing this the
sysjobschedules tables
does not seem to update with the next information immediately. Does anynoe
know of the frequency of this update or what controls it?
Cheers,
JohnYou might want to take a look at sp_help_job instead.
Andrew J. Kelly SQL MVP
"John" <j.edwards@.ausbulk.com.au> wrote in message
news:413AE3B6-4491-42AE-9412-4025D74764A6@.microsoft.com...
> Hi,
> I'm trynig to write some simple code to check that all of my scheduled
jobs have completed (successfully or not) by checking that the next run date
and time for the active schedules is in the future. However when doing this
the sysjobschedules tables does not seem to update with the next information
immediately. Does anynoe know of the frequency of this update or what
controls it?
> Cheers,
> John

Does anyone know how often the sysjobschedules table is updated?

Hi
I'm trynig to write some simple code to check that all of my scheduled jobs have completed (successfully or not) by checking that the next run date and time for the active schedules is in the future. However when doing this the sysjobschedules tables does not seem to update with the next information immediately. Does anynoe know of the frequency of this update or what controls it
Cheers
JohnYou might want to take a look at sp_help_job instead.
--
Andrew J. Kelly SQL MVP
"John" <j.edwards@.ausbulk.com.au> wrote in message
news:413AE3B6-4491-42AE-9412-4025D74764A6@.microsoft.com...
> Hi,
> I'm trynig to write some simple code to check that all of my scheduled
jobs have completed (successfully or not) by checking that the next run date
and time for the active schedules is in the future. However when doing this
the sysjobschedules tables does not seem to update with the next information
immediately. Does anynoe know of the frequency of this update or what
controls it?
> Cheers,
> John

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.

Documented t-sql for file space ?

Hi There

I would like to write sql to check that space used by any given data file in SS2000.

sp_spaceused only returns the total space used by the DB or object in the DB, not for a specific data file.

DBCC SHOWFILESTATS does give me this information , but it is not documented in BOL, i would prefer not to use an undocumented command to ensure future use in later versions. Is DBCC SHOWFILESTATS an undocumented T-SQL command ? If so what documented t-sql command will provide me with this information ?

I could query system table's and work this out , but as mentioned this may not not work in future versions of SS.

Thanx

Dont worry, seems FILEPROPERTY function will do this for me , thanx