RE: Linked server strangeness in SQL 2005 Mgmt Studio

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=NULL,@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.
======================================================

.

Relevant Pages

  • Re: Updating SQL Server database tables with vbscript
    ... I think you'll need to post the script before anyone will have a clue. ... | We have a vbscript to update SQL Server database tables from text files. ...
    (microsoft.public.windows.server.scripting)
  • Re: error using DTS to transfere a job from one server to another
    ... Either Right Click | All Tasks | generate Script on the Job Icon ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ...
    (microsoft.public.sqlserver.dts)
  • Re: help :Deploying a preconfigured database
    ... What about the whole database structure and rights to table and strore ... server and have at the end a traight forward running database copy of what ... Transfer Logins and Passwords Between Instances of SQL Server ... > You will want to make sure that you create appropriate script files to set ...
    (microsoft.public.sqlserver.setup)
  • DTS Package execution fails Error (800403FE)
    ... The script worked fine on our development server. ... DB Provider for SQL Server Error Description: ... IF currentItem = rsTHEN ...
    (microsoft.public.sqlserver.dts)
  • 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)