Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

sql server ODBC Error

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...
  • 7
  • 6
1 Solution
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 :
nasiaAuthor Commented:
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...
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 :
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

nasiaAuthor Commented:
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???

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.

nasiaAuthor Commented:
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....
nasiaAuthor Commented:
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...???
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.

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.

nasiaAuthor Commented:
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...???
nasiaAuthor Commented:
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.....
nasiaAuthor Commented:
.... i forgot to tell you... yup.. i did have truncate on checkpoint on.....
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 :
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now