Strange error on linked server

[katbear]

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

 

[mrdenny]

Are there any errors in the SQL 2005 machines log about not being able to access the database (or anything out of the ordinary at all)?

Are you doing log shipping to the default database?

When a user logs into the SQL Server (which is what's happening when you use a linked server) your connect is opened in the context of the default database. It doesn't matter if you specify another database name in the command, the connection is still made to the default database.

Try this, Create a new stored procedure in a database other that your default database which just does a WAITFOR.

CREATE PROCEDURE usp_Waiting
AS
WAITFOR DELAY '00:05:00'
GO

Then go to the SQL 2000 machine and execute that query. While it's running log into the SQL 2005 machine with another window and to an sp_who2 and you can see the what database SQL 2005 shows that the spid from the SQL 2000 machine is using. It will be it's default database, not the database name specified in the command.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Then go to the SQL 2000 machine and execute that query. While it's running log into the SQL 2005 machine with another window and to an sp_who2 and you can see the what database SQL 2005 shows that the spid from the SQL 2000 machine is using. It will be it's default database, not the database name specified in the command.

If it's using the default database, it can still run stored procedures in other databases, correct? Which is what this test will be showing.

We don't use log shipping, nor were there any errors I could find. However, once I changed the default database of the user to the same database that contained the query, the problem went away.

Does this sound normal to you? Or is something strange here?

Thanks

 

[mrdenny]

Correct you can run commands in databases other than the default database.

Something is definetly strange here. What do you see in the SQL 2005 servers log file?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Well, the log shows a bunch of failed logins for the user on the 2005 box during the time we were trying to run the linked query. But that isn't too informative.

 

[mrdenny]

No, not really. Can you post a chunk of the log?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com