473,849 Members | 1,992 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Strange error on linked server - cannot open default database

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.Default Database.dbo.St oredProcedureNa me

However, after changing the user's default database to
"DefaultDatabas e" 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 "DefaultDatabas e", 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 "DefaultDatabas e", which may not always be the
case.

Thanks much

Apr 16 '07 #1
2 4169
sqlgirl (to*******@gmai l.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.Default Database.dbo.St oredProcedureNa me

However, after changing the user's default database to
"DefaultDatabas e" 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 "DefaultDatabas e", 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 "DefaultDatabas e", 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****@sommarsk og.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
Apr 16 '07 #2
On Apr 16, 9:30 pm, "sqlgirl" <tootsu...@gmai l.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.Default Database.dbo.St oredProcedureNa me

However, after changing the user's default database to
"DefaultDatabas e" 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 "DefaultDatabas e", 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 "DefaultDatabas e", 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

Apr 17 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3027
by: Sam | last post by:
Guys facing a strange problem any clue would really rescue me.. i am using a ASP application with following things Server : Intel Xeon (TM) CPU 2GHz, 2GB RAM, 136GB HDD OS : Windows 2000 Advanced server. IIS5.0 Database: Oracle (Using ADODB to connect to the Database) App server running on the same machine (COM+)
1
11743
by: Mahesh Hardikar | last post by:
Hi , I have been trying to connect to access database from SQL Server 7.0. This machine is having 7.0 as a default instance and 2000 as a named instance. Also the machine doesn't have access installed and Microsoft.Jet.4.0 is of version SP8 for Windows 2000 The access database is password protected. I have tried all, OPENROWSET, OPENDATASOURCE, linked server, and ODBC.
2
2700
by: Robert McGregor | last post by:
Hi all, I've got a Front End / Back End database that was working just fine. One day i opened the FE to find that if I tried to open one of the linked tables from the database window, nothing happened (hourglass for about 2 seconds then nothing). I tried relinking the tables and got the same response. (Access even completely bombed out once with a Dr Watson failure).
4
8130
by: Wayne Wengert | last post by:
I am trying to create a VB.NET Windows application to move some data from a local Access DB table to a table in a SQL Server. The approach I am trying is to open an OLEDB connection to the local Access DB and then add a Linked Table pointing to the table on the SQL Server and then run an "Insert Into (linked table)" query to add the new rows. I am having a problem getting the syntax to add that linked table to my local Access DB. When I...
0
23523
by: HKSHK | last post by:
This list compares the error codes used in VB.NET 2003 with those used in VB6. Error Codes: ============ 3: This Error number is obsolete and no longer used. (Formerly: Return without GoSub) 5: Procedure call or argument is not valid. 6: Overflow. 7: Out of memory.
4
7367
by: Praveen_db2 | last post by:
Hi All I am getting strange errors in my db2diag.log can any one tell me what these errors mean?? Following is the code from my db2diag.log ********************************************************************************************* 2006-02-23-17.53.12.253000 Instance:DB2 Node:000 PID:1600(db2syscs.exe) TID:440 Appid:AC10E010.J70A.00E883122250 base sys utilities sqleagnt_sigsegvh Probe:1 Database:DEVM_DB Error in agent...
8
15407
by: rdemyan via AccessMonster.com | last post by:
In 12 months of working on my app, I have never had a corruption problem with the backends, often with the front end but never the backends. So, of course, now that I'm planning to roll a new version of the app out next week, I suddenly had a problem. Here's the development configuration 1) Computer #1 a) Access 2000 - app is developed on this machine
1
2295
by: pitjpz | last post by:
We have moved our Database to another server. The server it was on used SQL 4 and the new one its on now uses SQL5 the only problem we can find is that when you attempt to delete a record from the DB the following happens: When Deleting a record: Fatal Error: Can't call method "fetchrow_arrayref" on an undefined value at GT::SQL::File::delete_records line 275. Stack Trace: GT::Base (2704): main::fatal called at...
10
6988
by: happyse27 | last post by:
Hi All, I got this apache errors(see section A1 and A2 below) when I used a html(see section b below) to activate acctman.pl(see section c below). Section D below is part of the configuration of section c. Not sure where went wrong as the web page displayed internal server error. Also, what is the error 543? and error 2114. Where to find the list of errors in websites as it is not the standard apache error. I could not find...
0
9741
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11008
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10349
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9501
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7899
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7070
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4545
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4140
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3177
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.