determine the assigned identity range

Is there any way to find out a subscriber's assigned identity range (at the
publisher)?
It would be helpful to have an idea where a row was added from its identity
value.
No, it only maintains the last range assigned - which will be the highest
one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tedcorpus@.hotmail.com> wrote in message
news:%23F7cc61eFHA.412@.tk2msftngp13.phx.gbl...
> Is there any way to find out a subscriber's assigned identity range (at
the
> publisher)?
> It would be helpful to have an idea where a row was added from its
identity
> value.
>
|||In the distribution database, MSrepl_identity_range holds the next ranges,
so You'll have to work with the metadata tables on the subscribers for this:
You could use this type of query:
SELECT sysobjects.name AS TableName, *
FROM MSrepl_identity_range INNER JOIN
sysobjects ON MSrepl_identity_range.objid =
sysobjects.id
The data from each subscriber could be amalgamated at the publisher using
linked servers,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks!
So I could compare max_identity/current_max columns across servers
(accounting for the range value). But linking the servers just for this
purpose might be more administration work than benefit at this point.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
> In the distribution database, MSrepl_identity_range holds the next ranges,
> so You'll have to work with the metadata tables on the subscribers for
> this:
> You could use this type of query:
> SELECT sysobjects.name AS TableName, *
> FROM MSrepl_identity_range INNER JOIN
> sysobjects ON MSrepl_identity_range.objid =
> sysobjects.id
> The data from each subscriber could be amalgamated at the publisher using
> linked servers,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You only need to look at the max value on the publisher's distribution
database. That will be the next assigned range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"TCorp" <tcorpus@.hotmail.com> wrote in message
news:eg1MgYagFHA.2548@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks!
> So I could compare max_identity/current_max columns across servers
> (accounting for the range value). But linking the servers just for this
> purpose might be more administration work than benefit at this point.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
ranges,[vbcol=seagreen]
using
>

Determine If Table Has Identity Field

Is there a way to read a system table to determine if a specific table has
an identity column?
Derek HartSELECT OBJECTPROPERTY(OBJECT_ID('tablename'), 'TableHasIdentity');
See OBJECTPROPERTY in Books Online for more information.
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:%23fatw1GcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> Is there a way to read a system table to determine if a specific table has
> an identity column?
> Derek Hart
>|||Use the OBJECTPROPERTY() function for this (tip: TableHasIdentity).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derek Hart" <derekmhart@.yahoo.com> wrote in message news:%23fatw1GcGHA.3380@.TK2MSFTNGP04.p
hx.gbl...
> Is there a way to read a system table to determine if a specific table has
an identity column?
> Derek Hart
>|||Derek Hart wrote:
> Is there a way to read a system table to determine if a specific table has
> an identity column?
> Derek Hart
Always state what version you are using.
In 2000:
...
EXISTS
(SELECT *
FROM dbo.syscolumns AS C
WHERE id = OBJECT_ID('dbo.tablename')
AND COLUMNPROPERTY(id,name,'IsIdentity')=1) ;
In 2005:
...
EXISTS
(SELECT *
FROM sys.identity_columns
WHERE object_id = OBJECT_ID('dbo.tablename')) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||In SQL Server 2005:
SELECT 'yes'
WHERE EXISTS
(
SELECT *
FROM sys.columns
WHERE
object_id = object_id('yourtablename')
AND is_identity = 1
)
In SQL Server 2000:
SELECT 'yes'
WHERE EXISTS
(
SELECT *
FROM syscolumns
WHERE
id = object_id('yourtablename')
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:%23fatw1GcGHA.3380@.TK2MSFTNGP04.phx.gbl...
> Is there a way to read a system table to determine if a specific table has
> an identity column?
> Derek Hart
>|||Nice, now we have many ways to do it:
2000 / 2005:
OBJECTPROPERTY
2000 (and 2005 via the compatability view):
COLUMNPROPERTY over the syscolumns table
2005 only:
Is_Identity from the sys.columns view
sys.Identity_Columns view (that's one I've missed until now -- thanks for
pointing it out, David)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146852429.607871.92140@.v46g2000cwv.googlegroups.com...
> Derek Hart wrote:
> Always state what version you are using.
> In 2000:
> ...
> EXISTS
> (SELECT *
> FROM dbo.syscolumns AS C
> WHERE id = OBJECT_ID('dbo.tablename')
> AND COLUMNPROPERTY(id,name,'IsIdentity')=1) ;
> In 2005:
> ...
> EXISTS
> (SELECT *
> FROM sys.identity_columns
> WHERE object_id = OBJECT_ID('dbo.tablename')) ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
T
Tina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:

> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>
|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...[vbcol=seagreen]
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
sql

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
TTina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:
> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
>> I'm developing a SQL Utility program intended to work with multiple
>> brands
>> of database. Right now we just want to get it working with SQL Server.
>> I
>> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
>> etc.
>> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
>> primary key is an Identity column.
>> Where can I look?
>> Thanks,
>> T
>>

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
TTina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE
_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:

> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...[vbcol=seagreen]
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE
_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
>

designing for encryption ...

In light of SARBOX, ChoicePoint security breaches, identity theft etc, what
are some best practices regarding encryption and security of this type of
info? I know this is a pretty broad question, so links to other sites are
great, but I'd like to hear some personal experiences and opinions.
The reason is I have identified what I consider potential security problems
in our systems. I need to get get a sense of how critical these particular
issues are (I tend to take the position that hyper vigilence is best so for
me everything is critical) and get them in front of management to hopefully
get some action. But I always think it's best to have at least a proposal
for a solution when presenting a problem.
Anyway, any feedback is most appreciated.
Bob Castleman
DBA PoseurHere is a little something from a mind far greater than mine...
http://vyaskn.tripod.com/sql_server...t_practices.htm
Peter
"The length of this document defends it well against the risk of its being
read."
Winston Churchill
"Bob Castleman" wrote:

> In light of SARBOX, ChoicePoint security breaches, identity theft etc, wha
t
> are some best practices regarding encryption and security of this type of
> info? I know this is a pretty broad question, so links to other sites are
> great, but I'd like to hear some personal experiences and opinions.
> The reason is I have identified what I consider potential security problem
s
> in our systems. I need to get get a sense of how critical these particular
> issues are (I tend to take the position that hyper vigilence is best so fo
r
> me everything is critical) and get them in front of management to hopefull
y
> get some action. But I always think it's best to have at least a proposal
> for a solution when presenting a problem.
> Anyway, any feedback is most appreciated.
> Bob Castleman
> DBA Poseur
>
>|||SARBOX is not an area I'm expert in but one thing I know about security
is that it isn't the same as encryption. Encryption is just one tool
for security. So "designing for security" is something different from
"designing for encryption" and I would advise you to focus on the
former rather than the latter. As I understand it SARBOX does NOT
mandate that any data be encrypted it just requires "adequate" internal
controls.
David Portas
SQL Server MVP
--|||I believe that Sarbanes Oxley suggests or recommends all "Sensitive" data be
encrypted in a data store.
SSNs
Account Numbers
Visa Numbers
etc etc etc
I could be wrong (I'm not a SOX Audit expert either)
Greg Jackson
PDX, Oregon|||Not a lawyer or security expert but California law, SB 1386
(http://info.sen.ca.gov/pub/01-02/bi...86_bill_2002092
6_chaptered.html) mentions unencrypted data. It seems that if someone where
to breach your database and personal information was encrypted you would not
need to disclose the breach.
See [url]http://informationw.com/story/showArticle.jhtml?articleID=10700814[/url]
for an overview...
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23Jf3G$8LFHA.576@.TK2MSFTNGP15.phx.gbl...
> I believe that Sarbanes Oxley suggests or recommends all "Sensitive" data
be
> encrypted in a data store.
> SSNs
> Account Numbers
> Visa Numbers
> etc etc etc
> I could be wrong (I'm not a SOX Audit expert either)
> Greg Jackson
> PDX, Oregon
>

Designating an identity column when you already have primary key

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

Hello my friend,

Turn Allow Nulls off for these other 2 columns. In addition to this, here is some SQL you can run at any time to check for duplicate values: -

SELECT ErrorTypeID, COUNT(*) AS Counter
FROM TBLERROR
GROUP BY ErrorTypeID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Kind regards

Scotty

|||tblErrror is a custom table in my database. Substitute it for your table and the field you want to check|||

Thanks, Scotty.

Is there any way to apply manual constraints so the duplicate value doesn't end up in the table in the first place, or would I instead have to insert new rows through a stored procedure and apply the check/abort in there?

|||

Hello again my friend,

Put this index on your table: -

CREATE UNIQUE INDEX MyIndexName ON MyTableName (MyFieldName)

If there are duplicates already, the above statement will fail. If there are not, it will work and if someone, or some code, tries to insert a duplicate, the record will not be inserted/updated and an error will result.

Kind regards

Scotty

|||

If you want to remove an index you have added, do the following: -

DROP INDEX MyTableName.MyIndexName

Kind regards

Scotty

|||

EricLaszlo:

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

The IDENTITY property can generate auto numbers for your column however it does not guarantee uniqueness because IDENTITY can generate gaps, so Microsoft recommend you use GUID instead. But you cannot use GUID because you have three columns that need to be Unique so that leave only one option Unique constraint because you can define more than one Unique constraint in a table. I have seen table definitions with many constraints so you could run some tests in relation to ADO.NET as needed for your application. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

|||

Thank you both very much for your input.

Based on that, I was able to apply the contstraints with the following syntax:

ALTER

TABLE Profiles

ADD

CONSTRAINT ncEmail

UNIQUE

NONCLUSTERED(Email)

Having read a little about clustered indices in my travels, I tried to apply one to my Id column - didn't notice that this column already had a clustered index - not sure if that was from designating is as and Identity column or applying primary key.

In any case, I think I'm good for now...

design question - parent child tables & identity columns

I don't know if this is the right forum but...

In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?

Nope. But, that also brings up a very interesting point. Why is your user directly querying the data within the table in such a way that they would even see this value? It's an internal structural element and should not have any business meaning at all. Put an identity on the orderdetail table, link it to it's parent OrderID in the orders table. Now, in the application which your users should be using to work with data, yank the order detail rows out, display them in ascending order of the OrderDetailID, and then on the application tier just number the lines from 1 - N. The user now gets the display that they want and you aren't tying a structural element to a business meaning.

Why wouldn't you want to give this key a business meaning? Very simple. User enters an order with 10 line items. They then decide to delete lines items 3, 6, and 7. If you are physically storing this data, you would then have to update the foreign keys and then the primary key (parent) upon which they depend which is something that should never be done.

|||

The users are not directly querying the data. The might see it in a report. Users see keys all the time and they are very useful. An invoice number is a key, a UPS tracking number is a key, a Bloomberg trouble ticket is a key. They are very useful in tracking down problems. When these objects have detail items users do communicate using the codes ("I have a problem with order #2356, item #5"). I was trying to find a way to make this easy by keeping the 2nd number low ("I have a problem with order #2356, item #58694937").

I wouldn't need to delete the rows, I could mark them as deleted and strike through them on a report or just eliminate them from the report and have gaps in the numbering.

|||So the answer to that is you have to custom code that yourself which is also going to mean performance, scalability, and concurrency issues.

Design help

I have two tables Publications and TM_Pub.

CREATE TABLE [dbo].[Publications] (
[Pub_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pub_Pres] [int] NULL ,
[Title] [nchar] (200) COLLATE Latin1_General_CI_AI NULL ,
[Authors] [char] (100) COLLATE Latin1_General_CI_AI NULL ,
[Location] [char] (50) COLLATE Latin1_General_CI_AI NULL ,
[Publication] [char] (150) COLLATE Latin1_General_CI_AI NULL ,
[Pub_date] [datetime] NULL ,
[Pres_enddate] [datetime] NULL ,
[Pres_Desc] [text] COLLATE Latin1_General_CI_AI NULL ,
[Pub_detail] [char] (20) COLLATE Latin1_General_CI_AI NULL ,
[keywords] [text] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[TM_PUB] (
[TM_PUB_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pub_ID] [int] NULL ,
[TM_ID] [int] NULL
) ON [PRIMARY]

Each Pub_id can have multiple TM_id's. But I want to ensure that I do not create any duplicate Pub_id; TM_id records.

The user will be looking at an indivdual Publication record (Pub_id) along with a all the available TM_id's, with the TM_id's currently assigned to the Pub_id selected. The user will be able to select or unselect TM_ids for the Pub_id.

I am having trouble figuring out how to take the users selection and insert new records in the TM_Pub table if they do not already exist as well as delete records if the do exist. I plan to send my sproc two parameters @.pub_id and @.TMidstring.

I am looking for ideas on how best to accomplish this.

Thanks.Hi

If I've understood your problem, why don't you just use Pub_ID and TM_ID as a composite key? Like that you won't be able to have any duplicates.

In the case of a junction table like this, I would delete all the old records for a particular Pub_ID or TM_ID, and insert all the new ones, so that you don't have to check each time if they already exist.

Does that help or am I way off?!

A.