Inconsistant VB/DMO results in run script action

FinalBuilder Discussion
1

I am using FB to try to script out SQL Server jobs, check in/out of source control, etc.    I'm using VB and the SQL Server DMO library for this purpose.   The issue I have is that within the run script action, in the script editor, I am getting conflicting behavior between what telesense indicates and runtime behavior.   Looking at the code sample below,  you will see the line 'Category = Job.Category'... This code works so, clearly, 'category' is a valid object in the 'job' collection.   However, in the run script action script editor, if I type 'job.' to see the telesense list of valid objects and methods in the collection, 'Category' is not listed.    Can anyone explain what the issue is with this?

Similarly, I have seen other VB\DMO code samples with job.jobname where 'jobname' is a member fo the job collection.   However, again, telesense does not list it.   Unlike the the 'category' object, including job.jobname in the code causes a run-time error and an error message indicating that 'jobname' is not part of the collection.   I'm not sure why code samples I've been looking at seem to support different collections.  Is it a VB version issue?  

Suggestions please?

Clive Richardson

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


Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oSQLServer = CreateObject("SQLDMO.SQLServer2")

oSQLServer.LoginSecure = True
oSQLServer.Connect "SQL1"

idStep = 1
For Each job in oSQLServer.JobServer.Jobs
       Category = Job.Category   ' not included in telesense but works at runtime.
       if Category = "Application" then


           'JName = job.JobName

           JName = oSQLServer.JobServer.Jobs.Item(idStep).Name
           JName = replace(JName, " ","_")
           JName = replace(JName, ":","")
           JName = replace(JName, ".","")
           JName = replace(JName, ",","")
           jName = BaseDirectory & "\Target_Server_Job_Scripts\" & JName & ".sql"
           Set oFSOWrite = oFSO.OpenTextFile( JName, ForWriting, True)

           oFSOWrite.WriteLine job.script

           oFSOWrite.Close

       end if
       idStep = idStep + 1
Next

2

Hi Clive

I’m no expert in DMO, however there are some differences between VB and VBScript that means it’s not a given that code that worked on VB will work on VBScript. One of the biggest issues is that some com objects simply do not work using late binding (via IDispatch) under VBScript. Also, VBScript often just does not know about constants declared in the typelibrary of com objects. If in doubt, use the values of the constants rather than the names. We try to work around this for the common types in FinalBuilder by registering the constants with our active scripting implementation, but obviously cannot do that for every possible type library.

Intellisense (or code completion) in the script editor in FinalBuilder is driven by the type libraries you use. When the parser sees a reference to CreateObject, it parses the ProgID (in this case SQLDMO.SQLServer2) and then finds the type library and used microsofts tlbinf type liibrary parser object to extract the type information from the library and add it to our internal code completion library. While some methods may be visible from the type library they may not be automatable (via IDispatch), and we are not able to detect that.

It’s also possible if there are multiple versions of type libraries we may not be able to load the correct one (depends what information is available in the registry) and it’s possible a different version to the one you are referencing is loaded.

So to sum up, the code completion is there as an aid… but not as a definitive reference, so you should rely on the doco for the library you are using rather than what code completion says.

HTH


3

Vincent,

Thankyou for your explanation.   In terms of a potential solution, what would you advise?   For example, code it up in Visual Studio using VB and produce a .exe that can be run from within FB?

Thanks,

Clive