At the moment I'm using table variables just to simulate the UNION ALL: in other words I'm using a table variable to store the output of a query, then I...

This is the method I was talking about:

http://blogs.technet.com/b/wardpond/archive/2005/08/01/408502.aspx

I find it a bit tricky and doesn't help code readability.

It's an option, anyway.

There are other ways to achieve the same, but they're a bit complicated.

One is using OPENROWSET to call the procedure, but if you find using a table variable to be...

It's not possible to retrieve current password. You can only change it.

Can you log in with a different account and reset that password?

You probably want to set up a SQL Agent job. Triggers are not time driven, but event driven.

Would a job solve your issue?

Jeff Moden (7/4/2010)

---

My recommendation would be to exercise the query that returns the largest number of rows first and use that to spontaneously create a Temp Table using SELECT/INTO. ...

That's where I looked for it. Never mind.

Paul White NZ (7/4/2010)

---

I would be...

It's explained in detail in the BOL page I posted.

SQLAgentReaderRole has some more privileges than SQLAgentUserRole.

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles.

It still can start/stop jobs, but owned ones only.

Paul White NZ (7/2/2010)

---

The simple answer is because the whole batch is optimised all-at-once. The optimiser does not split the query into two (at the UNION ALL) and optimise...

I would be really happy to get rid of that...

You have to be member of some fixed database roles in msdb to see sql agent.

See here:

http://msdn.microsoft.com/en-us/library/ms188283.aspx

Paul White NZ (7/2/2010)

---

Seems like...

On days like today I wish I was...

Glad I could help.

Let's see if this comments help you understanding what goes on behind the scenes:

-- Variable: sql string to execute

DECLARE @sql nvarchar(4000)

-- Variable: sql string to test for...