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 |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2001-12-19 : 11:16:13
|
| Hiya!I'm developing an app. using SQL Server 7 (as the back end) for a company supporting approx. 200 unrelated clients, all in different locations. There is no LAN or WAN connection between them, so we'd probably need to use TCP/IP. The problem is as follows: We have dynamic reports in our app. which are run based on data in two tables. Whenever we add a new report we'd like to send it to all of our clients i.e. update their tables to reflect the additional rows of new report info. How would this best be handled, by DTS or Replication or BCP? And how could it be done with either?Thanks,Sarah |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-19 : 13:43:48
|
| If I understand this correctly, each of your 200 clients has an installation of the database you've created, each with their own data. And what you are trying to do is update their databases with "the latest version" which is your new report.If I'm correct, then it seems to me that you could simply script the INSERT/UPDATE statements either into a batch file using osql or into a stored procedure. Distribute an "update utility" that each client can run that will execute your script. If necessary, you could even write your utility to download the new script file from your web site.If I read it wrong, and you are hosting 200 databases at your site, then again I think some sort of scripted update tool would be worth the time invested to build it.I hope that helps (and that I'm not totally off-base in understanding your question).-------------------It's a SQL thing... |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2001-12-19 : 15:59:00
|
| Hiya!Yes, you did understand my problem very well. You well deserve your title as Aged Yak Warrior. However, just for variety, are there any other choices of how to do this?Thanks,Sarah |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-19 : 17:01:46
|
| Thanks for your kind words. Hopefully one of the other regulars will jump in with some ideas, too. In regard to Replication, BCP and DTS, here are a few thoughts...Replication: Not my area of expertise at all, so I might be way off, but my understanding is this is best suited for situations where you need instantaneous updates. I didn't get the sense that this describes your situation, but it would probably work. But, you will need some sort of connection to your clients' server. Since there's no LAN/WAN connection, it sounds like you'd be going across the internet. Then one issue that will come up is your clients' firewall. Hopefully they have one between their SQL Server and the outside world, and they probably won't want to open it up for you.DTS: Handy for moving information between SQL Servers, but still have the connection issue discussed above. Also, DTS package may have to be redefined each time because the items you're moving will change.BCP: This is a command line utility that is great for importing or exporting large chunks of data. You could use this similarly to the way I talked about osql above.You might also want to read this thread that talks about an undocumented stored procedure called sp_MSforeachdb: [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11586[/url] but I don't think it'll work for you because you're not hosting all the databases.-------------------It's a SQL thing... |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-28 : 14:55:16
|
| I just got around to taking care of this one. I am using BCP like this: BCP "SELECT * FROM ... " (all new rows) queryout filename -c ...to export the new reports, send the txt file to the client, and I wrote a little .bat file on the clients machine that the client can execute by passing it two filenames (there will be two filenames for every import- data is coordinated between two PK/FK tables) and a username and password. The .bat file will BCP the data back into the table. I don't think I'll be doing updates: If someone needs an update, I'll just delete and reinsert the data.Sarah Berger MCSDEdited: It cracks me up when I look back and see how clueless I was a year ago. But I guess next year I'll do the same.Edited by - simondeutsch on 11/28/2002 14:56:30 |
|
|
|
|
|
|
|
|
Easily updating remote databases?