Posted by sqlgirl on 04/16/07 16:30

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

[Back to original message]