IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"ë'ì?´ ì'ì?±í' ë?´ì?©:
> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schema

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
Raj
Can I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will
|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"?? ??? ??:

> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>
|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schema
sql

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"?? ??? ??:

> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schem
a

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"?? ??? ??:

> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schem
a

IF funcionality in SQL server views

Hi,

I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.

For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e

Document number
SC11111
DN22222
SI33333

For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line

Any help appreciated.

Thank you in advance,

RajCan I just clarify what you're trying to do...

as I understand it, you want to select from different tables/views
depending on what the data is.

if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.

Otherwise, could you explain a bit further what the problem is.

Cheers
Will

IExtension.SetConfiguration not working

I am having

problems with the method IExtension.SetConfiguration in my Custom Data Extension.

According to MSDN

I am suppose to add <Configuration> tags in my config file, however this does not work for me.

The configuration string stays empty.

(http://msdn2.microsoft.com/en-us/library/microsoft.reportingservices.interfaces.iextension.setconfiguration.aspx)

This is an example of my comfig file:

<Extension

Name="Teradata" Type="namespace.path.to.connect,assemly.path.to.DLL">

<Configuration>value</Configuration>

</Extension>

I have also

tried Microsoft's exact example character for character

<Extension

Name="Teradata" Type="namespace.path.to.connect,assemly.path.to.DLL">
<Configuration>

<MyExtensionConfigurationData>

<MyExtensionData1>Value</MyExtensionData1>

<MyExtensionData2>Value</MyExtensionData2>

</MyExtensionConfigurationData>
</Configuration>
</Extension>

My SetConfiguration method

looks like this:

void

IExtension.SetConfiguration(string configuration)

{

string test_string =

configuration;

}

Does anyone see what I am missing?I do not see any additional steps on MSDN

that I missed.

Jason,
Is there a way I can contact you? It seems we are working towards similar issues.

R

IExtension.SetConfiguration not working

I am having

problems with the method IExtension.SetConfiguration in my Custom Data Extension.

According to MSDN

I am suppose to add <Configuration> tags in my config file, however this does not work for me.

The configuration string stays empty.

(http://msdn2.microsoft.com/en-us/library/microsoft.reportingservices.interfaces.iextension.setconfiguration.aspx)

This is an example of my comfig file:

<Extension

Name="Teradata" Type="namespace.path.to.connect,assemly.path.to.DLL">

<Configuration>value</Configuration>

</Extension>

I have also

tried Microsoft's exact example character for character

<Extension

Name="Teradata" Type="namespace.path.to.connect,assemly.path.to.DLL">
<Configuration>

<MyExtensionConfigurationData>

<MyExtensionData1>Value</MyExtensionData1>

<MyExtensionData2>Value</MyExtensionData2>

</MyExtensionConfigurationData>
</Configuration>
</Extension>

My SetConfiguration method

looks like this:

void

IExtension.SetConfiguration(string configuration)

{

string test_string =

configuration;

}

Does anyone see what I am missing?I do not see any additional steps on MSDN

that I missed.

Jason,
Is there a way I can contact you? It seems we are working towards similar issues.

R

IDENTITY_INSERT On/Off in sql compact

Hi,

Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :

SET IDENTITY_INSERT <tablename> OFF

and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = @.@.IDENTITY_INSERT ]

Im i doing something wrong or this instruction is not supported by the Compact edition.

Does anybody knows another way to do this?

Thank you

Alexander75

The IDENTITY_INSERT concept is not supported by SQL Compact Edition. Your best workaround for this is to use the ALTER TABLE / ALTER COLUMN IDENTITY to set the appropriate identity value for each row you want to insert. Not nice? You bet! But it's the only documented solution. I have it working on my database tranfer products so you can trust me it works.|||ok. Thanks|||You wouldn't happen to have a code sample for this, would you?

Thanks in advance...|||My samples use native code, but you can use the .NET CF classes for this. The critical part is to determine the IDENTITY properties before altering the table and this can be done by querying the INFORMATION_SCHEMA (see the BOL).

Identity sequence of multi-threads insertion

Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:

Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.

Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.

One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.

So is there any other good solutions for this problem? Thanks.

A solution may be that process 1 uses row-level locking when select/update and process 2 use the readpast hint when selecting the records:

READPAST specifies that locked rows be skipped during the read. READPAST only applies

to transactions operating at the default READ COMMITTED isolation level, and will

only read past row-level locks. READPAST can only be used in SELECT statements.

Normal blocking can be worked around by having transactions read past rows being

locked by other transactions.
See the following article: http://www.sql-server-performance.com/rd_table_hints.asp

Else I cannot see any other solution except adding some external data structure to keep track of processing status.|||

Hi carlop, I'm afraid locks won't resolve this issue.

Maybe I should explain my problem more detailly.

Suppose Process No.1 have 3 threads working , and here are the identities generated after the insertion.

thread 0 : 1 , 4, 7

thread 1: 2 , 5, 8

thread 2: 3, 6, 9

And at one time point, thread 0 commited the transaction with 3 rows inserted , whilst Process No.2 is retrieving data from Table A , so only these 3 records were retrieved and processed. So Process No.2 will record "7" as the biggist identity it has processed, and next time it will start processing from identities greater than "7" . So records with identities 2, 5,3,6 are lost if thread 1 and thread 2 commited later.

|||You have to add an external struct that keeps track of the processing jobs. I see no other way.|||You could add another table that is logged to on insert to table A. Process 2 reads these records and deletes them from the new tableonce processed. When it goes back for a second time it reads the nest set to process and then deletes from. You could use Service Broker if you wanted as it has a nice queue mechanism|||

Maybe this is the only solution I think.

Thank you all.

Identity sequence for multithreads insert

Recently I'm working on a multi-thread solution based on SQL-Server, now I'm facing such a problem:

Suppose I have process No.1(with multi-threads) inserting data to Table A, which has its identity column auto generated. And process No.2(also with multi-threads) retrieving data from Table A ,generate some records and insert the result into Table B. Both of these two processes are doing batch processing(batch retrieving and batch writing), and they are running parallelly.

Now since process No.2 retrieve data sequencely by the identity of Table A, it found there exists missing results. This is due to that records with bigger identities are not necessarily commited earlier than those who have smaller identities.

One direct solution is add one flag field in Table A indicating whether this record has been processed by process No.2, and each time it was processed , the field will be set. But unfortunatelly the table structure is not supposed to be modified.

So is there any other good solutions for this problem? Thanks.

A solution may be that process 1 uses row-level locking when select/update and process 2 use the readpast hint when selecting the records:

READPAST specifies that locked rows be skipped during the read. READPAST only applies

to transactions operating at the default READ COMMITTED isolation level, and will

only read past row-level locks. READPAST can only be used in SELECT statements.

Normal blocking can be worked around by having transactions read past rows being

locked by other transactions.
See the following article: http://www.sql-server-performance.com/rd_table_hints.asp

Else I cannot see any other solution except adding some external data structure to keep track of processing status.|||

Hi carlop, I'm afraid locks won't resolve this issue.

Maybe I should explain my problem more detailly.

Suppose Process No.1 have 3 threads working , and here are the identities generated after the insertion.

thread 0 : 1 , 4, 7

thread 1: 2 , 5, 8

thread 2: 3, 6, 9

And at one time point, thread 0 commited the transaction with 3 rows inserted , whilst Process No.2 is retrieving data from Table A , so only these 3 records were retrieved and processed. So Process No.2 will record "7" as the biggist identity it has processed, and next time it will start processing from identities greater than "7" . So records with identities 2, 5,3,6 are lost if thread 1 and thread 2 commited later.

|||You have to add an external struct that keeps track of the processing jobs. I see no other way.|||You could add another table that is logged to on insert to table A. Process 2 reads these records and deletes them from the new tableonce processed. When it goes back for a second time it reads the nest set to process and then deletes from. You could use Service Broker if you wanted as it has a nice queue mechanism|||

Maybe this is the only solution I think.

Thank you all.

Identity Range not working for master

I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:

> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]

Identity Column values getting corrupted

I have bi-directional replication setup between SQL7 and SQL2000 and I have defined range for each publisher. Every thing has been working just fine and over time I have added several distribution agents for new tables for 2 way replication. In order to be sure both the databases get to do insert in their assigned range, I have a CHECK constraint with "NOT FOR REPLICTION" setup on each replicated table.

Just recently, I have encountered 2 tables for which the check constraint throws an error saying the application is trying to insert row that violates the check constraint. Upon doing DBCC CHECKIDENTI with NORESEED option, I find out that the identity column value is incorrect even though this column has the property "NOT FOR REPLICATION".

I have fixed the problem by running DBCC CHECKIDENT with RESEED option, but after a few days the identity column value gets corrupted again. Any ideas?

I don't think the application is doing insert with "SET IDENTITY INSERT" because a) the identity column is set with NOT FOR REPLICATION property and b)I have check constraints to keep the application from inserting in the wrong range.

Any help will be deeply appreciated. Thanks!

-A

What version of SQL Server 2000 are you running? In earlier versions there was a bug in the SQL engine such that under certain conditions the identity seed can be corrupted. This bug is fixed in SP3 and after.

IDENTITY Column produces gaps when insert fails

Hi

When an insert statement for a table having an identity column fails, there is a gap in the identity chain. Is this working 'as designed', or is there anything wrong with my configuration? I'm Using SQL 2005 Standard SP1.

Here's the script:

USE tempdb;
GO

-- Create Test Table
CREATE TABLE id_gaps
( id INT IDENTITY(1001,1)
, txt VARCHAR(10) NOT NULL UNIQUE
);
GO

-- Insert some useful data
INSERT INTO id_gaps (txt) VALUES ('Test 01');
INSERT INTO id_gaps (txt) VALUES ('Test 02');
INSERT INTO id_gaps (txt) VALUES ('Test 03');
INSERT INTO id_gaps (txt) VALUES ('Test 04');
GO

-- Insert a duplicate value unsing an explicit Transaction
-- It doesn't work using an implicit Transaction too.
BEGIN TRAN tr1
BEGIN TRY
INSERT INTO id_gaps (txt) VALUES ('Test 04');
COMMIT TRAN tr1;
END TRY
BEGIN CATCH
ROLLBACK TRAN tr1;
END CATCH
GO

-- Insert an additional row
INSERT INTO id_gaps (txt) VALUES ('Test 05');
GO

-- See the content of the table. There is a gap caused of the refused insert statement.
SELECT * FROM id_gaps;

-- Clean up
DROP TABLE id_gaps;

Thank you for any hints.

The following url may help you..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1010520&SiteID=1

But it is not advisable.... Here i pasted the updated query (not recommanded)

USE tempdb;
GO

-- Create Test Table
CREATE TABLE id_gaps
( id INT IDENTITY(1001,1)
, txt VARCHAR(10) NOT NULL UNIQUE
);
GO

-- Insert some useful data
INSERT INTO id_gaps (txt) VALUES ('Test 01');
INSERT INTO id_gaps (txt) VALUES ('Test 02');
INSERT INTO id_gaps (txt) VALUES ('Test 03');
INSERT INTO id_gaps (txt) VALUES ('Test 04');
GO

-- Insert a duplicate value unsing an explicit Transaction
-- It doesn't work using an implicit Transaction too.
BEGIN TRAN tr1
BEGIN TRY
INSERT INTO id_gaps (txt) VALUES ('Test 04');
COMMIT TRAN tr1;
END TRY
BEGIN CATCH
ROLLBACK TRAN tr1;
END CATCH
GO

IF IDENT_CURRENT('id_gaps') = (Select max(id) from id_gaps)
Begin
SET IDENTITY_INSERT mytable OFF;
Insert INto id_gaps values('Test 05');
End
else
Begin
SET IDENTITY_INSERT id_gaps ON;
Insert INto id_gaps(id,txt) select max(id) + IDENT_INCR('id_gaps'),'Test 05' From id_gaps;
SET IDENTITY_INSERT id_gaps OFF;
End

-- Insert an additional row
INSERT INTO id_gaps (txt) VALUES ('Test 06');
GO

-- See the content of the table. There is a gap caused of the refused insert statement.
SELECT * FROM id_gaps;

-- Clean up
DROP TABLE id_gaps;

|||

It is not possible to maintain complete sequence control when using IDENTITY values. As you have discovered, there are many reasons an INSERT may fail.

If you must absolutely control a numerical sequence, then you should have a 'numbers' table, lock it, get the next number, increment, and then unlock -but only after you are certain that your insert will succeed.

|||

Thank you for these tips

It's not really necessary to have a continuous nubering, i'm just wondering if there's a mistake in my configuration. But i see it's the normal behavior the identity column works.

Identity Column

Hi,

I need to turn off Identity of a column which already has records, i have tried SET IDENTITY_INSERT TableName OFF but it is not working. can any one please help me.

Do you mean turn it off permanently or what exactly? What are your exact, particular circumstances?

|||
To turn off identity temporarily and allow explicit inserts to the table, you have to issue SET IDENTITY_INSERT TableName ON. Note that you can have only one table with this property set to ON in a session. This will be in effect until you issue
SET IDENTITY_INSERT TableName OFF to come back to the identity.

If you wish to permanently delete the identity property then you may have to go for other solution, like adding a new column without identity to the table, insert the values for the existing data, delete the old column with identity property and rename the column back to the original name. Or you can create a new table with almost same structute except the idenity and export the data, drop the old table, rename the table name.

As a sidenote, why do you want to remove the identity property. As you can see, it can be done but its pain to do it.