Clear all the tables of a database

Is there a simple method to clear all the tables in a db or to make a copy of the mdf file without the datas ?

I tried to use TRUNCATE TABLE but it doesn't work with foreign keys ?

Thanks.

You probably will have to write your own script for this to reflect the dependencies. As you already stated, Truncate won′t work with foreign keys and an undocumented procedure like sp_msforeachtable won′t care about the order to process.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Clean up replication data

I have a develop machine for our web site. On this machine I have a copy of a database from the production site. The database on the production site is being replicated. When I copied the database to the develop machine and started testing some new developments I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'

This tells me that some replication data is also copied to the develop database.

Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:

USE [DBNAME]
GO

-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'

DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)

DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO

-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)

SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'

DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT

SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050

DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT

SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0

DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT

SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'

DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur

GO

-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'

DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)

DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication

Your script did it!

Checking Date column within the same table - DDL included

Hi all,
I have included All sample data, just copy and paste in Query Analizer
I would like to modify the query below to return all that had a prior
"pretest" 30 days prior or equal to
the "test". so the return result that I would like would look like this:
1 A 1997/12/08 pretest
1 A 1997/12/09 test
1 A 1997/12/11 test
3 C 1997/12/18 pretest
3 C 1997/12/19 test
4 D 1997/12/15 pretest
4 D 1997/12/16 test
5 E 1997/12/17 test
5 E 1997/12/17 pretest
6 F 1998/08/03 pretest
6 F 1998/08/04 test
6 F 1998/08/18 test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'AppointmentTable')
Begin
DROP TABLE AppointmentTable
End
CREATE TABLE [AppointmentTable] (
[tableid] [int] IDENTITY (1, 1) NOT NULL ,
[nameid] [numeric](18, 0) NOT NULL ,
[fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appointmentdate] [datetime] NOT NULL ,
[type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/08', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/09', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '1997/12/11', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '2003/05/07', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('1','A', '2003/05/08', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('2','B', '1997/12/12', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('2','B', ' 1998/02/24', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('3','C', '1997/12/18', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('3','C', '1997/12/19', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('4','D', '1997/12/15', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('4','D', '1997/12/16', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('5','E', '1997/12/17', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('5','E', '1997/12/17', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/03', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/04', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1998/08/18', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1999/07/07', 'pretest')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '1999/08/31', 'test')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
VALUES ('6','F', '2001/05/15', 'test')
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment Date], type as [Test
Type]
FROM AppointmentTable
ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type]DESC
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'AppointmentTable')
Begin
DROP TABLE AppointmentTable
End
Thanks for your help
GerryThis works using a UNION!! would there be a better way to write it?
Select u1.nameid as [nameid],u1.fullname as [Full Name], u1.appointmentdate
as [Exam Date], u1.type as [Procedure Type]
from appointmenttable u1
where u1.type = 'pretest'
and exists (SELECT * from appointmenttable u2
where u1.nameid = u2.nameid
and u2.type = 'test'
and u1.appointmentdate <= u2.appointmentdate
and Datediff(day,u1.appointmentdate ,u2.appointmentdate)
<= 30)
UNION
Select u2.nameid as [nameid],u2.fullname as [Full Name], u2.appointmentdate
as [Exam Date], u2.type as [Procedure Type]
from appointmenttable u2
where u2.type = 'test'
and exists (SELECT * from appointmenttable u1
where u1.nameid = u2.nameid
and u1.type = 'pretest'
and u1.appointmentdate <= u2.appointmentdate
and Datediff(day,u1.appointmentdate ,u2.appointmentdate)
<= 30)
ORDER BY [nameid],[Full Name],[Exam Date],[Procedure Type]DESC
thanks
Gerry
"gv" <viatorg@.musc.edu> wrote in message
news:OgIrxTr7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have included All sample data, just copy and paste in Query Analizer
> I would like to modify the query below to return all that had a prior
> "pretest" 30 days prior or equal to
> the "test". so the return result that I would like would look like this:
> 1 A 1997/12/08 pretest
> 1 A 1997/12/09 test
> 1 A 1997/12/11 test
> 3 C 1997/12/18 pretest
> 3 C 1997/12/19 test
> 4 D 1997/12/15 pretest
> 4 D 1997/12/16 test
> 5 E 1997/12/17 test
> 5 E 1997/12/17 pretest
> 6 F 1998/08/03 pretest
> 6 F 1998/08/04 test
> 6 F 1998/08/18 test
>
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
> 'AppointmentTable')
> Begin
> DROP TABLE AppointmentTable
> End
> CREATE TABLE [AppointmentTable] (
> [tableid] [int] IDENTITY (1, 1) NOT NULL ,
> [nameid] [numeric](18, 0) NOT NULL ,
> [fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [appointmentdate] [datetime] NOT NULL ,
> [type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/08', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/09', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '1997/12/11', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '2003/05/07', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('1','A', '2003/05/08', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('2','B', '1997/12/12', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('2','B', ' 1998/02/24', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('3','C', '1997/12/18', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('3','C', '1997/12/19', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('4','D', '1997/12/15', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('4','D', '1997/12/16', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('5','E', '1997/12/17', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('5','E', '1997/12/17', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/03', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/04', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1998/08/18', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1999/07/07', 'pretest')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '1999/08/31', 'test')
> INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type)
> VALUES ('6','F', '2001/05/15', 'test')
>
> SELECT nameid as [Name ID],fullname as [Full Name],
> CONVERT(varchar,appointmentdate, 111) as [Appointment Date], type as [Test
> Type]
> FROM AppointmentTable
> ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type]DESC
>
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
> 'AppointmentTable')
> Begin
> DROP TABLE AppointmentTable
> End
> Thanks for your help
> Gerry
>
>
>
>
>
>
>
>
>
>
>|||See if this helps:
select aouter.*
from #AppointmentTable aouter
where exists
(select * from #AppointmentTable
where aouter.nameid = nameid
and ((aouter.type = 'test'
and type = 'pretest'
and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
or (aouter.type = 'pretest'
and type = 'test'
and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
)
"gv" wrote:

> This works using a UNION!! would there be a better way to write it?
> Select u1.nameid as [nameid],u1.fullname as [Full Name], u1.appointmentdate
> as [Exam Date], u1.type as [Procedure Type]
> from appointmenttable u1
> where u1.type = 'pretest'
> and exists (SELECT * from appointmenttable u2
> where u1.nameid = u2.nameid
> and u2.type = 'test'
> and u1.appointmentdate <= u2.appointmentdate
> and Datediff(day,u1.appointmentdate ,u2.appointmentdat
e)
> <= 30)
> UNION
> Select u2.nameid as [nameid],u2.fullname as [Full Name], u2.appointmentdate
> as [Exam Date], u2.type as [Procedure Type]
> from appointmenttable u2
> where u2.type = 'test'
> and exists (SELECT * from appointmenttable u1
> where u1.nameid = u2.nameid
> and u1.type = 'pretest'
> and u1.appointmentdate <= u2.appointmentdate
> and Datediff(day,u1.appointmentdate ,u2.appointmentdat
e)
> <= 30)
> ORDER BY [nameid],[Full Name],[Exam Date],[Procedure Type]DESC
> thanks
> Gerry
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:OgIrxTr7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
>|||THANKS!!! BIG HELP!!!!
Gerry
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:0EF60332-ADE8-4C4D-9481-A1E8FB733234@.microsoft.com...
> See if this helps:
> select aouter.*
> from #AppointmentTable aouter
> where exists
> (select * from #AppointmentTable
> where aouter.nameid = nameid
> and ((aouter.type = 'test'
> and type = 'pretest'
> and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
> or (aouter.type = 'pretest'
> and type = 'test'
> and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
> )
> "gv" wrote:
>|||thanks again for your help
What if I wanted to only return the latest 65 distinct nameid based on
appointmentdate?
thanks
Gerry
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:0EF60332-ADE8-4C4D-9481-A1E8FB733234@.microsoft.com...
> See if this helps:
> select aouter.*
> from #AppointmentTable aouter
> where exists
> (select * from #AppointmentTable
> where aouter.nameid = nameid
> and ((aouter.type = 'test'
> and type = 'pretest'
> and DateDiff(d,appointmentdate,aouter.appointmentdate) between 0 and 30)
> or (aouter.type = 'pretest'
> and type = 'test'
> and DateDiff(d,aouter.appointmentdate,appointmentdate) between 0 and 30))
> )
> "gv" wrote:
>|||Hopefully this will help u:
select top 65 a.nameid
from #AppointmentTable a
where exists
(select * from #AppointmentTable b
where a.nameid = b.nameid and a.type <> b.type
and
((a.type = 'pretest' and datediff(d,a.appointmentdate,b.appointmentdate)
between 0 and 30)
or
(a.type = 'test' and datediff(d,b.appointmentdate,a.appointmentdate) between
0 and 30))
)
group by a.nameid
order by max(a.appointmentdate) desc
"gv" wrote:

> thanks again for your help
> What if I wanted to only return the latest 65 distinct nameid based on
> appointmentdate?
> thanks
> Gerry|||Thank you so much for your help!!!
Very Close!
The 65 needs to be Distinct from column namid so there could actually be
over 130 rows returned but only 65 different
nameid and within each nameid the pretest needs to be listed first. But
within each different nameid
you have it right where the order of Max appointmentdate is coming first.
Thanks again for your help Absar
Gerry :< )
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:74B787F2-88D4-4120-858B-1F1B8FA8B34A@.microsoft.com...
> Hopefully this will help u:
> select top 65 a.nameid
> from #AppointmentTable a
> where exists
> (select * from #AppointmentTable b
> where a.nameid = b.nameid and a.type <> b.type
> and
> ((a.type = 'pretest' and datediff(d,a.appointmentdate,b.appointmentdate)
> between 0 and 30)
> or
> (a.type = 'test' and datediff(d,b.appointmentdate,a.appointmentdate)
> between
> 0 and 30))
> )
> group by a.nameid
> order by max(a.appointmentdate) desc
> "gv" wrote:
>
>|||On Wed, 23 Nov 2005 14:29:56 -0500, gv wrote:

>Thank you so much for your help!!!
>Very Close!
>The 65 needs to be Distinct from column namid so there could actually be
>over 130 rows returned but only 65 different
>nameid and within each nameid the pretest needs to be listed first. But
>within each different nameid
>you have it right where the order of Max appointmentdate is coming first.
>Thanks again for your help Absar
>Gerry :< )
Hi Gerry,
Maybe this is what you need?
(Note - I also changed the way to determine the date range in order to
have a better chance of using an index -if there is any- on the
appointmentdate column).
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment
Date],
type as [Test Type]
FROM AppointmentTable AS a
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS b
WHERE b.nameid = a.nameid
AND b.type <> a.type
AND b.appointmentdate BETWEEN CASE WHEN a.type = 'test'
THEN DATEADD (day, -30,
a.appointmentdate)
ELSE a.appointmentdate
END
AND CASE WHEN a.type = 'test'
THEN a.appointmentdate
ELSE DATEADD (day, 30,
a.appointmentdate)
END)
AND a.nameid IN
(SELECT TOP 65 c.nameid
FROM AppointmentTable AS c
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS d
WHERE d.nameid = c.nameid
AND d.type <> c.type
AND d.appointmentdate BETWEEN CASE WHEN c.type = 'test'
THEN DATEADD (day, -30,
c.appointmentdate)
ELSE c.appointmentdate
END
AND CASE WHEN c.type = 'test'
THEN c.appointmentdate
ELSE DATEADD (day, 30,
c.appointmentdate)
END)
GROUP BY c.nameid
ORDER BY MAX(appointmentdate))
ORDER BY [Name ID],[Full Name],[Appointment Date],[Test Type] DESC
I've tested this against the test data you supplied (thanks for that, by
the way!). If I change the TOP 65 to TOP 3, I only get the results for
nameid 1, 4, and 5.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much for your help!
Almost got it.
I've added some more test data and included your query. Doesn't return the c
orrect top 6 based on appointmentdate. And
within each id the appointmentdate should be ordered by earlist date first b
ut it is correct to order the latest date of the id first.
sorry if I was not clear enough before. I would also like it to only return
those that had "checked"
at least once next to "test" within a group. See all sample data below tha
nks again Gerry
Returns this:
7 G 2005/07/07 test checked
7 G 2005/07/06 pretest -
8 H 2005/06/24 test checked
8 H 2005/06/22 test checked
8 H 2005/06/19 pretest -
9 I 2005/05/22 test
9 I 2005/05/20 pretest -
10 J 2005/04/10 test checked
10 J 2005/04/05 pretest -
11 K 2005/03/16 test checked
11 K 2005/03/14 pretest -
7 G 2004/05/05 test
7 G 2004/05/04 pretest -
7 G 2002/09/25 test
7 G 2002/09/04 pretest -
6 F 1998/08/18 test
6 F 1998/08/04 test checked
6 F 1998/08/03 pretest -
7 G 1992/07/30 test
7 G 1992/07/08 pretest -
should return this and in this order:
7 G 2005/07/06 pretest -
7 G 2005/07/07 test checked
8 H 2005/06/19 pretest -
8 H 2005/06/22 test checked
8 H 2005/06/24 test
9 I 2005/05/20 pretest -
9 I 2005/05/22 test checked
10 J 2005/04/05 pretest -
10 J 2005/04/10 test checked
11 K 2005/03/14 pretest -
11 K 2005/03/16 test checked
7 G 2004/05/04 pretest -
7 G 2004/05/05 test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Appo
intmentTable')
Begin
DROP TABLE AppointmentTable
End
CREATE TABLE [AppointmentTable] (
[tableid] [int] IDENTITY (1, 1) NOT NULL ,
[nameid] [numeric](18, 0) NOT NULL ,
[fullname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appointmentdate] [datetime] NOT NULL ,
[type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/09', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '1997/12/11', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '2003/05/07', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('1','A', '2003/05/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('2','B', '1997/12/12', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('2','B', ' 1998/02/24', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('3','C', '1997/12/18', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('3','C', '1997/12/19', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('4','D', '1997/12/15', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('4','D', '1997/12/16', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('5','E', '1997/12/17', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('5','E', '1997/12/17', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/03', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/04', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1998/08/18', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1999/07/07', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '1999/08/31', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('6','F', '2001/05/15', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2005/07/06', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2005/07/07', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2004/05/04', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2004/05/05', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2002/09/04', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '2002/09/25', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '1992/07/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('7','G', '1992/07/30', 'test','')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/19', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/22', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('8','H', '2005/06/24', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('9','I', '2005/05/20', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('9','I', '2005/05/22', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('10','J', '2005/04/05', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('10','J', '2005/04/10', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('11','K', '2005/03/14', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('11','K', '2005/03/16', 'test','checked')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('12','L', '2005/02/08', 'pretest','-')
INSERT INTO AppointmentTable (nameid,fullname,appointmentdate,type,st
atus)
VALUES ('12','L', '2005/03/11', 'test','checked')
SELECT nameid as [Name ID],fullname as [Full Name],
CONVERT(varchar,appointmentdate, 111) as [Appointment Date],
type as [Test Type],status
FROM AppointmentTable AS a
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS b
WHERE b.nameid = a.nameid
AND b.type <> a.type
AND b.appointmentdate BETWEEN CASE WHEN a.type = 'test'
THEN DATEADD (day, -30,a.appointmentdate)
ELSE a.appointmentdate
END
AND CASE WHEN a.type = 'test'
THEN a.appointmentdate
ELSE DATEADD (day, 30,a.appointmentdate)
END)
AND a.nameid IN
(SELECT TOP 6 c.nameid
FROM AppointmentTable AS c
WHERE EXISTS
(SELECT *
FROM AppointmentTable AS d
WHERE d.nameid = c.nameid
AND d.type <> c.type
AND d.appointmentdate BETWEEN CASE WHEN c.type = 'test'
THEN DATEADD (day, -30,c.appointmentdate)
ELSE c.appointmentdate
END
AND CASE WHEN c.type = 'test'
THEN c.appointmentdate
ELSE DATEADD (day, 30,c.appointmentdate)
END)
GROUP BY c.nameid
ORDER BY MAX(appointmentdate)DESC )
ORDER BY [Appointment Date]desc
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Appo
intmentTable')
Begin
DROP TABLE AppointmentTable
End|||On Mon, 28 Nov 2005 15:06:38 -0500, gv wrote:

>Thanks so much for your help!
>Almost got it.
>I've added some more test data and included your query. Doesn't return the
correct top 6 based on appointmentdate. And
>within each id the appointmentdate should be ordered by earlist date first
but it is correct to order the latest date of the id first.
>sorry if I was not clear enough before. I would also like it to only return
those that had "checked"
>at least once next to "test" within a group. See all sample data below thanks
again Gerry
Hi Gerry,
Thanks for posting CREATE TABLE and INSERT statements. Helps a lot!
Before I start writing a query, let me clarify what I think you want to
get from your data. Correct me if I'm wrong.
- You need to find "pretest" with a "test" for the same nameid in the
next 30 days.
- Of those, you only wwant to select the 6 (or 65) most recent rows.
- The "pretest" rows should be returned in the order of most recent row
first.
- But the "test" rows should FOLLOW the accompanying "pretest" row, and
should be presented oldest first.
Finally, in the required output you have given, you have changed the
contents of these rows:

>8 H 2005/06/24 test checked
>8 H 2005/06/22 test checked
to this:

>8 H 2005/06/22 test checked
>8 H 2005/06/24 test
Why did you omit "checked" on this row? Why didn't you omit it on any
other rows? I don't understand that part of your requirement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CHECKALLOC error

Dear all,
When I launch a pump from a DTS appears the following errror:
Backup operations, CHECKALLOC, massive copy, SELECT INTO and the
manipulation operations of files in a current database must be done serial.
Launch again the statement after ended the current operation.
I haven't idea what happend, morevoer I've seen that in that sql server no
backups running.
Does anyone ever experienced this situation? Any thought will be welcomed.
Regards,
EnricEnric
How big is your data to be insertded ?
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:6BC63150-F39B-4585-9A99-5050ADE37A2B@.microsoft.com...
> Dear all,
> When I launch a pump from a DTS appears the following errror:
> Backup operations, CHECKALLOC, massive copy, SELECT INTO and the
> manipulation operations of files in a current database must be done
> serial.
> Launch again the statement after ended the current operation.
> I haven't idea what happend, morevoer I've seen that in that sql server no
> backups running.
> Does anyone ever experienced this situation? Any thought will be welcomed.
> Regards,
> Enric|||Hi
You don't say what other tasks are in the package, but at a guess you need
to do execute each task serially, try putting them all on the main thread
(right click the transformation and change the workflow properties).
John
"Enric" wrote:

> Dear all,
> When I launch a pump from a DTS appears the following errror:
> Backup operations, CHECKALLOC, massive copy, SELECT INTO and the
> manipulation operations of files in a current database must be done serial
.
> Launch again the statement after ended the current operation.
> I haven't idea what happend, morevoer I've seen that in that sql server no
> backups running.
> Does anyone ever experienced this situation? Any thought will be welcomed.
> Regards,
> Enric|||3325 KB. Just a plain file.
W in w out, happen the same.
Let me know
"Uri Dimant" wrote:

> Enric
> How big is your data to be insertded ?
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:6BC63150-F39B-4585-9A99-5050ADE37A2B@.microsoft.com...
>
>|||I'm so sorry all of you, effectively there was a backup running against that
db...
...
!!
Thanks a lot anyway,
Enric
"John Bell" wrote:
> Hi
> You don't say what other tasks are in the package, but at a guess you need
> to do execute each task serially, try putting them all on the main thread
> (right click the transformation and change the workflow properties).
> John
> "Enric" wrote:
>

Check Whether Or not a File is a New One

Hi,

I need to set up a package to copy a file from a network share to a server. Before the copy job, I need to check whether the file, e.g. test.txt, is created within one day, if not, then check again an hour later.

Did you search this forum? I know this question has been asked a couple times before; I just could fine this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=789950&SiteID=1