milos - Couldn't quite get yours to reorder the index descending.

ChrisM = Works perfectly, thank you.

Hia ll

I've got the query sorted out but it's horrendously slow!

This is my updated query:-

;with cte as (

SELECT

AD.VisitID

,AD.UnitNumber AS UnitNumber

,AD.AccountNumber AS AccountNumber

,ws.WardID AS Ward

,ws.WardLocation

,ws.ServiceSeqID AS ServiceSeqID

,ws.WardSeqID AS WardSeqID

,row_number() over (partition...

So the cte is almost acting as a temporary table having an insert done on it?

That makes sense.

John Mitchell-245523 (1/20/2014)

If you're getting collation conflict errors, you have three...

In that case, I'm going to leave well enough alone.

I found this script:-

DECLARE @collation NVARCHAR(64)

SET @collation = 'Latin1_General_CI_AS'

SELECT

'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] '

+ 'ALTER COLUMN ['...

So I've just found out.

If I viewed the extended properties for the relevant columns, I get this:-

Latin1_General_CI_AS

The General proeprties page shows me:-

SQL_Latin1_General_CP1_CI_AS

Looks like I do need to alter the collation...

I've checked all the relevant columns for the queries I use and they are all (including tables and databases) are on the same collation (Latin1_General_CI_AS).

Is there any way of listing...

That's what I thought, but I can't see where (not even at column level).

I'll have another look and probably come back later.

I have done that before :sick:, not doing it...

Thanks Gail

This is a production environment which is why I'm asking before I do (or even suggest) anything.

I've just double checked a few databases, tables and columns and come across...

Thanks folks, that looks like it's working perfectly.

Can someone please walk me through the code (I've never had much luck with CTEs)?

New table creation code is here:-

CREATE TABLE [dbo].[DailySnapshot_LIVE_RFT_DR_ABS_VIEW](

[UnitNumber] [varchar](30) NULL,

[AdmitDateTime] [datetime] NULL,

[DischargeDateTime] [datetime] NULL

CONSTRAINT [PK_DailySnapshot_LIVE_RFT_DR_ABS_VIEW] PRIMARY KEY CLUSTERED

(

[ViewRecNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY...

As long as I have a chair that works (even one that slowly descends), a desk that's not too badly beaten-up, a decent monitor (I agree with Knut on the...

Thanks folks.

I found some of the Microsoft tutorials but really needed something practice on. Should keep my busy this weekend. :ermm:

And if the manager is worth anything, the average programmers will be picking up...