Bug in Copy database routine

I copied some databases from a SQL 2000 server to a SQL 2005 server. All the tables in the databases with auto incrementing identity columns (not for replication) had the identity information removed from the table definition. I used the SQL Management Object method.This is a known issue in SMO that is expected to be fixed in Server Pack 1.

Peter|||So what is the recommended way to copy databases from SQL 2000 to SQL 2005?

British Summer Time stopping SQL backup routine

Hi All
I have set up several DB Maintenance Plans in my SQL 2000 installation to
backup 3 sep DBs to a designated 'day' folder (ie 7 different folders to
cover Mon - Sun) on the same HD every day at 1am. This was working fine
everyday until the clocks moved forward by 1 hour to accommodate BST
(British Summer Time) and then the backups stopped backing up.
Now my PC Server being a good SBS 2000 installation auto-changed it's clock
to 1 hour forward, but the backups have just stopped working.
To correct this, I simply went into each DB Maintenance plan , opened up the
schedule, unticked a day, ticked it again and then saved it (ala what I call
a refresh) and everything started working again.
I know I could put these backups into 1 folder so I didn't have to update 21
or so individual plans, but I need to put it into these day folders to make
it easier for the day-to-day monitor-er of these backups.
I also know this problem only appears to happen twice a year (day when the
clocks go back and day when the clocks go forward), but because it is such
an unnecessary procedure, I just wanted to ask you if there was any fix for
it.
Many thanks.
Rgds
RobbieThere's not really a "fix", as there isn't really a problem, at least not
with SQL Server. 1:00 AM doesn't exist on the day you go to summer time, as
the clock moves from 00:59:59 GMT to 02:00:00 BST. On the other side, you
would have 1:00 AM twice when the clocks go back.
The best practice, if you want to avoid these little irritations, is not to
schedule anything within this period, and instead start your backup at 00:59
AM.
--
Jacco Schalkwijk
SQL Server MVP
"Astra" <info@.noemail.com> wrote in message
news:emGph0QNFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have set up several DB Maintenance Plans in my SQL 2000 installation to
> backup 3 sep DBs to a designated 'day' folder (ie 7 different folders to
> cover Mon - Sun) on the same HD every day at 1am. This was working fine
> everyday until the clocks moved forward by 1 hour to accommodate BST
> (British Summer Time) and then the backups stopped backing up.
> Now my PC Server being a good SBS 2000 installation auto-changed it's
> clock
> to 1 hour forward, but the backups have just stopped working.
> To correct this, I simply went into each DB Maintenance plan , opened up
> the
> schedule, unticked a day, ticked it again and then saved it (ala what I
> call
> a refresh) and everything started working again.
> I know I could put these backups into 1 folder so I didn't have to update
> 21
> or so individual plans, but I need to put it into these day folders to
> make
> it easier for the day-to-day monitor-er of these backups.
> I also know this problem only appears to happen twice a year (day when the
> clocks go back and day when the clocks go forward), but because it is such
> an unnecessary procedure, I just wanted to ask you if there was any fix
> for
> it.
> Many thanks.
> Rgds
> Robbie
>|||Dohhh!!!
Many thanks Jacco
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ue9SDFRNFHA.3156@.TK2MSFTNGP15.phx.gbl...
There's not really a "fix", as there isn't really a problem, at least not
with SQL Server. 1:00 AM doesn't exist on the day you go to summer time, as
the clock moves from 00:59:59 GMT to 02:00:00 BST. On the other side, you
would have 1:00 AM twice when the clocks go back.
The best practice, if you want to avoid these little irritations, is not to
schedule anything within this period, and instead start your backup at 00:59
AM.
--
Jacco Schalkwijk
SQL Server MVP
"Astra" <info@.noemail.com> wrote in message
news:emGph0QNFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have set up several DB Maintenance Plans in my SQL 2000 installation to
> backup 3 sep DBs to a designated 'day' folder (ie 7 different folders to
> cover Mon - Sun) on the same HD every day at 1am. This was working fine
> everyday until the clocks moved forward by 1 hour to accommodate BST
> (British Summer Time) and then the backups stopped backing up.
> Now my PC Server being a good SBS 2000 installation auto-changed it's
> clock
> to 1 hour forward, but the backups have just stopped working.
> To correct this, I simply went into each DB Maintenance plan , opened up
> the
> schedule, unticked a day, ticked it again and then saved it (ala what I
> call
> a refresh) and everything started working again.
> I know I could put these backups into 1 folder so I didn't have to update
> 21
> or so individual plans, but I need to put it into these day folders to
> make
> it easier for the day-to-day monitor-er of these backups.
> I also know this problem only appears to happen twice a year (day when the
> clocks go back and day when the clocks go forward), but because it is such
> an unnecessary procedure, I just wanted to ask you if there was any fix
> for
> it.
> Many thanks.
> Rgds
> Robbie
>

Breaking a row of data into multiple CSV rows

Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.

Basically I need to do the following.

Selected from database:
Row1 Col1 Col2 Col3

Output:
Row1 Col1, Col2, Col3, abc
Row1 Col1, Col2, Col3, def
Row1 Col1, Col2, Col3, ghi

Thank youThis might help with part of it:

SELECT '"' + Row1 + '","' + Col1 + '","' + Col2 + '","' + Col3 + + '";'|||You could do a SELECT INTO 3 times for each "row" in the original table. It would be like:

SELECT INTO newtable
Col1, Col2, Col3, 'abc' AS Col4
FROM oldtable
WHERE Row=1

SELECT INTO newtable
Col1, Col2, Col3, 'def' AS Col4
FROM oldtable
WHERE Row=1

SELECT INTO newtable
Col1, Col2, Col3, 'ghi' AS Col4
FROM oldtable
WHERE Row=1

Then you would convert newtable to CSV afterward (with BCP maybe).
That's assuming there is a column named Row with a value of 1. But if you have a key column ( a field with unique values), the new table can't use that as a key column because you'll be making three copies of each row.
But since your example wasn't very specific, either is the answer.

Another method is to use a cursor and process each row.|||I think using the cursor would be a better option since the number of rows that i need to "break" are in thousands.

I am just not sure how to use cursor.|||Have a look in BOL (Books online) to see how cursors, but be warned that cursors can be inefficient, particularly with a 'large' number of rows - some people on this forum positively detest them!
Think about using temporary tables/table variables instead.

Lempster|||Funny you should ask

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_CSVTable]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_CSVTable]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION udf_CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN

DECLARE @.i int, @.c varchar(100)

SELECT @.Str = @.Str + ',', @.i = 1, @.c = ''

WHILE @.i <= len(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN isnumeric(@.c)=1 THEN @.c else Null END
, rtrim(ltrim(@.c))
, CASE WHEN isdate(@.c)=1 then @.c else Null END)
SET @.c = ''
END
ELSE
SET @.c = @.c + SUBSTRING(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO