Sql Server 7 and Citrix

Question

Sql Server 7 and Citrix

Ivan Motta

Grasshopper

Points: 20
More actions
November 2, 2005 at 11:07 am

#68504

Hey, I'm experiencing weird problems in SqlServer with a Crm system. I have one sqlServer and 12 Metaframe server that runs the application developed in VB6. The system connects with database through ADODB object. There are about 100 concorrent users. Sometimes the system lost link with the server causing problems with recordset objects. We are receiving a error from SqlServer (cannot insert duplicate primary key) for one insert made in the system, but "the weird". The server is executing insert transaction one time perfectly and after simply try execute the same again (for the same row that was inserted before). How can it be is the system send only one command to sql ..
Anynone hav experienced some like that ..
Thanks ..

Viewing 4 posts - 1 through 3 (of 3 total)

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

Alexandros A Nipirakis SSC-Addicted Points: 491 More actions · Answer 1

Greetings,

It would appear that you have a structure problem with your database. You have an primary key column which does not allow duplication of data, but you are duplicating data. Here is an article explaining how you would tell sql server to automatically incriment an identity column , so you dont have to worry about doing it manually from VB.

http://www.windowsitpro.com/Article/ArticleID/22080/22080.html?Ad=1

After turning your column into an identity, you should go into the VB and remove the part of the insert that is inserting into your primary key. For instance:

dim thisString as string

thisString = "insert into thisTable(myPrimaryKey, someData) values ( " + someInt +",'" +

someString + "')"

changing this to :

thisString = "insert into thisTable(someData) values ( '" +

someString + "')"

and the column myPrimaryKey will automatically incriment.

You should try printing out the vb inserts to make sure that your queries work ( by putting them into query analyzer ). If they fail there, then you know your VB is working properly. If you still cant get it, post the create for your table, and the VB code that is erroring out.

Thanks ,

Aleksei

A failure to plan on your part does not constitute an emergency on my part!

Ivan Motta Grasshopper Points: 20 More actions · Answer 2

Hi Aleksei,

I do not use identity in this case, but the current procedure is very simple and quick.

Begin Trans

query 1 : select max(PK) from table

new_pk = new_pk + 1

query 2 : insert into ... (new_pk,...)

Commit Trans

Initialy, I was thinkin that two diferent users were get the same code and trying to insert them.

After trace this transaction I perceived the true problem. The SqlServer performs the same transaction (for the same row inserted) two times. The first execution is saved in database correctly, but istead database end the transaction, simply executes again ... How can ir occurs ? This is a Software failure ?

Thansk,

Ivan

Ian Yates SSCoach Points: 19738 More actions · Answer 3

How did you "trace" the transaction? Using SQL Profiler? You may well see what looks like the same code executing twice, but the first time is probably a preparation. You could see the code doing

"prepare", "begin execution", "end execution" (three times!). But it only executes once.

Your use of select max(..) from table is going to cause you the exact issues you are experiencing, particular with 100 users. You should do as suggested and switch to an identity column and let SQL manage the column increment.

If you want to have an ID locally in your code before you perform your final insert, maybe consider using GUID columns as your client app can generate the ID and be sure that it will be unique everywhere (although they are a bigger datatype!).

If you must stick with your current code, make sure the entire block of code is wrapped in a SINGLE transaction by using a common ADO connection object and you will probably need to add

with (holdlock) to your select max(..) statement to ensure that other users don't get the max. You may even need to use a table lock... Yuk

Alternatively, have a separate table that just has a single identity column - rather than doing a select max(...) from tableA, you can just insert a row into your identity table and the identity value is the value you use as your PK in the other table. To get the identity value back to your code, you can return the value of @@IDENTITY after the insert into the identity column table.

Hope one of the suggestions helps! Cheers