Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the d
atabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the
database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As so
on as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a l
ittle while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google a
nd you should find some
info, possibly also in Books Online. So it is possible that you can query sy
s.databases (state_desc
column) to see what state the database is in and based on that connect. Stil
l a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google
and you should find some
> info, possibly also in Books Online. So it is possible that you can query
sys.databases (state_desc
> column) to see what state the database is in and based on that connect. St
ill a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
>> This is probably for the simple reason that it takes a while to create the database.
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.

I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:

open new connection
create database
create tables
populate tables
close connection

// open new connection /* can't do this yet, as it would break */

for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}

Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?

Thanks,

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/On 6 Oct 2006 17:36:01 -0700, Jason Kester wrote:

(snip)

Hi Jason,

You posted the same question and youy already received some answers
there.

In the future, please post to a single group only. It prevents people
wasting time on a problm that has already been solved.

--
Hugo Kornelis, SQL Server MVP

Default SQL 2000 Compatability Level problem

I have a SQL 200 server that creates all new databases in compatability level
65. This is creating problems for an application that requires level 80 to
create and populate. How do I change the default level for any new databases?
Chris Millette
MCP/Network Administrator
Community Bank & Trust
The process that creates the database deletes it if it cannot complete the
entire process. I did find a scipt that changes the model db level and that
resolved the issue.
Chris Millette
MCP/Network Administrator
Community Bank & Trust
"Hari Prasad" wrote:
[vbcol=seagreen]
> Change the compatibility level of Model database to 80; after that what ever
> database you create newly
> the compatibility level will be 80.
> Thanks
> Hari
> "Millette" wrote:

Default SQL 2000 Compatability Level problem

I have a SQL 200 server that creates all new databases in compatability level
65. This is creating problems for an application that requires level 80 to
create and populate. How do I change the default level for any new databases?
--
Chris Millette
MCP/Network Administrator
Community Bank & TrustChange the compatibility level of Model database to 80; after that what ever
database you create newly
the compatibility level will be 80.
Thanks
Hari
"Millette" wrote:
> I have a SQL 200 server that creates all new databases in compatability level
> 65. This is creating problems for an application that requires level 80 to
> create and populate. How do I change the default level for any new databases?
> --
> Chris Millette
> MCP/Network Administrator
> Community Bank & Trust|||The process that creates the database deletes it if it cannot complete the
entire process. I did find a scipt that changes the model db level and that
resolved the issue.
--
Chris Millette
MCP/Network Administrator
Community Bank & Trust
"Hari Prasad" wrote:
> Change the compatibility level of Model database to 80; after that what ever
> database you create newly
> the compatibility level will be 80.
> Thanks
> Hari
> "Millette" wrote:
> > I have a SQL 200 server that creates all new databases in compatability level
> > 65. This is creating problems for an application that requires level 80 to
> > create and populate. How do I change the default level for any new databases?
> > --
> > Chris Millette
> > MCP/Network Administrator
> > Community Bank & Trust

Default SQL 2000 Compatability Level problem

I have a SQL 200 server that creates all new databases in compatability leve
l
65. This is creating problems for an application that requires level 80 to
create and populate. How do I change the default level for any new databases
?
--
Chris Millette
MCP/Network Administrator
Community Bank & TrustChange the compatibility level of Model database to 80; after that what ever
database you create newly
the compatibility level will be 80.
Thanks
Hari
"Millette" wrote:

> I have a SQL 200 server that creates all new databases in compatability le
vel
> 65. This is creating problems for an application that requires level 80 to
> create and populate. How do I change the default level for any new databas
es?
> --
> Chris Millette
> MCP/Network Administrator
> Community Bank & Trust|||The process that creates the database deletes it if it cannot complete the
entire process. I did find a scipt that changes the model db level and that
resolved the issue.
--
Chris Millette
MCP/Network Administrator
Community Bank & Trust
"Hari Prasad" wrote:
[vbcol=seagreen]
> Change the compatibility level of Model database to 80; after that what ev
er
> database you create newly
> the compatibility level will be 80.
> Thanks
> Hari
> "Millette" wrote:
>