|
This helps indeed. More and more I am getting convinced that separate DB with permanent tables is the way to go. Thanks guys you're the best.
|
|
|
|
|
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is
(a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement.
(b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
|
|
|
|
|
|
Thank you responding. I will read through the documentation completely. But just at a first glance wouldn't this run into the issue of 2 clients inserting at the same time but then they both fetch the same serial number. To put it another way, client A should get 12345, client B should get 12346, but due to the timing of the inserts they both get 12346.
|
|
|
|
|
a new connection implies a new scope, SCOPE_IDENTITY will not return identities created through other connections.
|
|
|
|
|
As Luc has already answered on my behalf, SCOPE_IDENTITY() is scoped to your connection and ensures that you do not get a value that was inserted through another connection even if it were newer. There are other ways to read identity values though, but I suggested this method specifically for this reason.
|
|
|
|
|
I strongly agree with getting that off the client.
How to do so may depend on exactly what information is used to generate the serial number. I've seen some that use the date and a sequence, e.g. 120313123 (YYMMDDseq), with the sequence rolling over each day. Something like that could be done in the database, by a stored procedure. I understand that the upcoming version of SQL Server will have sequences built in (Oracle has had them for decades), but you could also create your own sequence (which is what I do when I need a sequence).
On the other hand, you probably shouldn't put any "information" in the serial number in the first place.
As to using identity columns... I don't; I find them to be very problematic, and a simple sequence works much better in most cases.
|
|
|
|
|
PIEBALDconsult wrote: As to using identity columns... I don't; I find them to be very problematic
I've been using them for over a decade and haven't really found any problems.
|
|
|
|
|
I have used them infrequently -- only in apps that someone else wrote -- and have always had trouble.
Using a sequence or GUIDs has never caused me trouble.
|
|
|
|
|
Shameel wrote: I've been using them for over a decade and haven't really found any problems.
Neither have I.
If I remember I will check my SQL 6.5 reference to see what I was using with that (since I have been using SQL Server longer than a decade.)
* UPDATE * Yep 6.5 had them so I haven't had any trouble using them for 15 years.
modified 17-Mar-12 13:32pm.
|
|
|
|
|
As others have implied you need to change your strategy. Personally I would leave the serial number method exactly as it is but would not have it as the primary key. A primary key should hold no intelligence in it's data, so create another field (based on IDENTITY or GUID spit)) and use the serial no as a piece of clients code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
First of all, separate the serial number and the primary key. If you use a Guid as the key, you can "calculate" it (Guid.NewGuid()) already on the client. In case of an autoincrement value, you can query it from the database with a SELECT @@ID.
Your serial number column should still have a unique index.
When you insert your data, omit the serial number first.
You can take two different approaches here:
(1) Create a trigger. In the trigger function, calculate your serial number. Take care that two calculations may happen at the same time in different threads. The trigger function then updates your data. Retrieve the inserted serial number with a select query.
(2) Calculate the serial number on the client. Do an update query. When the update fails due to a duplicate value, calculate again with adjusted parameters, and try again, till the update does not fail. Here, I'd use a transaction, and commit when the update was succesful. Looks bad, but with a low chance of duplicates, it will not cause bad performance.
|
|
|
|
|
Hi,
I am getting a different error:
1045 - Access denied for user "root@192.168.1.9" using password: YES
I am sure about the password because I am able to login to it on the server itself which has the IP 192.168.1.1 but I am unable to login from my PC which has the IP 192.168.1.9
Kindly help..
|
|
|
|
|
You need to allow access to the server from all locations. It looks like you have set access to the server from localhost only.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
You need to add additional access rights by executing the following command in Mysql console line:
GRANT ALL on <dbname>.* TO 'root'@'192.168.1.%'
This will allow all computers in the 192.168.1.x subnet to login to your MySQL server.
|
|
|
|
|
can I replace the
<dbname> with EVERYTHING so full access will be granted to all databases for everyone in that IP range?
|
|
|
|
|
Sure just replace it with a '*' and you will grant access to all the schema's in the database.
|
|
|
|
|
I did but still getting the same when using Navicat as well Visual Studio? How can I troubleshoot?
Access denied for user 'root'@'192.168.1.9' (using password: YES)
|
|
|
|
|
any idea what can I do to fix this?
FYI: MySQL is running on debian..
|
|
|
|
|
Is this a VPS or dedicated server, if so have you verified that MySQL is even listening to external IP addresses. Most hosting providers will limit access to MySQL from outside the server itself.
If you have a VPS or dedicated server look in the '/etc/mysql/my.cnf'
And make sure the rule below is not applied or change it to your network IP address for that server.
bind-address = 127.0.0.1
|
|
|
|
|
Problem solved by just changing the password of the root to another password although the old password was correct but I don't know why I was getting that error..
anyhow, thanks everyone..
|
|
|
|
|
how to create a scheduled job using query in sql2008. I need a sample!
Thanks in advance
|
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
