Padding identity column values with zeros

Hi Guys

Is there an easy way to pad identity column values with zeros to the left (0001, 0010,0100, etc) or I will just have to format them before displaying

George

This works for me a lot of times.

REPLICATE('0', 4-len(cast(id as char(4))) + cast(id as char(4))

|||

You could use an expression like:

right(replicate('0', 8) + cast($identity as varchar), 8)

-- or

right(replicate('0', 8) + cast(identitycol as varchar), 8)

You could either use a computed column or a view with the expression. Alternatively, you could simply do these type of operations on the client side.

P2P Replication - Issue in Manual Identity Management

I am using P2P replication between two server. I face a big issue in manual
identity management. I did as follows
Step 1: Built MyDB1 with data in server1.
Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
p2ptranrepl.doc)
Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
(again as per steps in msdn white paper)
Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
necessary check constraint on identity column. Also made Identity column NOT
FOR replication. Following Hillary Cotter's writeup in
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
2000. Added necessary check constraints
Step 5: As per the msdn whitepaper setup using "Configure peer to peer
topology' wizard.
Step 6: Now say the identity range of server 1 is consumed and I want to
reseed the value to say 2001-3000. Server2 has max as only 1010 and hence
left as is.
I reseed server1 table1 and also change the check constraints accordiingly
from 2001-3000.
**********Now the issue is this change in constraint gets automatically
propogated to the subscriber. But i do not want the subscribers to move to
the new range. They should be in 1001-2000 only. I have verifed that 'Copy
Check Constraints' property is set to FALSE. But still the constraints are
getting propogated to subscribers****************************
This becomes a blocking issue for my manual identity management.
Kindly help to resolve the same.
Thank you,
Lalitha
remove the check constraints. They aren't necessary. I think you should be
using larger ranges as well.
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
"Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>I am using P2P replication between two server. I face a big issue in manual
> identity management. I did as follows
> Step 1: Built MyDB1 with data in server1.
> Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
> p2ptranrepl.doc)
> Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
> (again as per steps in msdn white paper)
> Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
> necessary check constraint on identity column. Also made Identity column
> NOT
> FOR replication. Following Hillary Cotter's writeup in
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
> I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
> 2000. Added necessary check constraints
> Step 5: As per the msdn whitepaper setup using "Configure peer to peer
> topology' wizard.
> Step 6: Now say the identity range of server 1 is consumed and I want to
> reseed the value to say 2001-3000. Server2 has max as only 1010 and
> hence
> left as is.
> I reseed server1 table1 and also change the check constraints accordiingly
> from 2001-3000.
> **********Now the issue is this change in constraint gets automatically
> propogated to the subscriber. But i do not want the subscribers to move to
> the new range. They should be in 1001-2000 only. I have verifed that 'Copy
> Check Constraints' property is set to FALSE. But still the constraints are
> getting propogated to subscribers****************************
> This becomes a blocking issue for my manual identity management.
> Kindly help to resolve the same.
> Thank you,
> Lalitha
>
|||Hi,
Thanks for the response.
Though I use large ranges, having the check constraints is for making the
solution fool proof. This validation will ensure that no data gets inserted
other than my configured range.
When 'Copy Check Constraints' is set to FALSE, why is P2P behaviour different?
1) Is this a bug in P2P which will be fixed in future service packs?
2) Is this a limitation of P2P?
If this is the limitation of P2P then I intend to go for merge.
Please clarify.
Thanks for your inputs...
Lalitha
"Hilary Cotter" wrote:

> remove the check constraints. They aren't necessary. I think you should be
> using larger ranges as well.
> --
> 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
>
> "Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
> news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>
>

Override Identity Column using Datatable

I need copy a table from a remote (hosted) SQL 2000 database server to my local machine. I don't have access to backups and am unable to correctly configure my local machine to add a linked server. So I plan to retrieve the data to a datatable, copy it in code and save it to my local server. But the table contains an identity column which I will need to insert the values manually so they match the original.

Can anyone tell me how I can set the datatable's save to use my manual values instead of the autonumber value?

Thanks.

there are two options

1. First remove the constraint on identity column, then import the data, after that apply the constraint on iidentity column.

2. Add another column for it,

|||

SET IDENTITY_INSERT {YourTableName} ON

INSERT INTO {YourTableName} ....

SET IDENTITY_INSERT {YourTableName} OFF

|||

Thank you for the response Motley, but I don't want to have to create SQL statements; I want to do this directly in code using a datatable. Do you know how to do that?

|||

If you do not want to write sql query, then I suggest to go for answer1, turn off identity feature, import the data and turn it on.

|||

You would use the "SQL Statements" I gave above in a sqldataadapters's updatecommand.

|||

Hi,

Actually the code is also calling the SQL statements to do the update. So you will always need some SQL Statements for updating.

I suggest you use the way Girijesh has provided. Turn off the identity contraint off and import data.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!