• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12640
  • Last Modified:

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 ?


0
Julian Hansen
Asked:
Julian Hansen
  • 12
  • 6
  • 3
  • +4
5 Solutions
 
leonstrykerCommented:
Normally this type of thing (SQL Server database creation) is handled with SQL-DMO.  I do not know C++, but here are some samples which may be useful to you:

http://www.sqldev.net/sqldmo/SamplesCPP.htm

Leon
0
 
Julian HansenAuthor Commented:
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.
0
 
leonstrykerCommented:
Is it possible you may not have the rights to make the changes on that particular database?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Julian HansenAuthor Commented:
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.
0
 
leonstrykerCommented:
Doing some digging around, I think the reason this message is returned is that, that particular database is setup to return all message alerts, both errors and none errors and the others are not.  If that is the case then the behaivior maybe modified by updating the settings on the database.  

Take a look at the sp_update_alert store procedure:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ua-uz_02r8.asp

Leon
0
 
Julian HansenAuthor Commented:
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.
0
 
leonstrykerCommented:
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
0
 
Lori99Commented:
Could you eliminate the USE statement and just create your tables fully qualified with the database name?  If you say

CREATE TABLE database.dbo.tablename

you would not have to execute the USE statement first to get the table created in the correct database.
0
 
Julian HansenAuthor Commented:
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 ;)
0
 
stu_pbCommented:
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!
0
 
stu_pbCommented:
Or you could move the pConn->Execute functions into its own function

void ConnectionExecute(CString sQuery)
{
try
{
     pConn->Execute(sQuery.AllocSysString(),NULL,adCmdText);
}
catch(_com_error & e)
{
     if (!DisplayADOError ( pConn ) )
          DisplayCOMError ( e ) ;
}
}

void YourFunction()
{
     CString sQuery ;
    sQuery.Format ( "CREATE DATABASE [%s] ON NAME .... " ) ;
    ConnectionExecute(sQuery);
   
    sQuery.Format ( "USE %s", m_database) ;
    ConnectionExecute(sQuery);

    sQuery.Format ( "Create table x ... " ) ;    // Code does not get here
   ConnectionExecute(sQuery);

 ...
    // More create statements
}

Good Luck!
0
 
Julian HansenAuthor Commented:
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.
0
 
Julian HansenAuthor Commented:
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.
0
 
stu_pbCommented:
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!
0
 
Julian HansenAuthor Commented:
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.


0
 
leonstrykerCommented:
ADO is just a messenger, I do not believe you will be able to disable it at this level.
0
 
rafranciscoCommented:
Hi julianH,

I don't know if it's already too late but here goes anyway.

First, try creating the database using your current connection:

try
{
    CString sQuery ;
    sQuery.Format ( "CREATE DATABASE [%s] ON NAME .... " ) ;
    pConn->Execute ( sQuery.AllocSysString(), NULL, adCmdText ) ;    // THIS Works
catch ( _com_error & e )
{
   if (!DisplayADOError ( pConn ) )
     DisplayCOMError ( e ) ;
 }

I believe you don't have a problem with that part.  After creating the database, close the connection and open a new connection and this time, the Initial Catalog or Data Source for your connection will now be the new database.  Given this, you don't have to issue the USE command to change database.  I am not really familiar with C++ so I don't know how it will look like in code.
0
 
DanRollinsCommented:
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.
0
 
Julian HansenAuthor Commented:
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
0
 
DanRollinsCommented:
There is no "resume next" equivalent in C++ (and it has caused endless heartache for VB programmers.  Since it masks the true error, it makes debugging difficult.

I believe that leonstryker may have answered your second item in his http:#13888487  post.  It is probably a specific setting for the ODBC or SQL Server at that one site.  Perhaps you can try joggling those settings around on a staging server to reproduce the error... even if you don't want to ask customers to change their settings, it would be good to know the root of the problem.

-- Dan
0
 
leonstrykerCommented:
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?
0
 
andrewbleakleyCommented:
What is your connect string ? Does it have an Initial Catalouge Specified ?
0
 
Julian HansenAuthor Commented:
Andrew,

Yes it does - that is not where the problem is.
0
 
Julian HansenAuthor Commented:
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.
0
 
Julian HansenAuthor Commented:
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
0
 
DanRollinsCommented:
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
0
 
Julian HansenAuthor Commented:
Thanks Dan,

We live and learn.

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 6
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now