| Author |
Topic |
|
sqlmyworld
Starting Member
38 Posts |
 Posted - 2012-07-06 : 00:27:27
|
| We have lots of linked servers (in SQL env) which connect remote db objects like procedures and tables. We need to move some databases from existing server SQL01 to new server SQL02. SQL01 is a linked server on other server SQL03 using @srvproduct=N'SQL Server' (not @srvproduct=N'OLEDB') that means server type is SQL Server not other datasource. SQL03 DB stored proc contains hard coded reference of SQL01. Questions: 1) Is there any way I can easily update all objects (like stored proc etc) that refers server SQL01?2) Is there any way the link server is called by inline function or something? So instead of changing server name in all stored proc we can change it to one place only.Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-07-06 : 01:08:29
|
| Do you really need to change the linked server name? I wouldn't. I'd just setup an alias on the server to redirect it to the proper server, that way you can keep the linked server name.What I would recommend going forward is using a generic name for the linked server and then always using an alias to redirect you to the proper place.To answer your question, you can let Management Studio script out the stored procedures, views, and functions using the generate script wizard. Then do a find/replace and execute the modified script. To answer your other question, you could do that and I know we are doing that on one specific system, well not through a function but I don't recall right now, but I certainly wouldn't recommend this method. I can lookup what we are doing when I return from vacation on Monday though.Why do you need linked servers though?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2012-07-06 : 02:20:22
|
| Tara, thanks for your quick reply.It's good to know that SQL Alias can be used. Just wondering, does sql alias work for selective redirect. I mean we are moving some databases from SQL01 to SQL02 server (not all db). So how SQL03 redirects calls using alias name and actual linked server name?For example, We have following two databases DB1 and DB2 on SQL01 serverSQL01.DB1.DBO.(Table1 to Table10)SQL01.DB2.DBO.(Table2 to Table20)Now DB2 database needs to move on SQL02 server. How SQL03 differentiate between DB1 and DB2 databases using actual linked server and Alias as both name would be "SQL01" in this example. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2012-07-10 : 00:43:14
|
| I have created two aliases S1 and S2 for sql servers SQL01 and SQL02 on SQL03 server. Now the issue is I can't call SQL Objects (stored procedure S1.DB1.DBO.SP1 or table S1.DB1.DBO.TABLE1) using aliase name from sql server SQL03. did I miss anything or did something incorrect? Should it work like the way linked server works, for example --(SQL01.DB1.DBO.table1)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2012-07-12 : 01:40:38
|
| Tara, I tested the SQL Aliase using Universal Data Link (UDL) file and the test connection was successful. I use below link to create UDL to test an aliase.http://sqladm.blogspot.com.au/2011/04/sql-alias.htmlBut when I use S1.DB1.DBO.Table1 from SQL03 server it gives following error. Msg 7202, Level 11, State 2, Line 2Could not find server 'S1' in sys.servers. Verify that the correct server name was specified.Even I can't connect SQL01 as an Aliase Name "S1" in SSMS. Got below error. Please note that - Named Pipe (NP) protocol is enabled in SQL Server Configuration Manager and SQL Server Service also restarted after enabling NP. -----------------------------Cannot connect to S1.A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)------------------------------The SQL03 and Aliase Server (SQL01) both have 64bit OS and also 64 bit SQL Standard Edition. I also check the registry entry at "\\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\" and it entry exists as Value Name - "S1" and Value data - "DBMSSOCN,SQL01,1433"Any thought? your valuable input will help a lot. |
|
|
|
sqlmyworld
Starting Member
38 Posts |
Posted - 2012-07-12 : 02:07:04
|
| Tara, dont worry about above post. Now I understand how exactly Aliase should work by reading AMIR QURESHI's comment on below Microsoft site.http://msdn.microsoft.com/en-us/library/ms190445(v=sql.105).aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Linked Server issue
Tara Kizer