An interesting error occurred today along with some inconsistent behaviour.
On our test database we have one database, that is the main one and in which
all the SPs are located. Then we have a secondary database, the data in
which is accessed only by stored procs run from the main database. Recently
we updated this secondary database by dropping the old one and recreating
it. This changed, presumably, the db_id for that database, same name.
Now when we tried to run any of the SPs that access this secondary database,
they caused error number 913, "Could not find database ID %d. Database may
not be activated yet or may be in transition." Which seems logical if the
secondary database now has a different db_id from the old one that was
dropped. However, when the same SPs are executed in Query Analyzer, they run
fine, no error, and they return data.
Any idea what's going on? Why would execution of the SPs cause an error from
the application viewpoint but not in Query Analyzer?
This really has me puzzled.
TIA
Michael MacGregor
Database ArchitectMichael MacGregor wrote:
> Any idea what's going on? Why would execution of the SPs cause an error from
> the application viewpoint but not in Query Analyzer?
How are you executing the sprocs within your app? Just curious if
you're using sp_prepexec/sp_execute. I suspect what you're seeing is
due to cached execution plans. Try running DBCC FREEPROCCACHE, that
will flush out all of the currently cached plans, and each subsequent
query will compile a new plan. The database ID is part of that plan.|||Michael,
- what interface are you using to connect to SQL Server from your application?
- can you post the code where you set the connection properties?
AMB
"Michael MacGregor" wrote:
> An interesting error occurred today along with some inconsistent behaviour.
> On our test database we have one database, that is the main one and in which
> all the SPs are located. Then we have a secondary database, the data in
> which is accessed only by stored procs run from the main database. Recently
> we updated this secondary database by dropping the old one and recreating
> it. This changed, presumably, the db_id for that database, same name.
> Now when we tried to run any of the SPs that access this secondary database,
> they caused error number 913, "Could not find database ID %d. Database may
> not be activated yet or may be in transition." Which seems logical if the
> secondary database now has a different db_id from the old one that was
> dropped. However, when the same SPs are executed in Query Analyzer, they run
> fine, no error, and they return data.
> Any idea what's going on? Why would execution of the SPs cause an error from
> the application viewpoint but not in Query Analyzer?
> This really has me puzzled.
> TIA
> Michael MacGregor
> Database Architect
>
>|||I've already totally recreated all the appropriate SPs so that the right
db_id gets used. I'm just trying to figure out why we saw different
behaviour for the same SP, using the same parameters, between the app and
Query Analyzer. Even if the SP had a cached execution plan, it should use
the same plan whether the SP was called from the app or Query Analyzer.
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150132439.326685.142290@.y43g2000cwc.googlegroups.com...
> Michael MacGregor wrote:
>> Any idea what's going on? Why would execution of the SPs cause an error
>> from
>> the application viewpoint but not in Query Analyzer?
> How are you executing the sprocs within your app? Just curious if
> you're using sp_prepexec/sp_execute. I suspect what you're seeing is
> due to cached execution plans. Try running DBCC FREEPROCCACHE, that
> will flush out all of the currently cached plans, and each subsequent
> query will compile a new plan. The database ID is part of that plan.
>|||Michael MacGregor wrote:
> I've already totally recreated all the appropriate SPs so that the right
> db_id gets used. I'm just trying to figure out why we saw different
> behaviour for the same SP, using the same parameters, between the app and
> Query Analyzer. Even if the SP had a cached execution plan, it should use
> the same plan whether the SP was called from the app or Query Analyzer.
That's why I asked about sp_prepexec. A single query can have multiple
plans cached if you're compiling them using sp_prepexec. Capture a
trace log using Profiler, see if there are sp_prepexec/sp_execute calls
being made by your app, and look closely at some of those calls.|||Where in the trace do I see whether it is sp_prepexec or sp_execute?
I asked one of our Java guys and he didn't know. Unfortunately our lead Java
developer is on vacation right now and I'm sure he'd know the answer
immediately.
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150133773.482772.324100@.u72g2000cwu.googlegroups.com...
> Michael MacGregor wrote:
>> I've already totally recreated all the appropriate SPs so that the right
>> db_id gets used. I'm just trying to figure out why we saw different
>> behaviour for the same SP, using the same parameters, between the app and
>> Query Analyzer. Even if the SP had a cached execution plan, it should use
>> the same plan whether the SP was called from the app or Query Analyzer.
> That's why I asked about sp_prepexec. A single query can have multiple
> plans cached if you're compiling them using sp_prepexec. Capture a
> trace log using Profiler, see if there are sp_prepexec/sp_execute calls
> being made by your app, and look closely at some of those calls.
>|||This is what my Java guy gave me when I asked him for the information. Is
this what you wanted?
public static String strDbUser ="<userID>";
public static String strDbPwd ="<pwd>";
public static String strDefaultServer = "<sqlserver>";
A JDBC SQL server driver is being used as an interface between the java app
and sql server.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:75FF2CB6-94CB-423B-A17F-EEE0688825A6@.microsoft.com...
> Michael,
> - what interface are you using to connect to SQL Server from your
> application?
> - can you post the code where you set the connection properties?
>
> AMB
> "Michael MacGregor" wrote:
>> An interesting error occurred today along with some inconsistent
>> behaviour.
>> On our test database we have one database, that is the main one and in
>> which
>> all the SPs are located. Then we have a secondary database, the data in
>> which is accessed only by stored procs run from the main database.
>> Recently
>> we updated this secondary database by dropping the old one and recreating
>> it. This changed, presumably, the db_id for that database, same name.
>> Now when we tried to run any of the SPs that access this secondary
>> database,
>> they caused error number 913, "Could not find database ID %d. Database
>> may
>> not be activated yet or may be in transition." Which seems logical if the
>> secondary database now has a different db_id from the old one that was
>> dropped. However, when the same SPs are executed in Query Analyzer, they
>> run
>> fine, no error, and they return data.
>> Any idea what's going on? Why would execution of the SPs cause an error
>> from
>> the application viewpoint but not in Query Analyzer?
>> This really has me puzzled.
>> TIA
>> Michael MacGregor
>> Database Architect
>>|||Michael MacGregor wrote:
> Where in the trace do I see whether it is sp_prepexec or sp_execute?
> I asked one of our Java guys and he didn't know. Unfortunately our lead Java
> developer is on vacation right now and I'm sure he'd know the answer
> immediately.
In the "TextData" field, you'll see executions of "sp_prepexec" and
"sp_execute", each with a string of parameters attached to it.|||Ok, I see "exec <sp>".
Another point to note is that the developers also restrated the BEA server,
which should have closed all DB connections and re-established them.
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150138844.829918.297480@.c74g2000cwc.googlegroups.com...
> Michael MacGregor wrote:
>> Where in the trace do I see whether it is sp_prepexec or sp_execute?
>> I asked one of our Java guys and he didn't know. Unfortunately our lead
>> Java
>> developer is on vacation right now and I'm sure he'd know the answer
>> immediately.
> In the "TextData" field, you'll see executions of "sp_prepexec" and
> "sp_execute", each with a string of parameters attached to it.
>|||Michael,
Seems like the connection is using the default database for that user. If
the default db was pointing to the the one you recreated, then the default db
for that user is pointing to the wrong db_id. You will have to change the
default db for your users, using sp_defaultdb.
AMB
"Michael MacGregor" wrote:
> This is what my Java guy gave me when I asked him for the information. Is
> this what you wanted?
> public static String strDbUser => "<userID>";
> public static String strDbPwd => "<pwd>";
> public static String strDefaultServer = "<sqlserver>";
> A JDBC SQL server driver is being used as an interface between the java app
> and sql server.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:75FF2CB6-94CB-423B-A17F-EEE0688825A6@.microsoft.com...
> > Michael,
> >
> > - what interface are you using to connect to SQL Server from your
> > application?
> > - can you post the code where you set the connection properties?
> >
> >
> > AMB
> >
> > "Michael MacGregor" wrote:
> >
> >> An interesting error occurred today along with some inconsistent
> >> behaviour.
> >> On our test database we have one database, that is the main one and in
> >> which
> >> all the SPs are located. Then we have a secondary database, the data in
> >> which is accessed only by stored procs run from the main database.
> >> Recently
> >> we updated this secondary database by dropping the old one and recreating
> >> it. This changed, presumably, the db_id for that database, same name.
> >>
> >> Now when we tried to run any of the SPs that access this secondary
> >> database,
> >> they caused error number 913, "Could not find database ID %d. Database
> >> may
> >> not be activated yet or may be in transition." Which seems logical if the
> >> secondary database now has a different db_id from the old one that was
> >> dropped. However, when the same SPs are executed in Query Analyzer, they
> >> run
> >> fine, no error, and they return data.
> >>
> >> Any idea what's going on? Why would execution of the SPs cause an error
> >> from
> >> the application viewpoint but not in Query Analyzer?
> >>
> >> This really has me puzzled.
> >>
> >> TIA
> >>
> >> Michael MacGregor
> >> Database Architect
> >>
> >>
> >>
>
>|||Forgot to mention that you can also specify the db to which you want to
connect in the connection properties. I am not familiar with JDBC but this
should be something comon in every interface.
AMB
"Alejandro Mesa" wrote:
> Michael,
> Seems like the connection is using the default database for that user. If
> the default db was pointing to the the one you recreated, then the default db
> for that user is pointing to the wrong db_id. You will have to change the
> default db for your users, using sp_defaultdb.
>
> AMB
>
> "Michael MacGregor" wrote:
> > This is what my Java guy gave me when I asked him for the information. Is
> > this what you wanted?
> >
> > public static String strDbUser => > "<userID>";
> >
> > public static String strDbPwd => > "<pwd>";
> >
> > public static String strDefaultServer = "<sqlserver>";
> >
> > A JDBC SQL server driver is being used as an interface between the java app
> > and sql server.
> >
> > "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> > news:75FF2CB6-94CB-423B-A17F-EEE0688825A6@.microsoft.com...
> > > Michael,
> > >
> > > - what interface are you using to connect to SQL Server from your
> > > application?
> > > - can you post the code where you set the connection properties?
> > >
> > >
> > > AMB
> > >
> > > "Michael MacGregor" wrote:
> > >
> > >> An interesting error occurred today along with some inconsistent
> > >> behaviour.
> > >> On our test database we have one database, that is the main one and in
> > >> which
> > >> all the SPs are located. Then we have a secondary database, the data in
> > >> which is accessed only by stored procs run from the main database.
> > >> Recently
> > >> we updated this secondary database by dropping the old one and recreating
> > >> it. This changed, presumably, the db_id for that database, same name.
> > >>
> > >> Now when we tried to run any of the SPs that access this secondary
> > >> database,
> > >> they caused error number 913, "Could not find database ID %d. Database
> > >> may
> > >> not be activated yet or may be in transition." Which seems logical if the
> > >> secondary database now has a different db_id from the old one that was
> > >> dropped. However, when the same SPs are executed in Query Analyzer, they
> > >> run
> > >> fine, no error, and they return data.
> > >>
> > >> Any idea what's going on? Why would execution of the SPs cause an error
> > >> from
> > >> the application viewpoint but not in Query Analyzer?
> > >>
> > >> This really has me puzzled.
> > >>
> > >> TIA
> > >>
> > >> Michael MacGregor
> > >> Database Architect
> > >>
> > >>
> > >>
> >
> >
> >
RoelAlblas:
yinibabe: