USE OF 'GO' in ADO SQL

[doco]

I am curious about why the use of 'GO' in a sql string used in an ADO Connection (from and Access 2007 VBA procedure) produces a 'syntax' error when using the very same string in SQL Server works perfectly.

Any ideas?

TIA

 

[Galaxiom]

Go is the instruction in SQL to execute a command that has been entered. It can be used between commands in a sequence to process then load the next command in the batch.

The ADO Recordset supports a single command. The Go is implicit and no further instructions are supported.

 

[Banana]

Actually, ADO (or rather OLEDB) as well ODBC does in fact support multiple statements. However, this setting is dependent on the driver having the support for it. Some drivers does indeed support but this may not be turned on by default. That is the case for SQL Server. The connection string should have this part:

MARS_connection=yes;"

Then the GO command will work and you can obtain multiple recordsets with one command.

For either DAO or ADO Recordset where a multiple statements is executed, you use .NextRecordset to get the next recordset. Be sure to read the documentation and be aware of how many recordsets you actually have because with ADO, it could be misleading (raises an error saying provider doesn't support multiple statements) if you call .NextRecordset too many times.

 

[Galaxiom]

I imagine these recordsets from MARS are forward only one step at a time?
And I suppose you get an error if the last step is already taken.

Can the steps be counted with VBA?

Is there a way to go back to the beginning or do you have to reload the recordset?

 

[doco]

Here is a brief

Use ThisDatabase GO -- syntax error from ADO

IF EXISTS( #temp_table ) DROP #temp_table GO

SELECT fields
INTO #temp_table
FROM various_tables
WHERE some_criteria

GO

IF EXISTS( this_table ) DROP this_table GO

SELECT fields_from_temp
INTO dbo.this_table
FROM #temp_table t1 LEFT OUTER JOIN
        #temp_table t2 ON
        t1.id = t2.id 
            and t2.additional_criteria LEFT OUTER JOIN
        #temp_table t3 ON
        t1.id = t3.id 
            and t3.additional_criteria
WHERE t1.some_criteria
ORDER BY what_ever

This is a psuedo-code of sorts but is the general idea of what I am attempting. It works using the script directly from enterprise manager but ADO throws a fuss from Access.

In the case above I am not want to return anything but to create a table to be used later. I will assume if the very first [GO] returns a syntax error in ADO, that each subsequent will as well.

 

[Banana]

I didn't test the details, but speaking from my MySQL experience:

1) No. It can be any valid recordset types.

2) Not sure what is meant here. You would obtain different recordsets into a single variable, then either break it out or advance:

Dim r As ADODB.Recordset
Dim r1 As ADODB.Recordset
Dim r2 As ADODB.Recordset
...

Set r1 = r.NextRecordset
Set r2 = r1.NextRecordset

r.NextRecordset

(untested, sample code)

But no, there's no PreviousRecordset or anything like that. You can at least contain different recordset in different variables but that's it.

Minor correction of my previous post: I saw DAO had the same method but looking closer at the documentation, I realized it's only available in ODBCDirect, which is being deprecated so it's pretty much an ADO thing.

 

[Banana]

This is a psuedo-code of sorts but is the general idea of what I am attempting. It works using the script directly from enterprise manager but ADO throws a fuss from Access.

But you don't need to call USE XXX in ADO. Database is already defined as a part of the connection string.

Also, to be explicit, you have to use connection for SQL Server, not via say, CurrentProject.Connection which would be using ACE provider and thus cannot process multiple statements.

In the case above I am not want to return anything but to create a table to be used later. I will assume if the very first [GO] returns a syntax error in ADO, that each subsequent will as well.

That's fine - as long the support for multiple statement is enabled, then you should be able to do that in a single Execute method.

 

[Galaxiom]

I imagine these recordsets from MARS are forward only one step at a time?

I'll clarify that. I meant that the sequence of recordset commands could only be moved through in a forward direction. Didn't mean "forward only" recordsets per se as Banana took it to mean.

It does seem a tidy way to make an array of recordsets from a simple construct that can be integrated with a series of events. So for example one could select a "before" recordset then use the next one to execute an update and so on to produce a sequence of commands and partial rollback steps in a very simple loop.

The fact that MARS is off by default might suggest there is a reason to only use it with caution. Wonder what it might be.

 

[Banana]

Sorry for the misunderstanding, Galaxiom. Yes, it's forward only for navigating between recordsets.

To be sure, I've never got a clear answer on why MARS is off by default when I understand that's actually encouraged to do similar things in other settings (e.g. programming in .NET for instance - I'm left with the impression they like "chunky" over "chatty" and thus if one can do multiple statements in one execution. Same is true for MySQL, but one has to turn on the option in the ODBC driver.

FWIW, I've used it with it on for remote MySQL for Access database with no apparent ill effects and in fact to better effect. For instance, I had better transaction support with than without.

Regarding the idea of "Before" and "After", just so one is aware - I understand that ADO also has means to filter between edited changes so one can see what was edited, and what was the original values so there's no need to have a "Before" and "After" per se, but that's not to say that one shouldn't use multiple statements.

I'd claim a more common use for multiple sets is to extract both master and detail recordsets in one big chunk. Afterward, it can be then filtered and sync'd locally without any further communication with the server.

 

[Banana]

Addendum: This article briefly discuss whether MARS is good or not.

Too brief for me, I'm afraid. I'm still trying to find something definitive on why MARS is not on default - that actually is one of my long standing question.

 

[Galaxiom]

I'm sure there is a lot to the detail but the basic explanation of what is going on was very clear. Thanks. Another day, another big aspect of databases I had never heard of before.

I think the "firehose" delivery section explains why MARS is not on by default. In the wrong hands it could really tie up server resources unnecessarily.

 

[doco]

Thanks guys. I have chosen a different route that works by creation of individual commands for each of the batches.

Private Sub cmdUpdateFirePatrol_Click()

    Dim conn As ADODB.Connection
    Dim sqlDropTemp As String
    Dim sqlDropCharges As String
    On Error GoTo EH
    
    
    sqlDropTemp = "IF EXISTS ( SELECT * FROM  tempdb.dbo.sysobjects o "
    sqlDropTemp = sqlDropTemp & "WHERE o.xtype IN( 'U' ) AND o.id = object_id( N'tempdb..#fpatrol_tmp' )) "
    sqlDropTemp = sqlDropTemp & " DROP TABLE #fpatrol_tmp; "
    
    sqlDropCharges = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
    sqlDropCharges = sqlDropCharges & "WHERE TABLE_NAME = 'fire_patrol_charges') "
    sqlDropCharges = sqlDropCharges & "DROP TABLE fire_patrol_charges;"
    
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Driver={SQL Server};Server=XXX.XXX.XX.XX;Database=DB;Uid=user; Pwd=pswd"
    conn.Open
    conn.Execute sqlDropTemp
    conn.Execute sqlDropCharges
    conn.Execute GetTempString      '   private function that returns sql
    conn.Execute GetChargesString   '   private function that returns sql
    conn.Close
    
TheExit:
    Set conn = Nothing
    On Error GoTo 0
    Exit Sub
    
EH:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "UPDATE FIRE PATROL CLICK()"
    GoTo TheExit
    
End Sub

Thanks