sqlbee19 (5/23/2012)

---

Please run below code

----------------------------

set nocount on

declare @role varchar(255),@dbname varchar(255)

declare @rowid int

declare @user nvarchar(255)

declare @cmd varchar(8000)

set @dbname = 'Mydatabasedashboardaaaaaaaaa1111'

set @role = 'db_owner'...

you need to use a ranking function to find the first instance of a value

see my example below

select x.* from

(

select id,name,ROW_NUMBER() over (partition by id order by name asc)...

Steve Jones - SSC Editor (5/23/2012)

---

Wouldn't that be?

Mr or Mrs or Miss or Ms 500

Too long, and to be honest, didn't think about it. Plus, could you get to 500...

lucky it's a test box

+1 howard beat me to it, but slightly different solution

derek.colley (5/23/2012)

---

OK, I'm trying and failing (to an extent) to get my head around set-based iteration.

As a sometime dev who is used to loops (go VBScript go!) and more 'traditional'...

in that same database there was a checkpoint that seemed to have hung - looks like that was the issue - the checkpoint was doing nothing and had been for...

computed and PERSISTED would be even better

To answer your question, absolutely not. And that's why this problem won't be fixed until it becomes a real problem. It's also very typical. It's the reason...

Amen to that - praise be to edgar F codd and Raymond F Boyce!!!!!

alter the CTE to be as follows

with x (mon,monid,monthord) as

(select DATENAME(month,1) as month,1 as monid,1 as monthord

UNION ALL

select DATENAME(month,X.monid+31) as month,X.monid+31 as monid,x.monthord+1 as monthord from x

where monid<=311

)

now you have...

not really - in sql 2000 it was an enterprise edition...

hmm

but the funny thing is that yesterday the query was working fine.... what can have changed to make the showcontig stall ?

i simplified the code just to match the CTE i wrote for you, but it would look a little like this

with x (mon,monid) as

(select DATENAME(month,1) as month,1 as monid

UNION...

sanujss (5/23/2012)

---

Hi,

I am working on a GPS related issue. There is a table where we keep geographical information of vehicles tracked using GPS devices. This devise will be sending...