November 27, 2002 at 5:45 pm
I'm interested in hearing if anyone has experienced particular problems implementing and using views that reference linked servers. Currently, we've got a bunch of SP's that have embedded selects/inserts/updates that use linked servers. These work just fine except when we need to move the SP's between Dev/Test/Prod or when the "linkee" table changes in some way. I'm hoping that the using views in this way will make these interfaces easier to manage. OTOH, I don't want to trade one set of headaches for another...
Thanks
November 28, 2002 at 5:43 am
We use linked servers and use them in stored procedures. We simply sript out all the sp's we plan to move and do a find and replace to change which linked server is being used when moving the SP's from dev to testing and then to production.
I don't know if you can or can't use them in views. If you can, then if you can reuse the view in more than one stored procedure you might reduce the number of SP's that need to be changed. You would then only have to make the changes when needing to modify the views.
I would only use views if they are indexed or all the tables referenced by them are small, otherwise you will have performance problems because SQL Server won't be able to take advantage of indexes to pull the rows from the views.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 28, 2002 at 6:31 am
If you point to logical linked server names (based on purpose) rather than physical names, then you would just have to know what logical linked servers you need and create them in the environment. For instance, if I know I needed to connect to a server providing payroll functions, I could use sp_addlinkedserver in the following manner:
EXEC sp_addlinkedserver
@server = 'PayrollServer'
, @srvproduct = ''
, @provider = 'SQLOLEDB'
, @datasrc = 'MyPhysicalServer'
GO
If in my code I refer to PayrollServer, I can create a linked server connection to any physical server I want and it means I don't have to change the code in my stored procedures and queries.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 2, 2002 at 5:49 am
Fortunately this happened only once to me and that went away. But the solution Brian states is a wonderfull thing. The only concern you need to be aware or if you licensing on your servers. If the are client liecenses you will be potentially using multiple licenses for the same server if a proc hits it for multiple reasons on different links then each generates a unique connection. If not then Brians is my preferred, otherwise Roberts is where I would go.
December 2, 2002 at 6:09 pm
Food for thought.
thank-you all...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply