MSSQL: "Changed Database Context" - is there a way around this (ADO, C++, MSSQL)
I recently developed a database installation utility that creates an MSSQL database for our application. The application uses ADO to run commands against the SQL server. The database and objects in it are created using SQL queries.
After creating the database the application executes a
USE <DATABASENAME>
Where database name is the name of the database entered into the utility.
The code is MFC based Visual Studio .Net using ADO to connect. All database code is wrapped in try / catch statements.
This seems to work fine except at one site where we are get an "Error" [Error 0 Changed Database Context to 'databasename']. Research seems to indicate this is a purely informational message and can be ignored. However, given the code is in a try / catch block we have the following problem
try
{
CString sQuery ;
sQuery.Format ( "CREATE DATABASE [%s] ON NAME .... " ) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // THIS Works
sQuery.Format ( "USE %s", m_database) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // Exception is thrown with error = 0 Changed Database Context to '<databasename>'
sQuery.Format ( "Create table x ... " ) ; // Code does not get here
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText );
...
// More create statements
}
catch ( _com_error & e )
{
if (!DisplayADOError ( pConn ) )
DisplayCOMError ( e ) ;
}
Basically the problem at the site in question is that an exception is being thrown when the DB context is changed thereby interrupting execution so the rest of the database create statements are not run.
Question:
Any idea why this information message is coming through as an exception ?
How do I stop it ?
Is there an equivalent of resume next that can be used in catch whereby an error can be ignored and code can continue processing ?
After creating the database the application executes a
USE <DATABASENAME>
Where database name is the name of the database entered into the utility.
The code is MFC based Visual Studio .Net using ADO to connect. All database code is wrapped in try / catch statements.
This seems to work fine except at one site where we are get an "Error" [Error 0 Changed Database Context to 'databasename']. Research seems to indicate this is a purely informational message and can be ignored. However, given the code is in a try / catch block we have the following problem
try
{
CString sQuery ;
sQuery.Format ( "CREATE DATABASE [%s] ON NAME .... " ) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // THIS Works
sQuery.Format ( "USE %s", m_database) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // Exception is thrown with error = 0 Changed Database Context to '<databasename>'
sQuery.Format ( "Create table x ... " ) ; // Code does not get here
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText );
...
// More create statements
}
catch ( _com_error & e )
{
if (!DisplayADOError ( pConn ) )
DisplayCOMError ( e ) ;
}
Basically the problem at the site in question is that an exception is being thrown when the DB context is changed thereby interrupting execution so the rest of the database create statements are not run.
Question:
Any idea why this information message is coming through as an exception ?
How do I stop it ?
Is there an equivalent of resume next that can be used in catch whereby an error can be ignored and code can continue processing ?
ASKER
Thank you leon,
But I am not using DMO I am using ADO to pass SQL queries to the database.
The implementation works in all instances but this one so far. I would like to find out how to get my implementation to work.
But I am not using DMO I am using ADO to pass SQL queries to the database.
The implementation works in all instances but this one so far. I would like to find out how to get my implementation to work.
Is it possible you may not have the rights to make the changes on that particular database?
ASKER
Nope - that is not it. Two reasons.
1. As noted in the original post the application runs past the point where the database is created - and the database is successfully created. If the login has permissions to create the database, by default it has permissions to modify the database
2. The problem in question is not about an error. No error is reported. Error 0 means that the message is information only in this case notification that the datbase context was successfully changed. The problem is that this message is being picked up in the catch block of the code which means that execution is interrupted before the database objects are created.
If I could replicate the problem I could probably solve it. The problem is that the site where this is happening is 1500Km away and I don't feel like going there just to solve what seems to be a minor issue - hence my post.
1. As noted in the original post the application runs past the point where the database is created - and the database is successfully created. If the login has permissions to create the database, by default it has permissions to modify the database
2. The problem in question is not about an error. No error is reported. Error 0 means that the message is information only in this case notification that the datbase context was successfully changed. The problem is that this message is being picked up in the catch block of the code which means that execution is interrupted before the database objects are created.
If I could replicate the problem I could probably solve it. The problem is that the site where this is happening is 1500Km away and I don't feel like going there just to solve what seems to be a minor issue - hence my post.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is correct.
However, I am not really in a position to tell potential users that in order to use my application they first have to disable settings on their server. They may need those settings enabled for other reasons.
I am interested in the correct manner in dealing with these messages from the perspective of the client.
However, I am not really in a position to tell potential users that in order to use my application they first have to disable settings on their server. They may need those settings enabled for other reasons.
I am interested in the correct manner in dealing with these messages from the perspective of the client.
Hmm, in that case I do not think I would be able to help you, since it is more of a C++ issue. You may want to post this question in that TA or (C# and VB.NET since those languages use the same try/catch method of error traping).
But, it seems to me you should be able to resume code execution if the error is not fatal, as in this case.
Leon
But, it seems to me you should be able to resume code execution if the error is not fatal, as in this case.
Leon
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No I wouldn't but then I would have to go and change a lot of code (the app creates a lot of tables, views and stored procs) - which I don't want to do. Besides I want to know how to deal with these return messages properly - I am sure they will pop up again in the future - may as well deal with them now ;)
Try this
try
{
CString sQuery ;
sQuery.Format ( "CREATE DATABASE [%s] ON NAME .... " ) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // THIS Works
sQuery.Format ( "USE %s", m_database) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // Exception is thrown with error = 0 Changed Database Context to '<databasename>'
}
catch ( _com_error & e )
{
if (!DisplayADOError ( pConn ) )
DisplayCOMError ( e ) ;
}
try
{
sQuery.Format ( "Create table x ... " ) ; // Code does not get here
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText );
...
// More create statements
}
catch ( _com_error & e )
{
if (!DisplayADOError ( pConn ) )
DisplayCOMError ( e ) ;
}
Good Luck!
try
{
CString sQuery ;
sQuery.Format ( "CREATE DATABASE [%s] ON NAME .... " ) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // THIS Works
sQuery.Format ( "USE %s", m_database) ;
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ; // Exception is thrown with error = 0 Changed Database Context to '<databasename>'
}
catch ( _com_error & e )
{
if (!DisplayADOError ( pConn ) )
DisplayCOMError ( e ) ;
}
try
{
sQuery.Format ( "Create table x ... " ) ; // Code does not get here
pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText );
...
// More create statements
}
catch ( _com_error & e )
{
if (!DisplayADOError ( pConn ) )
DisplayCOMError ( e ) ;
}
Good Luck!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks stu,
I did try that to determine if it was in fact the USE statement. I could also remove the exception handling all together - I would like to avoid having to do either.
My preference is for clean code that is implemented correcly - workarounds have a nasty habit of attracting issues.
I do appreciate all the comments though.
I did try that to determine if it was in fact the USE statement. I could also remove the exception handling all together - I would like to avoid having to do either.
My preference is for clean code that is implemented correcly - workarounds have a nasty habit of attracting issues.
I do appreciate all the comments though.
ASKER
Stu_pb,
I posted my comment after reading your first post but before reading your second so read my previous post as being between your two posts and the following to come after .... !? ;)
Your second solution looks interesting. It is a more subtle workaround and would probably work - I would like to try and get an answer on how to do it in a single try catch block.
I posted my comment after reading your first post but before reading your second so read my previous post as being between your two posts and the following to come after .... !? ;)
Your second solution looks interesting. It is a more subtle workaround and would probably work - I would like to try and get an answer on how to do it in a single try catch block.
I can't think of any way to do this with 1 try catch block, other than not using exception handling for the remaining create statements.
If you find a solution, please post it, I am curious to see how it can be done.
Good Luck!
If you find a solution, please post it, I am curious to see how it can be done.
Good Luck!
ASKER
In that context I think you are correct. What would seem logical to me is some way of turning off or disabling ADO from throwing an exception in the first place.
ADO is just a messenger, I do not believe you will be able to disable it at this level.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Maybe I missed this above... If so, sorry...
What's wrong with having two separate try/catch blocks?
In the one that surrounds, the USE command, ignore the exception if the error is 0.
What's wrong with having two separate try/catch blocks?
In the one that surrounds, the USE command, ignore the exception if the error is 0.
ASKER
Dan - interesting idea I will give this some consideration along with stu's comments above
rafrancisco - thanks - that would probably work as well however I would like to try and get a solution that does not requre re-establishing the connection if possible.
The main reason for my post was to understand exactly what is happening and what the correct method is for dealing with situations like these i.e.
* Are nested try catch blocks (as per Dan's recommendation) the generally accepted practice for handling situations where you want to ignore an exception - is this the only C++ alternative to the VB resume next statement?
* Why on some SQL servers does this exception get thrown and not others? I cannot replicate this in our environment on either our SQL2000 or MSDE box - for both the code executes without incident? How can I replicate the error?
Thanks for the input so far
rafrancisco - thanks - that would probably work as well however I would like to try and get a solution that does not requre re-establishing the connection if possible.
The main reason for my post was to understand exactly what is happening and what the correct method is for dealing with situations like these i.e.
* Are nested try catch blocks (as per Dan's recommendation) the generally accepted practice for handling situations where you want to ignore an exception - is this the only C++ alternative to the VB resume next statement?
* Why on some SQL servers does this exception get thrown and not others? I cannot replicate this in our environment on either our SQL2000 or MSDE box - for both the code executes without incident? How can I replicate the error?
Thanks for the input so far
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I know this is kind of a hack, and I do not know if this is even possible in C++, but can you place a GoTo statement in the Catch, which will continue running the code if the error level is below a certain value?
What is your connect string ? Does it have an Initial Catalouge Specified ?
ASKER
Andrew,
Yes it does - that is not where the problem is.
Yes it does - that is not where the problem is.
ASKER
Ok, problem solved. I eventually resorted to creating a version with debug statements after every line. I then managed to get hold of a very nice support technician on the site who was patient enough to help me with the debugging.
The problem was not with the database code - it just manifestied itself there. Here is what happens. The problem is actually with the MSXML2.DOMDocument component.
The application reads the SQL statements from an XML file and submits them to the database by means of an ADO connection. This is done in two batches the first is within a transaction frame the second is outside of a transaction frame.
When I coded this I stupidly put the XML code and DB code within the same try catch block and when an exception was thrown I did not cater for the possibility that the MSXML component was throwing the exception - duh.
The reason the Error [0] was popping up was because of the following
try
{
docPtr.CreateInstance ( "Msxml2.DOMDocument.3.0" ) ;
varOut = docPtr->loadXML ( _bstr_t(lpszXML ) ) ; <----- Exception thrown here
if (varOut )
{
// do database stuff here
// If we are in batch1 BEGIN TRANS
}
bSuccess = TRUE ;
}
catch ( _com_error e )
{
// display error
// if in a transaction frame ROLLBACK TRANS <----- This code runs there is a connection but no transaction frame yet - this is where the Error(0) exception is thrown
bSuccess = FALSE ;
}
// if in a transaction frame and bSuccess == TRUE COMMIT TRANS
Once I found this out I was able to replicate the problem by running it on a new installation of Windows 2000 SP3 which proves it was not a server setting (thanks anyway leonstryker).
My code is not well written - it was put together in haste to meet an unreasonable deadline.
Thanks to all who responded.
The problem was not with the database code - it just manifestied itself there. Here is what happens. The problem is actually with the MSXML2.DOMDocument component.
The application reads the SQL statements from an XML file and submits them to the database by means of an ADO connection. This is done in two batches the first is within a transaction frame the second is outside of a transaction frame.
When I coded this I stupidly put the XML code and DB code within the same try catch block and when an exception was thrown I did not cater for the possibility that the MSXML component was throwing the exception - duh.
The reason the Error [0] was popping up was because of the following
try
{
docPtr.CreateInstance ( "Msxml2.DOMDocument.3.0" ) ;
varOut = docPtr->loadXML ( _bstr_t(lpszXML ) ) ; <----- Exception thrown here
if (varOut )
{
// do database stuff here
// If we are in batch1 BEGIN TRANS
}
bSuccess = TRUE ;
}
catch ( _com_error e )
{
// display error
// if in a transaction frame ROLLBACK TRANS <----- This code runs there is a connection but no transaction frame yet - this is where the Error(0) exception is thrown
bSuccess = FALSE ;
}
// if in a transaction frame and bSuccess == TRUE COMMIT TRANS
Once I found this out I was able to replicate the problem by running it on a new installation of Windows 2000 SP3 which proves it was not a server setting (thanks anyway leonstryker).
My code is not well written - it was put together in haste to meet an unreasonable deadline.
Thanks to all who responded.
ASKER
Although none of the posts actually pinpointed the problem - most helped me to either narrow down the problem or to learn more about the code I was working with. I also got some ideas that I will be using in other parts of the code. I have assigned points to those who assisted in this regard.
Thanks again
JulianH
Thanks again
JulianH
Thanks for the points and the grade. :-)
Do note that had the code in your question shown the additional COM-related function calls, we might have focused-in on that earlier.
Also, having the "large" try/catch is a lot like the VB "resume next" problem -- it masks the error. The simplest debugging technique is to simply remove all exception handling and let all errors occur "naturally" That helps you locate the error, so you can fix it, then you can add-back the exception handling later.
-- Dan
Do note that had the code in your question shown the additional COM-related function calls, we might have focused-in on that earlier.
Also, having the "large" try/catch is a lot like the VB "resume next" problem -- it masks the error. The simplest debugging technique is to simply remove all exception handling and let all errors occur "naturally" That helps you locate the error, so you can fix it, then you can add-back the exception handling later.
-- Dan
ASKER
Thanks Dan,
We live and learn.
We live and learn.
http://www.sqldev.net/sqldmo/SamplesCPP.htm
Leon