| Groups | Blog | Home |
sql server connect : Linked server strangeness in SQL 2005 Mgmt Studio
messing with a linked 2000 server default instance. Maybe this isn't supported. :-) In the Object Explorer, go to the Linked Servers item, right-click and select "New Linked Server". In the "Linked Server" box, give the name of a SQL 2000 server that has a default instance. For Server Type, check "Other Data Source", and "Microsoft OLE DB Provider for SQL Server" as the Provider. I have to put a BLANK in the Product Name box. (If you use SQL Server for the Product Name, then then the next time you go into the dialog, the Server Type will have changed to SQL Server.) For the Data Source, use the server name again. Set up anything you need in the Security page. This linked server will then work to get data from the server. BUT, if you right-click on the server definition in Management Studio and script the server to a new query editor window, you'll end up with an sp_addlinkedserver command that omits the Product Name, therefore the created script won't run. If I select "SQL Server" for this linked server, instead of doing it the way I described here, then I get a "login failed" message when I try to use the linked server.
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. ======================================================
Hi DWalker,
Just check with you to see if the suggestions were helpful. If you have any questions or concerns, please feel free to let me 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. ======================================================
changliw@online.microsoft.com (Charles Wang[MSFT]) wrote in
news:dmUlJK91HHA.6140@TK2MSFTNGHUB02.phx.gbl: [quoted text, click to view] > 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. ====================================================== >
changliw@online.microsoft.com (Charles Wang[MSFT]) wrote in
news:dmUlJK91HHA.6140@TK2MSFTNGHUB02.phx.gbl: [quoted text, click to view] > 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 > You can't put blank (or nothing) as the product name in the GUI, but you can use '' as the name in the sp_addlinkedserver procedure.
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. ======================================================
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 [quoted text, click to view] "Charles Wang[MSFT]" <changliw@online.microsoft.com> wrote in message news:6SYlxly2HHA.5204@TK2MSFTNGHUB02.phx.gbl... > 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. > ====================================================== > > > > > > >
Hi RLF,
Thank you for your additional comments. Just clarify that I indeed tried inputing BLANK or zero-length string or one or more spaces into the Product Name via SSMS (not by directly running T-SQL script), however SSMS popped up a message window to tell me " '(null)' is an invalid product name.", so I could not finish creating the linked server via SSMS. Later as David mentioned, I input '' to the product name field and the linked server was created successfully and it worked fine. However there seemed to be some differences from David's. That is why I would like to get some screenshots from David. It is a better way to let us know what the real problem that David encountered was. Hi David, You can directly mail the screenshots to me. If the screenshots are not large, you can attach them with your response to this post via Outlook Express so that other community members can see it in Outlook Express. Look forward to your response. 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. ======================================================
Charles,
I understand. I just thought from the conversation that maybe you were using '' as typed into the GUI and David was using '' as typed into an sp_addlinkedserver parameter. (Which is a different thing of course.) Having said that, I consider it an SSMS bug that I can create a linked server with a non-Null, zero-length character string name, but SSMS will not script out that linked server the same way it was created. RLF [quoted text, click to view] "Charles Wang[MSFT]" <changliw@online.microsoft.com> wrote in message news:IXVO1GU3HHA.4100@TK2MSFTNGHUB02.phx.gbl... > Hi RLF, > Thank you for your additional comments. > > Just clarify that I indeed tried inputing BLANK or zero-length string or > one or more spaces into the Product Name via SSMS (not by directly running > T-SQL script), however SSMS popped up a message window to tell me " > '(null)' is an invalid product name.", so I could not finish creating the > linked server via SSMS. > > Later as David mentioned, I input '' to the product name field and the > linked server was created successfully and it worked fine. However there > seemed to be some differences from David's. That is why I would like to > get > some screenshots from David. It is a better way to let us know what the > real problem that David encountered was. > > Hi David, > You can directly mail the screenshots to me. If the screenshots are not > large, you can attach them with your response to this post via Outlook > Express so that other community members can see it in Outlook Express. > > Look forward to your response. > > 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. > ====================================================== >
Hi RLF,
Thanks for your feedback. I understand your meaning. For your concern, "Having said that, I consider it an SSMS bug that I can create a linked server with a non-Null, zero-length character string name, but SSMS will not script out that linked server the same way it was created.", I think that it should be a by design enhanced security feature of SSMS. Let us wait for David's response for further research. 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. ======================================================
Hi David,
We have not received your response. Could you please let us know the issue status? If you need further assistance, please feel free to post back. We are very glad to work with you for further research. 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. ======================================================
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |