SQL-DMO Database object in VB 6 calling ExecuteWithResults() with batch of T-SQL
-
I have a Query Analyzer SQL Scripts to create a number of views that I would like to run in a VB 6 program. When using Query Analyzer all is well. However using a SQL-DMO Database object in VB 6 calling ExecuteWithResults(), I can only create one view at a time. IF EXISTS fails with a 0x8004009C syntax error. The GO between the CREATE VIEWs also fails. Can I call all of this as a batch some how, perhaps with a different API or Object or do I need to programatically only do one CREATE at a time with ExecuteWithResults?
IF EXISTS (SELECT 1 FROM information_schema.routines WHERE routine_name='v_coh') DROP VIEW dbo.v_coh
GO
CREATE VIEW dbo.v_coh
[parameters AS and SELECT removed so you get the big picture]
GO
IF EXISTS (SELECT 1 FROM information_schema.views WHERE table_name='v_ic_fifo_max') DROP VIEW dbo.v_ic_fifo_max
GO
CREATE VIEW dbo.v_ic_fifo_max
[more code removed so you get the big picture]
-
ExecuteWithResults expects a resultset to be returned so you can't execute this as a batch because the 'GO' signifies multiple batches.
You should be able to execute it using ExecuteImmediate and including the SQLDMOExec_NoCommandTerm ExecutionType constant. This ignores the command terminator in the script and executes it as a single batch.
--------------------
Colt 45 - the original point and click interface
-
I usually parse the script before running as well, as an error in a view can trigger errors in all views looking at that view, 1 errors easy to find, >1 is trickier. I also to split the scripts up between go's and log for each command that fails as vb strings don't cope with large scripts. You could do all of this with a regular ado connection and knowledge of the relevant stored procedures, dmo's just another api to learn (thats now redundant with sql2005) it just wraps the system sps anyway (the system sps dont tend to be different between versions) and like any code has a few bugs.
Phil Nicholas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply