Posted by M A Srinivas on 04/17/07 05:31

On Apr 16, 9:30 pm, "sqlgirl" <tootsu...@gmail.com> wrote:
> 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

[Back to original message]