|
|
Posted by Erland Sommarskog on 04/16/07 22:08
sqlgirl (tootsuite@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|