Issue updating SQL 2000 DB with "No Count" turned on

Question

Issue updating SQL 2000 DB with "No Count" turned on

Rich Hansell

SSCrazy

Points: 2346
More actions
November 22, 2006 at 9:31 am

#368081

HI Guys/gals.. I have a developer who has written a bunch of apps that use Access to update a SQL back end.. we are moving them to a controlled environment where we have No Count set on.. this breaks his apps here is an example.. Anyone know any work arounds?
-----
Option Compare Database
Option Explicit
Const strDBConnection = "PROVIDER=SQLOLEDB;DATA SOURCE=QADBSVR;Initial Catalog=TargetDB;Trusted_Connection=Yes"
Sub test_Insert()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rec As New ADODB.Recordset
cn.ConnectionTimeout = 600
cn.CommandTimeout = 600
cn.Open strDBConnection
cn.Execute ("Delete from TartgetDB_Temp")
rec.Open "tmpTransactions", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.Open "TartgetDB_Temp", cn, adOpenDynamic, adLockOptimistic
Do Until rec.EOF
rs.AddNew
rs(0) = rec(0)
rs(1) = rec(1)
rs(2) = rec(2)
rs(3) = rec(3)
rs(4) = rec(4)
rs(5) = rec(5)
rs(6) = rec(6)
rs(7) = rec(7)
rs(8) = rec(8)
rs(9) = rec(9)
rs(10) = rec(10)
rs(11) = rec(11)
rs(12) = rec(12)
rs(13) = rec(13)
rs.Update -- Breaks here
Loop
On Error Resume Next
rec.Close
Set rec = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Open up the profiler and run this code. Grad the insert statement sent by access. Then rerun it in query analyser. You should get a more usefull error message.

I would suspect a data conversion error or an update on a identity column / calculated field at this point... but I can't tell without the actual error.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Also this would run MUCH MUCH faster :

Insert into DbName.dbo.Tablename (col1, col2, coln) Select Col1, Col2, Coln from Db2.dbo.TableName.

Rich Hansell SSCrazy Points: 2346 More actions · Answer 3

Thanks Ninja.. I would have preferred he had done it like that.. but as always.. he doesn't have time to do a rewrite.. So in any case I figured out the issue.. Since he is using ADO and opening a cursor serverside, Access gets confused when the nocount is set to on. So the key was to add in a line in his code setting cursors to run client side. And it worked like magic..Thanks again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

What rewirte??? Takes 2 minutes to write this and test!

Rich Hansell SSCrazy Points: 2346 More actions · Answer 5

lol.. He insists that he would have to rewrite most of his apps if he were to move away from ADO.. He is a silly goose for sure, but we don't have time for him to futz with them at the moment..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

How can a whole application depend on a single SUB? I mean it's not like we're asking him to change the MAIN() FUNCTION!!!

Anyways as long as it works and no one suffers from this, I can let it go.

~~For now!~~