468,136 Members | 1,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,136 developers. It's quick & easy.

Strange error on linked server - cannot open default database

Hi,

We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it is
not up to date). Server2 is a 2005 box, SP2.

I set up Server1 (2000) to have a linked server to Server2 (2005). The
reason I did this is because we are using a stored procedure on
Server2 to send mail, as we have found that using mail on 2000 doesn't
always work as advertised.

When I set up the linked server on the 2000 box, for security I just
set it up to use a SQL Server user on the 2005 box. The SQL Server
user on the 2005 box has permissions to run the stored procedure for
sending mail.

Here's the weird thing though. When calling the stored procedure on
the 2005 box from the 2000 box, sometimes we get an error that "The
default database cannot be opened", and the query does not run on the
2005 box. However, it only happens *sometimes*. Other times, the query
runs fine.

Since the problem seeemed to be with the default database, I changed
the SQL Server user on 2005 default database to the SAME database that
contains the stored procedure.

However, I just don't understand why it's even TRYING to open the
default database, since when we called the linked server we are doing
so as, and it's referencing the default database in the name:

EXEC Server2.DefaultDatabase.dbo.StoredProcedureName

However, after changing the user's default database to
"DefaultDatabase" as shown above, the query runs fine.

Why are we having this problem? That is, if I change the default
database to something other than "DefaultDatabase", then the query
doesn't run, even though the database name is referenced in the above
query??

Obviously, this is not desireable, because that means we can only run
queries that are in "DefaultDatabase", which may not always be the
case.

Thanks much

Apr 16 '07 #1
2 3932
sqlgirl (to*******@gmail.com) writes:
Since the problem seeemed to be with the default database, I changed
the SQL Server user on 2005 default database to the SAME database that
contains the stored procedure.

However, I just don't understand why it's even TRYING to open the
default database, since when we called the linked server we are doing
so as, and it's referencing the default database in the name:

EXEC Server2.DefaultDatabase.dbo.StoredProcedureName

However, after changing the user's default database to
"DefaultDatabase" as shown above, the query runs fine.

Why are we having this problem? That is, if I change the default
database to something other than "DefaultDatabase", then the query
doesn't run, even though the database name is referenced in the above
query??

Obviously, this is not desireable, because that means we can only run
queries that are in "DefaultDatabase", which may not always be the
case.
When a client connects, the user is put into his default database,
unless the connect string requests a certain database. It doesn't
seem that the database in the EXEC string is added to the connection
string, and thus the default database for the user on the remote
server must be usable. That is, the user must be a valid user in
that database, and the database must exist and be online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 16 '07 #2
On Apr 16, 9:30 pm, "sqlgirl" <tootsu...@gmail.comwrote:
Hi,

We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it is
not up to date). Server2 is a 2005 box, SP2.

I set up Server1 (2000) to have a linked server to Server2 (2005). The
reason I did this is because we are using a stored procedure on
Server2 to send mail, as we have found that using mail on 2000 doesn't
always work as advertised.

When I set up the linked server on the 2000 box, for security I just
set it up to use a SQL Server user on the 2005 box. The SQL Server
user on the 2005 box has permissions to run the stored procedure for
sending mail.

Here's the weird thing though. When calling the stored procedure on
the 2005 box from the 2000 box, sometimes we get an error that "The
default database cannot be opened", and the query does not run on the
2005 box. However, it only happens *sometimes*. Other times, the query
runs fine.

Since the problem seeemed to be with the default database, I changed
the SQL Server user on 2005 default database to the SAME database that
contains the stored procedure.

However, I just don't understand why it's even TRYING to open the
default database, since when we called the linked server we are doing
so as, and it's referencing the default database in the name:

EXEC Server2.DefaultDatabase.dbo.StoredProcedureName

However, after changing the user's default database to
"DefaultDatabase" as shown above, the query runs fine.

Why are we having this problem? That is, if I change the default
database to something other than "DefaultDatabase", then the query
doesn't run, even though the database name is referenced in the above
query??

Obviously, this is not desireable, because that means we can only run
queries that are in "DefaultDatabase", which may not always be the
case.

Thanks much
You can make tempdb as the default database in case default database
name is changed/ default database is dropped . When client logs in
you want get an error

Apr 17 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mahesh Hardikar | last post: by
2 posts views Thread by Robert McGregor | last post: by
4 posts views Thread by Wayne Wengert | last post: by
4 posts views Thread by Praveen_db2 | last post: by
8 posts views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by pitjpz | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.