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 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
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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by Sam |
last post: by
|
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
|
reply
views
Thread by HKSHK |
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
| | | | | | | | | | | | |