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 |
|
magictech
Starting Member
44 Posts |
 Posted - 2003-06-12 : 15:57:21
|
| I'm trying to run a TQSL script against a SQL Server 2000 database using SQL-DMO and Vbscript. The output of this query would be use to populate a table in another SQL Server database on a different server. Has anyone use SQL-DMO to connect SQL Server and run a t-sql script against a database and use the output to populate a table on a different server? Any informatiom would be helpfull.Regards Magictech |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-12 : 16:20:34
|
| Why do you need to use SQL-DMO and Vbscript? To populate a table in another database on another server just use linked servers.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-12 : 17:04:40
|
| Or look at openrowset.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
magictech
Starting Member
44 Posts |
Posted - 2003-06-12 : 20:42:05
|
| Thanks for your responds Tara. The reason I would like to use Vbscript and SQL-DMO is because this procedure has to be very dynamic and would be use on an ongoing basis. The purpose of the script is to collect metadata from our SQL Server databases (one at a time) and populate another database that's suppose to be the backend to some application that manages our metadata . The rule of the vbscript is to collect the following information: server name,userid ,password and database name from the individual executing the script and pass it over to the SQL-DMO (objects and methods). The SQL-DMO will then make a connection to the SQL Server database and run the T-SQL script (I already have the T-SQL script)against the specified database. The T-SQL scripts only against the system tables of a particular database. The SQL-DMO would then take the result-set from SQL Server and either copy it directly to the target database or store it on a text file on the OS. If the result set is save on the text file, I would then use bcp to populate the target database.Actually, I already have most of the script except for the part were -SQL DMO make connection to SQL server, runs the T-sql script and get the result set back and either populate the table directly or save it on a text file. Any help would be greatly appreciated.Regards |
|
|
|
SQLScripter
Starting Member
2 Posts |
Posted - 2003-07-08 : 12:39:21
|
| Hi !I don't know if i can help you, but i write a tool which script your data in T-SQL format. The result is readable and executable in Query Analyzer, so it's easy to deploy the result later on your target server.It's Freeware.[url]http://www.sqlscripter.com[/url]Regards |
|
|
|
|
|
|
Using SQL -DMO and Vbscript against SQL Server