August 19, 2004 at 12:38 pm
How do you specify multiple tables to the DMO script method (within a T-SQL s.p.) called by sp_OAMethod? Also how do you specify all tables in the database ?
The example below scripts the drop/create of 1 pubs table ("authors") but what is the syntax for specifying a list of tables? I've tried:
tables("authors, publishers")
tables("authors","publishers")
-----------------------------------------------
SET @ScriptType = '"'+CAST((
1 | -- DROP option
4 | -- CREATE option
64 -- don't return script to caller
-- (write to script file only)
) AS VARCHAR(80))+'"'
SET @Script2Type = '"0"' -- no scripting options
SET @ScriptFilePath = '"C:\Create_pubs_tables.sql"'
SET @ScriptExecString
= 'Databases("pubs").Tables("authors").Script '
+'('+@ScriptType+','+@ScriptFilePath+')'
EXEC @ErrorStatus
= sp_OAMethod
@objectSQLDMO
,@ScriptExecString
I'm sure this is probably easier to do in VBScript but I'd like to know how to do this in T-SQL.
Jeff
August 19, 2004 at 1:14 pm
I'd suggest making it work in VBS to start with, then porting it to TSQL if you have a reason (or interest) in doing so. As I remember you can only do one table at a time, you would need a loop to make it work - in either language.
August 19, 2004 at 2:16 pm
I didn't find anything on how to specify more than 1 table on a call to the script method (which may be impossible). But this doesn't matter since calling it within a table loop with arg ScriptType=74077 (whatever bitwise OR that results from I'll never know) and the same script output file will neatly append everything the T-SQL script for all tables into the same script output file. I found this on mssqlcity.com.
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply