Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: SQL Limitations with ADO

    Does anyone know the limitations on the SQL that can be used with ADO,
    and if so is there anyway round them like running procedures directly in MS SQL,

    Ill explain what im doing

    what im trying to achieve is to send SQL Statements(which are written in text files) to MS SQL to update the current information which we are working on, the reason for doing this is that not everyone in the office is comfartable with using SQL so can use a excel as a batch file to run the correct series of SQL Statements

    So far my code works fine with the simple statements but when i try doing anything more complex it returns an error

    Ill Post my code if anyone would like it


    Dave

  2. #2
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    What is the error, and is it produced by Excel or SQL Server (I assume that's what you mean by MS SQL)?
    Make something idiot proof and someone will make a better idiot...

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    What im getting is
    Error -2147217900
    Method execute of object '_ Command failed

    the process works with simple querys such as

    SELECT COUNT(*) FROM Table

    but when more complicated SQL is being used this error occurs
    and yes i do mean SQL Server the code works fine when running from SQL Server but when im trying to pass through the code this error occurs on the command.Execute statement,

    My thoughts on this were that the SQLOLEDB Engine couldn't handle it as i was trying to pass through the query externally.

  4. #4
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    It looks like the problem is with Excel, as this doesn't look like a SQL Server error. Could you post one of the queries that fails with this error, along with the exact code that is executing the query?
    Make something idiot proof and someone will make a better idiot...

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Sure but this is going to be long

    This is the Code for Getting access to SQL Server
    I havnt bothered putting up the code for getting information from the textfile or the lookup for the xl file
    Code:
    Sub GetConnection(ByVal strSQL As String, ByVal RowNum As Integer)
       Dim conSQL As ADODB.Connection
        
        
        'SQL String will later replace this with a function further Down to
        'Extract Data either from text files or via Excel's cells
    
        
        'set the properties and connect to the MS SQL Server
        Set conSQL = New ADODB.Connection
        conSQL.Provider = "SQLOLEDB"
        conSQL.Properties("Data Source").Value = "COTSVALESQL" 'SERVER Name
        conSQL.Properties("Initial Catalog").Value = "keyBaseData" 'DatabaseName
        conSQL.Properties("Integrated Security").Value = "SSPI" 'Use Windows Authentication
        
        conSQL.Open
        
        Debug.Print conSQL.Properties("SQL Support").Type
        'Execute the SQL Command Specified Above
        Set cmdCheck = New ADODB.Command
        Set cmdCheck.ActiveConnection = conSQL
        cmdCheck.CommandText = strSQL
        
        'Execute the SQL Command and Trap any errors that Occur
        Set rstCheck = New ADODB.Recordset
        On Error GoTo Err_Command
        cmdCheck.Execute
        On Error GoTo 0
        
            
        'Clean up and leave nothing open
        rstCheck.Close
        conSQL.Close
        Set conSQL = Nothing
        Set rstCheck = Nothing
        
        Exit Sub
        
    Err_Command:
    
        Dim ErrNo As Error
    
           ' Notify user of any errors that result from executing the query
              MsgBox "Error number: " & Err.Number & vbCr & _
                 Err.Description
    
       
        ' clean up
        
        If Not conSQL Is Nothing Then
            If conSQL.State = adStateOpen Then conSQL.Close
        End If
        Set conSQL = Nothing
        
        Cells(RowNum, 5).Value = "not Processed"
        Cells(RowNum, 6).Value = "Stopped Processing at " & Now() & "due to SQL Error"
        End
    End Sub
    and the SQL Code

    Code:
    /* This Query will update the KeyBaseData Sunrise Database */
    
    -- Sunrise
    -- [VIEW_SunriseReferrals]
    -- [VIEW_Tmp_CMORD]
    -- [VIEW_TmpCMTASK]
    -- 
    -- 
    
    -- Setup the database.
    use KeyBaseData
    go
    set dateformat dmy
    go
    
    /* SUNRISE [VIEW_SunriseReferrals] */
    -- First remove the existing data.
    Print 'Deleting the old year-to-date [VIEW_SunriseReferrals] data...'
    go
    Delete 
    from KeyBaseData..[VIEW_SunriseReferrals]
    where AdmitDTM >= '1/04/2003'
    go
    
    -- Now to try and up-date the data.
    Print 'Copying the new year-to-date [VIEW_SunriseReferrals] data...'
    go
    Insert into KeyBaseData..[VIEW_SunriseReferrals]
    select *
    from RIKNT05.PCTDataMart.dbo.[VIEW_SunriseReferrals] S
    where S.AdmitDTM >= '1/04/2003'
    go
    
    
    /* SUNRISE [VIEW_Tmp_CMORD] */
    -- First remove the existing data.
    Print 'Deleting the old year-to-date [VIEW_Tmp_CMORD] data...'
    go
    Delete 
    from KeyBaseData..[VIEW_Tmp_CMORD]
    -- No Date field available for part up-date
    go
    
    -- Now to try and up-date the data.
    Print 'Copying the new year-to-date [VIEW_Tmp_CMORD] data...'
    go
    Insert into KeyBaseData..[VIEW_Tmp_CMORD]
    select *
    from RIKNT05.PCTDataMart.dbo.[VIEW_Tmp_CMORD]
    -- No Date field available for part up-date
    go
    
    
    /* SUNRISE [VIEW_TmpCMTASK] */
    -- First remove the existing data.
    Print 'Deleting the old year-to-date [VIEW_TmpCMTASK] data...'
    go
    Delete 
    from KeyBaseData..[VIEW_TmpCMTASK]
    where SignificantDTM >= '1/04/2003'
    go
    
    -- Now to try and up-date the data.
    Print 'Copying the new year-to-date [VIEW_TmpCMTASK] data...'
    go
    Insert into KeyBaseData..[VIEW_TmpCMTASK]
    select *
    from RIKNT05.PCTDataMart.dbo.[VIEW_TmpCMTASK]
    where SignificantDTM >= '1/04/2003'
    go

  6. #6
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    I don't see anything wrong with either, but I guess it could be that the exchange of data from a text file to the commandtext of the command object is causing the problems.

    One thing that does strike me though, is why you don't write the SQL as stored procedures and call the stored procedures from VBA? That way you don't have any kind of intermediary files.
    Make something idiot proof and someone will make a better idiot...

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    not my decsion, This is how my boss wanted the process to go as the SQL is changing all the time to reflect the current buisness need (never out of development), im new to SQL (though i know a bit of vba),
    The data gets read into the text file fine enough (i checked this out myself) and this is one of the simpilar pieces of SQL it has to deal with, back to the drawing board i think, and ill look into running stored procedures and speak to my boss about that,
    But Cheers for your input
    Dave

  8. #8
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Maybe you could test the theory by creating a stored procedure and calling it from VBA. If there were no problems with it, that way you could prove that there was a problem with the mechanism rather than the code.
    Make something idiot proof and someone will make a better idiot...

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Will do, time to teach myself how to create a stored procedure
    Ill let you know how it goes

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Just a quick update on where i am with this,
    I think a set of stored procedures will work, but ive figured out my problem (at least i think ive figured it out)

    what is causing the problems is the use of GO, which is not a proper Transact-SQL Statement From Books on Line
    Quote Originally Posted by Books On Line
    Applications based on the DB-Library, ODBC, or OLE DB APIs receive a syntax error if they attempt to execute a GO command. The SQL Server utilities never send a GO command to the server.
    What i think ill have to do is read in the text file and find the words "GO" and split the statements up into chunks to send to SQL,
    Im Still looking at stored Procedures as well best to get multiple soloutions to the same problem ill post my revised code once done (assuming it works)

Posting Permissions

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