Thanks Scott,

Very useful.

Scott,

I'm curious as well as to what the SQL would be to handle all columns at once.

Been thinking about it -- not too hard -- and coming up...

Although that does work nicely in the scenario I presented, that won't work in for my real-life case: there are other inserts into identity tables earlier in the procedure.

I should...

Hmm not quite: let's say there are x groups, and the parents' ids aren't always sequential.

Very clever.

Thanks again!

P

That's working in the back-end Eirikur, thank you!!

I have to dump the empty result set into a #tmp table so the front-end doesn't puke, but that's a minor detail.

Eirikur,

This is select @@version:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows...

DECLARE @code varchar(max)

SET @code = 'SELECT * from FROM sys.tables';

BEGIN TRY

EXEC ('SET PARSEONLY ON; ' + @code + '; SET PARSEONLY OFF;')

SELECT 'Success'

END TRY

BEGIN CATCH

SELECT 'Error'

END CATCH

This correctly gets...

Thanks for the reply Sigerson,

What I'm actually after is a T-SQL procedure or function to which I can pass the variable containing the generated code...

I tried a roll-your-own proc which...

One more question:

Let's assume I go with the certificate method.

Each night, we do database transfers to various target servers, using smo.Transfer.

Does the certificate/permissions granted by the cert travel...

My apologies Emil, you are correct.

In fact, Erland's article also covers the Execute As scenario. But I am looking at the Certificates section.

djj provided the link above. I'm looking into it now...

My bad, I guess I didn't go into enough detail in the OP.

I am aware of the dangers of xp_cmdshell, and I use it a) sparingly and b) carefully -...

Hi Jeff,

To be clear, it's not her account that has privs on xp_cmdshell. And she is a trusted user.

Moreover, the sproc she's executing that's calling xp_cmdshell take no parameters....

OK, but that account already owns quite a few DBs, while not having a "personal" login. So I wondered if there was a way.

Thanks Gary.