Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts

Monday, March 19, 2012

[SQLCE Mobile]Do Select during Transaction

Hi,

I need to do a Select and after a Insert during a Transaction. But when i want to commit this transaction i've the following error message :

The transaction can not be committed if there are any opened cursors in the scope of this transaction. Make sure all data readers / result sets are explicitly closed before committing the change.

I don't understand this error message. How can i close my cursors.

Before my Insert i'm closing my SqlCeDataReader and i dispose it. My SqlCeCommand are also closed and dispose.

Do you have any ID?

Thanks.

Hi Kardath,

I tried to repro this here and could not reproduce the problem. I am able to have both INSERT and SELECT in a single transaction. Can you please try to dispose the all the sqlce objects and see if the problem disappers?

Thanks,

Laxmi

Thursday, March 8, 2012

[OTP] Multi User Application Database Transaction Stability

I am currently developing a multi user VB.NET 2005 application and this is my first time to involve in multi user application. Anyway, i know that multi-user application is not as easy as standalone application. I need to take care of the database stability for each and every user. My concern is may i know how to build up this stability for each and every transaction in terms of Data insert and update? This database resides on server and the user is able to access the data through the client application.

INSERT

The primary key is generated by application when the user enters into the data insert screen and it is done by counting the total amount of records in database. If two users perform insert at the same time, may i know how to deal with these primary keys? I am sure my application must be able to generate two different primary keys for these two users based on the time gap e.g. 0.001 milli second for the first user who enters into the screen and occupy the first primary key than the second user. But i have no idea on how to translate this to code.

UPDATE

If a particular record is viewed by a user and that user peforms an updates on the record while another user updates it at the same time, i may need to figure out a solution for this else the database will really be crashed. Is there any suggestion on it? May i know how to implement lock and unlock the record? Thank you.

For the INSERT part, you can't really generate keys like that from the client side without locking the entire table on the server. You can look at use of IDENTITY column for example which allows you to generate sequential numbers on the server. The server handles the concurrency part. There are other ways to generate keys on the client like using GUID for example. But all of these depends on the type of application you are writing and your data model.

For the UPDATE part, when one connection is updating a particular row or rows then any other connection that tries to update the same row(s) will be blocked. So the concurrent updates will happen in serial fashion depending on which ran first.

My suggestion would be to look at some of the topics on concurrency, locking, transactions in Books Online to get an idea of what the database engine can do. You should also get a book on SQL Server that describes some of these concepts so you don't try to invent or reinvent elaborate techniques on the client which might be suspect at best.

Sunday, February 19, 2012

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denie

Hi all.

I'm running a large data transaction and get the next error :

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

In this case, I process over many, many records, and then it finally indicated the server doesn't exist. This only occurs sometime.

I have SQLServer 2000 on Windows 2003 Server

Some idea how to solve this.

Thanks advanced

Wil.How are you running this large transaction? VIA ODBC or something? Can you please give us a little more detail.

Thanks,
-Kilka|||Just saw date.|||I also have the same problem as Wil, i'm running my application on :
.... Windows Server 2003
.... SQL Server 2000
.... Delphi 6
when i try to connecting the sql server with delphi application, then the error
'[DBNETLIB][ConnectionOpen(Connect()).]SQLServer does not exist or access denied.' raised.

I wonder if it's got anything wrong with the sql server instalation or the network system.

I'm desperately need help.

thank you
mayra|||My first guess would be that you have not applied Service Pack 3a or later to SQL Server 2000. Windows Server 2003 won't allow SQL Server access to the network until after a current service pack is applied, in order to prevent malware such as Slammer (http://www.microsoft.com/technet/security/alerts/slammer.mspx) from disrupting networks and the Internet.

-PatP|||Thank you Pat for your reply, i really appreciate that ;)

I just read from the book "SQL Server 2000 Bible",that windows server 2003 is using the SQL Server 2000 enterprise edition, while i'm using the personal edition.
So i think that's the main problem, then i started to re-install my sql server , and it works.

Thanks again Pat

mayra|||KBA http://support.microsoft.com/kb/328306/en-us will explain the root cause to resolve the issue.

Thursday, February 9, 2012

@@ROWCOUNT AND TRANSACTION

I was testing some checking techniques to add to my SPs while retreiving data or inserting a series of reocords to a series of tables.

Check this testing small piece of code:

begin transaction t1select * from roles where rolename = 'student'if @.@.rowcount = 0goto cleanupselect * from usersif @.@.rowcount = 0goto cleanupcommit transaction t1goto endupcleanup:rollback transaction t1SET ROWCOUNT 0endup:print @.@.rowcount


On one of the testing cases: I made the first query return 1 record and the second returned 5 records, so we ended up at the label "endup", I printed out the RowCount it was 0, does the commit transaction reset that variable?

In addition, are there smart and effective techniques to check against validity of the query statements inside the SP, for example, I can use @.@.ERROR and @.@.ROWCOUNT in a transaction based SP to know if I proceed with sub queries, are there more stuff like that?

Regards

Bilal,

Actually I don't know if commit transaction resets the@.@.rowcount field, but from my experience the only way to use the @.@.rowcount andthe @.@.error fields, is to declare local variables and immediately after DML statementscopy the @.@.rowcount and @.@.error fields to the local variable, for example:

Declare @.xRowCount int

Declare @.xError int

Select ……

Select @.xRowCount = @.@.rowcount, @.xError = @.@.Error

Following this method you are sure that the values don't get"messed up". I always insert this "copystatement" after select, insert, update and delete statements.

BTW:

Why do you have the SET Rowcount 0 after the rollback statement(The set rowcount 0 has nothing to do with the value in the @.@.rowcountSmile [:)])?And why do you use transactions in this example (I mean as long as you justhave select statements and none update, insert or delete statements?).

|||

Hello:

Thanks for the reply.

I usually use transactions when I have update, insert or delete and off ocurse multiple statements need to be executed. I used it here to show something, wierd for me at leastSmile [:)]. The first statement returned @.@.ROWCOUNT = 1, the second 5, and the final result (ROWSCOUNT value) was 0 and that made me think of the effect of the transaction on RowCount.

Thanks for the hints.

Regards

|||Hi.

You are right. The commit transaction reset the rowcount to 0 (at least according to the SqlServer 2005 documentation).

The documentation stats that:
Statementssuch as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Mark the words "Such as", meaning you can't be sure about the other statements. In other words, never trust the @.@.rowcount except right after a select, insert, update or delete statement.

I agree that is't a bit wired.