How to increment ID sequentially?

I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?

Look up DBCCCHECKIDENT. You can use it to view and / or update the identity seed value.

|||Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx|||

Hi mychucky,

You would have to use 'identity seed' property in your table design window in your SQL Server.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Thanks! I'll give it some reading. It's complicated then I thought. I thought there is a button in SQL Management Studio where I can just click and all is reset.|||

ZLA:

Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Thanks so much! I got it working now. I would never have found the solution if without your link.

How to include the date as part of the log file name...

Hello to all,
I'm using SQL Server agent to perform some schedule jobs.
I have it setup so that it will write to an result output
file (Jobs Steps -> Advanced -> Output file:
The problem is that I would like to name this output file
such as 'result%date%.log' where %date% is the current
date so that I can get a seperate log file each day.
Does anyone know how to do this ? %date% does not work.
Thanks in advance.
KinKin:
The way I have it up is that I do archiving of these output log files every
day as a separate job. In the archiving process I copy the files and add
date to the filename as follows:
declare @.cmd sysname
declare @.var sysname
set @.var = convert(varchar(11),getdate(),112)
--add date to file and archive
set @.cmd = 'copy e:\Logs\DBMaintenance\*.* e:\Logs\Archive_Logs\*' + @.var +
'.txt'
exec master..xp_cmdshell @.cmd
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||I'm not sure that there is a direct way to do that in SQL Agent. You could
always build you're own backup log command and execute that directly. Also,
you could simply let the job name it what it wants, and then add a step that
uses xp_cmdshell to rename the file.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||>--Original Message--
>Hello to all,
>I'm using SQL Server agent to perform some schedule
jobs.
>I have it setup so that it will write to an result output
>file (Jobs Steps -> Advanced -> Output file:
>The problem is that I would like to name this output file
>such as 'result%date%.log' where %date% is the current
>date so that I can get a seperate log file each day.
>Does anyone know how to do this ? %date% does not work.
>Thanks in advance.
>Kin
>.
>

How to include MSDE with Setup of custom applications

I am using VS .NET 2003 Bootstrapper Plug-In to create an
installer that redistribute the .NET Framework 1.1 and
MDAC 2.8 with my application.
How can I include MSDE and provide the switches in order
to install MSDE on clients machine as well?
Thanks
Hi,
Unfortunately, all the auto-install options currently have "issues". I'd
strongly suggest you:
1. Configure the setup.ini file to have your default settings.
2. Get the client to run the setup.exe program to install the MSDE.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"pankaj banga" <pankaj@.discussions.microsoft.com> wrote in message
news:12f201c4a8db$5d598ab0$a501280a@.phx.gbl...
>I am using VS .NET 2003 Bootstrapper Plug-In to create an
> installer that redistribute the .NET Framework 1.1 and
> MDAC 2.8 with my application.
> How can I include MSDE and provide the switches in order
> to install MSDE on clients machine as well?
> Thanks
|||Thanks HTH,
Then how would I create my tables, stored procs and load
client specific data into tables. Actually I am new to
all this. I read about msde bootstrap installer, are
there issues with it as well?

>--Original Message--
>Hi,
>Unfortunately, all the auto-install options currently
have "issues". I'd
>strongly suggest you:
>1. Configure the setup.ini file to have your default
settings.
>2. Get the client to run the setup.exe program to
install the MSDE.
>HTH,
>--
>Greg Low [MVP]
>MSDE Manager SQL Tools
>www.whitebearconsulting.com
>"pankaj banga" <pankaj@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:12f201c4a8db$5d598ab0$a501280a@.phx.gbl...
an[vbcol=seagreen]
order
>
>.
>
|||hi,
"pankaj banga" <pankaj@.discussions.microsoft.com> ha scritto nel
messaggio news:230901c4a950$1bb0dcb0$a301280a@.phx.gbl
> Thanks HTH,
> Then how would I create my tables, stored procs and load
> client specific data into tables. Actually I am new to
> all this. I read about msde bootstrap installer, are
> there issues with it as well?
>
Microsoft provides a deployment toolkit, in release candidate at the current
time, you can dowload from
http://www.microsoft.com/downloads/d...displaylang=en
I did not installed it, but I think some database deployment feature are
present... and some drawbacks of this toolkit have been posted here... don't
know the current state and/or the final release...
personally I do deploy apps with a companion tool which will read and
execute DDL scripts as long as INSERT INTO scipts, BCP and so on.. other
ways are backup/restore and sp_attach_db
I already discused these 3 methods of mine in http://tinyurl.com/6ux7p and
http://tinyurl.com/4x8pv ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Would it be easer to backup and restore production database on the
development server under different name?
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Or you can do something like this, with is not the best solution:
DECLARE @.name SYSNAME
DECLARE rs CURSOR
READ_ONLY
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--
OPEN rs
--
FETCH NEXT FROM rs INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- find your tables you need
IF @.name = 'Table1'
BEGIN
PRINT @.name
--
DELETE
FROM OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
--
INSERT INTO OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
SELECT * FROM TABLE1
--
END
END
FETCH NEXT FROM rs INTO @.name
END
--
CLOSE rs
DEALLOCATE rs
GO
But before that you have to add linked server and linked server login
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||DTS?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > I have two different setup, one for development and other for
production.
> > Two different SQL server 2000 installed on two different Windows 2000
> > server. I want to import all data of selected tables from production
> server
> > to development server. Databases name on both seerver are different.
> Before
> > I import, I want to remove all information from selected tables from
> > development database. Can anyone suggest me TSQL code?
> >
> > Thanks.
> >
> >
> >
>|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Thanks Aaron and Sergey.
> >
> > Backup restore works, but sometime I just want to few tables and not all
> > tables from production data. what I would like to do is store all tables
> > name in array and for each array element first delete all records from
> table
> > (name in array) of development server and copy all records from
production
> > server. Any idea? or any better solution?
> >
> > Thanks.
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > I have two different setup, one for development and other for
> production.
> > > Two different SQL server 2000 installed on two different Windows 2000
> > > server. I want to import all data of selected tables from production
> > server
> > > to development server. Databases name on both seerver are different.
> > Before
> > > I import, I want to remove all information from selected tables from
> > > development database. Can anyone suggest me TSQL code?
> > >
> > > Thanks.
> > >
> > >
> > >
> >
> >
>|||www.sqldts.com
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> > DTS?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > Thanks Aaron and Sergey.
> > >
> > > Backup restore works, but sometime I just want to few tables and not
all
> > > tables from production data. what I would like to do is store all
tables
> > > name in array and for each array element first delete all records from
> > table
> > > (name in array) of development server and copy all records from
> production
> > > server. Any idea? or any better solution?
> > >
> > > Thanks.
> > >
> > >
> > > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > > I have two different setup, one for development and other for
> > production.
> > > > Two different SQL server 2000 installed on two different Windows
2000
> > > > server. I want to import all data of selected tables from production
> > > server
> > > > to development server. Databases name on both seerver are different.
> > > Before
> > > > I import, I want to remove all information from selected tables from
> > > > development database. Can anyone suggest me TSQL code?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.
You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
|||DTS?
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table[vbcol=seagreen]
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
production.
> server
> Before
>
|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> table
production
> production.
>
|||www.sqldts.com
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
all[vbcol=seagreen]
tables[vbcol=seagreen]
> production
2000
>

How to ignore error and continue trasactional replication

Hi,
I have a trasactional replication setup in SQL 2000 with SP3. Accidentally,
I deleted a row in the suscriber table. When I deleted the same row in the
publisher database, I have error in the replication monitor "the row was not
found at the Subcriber when applying the replicated command".
Please let me know how do I get rid of this error and continue replication.
I don't want to reinitialize the subscriber again because the table is big
and takes lot of time.
I appreciate your help!!
Thanks
Chinna.
gonzo - cowboy- to hell with database consistency approach - right click on
your failed agent, select agent profiles, and then select the continue on
data consistency profile.
more cautious approach. enable logging for your distribution agent, and
restart it. Find out the row which you deleted. Get this row from teh
publisher and manually construst the insert statement and put it back in the
subscriber. Restart your distribution agent.
check out this kb article for more info on how to do the logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Hi,
> I have a trasactional replication setup in SQL 2000 with SP3.
Accidentally,
> I deleted a row in the suscriber table. When I deleted the same row in the
> publisher database, I have error in the replication monitor "the row was
not
> found at the Subcriber when applying the replicated command".
> Please let me know how do I get rid of this error and continue
replication.
> I don't want to reinitialize the subscriber again because the table is big
> and takes lot of time.
> I appreciate your help!!
> Thanks
> Chinna.
>
|||Thanks to everyone who helped me. It worked!!. I have changed back to default.
I appreciate your help!!
Thanks
Chinna.
"Hilary Cotter" wrote:

> gonzo - cowboy- to hell with database consistency approach - right click on
> your failed agent, select agent profiles, and then select the continue on
> data consistency profile.
> more cautious approach. enable logging for your distribution agent, and
> restart it. Find out the row which you deleted. Get this row from teh
> publisher and manually construst the insert statement and put it back in the
> subscriber. Restart your distribution agent.
> check out this kb article for more info on how to do the logging.
> http://support.microsoft.com/default...&Product=sql2k
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
> message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Accidentally,
> not
> replication.
>
>