Autonumber field: primary

Showing posts with label primary. Show all posts

Tuesday, March 27, 2012

Backing up online log after loss of primary data file

My question is this:

Is there anyway to backup the current online log after complete loss of the current correponding datafile?

Example:

(2) Logical Disk Volumes

Disk 1 (D:) contains pubs_data.mdf
Disk 2 (E:) contains pubs_log.ldf

Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.

Thanks,
PI can't beleive there is no way to get at the information in the current online log depite the fact that the database file is gone.

So, if I can't get at the current online log I must rely on a backup of the log which I would take every 5 or 15 minutes but that 0-5 minute period is not retrievable even if its just the data file that fails.

Any thoughts?

Thursday, March 22, 2012

Backing up a primary log shipping database

Hi,
Does backing up a primary database in a log shipping pair cause problem in
the log shipping?
Aside from an on-going log shipping maintenance between a Primary &
Secondary (latency 15 minutes) database, I would like to schedule a daily
complete backup of the primary database and an hourly transaction log backup.
I already tested this and so far log shipping continued but I just want to
confirm if this has no lurking issues. AFAIK, transaction log backup
truncates the log files that is why I am wondering how can the other
transaction log backup the log files if it is already truncated.
Your comments will be highly appreciated.
--
...A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
backups of a log shipped database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:714B75B2-AA8C-46FF-8B1F-A1B6B348F2CA@.microsoft.com...
> Hi,
> Does backing up a primary database in a log shipping pair cause problem in
> the log shipping?
> Aside from an on-going log shipping maintenance between a Primary &
> Secondary (latency 15 minutes) database, I would like to schedule a daily
> complete backup of the primary database and an hourly transaction log backup.
> I already tested this and so far log shipping continued but I just want to
> confirm if this has no lurking issues. AFAIK, transaction log backup
> truncates the log files that is why I am wondering how can the other
> transaction log backup the log files if it is already truncated.
> Your comments will be highly appreciated.
> --
> ...|||This is a multi-part message in MIME format.
--010003020805070908090803
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
But what about transaction log backups? Does xp_sqlmaint (when it's
doing the copy phase on the secondary server) look up the msdb.backupset
& msdb.backupmediafamily tables on the primary server to get the
location of the physical log backup files (even the ones done by
processes other than the logshipping plan's xp_sqlmaint call, like
another maint plan or ad-hoc tlog backups)? Or does it just pick up log
backup files from a certain directory or created by the xp_sqlmaint
backup phase on the primary?
If it's the later then ad-hoc log backups, or log backups done by
another maint plan, would disrupt the logshipping sync (and in fact the
log shipping tlog backups would make the log backups done by the other
maint plan out of sequence).
On the whole, having 2 different plans (one log shipping plan and one
other maint plan) doing transaction log backups just sounds like a bad
idea (and redundant anyway). Why have another plan doing hourly tlog
backups when the logshipping plan is doing them every 15 minutes anyway?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tibor Karaszi wrote:
>A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
>backups of a log shipped database.
>
>
--010003020805070908090803
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>But what about transaction log backups? Does xp_sqlmaint (when
it's doing the copy phase on the secondary server) look up the
msdb.backupset & msdb.backupmediafamily tables on the primary
server to get the location of the physical log backup files (even the
ones done by processes other than the logshipping plan's xp_sqlmaint
call, like another maint plan or ad-hoc tlog backups)? Or does it just
pick up log backup files from a certain directory or created by the
xp_sqlmaint backup phase on the primary? 
 
If it's the later then ad-hoc log backups, or log backups done by
another maint plan, would disrupt the logshipping sync (and in fact the
log shipping tlog backups would make the log backups done by the other
maint plan out of sequence). 
 
On the whole, having 2 different plans (one log shipping plan and one
other maint plan) doing transaction log backups just sounds like a bad
idea (and redundant anyway). Why have another plan doing hourly tlog
backups when the logshipping plan is doing them every 15 minutes anyway? 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Tibor Karaszi wrote:
<blockquote cite="mideqP33exzFHA.3312@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
backups of a log shipped database.
</pre>
</blockquote>
</body>
</html>
--010003020805070908090803--|||I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
log backups does. I was under the impression that the OP was asking about database backup, so thanks
for pushing the point, Mike. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:ONlx0j4zFHA.1192@.TK2MSFTNGP10.phx.gbl...
> But what about transaction log backups? Does xp_sqlmaint (when it's
> doing the copy phase on the secondary server) look up the msdb.backupset
> & msdb.backupmediafamily tables on the primary server to get the
> location of the physical log backup files (even the ones done by
> processes other than the logshipping plan's xp_sqlmaint call, like
> another maint plan or ad-hoc tlog backups)? Or does it just pick up log
> backup files from a certain directory or created by the xp_sqlmaint
> backup phase on the primary?
> If it's the later then ad-hoc log backups, or log backups done by
> another maint plan, would disrupt the logshipping sync (and in fact the
> log shipping tlog backups would make the log backups done by the other
> maint plan out of sequence).
> On the whole, having 2 different plans (one log shipping plan and one
> other maint plan) doing transaction log backups just sounds like a bad
> idea (and redundant anyway). Why have another plan doing hourly tlog
> backups when the logshipping plan is doing them every 15 minutes anyway?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tibor Karaszi wrote:
>>A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
>>backups of a log shipped database.
>>
>|||This is a multi-part message in MIME format.
--050800090501060406070700
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
How do you implement log shipping if you don't use xp_sqlmaint (as
that's what the DB Maintenance Wizard uses when it creates all the
SQLAgent jobs)? Have you implemented your own home-grown log shipping
solution?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tibor Karaszi wrote:
>I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
>backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
>log backups does. I was under the impression that the OP was asking about database backup, so thanks
>for pushing the point, Mike. :-)
>
>
--050800090501060406070700
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>How do you implement log shipping if you don't use xp_sqlmaint (as
that's what the DB Maintenance Wizard uses when it creates all the
SQLAgent jobs)? Have you implemented your own home-grown log shipping
solution?</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Tibor Karaszi wrote:
<blockquote cite="mide9cjH47zFHA.3152@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
log backups does. I was under the impression that the OP was asking about database backup, so thanks
for pushing the point, Mike. :-)
</pre>
</blockquote>
</body>
</html>
--050800090501060406070700--|||> Have you implemented your own home-grown log shipping
> solution?
Yep. I'm using www.dbmaint.com (since I write part of it, and while implementing test-restore I
realized it was pretty much same thing as implementing log shipping), but lots of people here also
just use their own jobs (I'm, pretty certain some has been posted here).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uayUtD8zFHA.1168@.TK2MSFTNGP10.phx.gbl...
> How do you implement log shipping if you don't use xp_sqlmaint (as
> that's what the DB Maintenance Wizard uses when it creates all the
> SQLAgent jobs)? Have you implemented your own home-grown log shipping
> solution?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tibor Karaszi wrote:
>>I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
>>backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
>>log backups does. I was under the impression that the OP was asking about database backup, so
>>thanks
>>for pushing the point, Mike. :-)
>>
>

Friday, February 24, 2012

Avoiding "Violation of Primary Key" with replication

I have a centralized database (server A), and two remote databases
(server B & C) all with a copy of the same database with a table with
user entered, primary key like ProductID. There is a possiblity that
the same ProductID gets entered into both B & C (which is ok).
I create a snapshot of B and replicate to A.
Is there a way to replicate C to server A and have replication ignore
any Primary Key violations during replication?
you can select the continue on data consistency profile. To do this right
click on your distribution agent and select agent profiles.
I would however advise you to come up with some partitioning scheme -
whether this involves adding another column to your pk with a location
specific parameter, or if you are using the identity property on you pk, go
to server b and type
DBCC Checkident ('YourTable',1000000)
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
<dkolva@.atlab.com> wrote in message
news:1110815869.167577.303520@.g14g2000cwa.googlegr oups.com...
> I have a centralized database (server A), and two remote databases
> (server B & C) all with a copy of the same database with a table with
> user entered, primary key like ProductID. There is a possiblity that
> the same ProductID gets entered into both B & C (which is ok).
> I create a snapshot of B and replicate to A.
> Is there a way to replicate C to server A and have replication ignore
> any Primary Key violations during replication?
>

Avoid Inserting Duplicate Entries

Hi all,

I am somewhat new to sql server. So help me in whatever way you can.

I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

The insert query is as below.

table A - CORE
table B - CRF

INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))

I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

Thank you in advance for all who try to help me out.

Quote:

Originally Posted by desirocks

Hi all,

I am somewhat new to sql server. So help me in whatever way you can.

Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

The insert query is as below.

table A - CORE
table B - CRF

I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

Thank you in advance for all who try to help me out.

i believe CONSTRAINT are still the best way to enforce your data integrity. you can also do this through trigger or check the table before insert, but i believe it'll be slower.|||

Quote:

Originally Posted by ck9663

i believe CONSTRAINT are still the best way to enforce your data integrity. you can also do this through trigger or check the table before insert, but i believe it'll be slower.

I AM NOT CONCERNED WITH THE PERFORMANCE... CAN YOU HELP ME WRITE THIS TRIGGER OR PROVIDE SYNTAX TO CHECK THE TABLE BEFORE THE INSERT?|||

Quote:

Originally Posted by desirocks

Hi all,

I am somewhat new to sql server. So help me in whatever way you can.

Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

The insert query is as below.

table A - CORE
table B - CRF

I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

Thank you in advance for all who try to help me out.

Your above INSERT QUERY should work fine. What is the problem that you are facing here??|||

Quote:

Originally Posted by amitpatel66

Your above INSERT QUERY should work fine. What is the problem that you are facing here??

Since table CRF does have a composite key it doesnt allow duplicate entries and hence my application gives me an error. I want to some how check if the entry already exists or not and based upon the same i need to insert. Is there some way i can change the query ?|||It would help to know what column(s) comprise your primary key on table B. You say that A nad B have different unique columns, but your insert statement only limits duplicates based on table A's unique key. If table B has a different unique key then that is what you need to check fo in your not in statement. Also, a NOT EXISTS should be faster, but first you need to figure out what is unique on table B.|||OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_MIN,16),
LEFT(ACCT_NUM_MAX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM CORE A
WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.CORE_UID = A.UID and B.BIN = A.BIN)|||

Quote:

Originally Posted by rob313

OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_MIN,16),
LEFT(ACCT_NUM_MAX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM CORE A
WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.CORE_UID = A.UID and B.BIN = A.BIN)

hi rob,

The composite key comprises of ACCT_NUM_MIN and ACCT_NUM_MAX. The UID of CORE is being transferred to CRF and CRF also has its own UID. Can you specify what whould be the change in the query now?|||In that case, this should work:

INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_MIN,16),
LEFT(ACCT_NUM_MAX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM CORE A
WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.ACCT_NUM_MIN = A.LEFT(ACCT_NUM_MIN,16) and B.ACCT_NUM_MAX = A.LEFT(ACCT_NUM_MAX,16))

Avoid duplicate primary key error

How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ?

Thanks in advance !

stephen

Before insertin the new row, you can check the table to see whether there is a row in the table with the same key value as the new row. For example:

if exists (select * from Orders where OrderID=@.newOrderID)

//not insert

else //insert

|||

Can you show me the detail of correct syntax ? I'm very new in ASP.NET 2.0. I did type the statement in the ??.aspx.cs and syntax error found.

stephen

|||

Sorry I forgot to say: the code in my last post is T-SQL statements, so you can use them in SqlCommand or something else.

Friday, February 10, 2012

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
>> We are testing the upload of an Access 2002 data database to SQL
>> Server 2000. Some of the tables had AutoNumber fields that were
>> Random (due to it being a replicated table in Access). The upload
>> created triggers similar to the example below. However, Access
>> applications fail on insert because the primary key field is not
>> populated until the trigger runs. Any help is appreciated.
>> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
>> INSERT AS SET NOCOUNT ON
>> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
>> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
>> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
>> SELECT @.newc = (SELECT DocID FROM inserted)
>> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
>> David
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
David
David C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

Autonumber or unidue id generation

Hi,
I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?
Thanks
-SudhakarHi,

I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?

Thanks
-Sudhakar

Make the datatype int and set the Identity constraint on that column.
Joydeep|||Your two best choices are either an Identity column (integer), or a GUID with the default set to NewID().|||I would STRONGLY recommend using the GUID if that option is available to you. IDENTITY values are fine for many purposes, and are easy for legacy code to manage, but GUID values are much simpler in the long run. They are easier to use, easier to generate, easier to manage, and very nearly foolproof, at least in my experience.

-PatP|||Well, I use GUIDs almost exclusively, but I don't know that I would recommend them as strongly as Pat. They do have some drawbacks, and I am not talking about their size or the fact they they are difficult to type (stupid arguments...). One of my pet peeves is that I can't apply aggregate functions such as MAX and MIN to GUIDs, which, believe or not, can be handy thing to do some times, such as when eliminating duplicates. You have to cast the GUID as char(36) first, which throws any indexes out the window.

autonumber is resetting

i am using autonumber as a primary key in one table. in my app., row from that table will be moved to another table after some time but that autonumber is important. when there are no any data in first table, and new row is inserted, autonumber starts from 1 which i don't want as there will be data redundancy when it will be moved to another table. so is there any way i can force the autonumber to start from previous value rathe than 1?Are you using an IDENTITY column? If so, I do not understand why you would have the problem you describe.|||i am using identity datatype.

yeah, it's strange. i added that data via webform interface and it was 1. then i tried adding directly in db but it gave me 10 as new value which is correct.

so just wondering if there is any settings i need to use just to make sure that it doesn't happen again.

AutoNumber Column and ...

Hi *.*
I plan to crate a database and want to know if it's good to use autonumber
columns as my primary key or I should generate the numbers myself?
Also, If I want to replicate the DB, is it ok to use autonumber columns?
At last, Is there any way I can manage the autonumber start number? Means
autonumbering starts at 100 or 300000 instead of 0?
Thanks for your help,
HOA
Hoa wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
I suggest you take a step back and spend a month researching all this
along with getting your database professionally designed. Every one of
those questions are so "depends" in nature that the very fact that you
asked them tells me you don't have a good enough grasp of the situation
to appreciate the answers.
But, quickly:
1) Depends.
2) Sorta. Depends.
3) Yes.
There is an entire section in the Books Online that is called
"Replication Data Considerations" with a topic "Using IDENTITY Values".
Have you not even bothered to read that section prior to posting your
very open ended question?
Zach
|||Identity columns are fine for PK... as long as you plan on keeping your identity values unique. PK values MUST be unique; Identity does not explicitly enforce uniqueness. Identity columns are good for PKs in the fact that they are INTEGER datatype. This d
atatype is small, so it works well w/ indexes (PK defaults to unique, clustered)
Identity columns may need to be set to 'NOT FOR REPLICATION' in certain situations. See Books Online: Replication topology & NOT FOR REPLICATION for more info.
Yes, you can start an Identity value at a number greater than 0 or 1... you declare it with the IDENTITY(10,5) clause (10 being the 'seed' value, 5 being the increment.
"Hoa" wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
>
|||Oh boy. Books Online is your friend, don't let it be a stranger. These
issues are all covered in the documentation.
Your choice of a primary key depends on more factors than "it's good"... see
for a bit of my own opinion.
Using an IDENTITY in a replication scenario is okay, depending on what type
of replication. It will not work in merge replication, for example, since
IDENTITY can't guarantee uniqueness from two different database sources.
As for the identity, yes see the CREATE TABLE and DBCC CHECKIDENT tables in
Books Online. If the table hasn't been created:
CREATE TABLE Splunge
(sid INT IDENTITY(30000, 1))
INSERT Splunge DEFAULT VALUES
INSERT Splunge DEFAULT VALUES
SELECT * FROM Splunge
GO
DROP TABLE Splunge
Once again, please become familiar with Books Online. All of the questions
are answered, and then some, in the documentation you already have available
to you.
http://www.aspfaq.com/
(Reverse address to reply.)
"Hoa" <newpoorguy@.yahoo.com> wrote in message
news:eMTZ#DkeEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi *.*
> I plan to crate a database and want to know if it's good to use
autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
|||Using identity columns in replication is supported and as other posters have
indicated this is covered in BOL. The exact use of identities in replication
depends on hte type of replication you have selected, but if for example you
were to do a merge publication, another tab appears on the article
properties which allows you to have SQL Server assign ranges to publisher
and subscriber which ensures there is no overlap in assigned numbers - this
is enforced by check constraints. You can alternatively manage the identity
ranges yourself and Michael Hotek shows how to do this on his site
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison

Autonumber

Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
RegardsYou have to use the IDENTITY property of the column to produce auto number.
For example:
CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
GO
It is not really the ideal way to generate a primary key, but people do use
it for that purpose. If you could tell us about your table, may be someone
can suggest a better way.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.nospam.infovis.co.uk> wrote in message
news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards|||You can use this. But you should have a look on that article before.
http://www.sqlservercentral.com/columnists/tKetsdever/anidentitycrisis.asp
So be careful of what you need before.
--
P.RUELLO
DBA
"John" wrote:
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>|||It's a "companies" table that needs an id column. Also records in
"individual contacts" table are tied to their company record in "companies"
table via this id.
Thanks
Regards
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23hZJmerZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> You have to use the IDENTITY property of the column to produce auto
> number.
> For example:
> CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
> GO
> It is not really the ideal way to generate a primary key, but people do
> use
> it for that purpose. If you could tell us about your table, may be someone
> can suggest a better way.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good
> way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>

autonumber

sql 2000

how can i set primary key to autonumber?
i created a form with this table,, i get error when trying to insert..
i want "id: column to autogenerate number
...................................... ............................

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'Guestbook.dbo.guestbook'; column does not allow nulls. INSERT fails.FYI:

You need to create the Primary Key using IDENTITY [ ( seed , increment )

Tim S

From BOL ( Books On Line)

CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]|||is it possible to alter the table or column to make that changes?|||I have not found a way to alter the Table to make the change.

If use use the Enterprise Manager to change the column it creates a new table and copies the data into it and deletes the old table an renames the new table to match the old table name.

Tim S|||thanks

Autonumber

Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards
You have to use the IDENTITY property of the column to produce auto number.
For example:
CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
GO
It is not really the ideal way to generate a primary key, but people do use
it for that purpose. If you could tell us about your table, may be someone
can suggest a better way.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.nospam.infovis.co.uk> wrote in message
news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards
|||You can use this. But you should have a look on that article before.
http://www.sqlservercentral.com/colu...titycrisis.asp
So be careful of what you need before.
P.RUELLO
DBA
"John" wrote:

> Hi
> How does one set an auto-number field in sql server 2005? Is this a good way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>
|||It's a "companies" table that needs an id column. Also records in
"individual contacts" table are tied to their company record in "companies"
table via this id.
Thanks
Regards
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23hZJmerZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> You have to use the IDENTITY property of the column to produce auto
> number.
> For example:
> CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
> GO
> It is not really the ideal way to generate a primary key, but people do
> use
> it for that purpose. If you could tell us about your table, may be someone
> can suggest a better way.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good
> way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>

Autonumber

Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
RegardsYou have to use the IDENTITY property of the column to produce auto number.
For example:
CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
GO
It is not really the ideal way to generate a primary key, but people do use
it for that purpose. If you could tell us about your table, may be someone
can suggest a better way.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.nospam.infovis.co.uk> wrote in message
news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards|||You can use this. But you should have a look on that article before.
http://www.sqlservercentral.com/col...ntitycrisis.asp
So be careful of what you need before.
--
P.RUELLO
DBA
"John" wrote:

> Hi
> How does one set an auto-number field in sql server 2005? Is this a good w
ay
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>|||It's a "companies" table that needs an id column. Also records in
"individual contacts" table are tied to their company record in "companies"
table via this id.
Thanks
Regards
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23hZJmerZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> You have to use the IDENTITY property of the column to produce auto
> number.
> For example:
> CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
> GO
> It is not really the ideal way to generate a primary key, but people do
> use
> it for that purpose. If you could tell us about your table, may be someone
> can suggest a better way.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good
> way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>

Tuesday, March 27, 2012

Backing up online log after loss of primary data file

Thursday, March 22, 2012

Backing up a primary log shipping database

Backing up a primary log shipping database

Backing up a primary log shipping database

Friday, February 24, 2012

Avoiding "Violation of Primary Key" with replication

Avoid Inserting Duplicate Entries

Avoid duplicate primary key error

Friday, February 10, 2012

AutoNumber Primary Key

AutoNumber Primary Key

AutoNumber Primary Key

Autonumber or unidue id generation

autonumber is resetting

AutoNumber Column and ...

Autonumber

autonumber

Autonumber

Autonumber

Autonumber field

Blog Archive

About Me