Results 1 to 10 of 10

Thread: SQL-DMO and VB6

  1. #1
    Join Date
    Nov 2002
    Location
    Colchester, England
    Posts
    8

    Cool Unanswered: SQL-DMO and VB6

    Hi,
    I am trying to create a VB app that uses the SQL-DMO to run scripts on SQL Server 7 and 2000.
    The problem i have is that it will run a simple script fine but when a more complicated script is run with comments etc.. the ExecuteWithResultsAndMessages command gives syntax errors stating that there are invalid characters.
    But if i copy the String into Query Analyser and run it there is no problem.

    Have you got any ideas.

    Regards

    Peter

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What is the exact error message you receive ? What are executing with the ExecuteWithResultsAndMessages method ? Can you post some of your code ?

  3. #3
    Join Date
    Nov 2002
    Location
    Colchester, England
    Posts
    8
    There is a piece of code before this that reads in a Scripted Stored procedure, line by line and add this information to the FileInfo value which is a string.
    When the string that is in Fileinfo is copied into Query Analyser and run it executes perfectly. But the ExecuteWithResultsAndMessages call errors.
    I have been able to get the scripts to run using ExecuteImmediate with Continue on error selected, but this doesnt return any results of the script being run.
    The code i use is below.

    Length = Len(Fileinfo)
    Set oQueryResults = oDatabase.ExecuteWithResultsAndMessages2(Fileinfo, Messages, Length)

    Text1.Text = Text1.Text & "Messages" & Messages & vbNewLine
    Text1.Text = oQueryResults.Rows
    Set oQueryResults = Nothing


    Regards

    Peter

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    What is the error(s) that you receive ? You can loop through the errors collection to return multiple errors. What is the length of the file ? Have you been able to narrow down at what length it fails ?

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    How are these variables defined - Fileinfo, Messages, Length ?

  6. #6
    Join Date
    Nov 2002
    Location
    Colchester, England
    Posts
    8

    Cool Errors

    FileInfo
    is a String Which contains the Contents of the SQL Script that needs to be run

    Messages
    is a String which is used to store the messages returned from SQL when the command is completed

    Length
    is an Integer that holds the length of the FileInfo String. Which is generated usingl Length = LEN(FileInfo).

    The Errors that i received are Syntax Error near any GO commands which i managed to sure by stripping out any ANSI or QUOTED IDENTIFIER = ON, Lines in the String.
    Also It reports syntax errors on the last line of the string which i believe may be due to a carriage return.

    thank

    Peter

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    How long is the batch that is failing ? Have you tried varying the size to find a cut-off point ?

  8. #8
    Join Date
    Nov 2002
    Location
    Colchester, England
    Posts
    8

    Cool

    I am going to try varying the size, but that doesn't seem to explain why the GO Statements in the Stored Procedure are causing a Syntax Error.

  9. #9
    Join Date
    Dec 2002
    Location
    Madagascar
    Posts
    1

    Re: SQL-DMO and VB6

    Originally posted by pgolne
    Hi,
    I am trying to create a VB app that uses the SQL-DMO to run scripts on SQL Server 7 and 2000.
    The problem i have is that it will run a simple script fine but when a more complicated script is run with comments etc.. the ExecuteWithResultsAndMessages command gives syntax errors stating that there are invalid characters.
    But if i copy the String into Query Analyser and run it there is no problem.

    Have you got any ideas.

    Regards

    Peter
    Hi, I have developped a similar tool both for code generation VB/CFML/PHP and for various db admin tasks on SQL Server.

    Find attached here the main code that seems to have always
    worked for running scripts like the Query Analyser.
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2002
    Location
    Colchester, England
    Posts
    8

    Re: SQL-DMO and VB6

    Thanks :-)

Posting Permissions

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