Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mapperso
Starting Member
5 Posts |
 Posted - 2005-02-25 : 20:04:52
|
| I'm trying to query remote/linked servers using a variable name - effectively something like select * from @rem_svr.database.dbo.tableI know this is incorrect syntax, but I need something with the same end result.I've tried OPENDATASOURCE, but it doesn't accept variables as it's arguments. Also, Dynamic sql using "exec @sql_text" or the executesql procedure is not practical - the set of querries is very large and many of them are relatively big.Does anybody have ANY idea how to do this?Many Thanks,Mark |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-02-25 : 22:21:38
|
Does anybody have ANY idea how to do this?--The best idea is to NOT do this.Also, Dynamic sql using "exec @sql_text" or the executesql procedure is not practical - the set of querries is very large and many of them are relatively big.--Those are the other options for doing it.I know this is incorrect syntax, but I need something with the same end result.--You could (wouldn't recommend it), pass the @rem_svr into a query that changes the datasource for the linked server. You would then run the same statement all the time. Name a link server STUPIDIDEA, change the server it's pointing to each time by looking at @rem_svr, and run you code.--On the record though.....STUPIDIDEA!!!!I've been a DBA for.....hmmmm, let me see......11 years. Nope....can't think of a time I would have ever done this. Having said that, I'm sure you have a perfectly valid reason. Please share it.  It has to be something interesting.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
mapperso
Starting Member
5 Posts |
Posted - 2005-02-25 : 23:35:55
|
| Well, first off, I'm not a DBA - and that's likely a big part of the problem.We are transitioning from a proprietary DB system to MS-SQL, have roughly 45 sites spread across the globe and our biggest task is to ensure accurate and timely movement of data between sites. There is one "central" site that collects data from each of the remote sites and then redistributes specific partitions of data based on somewhat dynamic criteria.We've looked at transactional and merge replication, but neither meets all of the design requirements so the decision was made to implement custom replication. As terrible as this sounds, we've been able to develop reasonably efficient SQL that meets all the requirements and performs as good or better than Microsoft's pre-packaged replication - with the exception that we can't find a good way to reference multiple remote servers.I'm not sure I understand why having a variable server reference is a bad thing that should be avoided... ultimately, it's possible with dynamic SQL, just difficult to work with. And honestly, I can't see how having to write seperate SQL for each remote server is a good thing... it seems unmanageable and error prone to me.Also, the STUPIDIDEA hack won't work - there may be more than one parallel usage of the linked server name attempting to reference different servers - it would get pretty unstable.Thanks,Mark |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-02-26 : 00:36:13
|
| declare @server varchar(100)set @server='rem_svr.database.dbo.table'exec('select * from '+@server)MadhivananFailing to plan is Planning to fail |
|
|
|
mapperso
Starting Member
5 Posts |
Posted - 2005-02-26 : 09:19:14
|
quote:
Originally posted by madhivanandeclare @server varchar(100)set @server='rem_svr.database.dbo.table'exec('select * from '+@server)
Madhivanan, I'm tyring to avoid this - if the queries were simple it wouldn't be much of an issue. But most of them involve several joins, constants in quotes (which have to be carefully escaped), and are generally very long. This makes for a nasty looking string concatenation.Also, it's extremely difficult to debug this - for instance, I haven't found a good way to generate an execution plan for the dynamic SQL - all you get is an EXEC block - it doesn't seem to be able to embed the actual plan for the embedded query.The point of what I'm trying to do is re-use code, in this case, in the form of a stored procedure with an input variable being the remote server name. It almost seems as though I'm supposed to write 45 procedures to interface with the 45 servers  Is there any way to do something like that? Can I do something like this with a DTS package that accepts a remote server name as input?We've already looked at an external .Net solution, which could be more dynamic, but it imposes way to much overhead and isn't efficient enough.Thanks,Mark |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-26 : 09:55:15
|
quote:
We've looked at transactional and merge replication, but neither meets all of the design requirements
In what way(s) are they inadequate? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-02-26 : 10:21:02
|
quote:
all you get is an EXEC block - it doesn't seem to be able to embed the actual plan for the embedded query.
Well, that brings up an interesting item. The execution plans on this type of coding are never going to be efficient. SQL is a set-based language that build execution plans based on statistics for the most efficient way to move and manipulate sets of data. By using dynamic SQL, the engine doesn't ever know what it's going to be actually processing. As a result, it will need to find a new plan everytime it runs.quote:
The point of what I'm trying to do is re-use code
Let me rephrase what you said to be correct: "The problem of what I'm trying to do is re-use code". DO NOT!!!!! Download CodeSmith and come up with a naming standard. Have it autogenerate the code if you need to. SQL Server isn't an object oriented language. People think they're being brilliant and saving time by having reusable code. There are times when this can work. Trying to do what you are isn't one of them though.When NONE of the solutions out there will work for you, it should tell you something.....WHAT YOU ARE DOING IS WRONG!!!!! Your company isn't original enough that you need something nobody has ever created before. SQL is a set-based language, that best works against defined sets of data, going to defined delivery points. If you're going to create something that's "all things to all people", then you're better off creating the entire thing in .NET or some other language. It isn't going to run efficient anyway, so why bother putting it in SQL Server.Learn the language. Learn the technology. Implement. What you're doing now is just creating a support nightmare for the people unlucky enough to come after you.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2005-02-26 : 14:32:17
|
quote:
Originally posted by mapperso
Also, Dynamic sql using "exec @sql_text" or the executesql procedure is not practical - the set of querries is very large and many of them are relatively big.
Create a view on most of your complex "set of querries" and then use Dynamic sql using the view.Tim S |
|
|
|
mapperso
Starting Member
5 Posts |
Posted - 2005-02-26 : 14:47:58
|
quote:
Originally posted by robvolk
quote:
We've looked at transactional and merge replication, but neither meets all of the design requirements
In what way(s) are they inadequate?
The majority of data originates from geographically remote linked servers and is then aggregated into a central server. Last year the total row volume aggregated across all sites was about 200 million and we historically have had a growth rate of 100% year over year. The pipes between the remote and central site are not very large and the bw allocation for this application is a small percentage...We are also required to copy specific (and often dynamic) partitions of data from the central site to remote sites (iow, a horizontal partition of rows from remote site 1 may need to be copied to remote sites 3 and 7). Also, when the dynamic "filter" rules change, we have to ensure that not only new data that meets the row filter is replicated, but also some certain amount of historical data.Transactional replication doesn't work well because partitioning is static, and modifying the filter would require dropping subscribers, modifying the publication, and then re adding subscribers - this also implies to me that no row changes could happen anywhere, or that subscribers would need to be re-initialized.Merge replication with a dynamic horizontal filter seems more flexible, but the merge costs may be unacceptable - I was told this and haven't experienced it myself. Also, I'm not sure that the historical data would be taken care of automatically.That's the short explanation Thanks,Mark |
|
|
|
mapperso
Starting Member
5 Posts |
Posted - 2005-02-26 : 15:48:56
|
quote:
quote:
all you get is an EXEC block - it doesn't seem to be able to embed the actual plan for the embedded query.
Well, that brings up an interesting item. The execution plans on this type of coding are never going to be efficient. SQL is a set-based language that build execution plans based on statistics for the most efficient way to move and manipulate sets of data. By using dynamic SQL, the engine doesn't ever know what it's going to be actually processing. As a result, it will need to find a new plan everytime it runs.
Yeah, and I'm still trying to work with a set of data - just not one that's local. So are you implying that statistical data and schema information is kept up to date for linked servers? Also, unless I'm missing something, with respect to execution plan caching, dynamic SQL shouldn't be any different than an ad hoc query.quote:
quote:
The point of what I'm trying to do is re-use code
Let me rephrase what you said to be correct: "The problem of what I'm trying to do is re-use code". DO NOT!!!!! Download CodeSmith and come up with a naming standard. Have it autogenerate the code if you need to. SQL Server isn't an object oriented language. People think they're being brilliant and saving time by having reusable code. There are times when this can work. Trying to do what you are isn't one of them though.
This is an interesting idea and we actually thought about doing it ourselves... I'm not familiar with Codesmith, so I'll take a look and see how well it can be integrated.quote:
When NONE of the solutions out there will work for you, it should tell you something.....WHAT YOU ARE DOING IS WRONG!!!!! Your company isn't original enough that you need something nobody has ever created before. SQL is a set-based language, that best works against defined sets of data, going to defined delivery points. If you're going to create something that's "all things to all people", then you're better off creating the entire thing in .NET or some other language. It isn't going to run efficient anyway, so why bother putting it in SQL Server.Learn the language. Learn the technology. Implement. What you're doing now is just creating a support nightmare for the people unlucky enough to come after you.
That sounds like more of an opinion than anything... or maybe innovation is dead, or should be, or something like that... You know, you could have just said SQL can't handle it  And I still don't get the point - there are SO many other stupid things you can easily do to yourself, solving the problem with dynamic SQL being one, that I can't fathom how a dynamic server reference could make things worse - worse being very subjective at this point.Thanks,Mark |
|
|
|
|
|
|
|
|
Variable Remote Server Names