SQL DMO connection problem

Question

Post reply

SQL DMO connection problem

Brian Adams-265363

Mr or Mrs. 500

Points: 509
More actions
February 8, 2006 at 10:03 am

#111383

Greeetings,
   I am having trouble cycling through the SQL Servers on my network. I use a static list of servers that I connect to via SQL DMO, extract database info and then disconnect and set the SQLServer DMO object to Nothing. I then move to the next server and repeat the process.
The problem arises when I cannot connect to one of the SQL Servers (server is down, no permissions etc.). Even though I set the SQLServer DMO object to Nothing, any attempts to connect to subsequen SQL Servers fail. If I remove the server that causes the errror, I am able to connect to the "good" servers successfully. The code is pretty simple. Is this a DMO bug?
on error resume next
.
.
.
while not rstSQLinstances.eof
set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.Connect rstSQLinstances("ServerName") , "sa",rstSQLInstances("saPassword")
if err.number <> 0 then
Call writeerrortolog("Cannot connect to " & rstSQLInstances("ServerName") , Err.Number)
objSQLServer.Disconnect
set objSQLServer = Nothing
else
   '......extact sql server information
   objSQLServer.Disconnect
   set objSQLServer = Nothing
end if
rstSQLinstances.MoveNext                                                              'Move to next SQL Server
wend

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32352 More actions · Answer 1

Does changing the flow help? There is no need to keep objSQLServer open whilst writing your log.

Call writeerrortolog("Cannot connect to " & rstSQLInstances("ServerName") , Err.Number)

objSQLServer.Disconnect

set objSQLServer = Nothing

to

objSQLServer.Disconnect

set objSQLServer = Nothing

Call writeerrortolog("Cannot connect to " & rstSQLInstances("ServerName") , Err.Number)

Brian Adams-265363 Mr or Mrs. 500 Points: 509 More actions · Answer 2

Thanks for the suggestion. I changed the flow but unfortunately still get the same messages. I print out the login info for each server to make sure it is correct and it is.

SERVER= (bad server) sapwd = (bad server pwd) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

SERVER= (good server) sapwd = (good server pwd) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

SERVER= (good server #2 ) sapwd = (good server #2 pwd) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

etc...

If I remove the "bad server' the other servers connect successfully.

Thanks for the suggestion though. You are right. I have no reason to keep the SQLServer object open.

Onward through the fog...

Paul Williams SSCommitted Points: 1930 More actions · Answer 3

Is it failing on the Disconnect property rather than when you set it to Nothing.

This would be because it has failed to connect in the first place.

BTW. Try to avoid harcoding connection parameters in code, particularly the 'sa' password !

Paul R Williams.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Try it this way

set objSQLServer = CreateObject("SQLDMO.SQLServer")

while not rstSQLinstances.eof

on error resume next

objSQLServer.Connect rstSQLinstances("ServerName") , "sa", rstSQLInstances("saPassword")

if err.number <> 0 then

Call writeerrortolog("Cannot connect to " & rstSQLInstances("ServerName") , Err.Number)

err.clear

Else

objSQLServer.Disconnect

if err.number <> 0 then

Call writeerrortolog("Cannot disconnect from " & rstSQLInstances("ServerName") , Err.Number)

err.clear

End If

end if

on error got 0

rstSQLinstances.MoveNext 'Move to next SQL Server

wend

set objSQLServer = Nothing

Brian Adams-265363 Mr or Mrs. 500 Points: 509 More actions · Answer 5

Paul,

Thanks for the suggestions. Two points however:

1.) Its not 'failing' during the disconnect. It is failing when I try to connect to any subsequent SQL Server.

2.) I am not hardcoding connection parameters. I originally used a trusted connection and still had the same problem. The manner that I showed on here passes the values via an ado recordset. This is to eliminate the possibility that it was a trusted connection problem.

Brian Adams-265363 Mr or Mrs. 500 Points: 509 More actions · Answer 6

Antares,

I am going to try your suggestion. I will let you know if it solves my problem.

Thanks!

Brian Adams-265363 Mr or Mrs. 500 Points: 509 More actions · Answer 7

Antares,

Your suggestion seems to have solved my problem! Many many thanks. I have been trying to figure this one out for about a week. I guess the moral(s) of the story are:

Do not destroy and recreate the DMO SQLServer object for every iteration.

Add the On Error resume next statement within the iteration.

Add the On Error Goto 0 statement after the iteration

Once again, thank you for your help!

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Do not destroy and recreate the DMO SQLServer object for every iteration. -- Actually doesn't really matter I just hate wasting the resources used to create an destroy an object multipl times when I can easily reuse it anyway.

Add the On Error resume next statement within the iteration.

Add the On Error Goto 0 statement after the iteration

Error Logic should be narrowed only to those areas where you expect failures to potentially cause issues and you already know how you are goin to deal with it. I have had to debug many apps because someone put ON ERROR RESUME NEXT at the start and something wet wrong they hadn't had a direct plan for. Better to hv it blow up and tell you than to skip over the issue.

Actually I think the key for my chang is the movin of the Disconnect to only the else for the first error condition to trap and the trapping the potential error with Disconnect should it occurr, plus using err.clear to reset the error object to 0 after we trapped.