Easy Question: Handling Login Exceptions

I have some very simple code that connects to an SQL Server instance using the SMO Server object. I have everything working fine, but I would like to be able to know if the login failed and respond to the error. Does anyone have a snippet that will do this? Do have to catch an exception or handle an event or....

Thanks!

This is how I connect in SMO (VB.Net):

' Connect to the server

Dim srvMgmtServer As Server

srvMgmtServer = New Server("MyServer\MyInstance")

Dim srvConn As ServerConnection

srvConn = srvMgmtServer.ConnectionContext

srvConn.LoginSecure = True

Hope that helps.

|||Ok, that is fine, but what if "MyServer\MyInstance" doesn't exist, or the user supplies invalid credentials? I want to be able to tell the user the login failed and why. There must be an exception thrown somewhere, but I can’t find a simple way to handle an invalid login. Right now I am opening the connection to the server right after I create the instance, and then catching any exceptions, but this seems like a bit of a hack. There has to be a way to do something like this:

try
{

srvMgmtServer = New Server("MyServer\MyInstance")

}
catch(SomeException)
{

//Do Stuff Because The Login Failed

}
|||

Yes, I'm sorry. The method of handling the exception is exactly what you've shown. If you catch(ex), then you can scroll through the InnerException property until you get to the base level. When I just catch the error at the top level the Message property contains ""Failed to connect to server MyServer\MyInstance."

In VB.Net the code looks something like this:

Catch ex As Exception
Console.WriteLine("There has been a VB error. " + ex.Message)
Do While ex.InnerException IsNot (Nothing)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
End Try

Hope that helps.

Easy Question: Handling Login Exceptions

I have some very simple code that connects to an SQL Server instance using the SMO Server object. I have everything working fine, but I would like to be able to know if the login failed and respond to the error. Does anyone have a snippet that will do this? Do have to catch an exception or handle an event or....

Thanks!

This is how I connect in SMO (VB.Net):

' Connect to the server

Dim srvMgmtServer As Server

srvMgmtServer = New Server("MyServer\MyInstance")

Dim srvConn As ServerConnection

srvConn = srvMgmtServer.ConnectionContext

srvConn.LoginSecure = True

Hope that helps.

|||Ok, that is fine, but what if "MyServer\MyInstance" doesn't exist, or the user supplies invalid credentials? I want to be able to tell the user the login failed and why. There must be an exception thrown somewhere, but I can’t find a simple way to handle an invalid login. Right now I am opening the connection to the server right after I create the instance, and then catching any exceptions, but this seems like a bit of a hack. There has to be a way to do something like this:

try
{

srvMgmtServer = New Server("MyServer\MyInstance")

}
catch(SomeException)
{

//Do Stuff Because The Login Failed

}
|||

Yes, I'm sorry. The method of handling the exception is exactly what you've shown. If you catch(ex), then you can scroll through the InnerException property until you get to the base level. When I just catch the error at the top level the Message property contains ""Failed to connect to server MyServer\MyInstance."

In VB.Net the code looks something like this:

Catch ex As Exception
Console.WriteLine("There has been a VB error. " + ex.Message)
Do While ex.InnerException IsNot (Nothing)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
End Try

Hope that helps.

sql

dynamically switching databases in a script

I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.

I need the correct syntax, the following won't work:

DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname

OPEN DBS

FETCH NEXT
FROM DBS
INTO
@.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN

USE @.dbname

The last line - the "USE" statement - is invalid. The following for example works:

USE master

But when supplied a declared variable a syntax error results for the use command because it expects an identifier.

So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?

Thanks for your time.

This is not possible right now since you cannot use variables in lot of statements in place of options or identifiers. You can use dynamic SQL though and below is the easiest way to do it:

declare @.sp nvarchar(500)

...

while .....

begin

-- use dbo.sp_executesql for SQL Server 2000

set @.sp = quotename(@.dbname) + N'sys.sp_executesql'

exec @.sp N'your sql string that needs to execute against db'

...

end

|||although this allowed the "use .." statement to run, it didn't have the effect I need. The remainder of the script was still running in the context of the original database.|||

So here's what I really need:

I need a way to switch the context of a script from one database to another, where I do not know the name of the databases beforehand (so they can't be hard-coded).

|||What I suggested will work provided the code that you want to run within context of the database is executed dynamically. Another approach is to pre-process the script file based on the database name and then run it. With SQL Server 2005, you can do this using SQLCMD pre-processing features.

dynamic table names in stored procedure...

Hello all,

Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name?

Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...

Cheers,
Justinyou will have to create a dynamic query something on these lines

declare @.table nvarchar(20)
set @.table = 'Customers'
declare @.sql nvarchar(100)
set @.sql = 'select * from ' + @.table
exec sp_executesql @.sql

i used the northwind database as an example for this and this example works on the customers table...

so if your column names will not make a difference then you will need to create a dynamic query on these lines and execute it|||http://www.sommarskog.se/dynamic_sql.html|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.|||SQL Injection - look it up or better still read Jesse's link.

If you must do this then I would recommend at a minimum that you only allow acceptable values for @.tablename rather than strip out any naughty looking code. One way to verify is to use a paramaterised query that checks that there is a table whose name equals the value of @.tablename and only execute the final string if there is.

Paranoia ... a man after my own heart.