Lets clarify something here!

Are you syaing that the DBCC CHECKDB and the ETL is running at the same time each night?

If so, DBCC CHECKDB will always lock the tables!

Also, is...

You may have to place this at the top of your code,

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT ...........

UPDATE ...........

COMMIT TRANSACTION

Unless this is some sort of a joke, just go into EM and right click the Subscription and DELETE it!

Check and see if the Table Test has a Primary Key.

Obviously the people you have been talking to don't know how to use SQL Server Replication or don't know how to keep data synchronised with SQL Replication!

You have 2 choices.

1....

The front end application doesn't use any date settings it just passes the 2 variables as strings and now the stored procedure does the remaining conversion to DATETIME Datatypes.

On the...

Ok. Thanks Sergiy. I used the Style 103 and it works.

I have learnt alot about converting Dates today.

Thanks.

I think you may have made a typo on the @EndDateString!

Here is what I have tried but it doesn't seem to like to CONVERT the @EndDate!

DECLARE @StartDate NVARCHAR(20), @EndDate NVARCHAR(20)

SET...

For option 2. Are you syaing to DECLARE @StartDate & @EndDate as NVARCHARS and then CONVERT them to DATETIME in SP?

So how do I get around this?

The user has to pass the date variables as DD/MM/YYYY Australian format.

How do I make it work given the facts Above?

Sergiy,

What do you mean "And you convert you parameters to DATETIME out of SP scope because parameters are datetime datatype.There is no point for SET DATEFORMAT DMY inside this...

Aaahhhh... There is always something to learn. Thanks for clarifying that! It all makes sense.

Thanks.

Thats fair enough PW but the Clustered Index in this case is Unique as it is the Primary Key!

I know your command will work but i thought that when you...

Because when I did a DBCC SHOWCONTIG on the NON Clustered INDEX, the Fragmentation still exists!

So then why didn't the 4 NON Clustered Indexes get rebuilt when I issued the command below on the Clustered Index?

DBCC DBREINDEX ('Act', PKActID, 90)