SQL-DMO (Active script within job step) Problems with Script statement 
Author Message
 SQL-DMO (Active script within job step) Problems with Script statement

I'm attempting to create job step that will create a single file for each
database on the server . Each file will contain the transact SQL statements
to completely regenerate the database.  I'm trying to use SQL-DMO statements
active script (Visual Basic script).

this is the code I've arrived at.
*************************************************************************

dim scripttype
dim objServer
dim objDatabase

Set objServer = CreateObject ( "SQLDMO.SQLServer" )
objServer.Connect".","sa" ,"??????"

 For Each objDatabase in objServer.Databases
 objDatabase.Script  SQLDMOScript_AppendToFile, "f:\dmo_test\" &
objDatabase.Name & ".sql"
 print " DB " &  objDatabase.Name
 For Each objTable in objDatabase.Tables
  objTable.Script SQLDMOScript_AppendToFile, "f:\dmo_test\" &
objDatabase.Name &  ".sql"
  print " TB " &  objTable.Name
 Next
Next
objServer.Disconnect
Set objServer = nothing

****************************************************************************
***

Currently the a file gets created with the Database name and script but gets
cleared by the "objtable.Script" statement . The table name print  confirms
that each table is being accessed.

Does any one have a sample of SQL-DMO code (Visual Basic Script) that uses
the Script statement and the associated arguments as it would be used within
a job step.

I know I'm doing something stupid . I just hope that one of you gurus can
help.

Thank You

Dave Gall
Title: Programming Leader

phone: (604)691-6481
fax: (604) 691-6237



Mon, 09 Dec 2002 03:00:00 GMT
 SQL-DMO (Active script within job step) Problems with Script statement

Hi,  i'm also facing the same problem when i write the script to file.The
file gets created but no content.But if i assign whole statement to a
variable(ie x=objTable.Script) i can see the script generated,but it is not
writing to file.What i did is open a file and write the variable (ie x) to
specified file,like normal file operation.I'm not sure ist it a good
method.If anybody knows the solution,please help. Thanx .



Quote:
> I'm attempting to create job step that will create a single file for each
> database on the server . Each file will contain the transact SQL statements
> to completely regenerate the database.  I'm trying to use SQL-DMO statements
> active script (Visual Basic script).

> this is the code I've arrived at.
> *************************************************************************

> dim scripttype
> dim objServer
> dim objDatabase

> Set objServer = CreateObject ( "SQLDMO.SQLServer" )
> objServer.Connect".","sa" ,"??????"

>  For Each objDatabase in objServer.Databases
>  objDatabase.Script  SQLDMOScript_AppendToFile, "f:\dmo_test\" &
> objDatabase.Name & ".sql"
>  print " DB " &  objDatabase.Name
>  For Each objTable in objDatabase.Tables
>   objTable.Script SQLDMOScript_AppendToFile, "f:\dmo_test\" &
> objDatabase.Name &  ".sql"
>   print " TB " &  objTable.Name
>  Next
> Next
> objServer.Disconnect
> Set objServer = nothing

> ****************************************************************************
> ***

> Currently the a file gets created with the Database name and script but gets
> cleared by the "objtable.Script" statement . The table name print  confirms
> that each table is being accessed.

> Does any one have a sample of SQL-DMO code (Visual Basic Script) that uses
> the Script statement and the associated arguments as it would be used within
> a job step.

> I know I'm doing something stupid . I just hope that one of you gurus can
> help.

> Thank You

> Dave Gall
> Title: Programming Leader

> phone: (604)691-6481
> fax: (604) 691-6237

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 25 Dec 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Active Script within a Job Step

2. Script size problem when executing an Active Script job

3. Active Scripting Job Steps

4. Active Script Job Step

5. Job steps with Active Scripting

6. Ststus from Active Script Job Step

7. Active Script step in Agent Job - No errors?

8. Write an active script Job Step

9. Active Script Job Step

10. Active Scripts within Jobs and CDO

11. Problem with an ActiveX script as a job step in SQL Server Agent

12. DTS +Active VB Script +Active x scripting host error (BETA3)


 
Powered by phpBB® Forum Software