Imitating ownership possible?

In SQL Server 2000 you could use the SETUSER command
(http://msdn2.microsoft.com/en-us/li...40(SQL.80).aspx) to
impersonate the user context for a SQL user account. However, this can only
be used by a sysadmin or db_owner and could not be used for Windows
accounts. In SQL Server 2005 you can use the EXECUTE AS clause to change the
execution context.

Here is a good article about EXECUTE AS:
http://blogs.msdn.com/sqlserverstor.../21/642061.aspx
HTH,

Plamen Ratchev
http://www.SQLStudio.comAwesome. Thanks for the quick response. I've got a lot of experience
in SQL in other languages, and some in SQL Server, but all the little
tricks of the trade I'm still short on.

Thanks again.

J

Im sure this is an easy one...Error trap to skip over a "bad" object.

Hello, I have the following code to iterate through each view in a SQL
Server and call the "sp_refreshview" command against it. It works
great until it finds a view that is damaged, or otherwise cannot be
refreshed. Then the whole routine stops working.

Can someone please help me re-write this code so that any views that
fail the "sp_refreshview" command get skipped. I'm sure it's just a
matter of putting some basic error trapping into the loop, but I've had
a few goes at it and failed.

Many thanks.

DECLARE @.DatabaseObject varchar(255)
DECLARE ObjectCursor CURSOR
FOR SELECT table_name FROM information_schema.tables WHERE table_type =
'view'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
Print @.DatabaseObject + ' was successfully refreshed.'
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO(rod.weir@.gmail.com) writes:
> Hello, I have the following code to iterate through each view in a SQL
> Server and call the "sp_refreshview" command against it. It works
> great until it finds a view that is damaged, or otherwise cannot be
> refreshed. Then the whole routine stops working.
> Can someone please help me re-write this code so that any views that
> fail the "sp_refreshview" command get skipped. I'm sure it's just a
> matter of putting some basic error trapping into the loop, but I've had
> a few goes at it and failed.

If you are on SQL 2005, lookup TRY-CATCH in Books Online.

If you are on SQL 2000, you could possibly do the linked-server trick:
http://www.sommarskog.se/error-hand...#linked-servers.

I'm not into refreshing views myself, but I can't think of a way to
detect this condition before-hand.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I need to refresh all views because there are some views that have
embedded views within them, using a Select * statement. When the
underlying view changes (new column etc), the parent view does not pick
up the new column in the embedded view that it references.

Using SQL Server 2000. Surely there must be a simple way to trap the
error and skip over it right?

Perhaps just after the following line...

EXEC sp_refreshview @.DatabaseObject

...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
primarily a VB developer, so this TSQL has got me a little puzzled.

I'll give your website a read. Thanks again.|||> ...you examine @.@.Error and ignore or continue in the loop?

Some errors will abort the batch so you are SOL after the error. If the
linked server doesn't work for you, you might try preceeding the
sp_refreshview with a select statement with SET FMTONLY ON. That will allow
you detect the error and skip the sp_refreshview for problem views.

DECLARE @.DatabaseObject nvarchar(261)
DECLARE ObjectCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'VIEW'

OPEN ObjectCursor
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
IF @.@.FETCH_STATUS = -1 BREAK
PRINT 'Refreshing view ' + @.DatabaseObject
EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject)
IF @.@.ERROR = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
PRINT 'View ' + @.DatabaseObject + ' refreshed'
END
ELSE
BEGIN
PRINT 'Error refreshing view ' + @.DatabaseObject
END
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rod.weir@.gmail.com> wrote in message
news:1143705825.644611.51750@.u72g2000cwu.googlegro ups.com...
> Thanks Erland,
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement. When the
> underlying view changes (new column etc), the parent view does not pick
> up the new column in the embedded view that it references.
> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.
> I'll give your website a read. Thanks again.|||[My newsserver had an outage, and my original post got lost. Now that it's
back, I'm reposting]

(rod.weir@.gmail.com) writes:
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement.

Did anyone tell you that this is bad practice? :-)

> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.

The problem is that there are quite few errors that abort the batch, and
those you cannot trap easily in SQL 2000. I seem to recall that refreshview
errors belongs to this group. The linked-server trick is a serious kludge,
but for this case it could be worth the pain.

Then again, if you are a VB developer, just code the loop in a VB program
or in VB script. That's probably easier than setting up linked servers for
this task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Dan,

Many thanks for your response. This code does exactly what I'm after.
It skipped over the bad queries and kept refreshing the good ones. I
modified the following line to speed it up a little bit.

EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject + 'Where 1=0')

Notice the Where 1 = 0 clause? Much quicker now.

Thanks Dan and Erland. Problem solved.

p.s. Erland. I am going to start another thread on the evils of
embedded queries. I have heard a lot of people say that this is a bad
practice, however I've never heard any really compelling evidence to
say why.

I'm newbie. Script to generate scripts (add job steps).

Hi, I want to add to my 50 single step jobs a second step with command
"execute JOB_STEP_FAIL <jobname>" where <jobname> is job name from sysjobs
table.
I want to use sp_add_jobstep .
I do not want to make this job by hand.
Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!If you have a way to identify the 50 Jobs you want to add a step to, then us
e
a cursor, or table variable, or while loop, to run sp_add_jobstep for each
one...
Say the job names all start with 'DD'
Then
Select Job_id
From msdb..sysjobs
Where name like 'DD%'
Will give you list of all jobs
Then you can use a while loop
Declare @.JobName varChar(200) Set @.JobName = ''
While Exists (Select * From msdb..sysJobs
Where name like 'DD%'
And name > @.JobName)
Begin
Select @.JobName = Min(name )
From msdb..sysJobs
Where name like 'DD%'
And name > @.JobName
-- --
Exec sp_Add_Job_Step
@.job_name = @.JobName,
@.step_id = 2,
@.Step_Name = 'FailStep',
@.subsystem = 'TSQL',
@.command = 'execute JOB_STEP_FAIL ' + @.JobName
End
But if <jobName> in your post is just the same job that just failed, it
looks like you are trying to reattempt the same job if it gets to this step,
But there is both a ReAttempt , and a Fail method on each jobStep that are
more appropriate for this purpose... You should investigate those
"andrea favero" wrote:

> Hi, I want to add to my 50 single step jobs a second step with command
> "execute JOB_STEP_FAIL <jobname>" where <jobname> is job name from sysjob
s
> table.
> I want to use sp_add_jobstep .
> I do not want to make this job by hand.
> Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!|||Thanks a lot for your fast answer.
Thanks
"CBretana" wrote:
> If you have a way to identify the 50 Jobs you want to add a step to, then
use
> a cursor, or table variable, or while loop, to run sp_add_jobstep for each
> one...
> Say the job names all start with 'DD'
> Then
> Select Job_id
> From msdb..sysjobs
> Where name like 'DD%'
> Will give you list of all jobs
> Then you can use a while loop
> Declare @.JobName varChar(200) Set @.JobName = ''
> While Exists (Select * From msdb..sysJobs
> Where name like 'DD%'
> And name > @.JobName)
> Begin
> Select @.JobName = Min(name )
> From msdb..sysJobs
> Where name like 'DD%'
> And name > @.JobName
> -- --
> Exec sp_Add_Job_Step
> @.job_name = @.JobName,
> @.step_id = 2,
> @.Step_Name = 'FailStep',
> @.subsystem = 'TSQL',
> @.command = 'execute JOB_STEP_FAIL ' + @.JobName
> End
>
> But if <jobName> in your post is just the same job that just failed, it
> looks like you are trying to reattempt the same job if it gets to this ste
p,
> But there is both a ReAttempt , and a Fail method on each jobStep that are
> more appropriate for this purpose... You should investigate those
>
> "andrea favero" wrote:
>

I'm a SQL Beginner

I continue to get syntax errors.
What is the command line for "List any titles that do not have a price
assigned" and list each title with its current cost and with the projected
cost increase of 15%"?No one can really tell you as you didn't provide any
information on the tables, what specific errors you are
getting, what statement you are using, etc. Generally,
something like:
Select titles
from your table
where price is null
and
select title, cost, cost + cost*.15 as IncreasedCost
from your table
-Sue
On Sun, 26 Mar 2006 14:43:59 -0800, Smokey
<Smokey@.discussions.microsoft.com> wrote:

>I continue to get syntax errors.
>What is the command line for "List any titles that do not have a price
>assigned" and list each title with its current cost and with the projected
>cost increase of 15%"?

I'm a SQL Beginner

I continue to get syntax errors.
What is the command line for "List any titles that do not have a price
assigned" and list each title with its current cost and with the projected
cost increase of 15%"?
No one can really tell you as you didn't provide any
information on the tables, what specific errors you are
getting, what statement you are using, etc. Generally,
something like:
Select titles
from your table
where price is null
and
select title, cost, cost + cost*.15 as IncreasedCost
from your table
-Sue
On Sun, 26 Mar 2006 14:43:59 -0800, Smokey
<Smokey@.discussions.microsoft.com> wrote:

>I continue to get syntax errors.
>What is the command line for "List any titles that do not have a price
>assigned" and list each title with its current cost and with the projected
>cost increase of 15%"?

IIF Statement on Select

Is there a way in an SQL Select statement to set up something similar to the
IIf command in Access:
Select LastName, IIf(CodeA = "Y","Yes'","No")
From NameTablermcompute,
Try using a "case" expression.
Select LastName, case when CodeA = 'Y' then 'Yes' else 'No' end as CodeA
From NameTable
AMB
"rmcompute" wrote:

> Is there a way in an SQL Select statement to set up something similar to t
he
> IIf command in Access:
> Select LastName, IIf(CodeA = "Y","Yes'","No")
> From NameTable|||There is no IIF in SQL Server, check out the CASE expression in Books
Online. Also, suggest you stay away from " as string delimiters.
SELECT LastName, CodeA = CASE CodeA WHEN 'Y' THEN 'Yes' ELSE 'No' END
FROM NameTable
Or
SELECT LastName, CodeA = CASE WHEN CodeA = 'Y' THEN 'Yes' ELSE 'No' END
FROM NameTable
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:2DA1A89A-17DA-4E9C-AF6F-AC37C2337277@.microsoft.com...
> Is there a way in an SQL Select statement to set up something similar to
> the
> IIf command in Access:
> Select LastName, IIf(CodeA = "Y","Yes'","No")
> From NameTable|||Thank you.
"Alejandro Mesa" wrote:
> rmcompute,
> Try using a "case" expression.
> Select LastName, case when CodeA = 'Y' then 'Yes' else 'No' end as CodeA
> From NameTable
>
> AMB
> "rmcompute" wrote:
>|||Thank you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There is no IIF in SQL Server, check out the CASE expression in Books
> Online. Also, suggest you stay away from " as string delimiters.
> SELECT LastName, CodeA = CASE CodeA WHEN 'Y' THEN 'Yes' ELSE 'No' END
> FROM NameTable
> Or
> SELECT LastName, CodeA = CASE WHEN CodeA = 'Y' THEN 'Yes' ELSE 'No' END
> FROM NameTable
>
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:2DA1A89A-17DA-4E9C-AF6F-AC37C2337277@.microsoft.com...
>
>