Strange error on linked server - cannot open default database

[sqlgirl]

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

[Erland Sommarskog]

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

[M A Srinivas]

On Apr 16, 9:30 pm, "sqlgirl" <[e-mail]:

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