Re: Linked server strangeness in SQL 2005 Mgmt Studio

David and Charles,

OK, I think I see what is going on here. Here is how to correlate what the
two of you are seeing. Suppose that I create a linked server with this
command.

EXEC master.dbo.sp_addlinkedserver @server = N'TalkToOtherDB', @srvproduct =
N'',
@provider=N'SQLNCLI', @datasrc=N'SQL001', @catalog=N'OtherDB'

When from Object Explorer you Script Linked Server As ... Create To you get
the following script which does not specify @srvproduct and will return the
error "'(null)' is an invalid product name." Therefore, there is an
inconsistent behaviour between the data that you can script into SQL Server
and the inaccurate script generated by SQL Server Management Studio.

EXEC master.dbo.sp_addlinkedserver @server = N'TalkToOtherDB',
@provider=N'SQLNCLI', @datasrc=N'SQL001', @catalog=N'OtherDB'


If I do the following select, you can see that sysservers.srvproduct is not
NULL.
SELECT name AS srvname, COALESCE(product, '*******') AS srvproduct
FROM sys.servers WHERE name = 'TalkToOtherDB'

srvname srvproduct
------------- ----------
TalkToOtherDB

So, even though the srvproduct was correctly set to a Zero-Length String,
the scripting is treating that string like a NULL and is not including the
@srvproduct in the script. (You have probably also found that you cannot
type a blank or a long string of blanks into the New Linked Server window,
since it will complain about NULL.)

Charles's example is where he actually typed, not a blank, nor a zero-length
string, but two single-quote characters. That explains why his example
script includes: @srvproduct=N''''''

Are we now on the same wavelength? (David, if you go back to setting
@srvproduct = 'SQL' (but not 'SQL Server') or even @srvproduct = 'David', it
should script properly.)

RLF



"Charles Wang[MSFT]" <changliw@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:6SYlxly2HHA.5204@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi David,
I collaborated with my colleague to perform a more careful test and we
reproduced one of your described issues.

As you mentioned that if we input "SQL Server" as the "Product Name", once
we opened the linked server properties window, "Server type" was changed
to
"SQL Server". This issue was reproduced at my side. I believe that this
issue should be caused by "Product Name". SSMS must recognize server type
by "Product Name". You can confirm this by creating a linked server with
"SQL Server" as "Server type" and then generating the linked server script
by "CREATE TO"->"New Query Editor Window". You will find that they both
have "@srvproduct=N'SQL Server'".
I will confirm this issue with our product team and if there is any
response, I will let you know. However sometimes the process may need a
long time, I recommend that you just mail me
(changliw_at_microsoft_dot_com) a response so that I can timely update
you.

Unfortunately we have not been able to reproduce the other issues as you
mentioned. Per my test, even the "Server type" was changed to "SQL
Server",
the linked server query still worked fine. I would like to check with you
what your linked server Security settings were. In my test environment,
the
two SQL Servers machines were in the same domain and I selected "Be made
using the login's current security context" as the Security option. After
that, I added the current login account to the logins of the linked server
and assigned it sysadmin permission for test.
Then the following query succeeded:
"SELECT * FROM [Charles-SQL2K].Northwind.dbo.Employees"

For the sp_addlinkedserver script omitting Product Name, I also did not
reproduce it. BLANK and SPACE are forbidden to the Product Name field when
I created the linked server. Per your suggestion, I just input '' as the
Product Name. After I created the linked server, the above query could
work. If I selected "Script Linked Server as" -> "CREATE TO" -> "New Query
Editor Window". The following statements were generated:
----------------------------------------------------------------------------
-------------------------------------
/****** Object: LinkedServer [CHARLES-SQL2K] Script Date: 08/10/2007
16:26:15 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CHARLES-SQL2K',
@srvproduct=N'''''', @provider=N'SQLNCLI', @datasrc=N'CHARLES-SQL2K'
/* For security reasons the linked server remote logins password is
changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'CHARLES-SQL2K',@useself=N'True',@locallogin=NULL,@rmtuser=NULL
,@rmtpassword=NULL
----------------------------------------------------------------------------
------------------------------------
As you can see, @srvproduct=N'''''', the Product Name was not omitted.

I am not sure if my test steps were completely same as yours. If there was
anything different, please feel free to point out. Also, for letting us
better understand your issue and further research, could you please mail
me
some screenshots of the issues? Your cooperation will be greatly
appreciated.

If you have any other questions or concerns, please feel free to let us
know.

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: jobs for a linked server in a different domain?
    ... Some time last week I had tried to adjust the security settings for the ... linked server but that presumes that there is a username and password on the ... > Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Linked Servers - HELP GEORGE or HILARY
    ... Do you mean the account that sql server is using to connect to ... > indexing service needs to be a dbo, or do you mean that the login the we ... We have set the security options on the ... > linked server to connect to indexing server using the sql login's current ...
    (microsoft.public.inetserver.indexserver)
  • Re: Data Migration
    ... My first thought was to use a linked server (where the data I want to migrate ... the keys and constraints on the new db. ... I already have a script that will make the old server structure like the ... "David Gugick" wrote: ...
    (microsoft.public.sqlserver.tools)
  • Re: Creating a linked server to DB2 within SQL Server Management S
    ... EXEC master.dbo.sp_addlinkedsrvlogin ... Please right click on the Linked Server definition and Script Linked Server ... Microsoft SQL Server Management Studio ...
    (microsoft.public.sqlserver.connect)
  • Re: Manual Lookup table to Query Table
    ... add a linked server to Excel, script a new SQL table and select * into.... ...
    (microsoft.public.sqlserver.programming)
Loading