RE: Linked server strangeness in SQL 2005 Mgmt Studio

changliw@xxxxxxxxxxxxxxxxxxxx (Charles Wang[MSFT]) wrote in
news:dmUlJK91HHA.6140@xxxxxxxxxxxxxxxxxxxxxx:

Hi,
I understand that you found that after you added the linked server
with BLANK product name in SSMS, the generated script could not run
since the Product Name was omitted. However if you selected "SQL
Server" for the linked server, you got a "login failed" message when
you tried to use the linked server.
If I have misunderstood, please let me know.

Before further research, I would like to check with you if SQL Server
2005 SP2 has been installed under your computer. You can run SELECT
@@VERSION to check this.

Thanks for your detailed description of your steps. According to the
steps, I performed a test at my side, however I found that it was
impossible to input BLANK as the Product Name. The SSMS will prompt an
error message so that I could not finish creating the linked server.
Then I used SQLSERVER as the Product Name. I selected "Be made using
the login's current security context" as the security option. After
that, I could run the following statement without any problem:
select * from [Charles-2k3].Northwind.dbo.orders

The generated scripts were also fine:
/****** Object: LinkedServer [Charles-2K] Script Date: 08/06/2007
09:59:21 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Charles-2K',
@srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'Charles-2K'
/* For security reasons the linked server remote logins password is
changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Charles-2K',@useself=N'True',@locallogin=NULL,
@rmtuser=NU
LL,@r mtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K', @optname=N'rpc
out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K', @optname=N'lazy
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Charles-2K', @optname=N'use
remote collation', @optvalue=N'true'

My SQL Server 2005 is SP2 with the version 9.0.3054.

If SQL Server 2005 SP2 has not been installed on your server, I
recommend that you install it first to see if this issue persists. You
can download the latest service packs from the following links:
Microsoft SQL Server 2005 Service Pack 2
http://www.microsoft.com/downloads/details.aspx?familyid=d07219b2-
1e23-
49c8- 8f0c-63fa18f26d3a&displaylang=en
Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at
different intervals than intended
http://support.microsoft.com/kb/933508

Hope this helps. Please feel free to let me know if you have any other
questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no
rights. ======================================================


Yes, SP 2 has been installed.
.

Relevant Pages

  • Re: MAS90
    ... To set up a linked server from SQL Server 2000 to MAS90 using Enterprise ...
    (comp.databases.ms-access)
  • Re: Support team security
    ... to view Linked Server properties (this is a tool requirement not a SQL ... Jasper Smith (SQL Server MVP) ... SQL Server Agent Job Schedule and Job history ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 2000 linked server problem
    ... of SQL Server 2000 to fix some known bugs on the SQL Server side. ... the both two SQL Server instances (local and linked server), ... If Windows Firewall or other third party firewall is used, ...
    (microsoft.public.sqlserver.connect)
  • RE: addling linked server
    ... What is the target for the linked server? ... Is it SQL Server? ... what security context you need to have the queries run on your linked ...
    (microsoft.public.sqlserver.server)
  • Connectivity Problem for Expert
    ... Protocols enabled in the server and client: ... I create a new login in the SQL Server. ... EXEC sp_defaultdb 'DM001\testesql', 'Pubs' ... in 1 server the connection was made successfully ...
    (microsoft.public.sqlserver.connect)