Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: SQL-DMO VBSCRIPT Table Object

    Hi All

    I have a simple problem that I am sure a SQL-DMO guru can help me with. I want access a table object in a DTS package My problem is that I do not know how to get a reference to the table object that I want to process. For the sake of simplicity lets say that I want the vbscript to access the table object for a table called "MyTable".

    How do I get a reference to the table object for "MyTable"

    I have tried the following with no success.

    Function Main()

    Dim objDMO 'as SQLServer
    Set objDMO = CreateObject( "SQLDMO.SQLServer" )
    objDMO.loginsecure = true
    objDMO.Connect "(local)"

    Dim objDB 'As Database
    Set objDB = objDMO.Databases("Integration")

    Dim objTable
    objTable = CreateObject("SQLDMO.Table")
    objTable = objDB.Tables.Item("MyTable")

    msgbox objTable.Name

    objDMO.DisConnect
    Set objDMO = nothing
    Main = DTSTaskExecResult_Success
    End Function

    When I execute this script I get the following error message <snip ...>The name [MyTable] was not found in the tables collection. <..snip>.

    Please note that the table in question does exist in the database being accessed.

    Thanks in advance for all help.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    It's always worth trying something in VB if you are stuck.

    Dim objDMO As New SQLDMO.SQLServer
    Dim objDb As New SQLDMO.Database
    Dim objTable As New SQLDMO.Table
    objDMO.loginsecure = True
    objDMO.Connect "(local)"
    Set objDb = objDMO.Databases("master")
    Set objTable = objDb.Tables.Item("sysobjects")
    Debug.Print objTable.Name
    objDMO.DisConnect

    so your code should be

    Dim objDMO 'as SQLServer
    Set objDMO = CreateObject("SQLDMO.SQLServer")
    objDMO.loginsecure = True
    objDMO.Connect "(local)"
    Dim objDb 'As Database
    Set objDb = objDMO.Databases("master")
    Dim objTable
    objTable = CreateObject("SQLDMO.Table")
    Set objTable = objDb.Tables.Item("sysobjects")
    msgbox objTable.Name
    objDMO.DisConnect

    But the error you receievd should have been that the attribute wasn't found on the msgbox statement as hadn't set the object.

    The error you are getting implies that you have misspelt the table name or are not connected to the correct datatbase.
    Try using the profiler to see what is sent to the server and to which database.

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    -Solved

    Thanks for your response.

    FYI I was able to solve the problem though I still don't know what caused it. The table I was trying to access in my code is called ANHWP_Jobs. When I changed the code to select another table "sysfiles" it worked. So I renamed ANHWP_Jobs to anhwp_jobs and low and behold the script ran correctly. I changed the name back to "ANHWP_Jobs" and ran the script once more and again it worked. Perhaps I had entered the name incorrectly though I am sure that I doubled checked many times. -

    Oh well on to the next challenge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •