When I hear Archive it makes me think you do not want to overwite the data in the Archive database, you just want to add to it. If you...

You have to create a temp table with the new schema. Insert the data from the current table. Delete the current table and then rename the temp table...

sa is not listed in the table and the Where clause is filtering out the Roles.

Can you use this?

DECLARE @LastMonDay_date DateTime

Set @LastMonDay_date =

CONVERT(varchar(10),dateadd(day, case(datename(weekday,GETDATE()))

when 'Monday' then 0

when...

You can replace the CTE with a derived table.

SELECT

CAST(su.name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],

NbrOfObjects

FROM sysusers su JOIN

(select count(*) NbrOfObjects, b.name

...

How about something like this.

WITH UserObjects

AS

(

select count(*) NbrOfObjects, b.name

from sysobjects a,

sysusers b

where a.uid = b.uid

group by b.name

)

SELECT

CAST(su.name AS sysname) COLLATE Latin1_General_CI_AS AS...

You will have to post the query. It could be a number of things.

You can use any, but you would have to do it like this.

Declare @Val1 int

Declare @Val2 int

Declare @Val3 int

Declare @Val4 int

Declare @Val5 int

Set @Val1 = 1

Set @Val2 = 1

Set @Val3...

They are automatically created.

dbo

guest

INFORMATION_SCHEMA

sys

I know you said you did not wan to write an awful case statement, but if the values are coming from database fields you could do something like this.

Declare @Val1...

It depends on your list. Is it a string or does each value come from a field?

Here is a way to get it if the list is a concatenated...

Yes. I should have clarified that in the first post. It is the number of database users not the current number of users connected.

It is using the following query.

Use DatabaseName

GO

SELECT

u.name AS [Name]

FROM

sys.database_principals AS u

WHERE

(u.type in ('U', 'S', 'G', 'C', 'K'))

It depends on how they are capturing the output. They may not be expecting an output parameter.

http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

The first line in the last query needs a name.

select ((left(pa.keycode, 1)) + cast(sum(cast(substring(pa.keycode,2,3) as int)) as varchar(5))) AS SomeName

The first query sets the variable @keycode, but I do not...