Linked server question.

#11

It occurred to me that one possibility is that you did someththing like
this:

EXEC sp_addlinkedserver 'myserver'
go
SELECT MYSERVER.master.dbo.sysdatabases

And the servers you have problem with are running with a case-sensitive or
binary collation.

Of course, this is a complete stab in the dark.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#12

It is not that hard to understand. The name is just the variable. I used the
same name on the SQL2000 servers that
work, just the same as what I used on the SQL2000 servers that don't work. I
have 5 or 6 including my test VM machine
that works. These two just happen to not work. EVERYTHING is the exact same.
I don't get what is so hard to understand about
that. SOMETHING ELSE has to be wrong. They are not typos.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Geoff (butterfly (AT) nomail (DOT) com) writes:
Not sure what you are asking for at this point, but I did this. I did
both
of these on the local 2000 machine.

SELECT * FROM master.dbo.sysservers
go
SELECT name FROM local2000ServerName.master.dbo.databases

Gave this.

(2 row(s) affected)

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'local2000ServerName' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.

I wanted to see the actual data in the columns, and I wanted to see
the actual names you use in your query. Since you use different names
every time you post, and they appear to be mockup names, there is zero
possibility that I can assist you you any further.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#13

Do you know how I would check this?

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

It occurred to me that one possibility is that you did someththing like
this:

EXEC sp_addlinkedserver 'myserver'
go
SELECT MYSERVER.master.dbo.sysdatabases

And the servers you have problem with are running with a case-sensitive or
binary collation.

Of course, this is a complete stab in the dark.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#14

Geoff (butterfly (AT) nomail (DOT) com) writes:

Quote:

It is not that hard to understand. The name is just the variable. I used
the same name on the SQL2000 servers that work, just the same as what I
used on the SQL2000 servers that don't work. I have 5 or 6 including my
test VM machine that works. These two just happen to not work.
EVERYTHING is the exact same. I don't get what is so hard to understand
about that. SOMETHING ELSE has to be wrong. They are not typos.

From the information you have given me, I have made two suggestions. And
they are really the only answers. If you don't want to share more
information, that's your call. And I make mine.

Quote:

Do you know how I would check this?

Whether you are using a different case than in sysservers? Well, the
error message gives you a strong hit to look there. Comparing whether
you use the same case or not, should not be diffiult.

You can investigate the server collation with

SELECT serverproperty('Collation')

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#15

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Geoff (butterfly (AT) nomail (DOT) com) writes:
Whether you are using a different case than in sysservers? Well, the
error message gives you a strong hit to look there. Comparing whether
you use the same case or not, should not be diffiult.

The case is the same, but......

Quote:

You can investigate the server collation with

SELECT serverproperty('Collation')

I checked the collation and on all the servers that work it looks like the
collation is SQL_Latin1_General_CP1_CI_AS

On the two servers that don't work, the collation is Latin1_General_BIN.

If this is my problem, do you know how I go about fixing it? Thanks.

#16

Geoff (butterfly (AT) nomail (DOT) com) writes:

Quote:

On the two servers that don't work, the collation is Latin1_General_BIN.

If this is my problem, do you know how I go about fixing it? Thanks.

Use the same spelling in both server definition and query. Spelling here
applies to case. That is, if you defined the server as MySeRVer, you need to
refer to it as MySeRVer, "myserver" or MYSERVER won't do. If necessary, run

SELECT convert(varbinary(128), name), name FROM master.dbo.sysservers

to get the exact code points.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Linked server question.

microsoft.public.sqlserver.server microsoft.public.sqlserver.server

Re: Linked server question. - 11-18-2011 , 05:17 AM

Re: Linked server question. - 11-18-2011 , 11:34 AM

Re: Linked server question. - 11-18-2011 , 11:35 AM

Re: Linked server question. - 11-18-2011 , 04:13 PM

Re: Linked server question. - 11-21-2011 , 10:00 AM

Re: Linked server question. - 11-22-2011 , 05:16 AM