ahem.... the tester didn't put the defect there (the developer did)...

see my example

create table footable (name varchar(1000))

go

select 'aaaaa';

with x (id) as

(select 1 as id

union all

select ID+1 from X as id

where id<10000

)

insert into fooTable

select d.name from sysdatabases d cross...

any code before the "WITH x as" must be terminated with a ;

the multiple rows returned in your select statement are corrupting the @cmd value - in theory the value returned should be correct (although if you use any fields from sys.server_principals...

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...