sql server ODBC Error

Posted on 1998-11-25
Last Modified: 2010-05-03
i don't know if this concerns vb... or it must be SQL... this is the case... We are using a SQL 6.5 server for our DB... a service program that coputes something logs to this db... currently we are running like 5000 records... never have we successfully finished the run for the 5000 records.. . after a thousand or so... there will be an odbc error.... when we open the enterprise manager for SQL... an error telling us that the syslog for tempdb is full... when we checked the size remaining for tempdb.. its still very big... we increased the size of the tempdb to 500 MB... we did run upto 2500 records without error... but 500 mb is too much to waste... what if we ran like 35000 records... how many Gbytes or Mbytes should we spare for tempdb... is this how it really works....???? needs some answer soon...
Question by:nasia
  • 7
  • 6

Accepted Solution

Stefaan earned 100 total points
ID: 1446717
Hi Nasia,

As your TempDB is 500 mb, this can't be causing the problem.  The problem is your Transaction Log.  As you process the 5000 records, for each record a transaction is created in the transaction log.  Once it gets full, your error is reported, and all transactions are cleared !

From what I can see you didn't create a separate transaction log for your database.  You should do so.  For each Database, you should create a Device to store the Data and a Device to store the transaction log.  I usually create a Device for the transaction log which is about 10% the size of the device which holds the data.

When creating a database, make sure you supply the Data Device and the Log Device you created.

One more thing you should consider, is setting a checkpoint or split up the process into smaller parts.  If you process 5000 records, this means at least 5000 transactions and the transactions won't be cleared untill all records are processed.  Try to process the records into smaller chunks, thus clearing the transactions from time to time.

If you process the records into smaller chunks (eg 500 records) your problem should be solved.

Hoping that this will be of any help.

Greetings and best regards,

Stefaan Lesage

E-Mail :

Author Comment

ID: 1446718
hello stefaan...
  we did use a seperate device for the dbs transaction log... as a matter of fact, its 50% of the data device... about splitting our proccess, i guess that would be a soulution if we'll be manually running the program, but thats not the case... 5000 thousand records would be the maximum size per run... maybe the average is around 2500 to 3000... there is one more thing... there comes a point wherein we cannot adjust or expand the devices size... and more the size's value becomes negative (-)... is this normal or is there a bug.... i've also set the dbs option to truncate the log in every checkpoint... will this help??? thanks...

Expert Comment

ID: 1446719
Hi Nasia,

Well another thing you could do is put your record processing procedure into transaction blocks.  Using the BEGIN TRANSACTION and COMMIT TRANSACTION commands from the SQL Server.

If you have a lot of insert or update statements, try putting them into a transaction block.

     INSERT INTO ..... VALUES (.....)

This will make sure that the transaction is committed for each insert command issued, thus freeing the space this command used in the Transaction log.

Try it out.  This should help.

Greetings and best regards,

Stefaan Lesage

E-Mail :
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.


Author Comment

ID: 1446720
sorry for taking so long to add a comment... well i've tried the transaction blocks stuff... but i don't know the syntax.. how can i use this for vb programing... i usually use to place SQL commands into string then use the SQLDB.execute STRINGSQL... how can i use this???


Expert Comment

ID: 1446721
You can use the transaction block code in the same way as you execute other statements.  You also put them in a String and Call the execute.

Lets say you have a variable StringSQL which holds your normal SQL string, use the folowing code (instead of your sqldb.execute stringsql) :

StringSQLTransact = "BEGIN TRANSACTION" + chr$(13) + STRINGSQL + chr$(13) + "COMMIT TRANSACTION"
SQLDB.Execute StringSQLSTransact

This should do the trick.


Author Comment

ID: 1446722
i tried it but still it won't work... wait.. will this code work if the table is a link table in access??? coz, my sql tables are all link tables....

Author Comment

ID: 1446723
i did confirm that the tempdb is the one growing... i made a run for my program then used the performance monitor to view the max tempdb used... as it shows.. while i keep on proccessing records my tempdb was also growing.... i think updates have something to do with it... well when i finished the program my tempdb space available was only a rough 30MB from the 99.9MB it was originally... i tried to truncate the tempdb to no avail... it is only when i stopped and restarted the sql services that the tempdb went back to its 99MB original size...??? why is this so???... i wish your transaction blocks could solve this...???

Expert Comment

ID: 1446724
Hi Nasia,

I don't know for sure if the proposed transaction blocks will work using a linked table in access, but you can try it.

Could you check your SQL Server Database settings and see if the 'Truncate on Checkpoint' option is checked ? If not, try to check it and try your stuff again.


Expert Comment

ID: 1446725
Hi Again Nasia,

I tried the Transaction blocks from withing Access and you are right, it doesn't seem to be working.

Could you show me the code you are trying to execute ? Then I'll do my best to help you.


Author Comment

ID: 1446726
this is part of my code... i don't want to give you everything its kinda long... the logic of the code is that.. i pull records from an as400 environment... maybe in one pull i'll get to have about 5000 records max... i will then proccess this records 1 by one... records are classified by a field which describes what db it goes... all db's reside in a 6.5 sql server... if a record has been added or updated i will write/update in the as 400 environment... right now i'm not using all dbs (there will be four)... since we've not come to that point of implementation... not all the proccess is just data transfer... it concerns computations also... which will also be logged in the database.. overall i have 3 tables per db to update per cycle...

one update procedure looks like this:

    strSQL = "UPDATE tblMIDCustomerLot SET tblMIDCustomerLot.MotherLot = '" & spltFrm & "', " & _
             "tblMIDCustomerLot.CustomerNum = '" & custCode & "', " & _
             "tblMIDCustomerLot.CustomerName = '" & cusnam & "', " & _
             "tblMIDCustomerLot.TextSiteCode = '" & site & "', " & _
             "tblMIDCustomerLot.PkgLd = '" & pkgLead & "', " & _
             "tblMIDCustomerLot.Device = '" & device & "', " & _
             "tblMIDCustomerLot.FPONum = '" & fpoNo & "', " & _
             "tblMIDCustomerLot.AssyStart = '" & strAssyStart & "', " & _
             "tblMIDCustomerLot.HoldCode = '" & holdCode & "', " & _
             "tblMIDCustomerLot.WaferRunNum = '" & waferRunNum & "', " & _
             "tblMIDCustomerLot.StartCode = '" & startCode & "', " & _
             "tblMIDCustomerLot.OrigShipBack = '" & origShipBack & "', " & _
             "tblMIDCustomerLot.RevShipBack = '" & revShipBack & "', " & _
             "tblMIDCustomerLot.SpecialNotes = '" & SpecialNotes & "', " & _
             "tblMIDCustomerLot.MIDSpecNum = '" & midSpecNum & "', " & _
             "tblMIDCustomerLot.PkgSidNum = '" & pkgSIDNum & "', " & _
             "tblMIDCustomerLot.PkgPartNum = '" & pkgPartNum & "', " & _
             "tblMIDCustomerLot.PkgDesc = '" & pkgDesc & "', " & _
             "tblMIDCustomerLot.LdSidNum = '" & ldSIDNum & "', " & _
             "tblMIDCustomerLot.LdPartNum = '" & ldPartNum & "', " & _
             "tblMIDCustomerLot.LdDesc = '" & ldDesc & "', " & _
             "tblMIDCustomerLot.Quantity = " & quantity & ", " & _
             "tblMIDCustomerLot.CalendarRef = '" & calendarRef & "' " & _
             "WHERE (((tblMIDCustomerLot.CustomerLot)='" & custLotNo & "'));"
    dbPackage(dbPos).Execute strSQL, dbFailOnError

dbPackage() holds an array of dbs (as i've said i'll be having 4 dbs) ... dbPos is the position of the current db being filled out...

all my insert statements are not yet in sql... i still use the addnew command... will it help if i also use sql statements for this...???

Author Comment

ID: 1446727
i'm having new problems again... you don't have to answer it but if you have a idea it will be most appreciated... everytime i execute my code and it reaches a sql statement the code hangs and a Dr. Watson is automatically started.... and dr watson is using up all cpu time and my program just stops... there is nothing wrong with my sql code... as a matter of fact this stuff happens anywhere.. it does not necessarily happens at a specific sql statement but sometimes it executes a statement w/o a problem then sometime it doesn't.... do you have an idea on this??? could it be virus... (it just started yesterday...).. i'm re-installing my vb5... hope it works.....

Author Comment

ID: 1446728
.... i forgot to tell you... yup.. i did have truncate on checkpoint on.....

Expert Comment

ID: 1446729
Hi Nasia,

I did a little searching yesterday, and I think I foud a solution.  Transaction blocks seem to be available in DAO (Access) if you use a workspace.  I have included a little piece of sample code.  It uses a database called 'Development' on my SQL Server for which I made an ODBS DSN called 'Development'.  The database contains a table called MasDocType which has 2 fields MasDocTypeID (Integer) and DocumentType (Char 50).

What the sample code does is insert a record into this table, but within a transaction block (make sure you use the committrans otherwise all changes won't be applied to the DB).  Here it is :

Sub TestTransaction
    Dim wspStandaard As Workspace, dbsTest As DATABASE
    Dim qdfPassThrough As QueryDef
    ' Get the Default WorkSpace
    Set wspStandaard = DBEngine.Workspaces(0)

    ' Open the Current DataBase
    Set dbsTest = wspStandaard.OpenDatabase("Test SQL.mdb")

    ' Create a QueryDef which we will use to execute our SQL
    Set qdfPassThrough = dbsTest.CreateQueryDef("InsertTest")

    ' Start your loop here
    wspStandaard.BeginTrans ' Start transactions.

    ' Connect via ODBC to our SQL Server DB
    qdfPassThrough.Connect = "ODBC;DATABASE=Development;UID=sa;PWD=DBAdmin;DSN=Development"

    ' Set the SQL Statement
    qdfPassThrough.SQL = "INSERT INTO dbo.MasDocType (MasDocTypeID, DocumentType) VALUES (11,'Testje');"

    ' Since this is an insert, it doesn't return records
    qdfPassThrough.ReturnsRecords = False

    ' Execute our Statement

    wspStandaard.CommitTrans ' Commit Transaction
    ' End your loop here
    ' This will make sure that each step in the loop is made within a trasaction block
    Set qdfPassThrough = Nothing

End Sub

You could now easily adapt this code for your application.  Create a loop where you will find the comments for it, and instead of my SQL Statement, use yours.  Just make sure that for each loop you call the .BeginTrans and .CommitTrans methods within the loop.  This will make sure you create and commit the transaction each time you cycle through the loop.

This should solve your problem.

Greetings and best regards,

Stefaan Lesage

E-Mail :

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now