Id (indentity) is increments on faults.

Hi,

When i eg. manually ad entries to a table and, cancels the insert Ms SQL
increment the counter on the ID anyway. Is there a way to avoid this
behavior?

Regards
AndersFlare (dct_flare@.hotmail.com) writes:
> When i eg. manually ad entries to a table and, cancels the insert Ms SQL
> increment the counter on the ID anyway. Is there a way to avoid this
> behavior?

Yes, don't use the IDENTITY property, but roll your own. IDENTITY works
that way by design. By grabbing one number which never has to be
rolled back, insertions into tables with IDENTITY columns can scale
better.

One way to get a key on your on is:

BEGIN TRANSACTION

SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl (UPDLOCK)

INSERT tbl (id, col1, col2, ...)
VALUES (@.id, @.par1, @.par2, ...)

COMMIT TRANSACTION

The UPDLOCK is required to avoid that two processes grab the
same id.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

I need help connecting Access 97 to SQL Server 2000

I have an Access 97 database that I connect to a SQL Server 2000
through ODBC. I have to manually set up the ODBC connection on each
user's machine if I want them to be able to use the application though.

Is there a way to store the connection string within the Access DB so
I don't have to touch the ODBC settings on each user's machine? Or is
there a different solution that I'm missing? There are way too many
for me to
setup. Thanks in advance.aperez@.easternbk.com wrote:
> I have an Access 97 database that I connect to a SQL Server 2000
> through ODBC. I have to manually set up the ODBC connection on each
> user's machine if I want them to be able to use the application though.
> Is there a way to store the connection string within the Access DB so
> I don't have to touch the ODBC settings on each user's machine? Or is
> there a different solution that I'm missing? There are way too many
> for me to
> setup. Thanks in advance.

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Use DSN-less connection strings. Like this (all one line):

ODBC;Driver=SQL Server;
Server=ServerName;Database=DBName;Trusted_Connecti on=Yes

Trusted_Connection=Yes means you're using Windows Logon security
(recommended).

If you have attached (linked) ODBC tables, you'll have to change the
tables' Connect property using VBA.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmmm44echKqOuFEgEQL9JACfayfFceXgmVsO6o+pKPKs8n V2D3cAoPJB
a1tvf0whesiFNyTgnQi1uhxN
=mRxB
--END PGP SIGNATURE--