|
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
|
|
|
|
|
|
|
|
Does anyone know the best way to join fields from several tables onto a single SSRS report for SSRS 2005 and SSRS 2008? 
|
|
|
|
|
Just guessing as it has been a while sine I did any SSRS, either concatenate the fields in the cell definition if you are in a matrix or create an expression and use that for the textbox content.
There is also the option of creating a formula and using that but I can remember the entry point for the code editor.
[edit]
Reread you question - several tables. I always use a stored proc and return 1 table as a result set
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Will this work if you need to join several tables from different databases which would mean different data sources?
|
|
|
|
|
It is possible especially if the DBs are on the same server, if they are not then the hassle of linked servers is irritating. Investigate doing the join in code in the report. You can throw the values into variables an manipulte the vars.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
