.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Execute inside Execute

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Is it possible to use EXECUTE inside an EXECUTE command?  For example, I build a dynamic SQL statement like so:
 SET NOCOUNT ON DECLARE @SQL NVARCHAR(MAX) = '; SELECT @SQL = @SQL + 'EXEC MSDB.dbo.sp_update_job @job_name = '' + j.[Name] + '', @owner_login_name = 'sa'; ' + CHAR(13) FROM MSDB.dbo.sysjobs j   LEFT JOIN Master.dbo.syslogins l     ON j.owner_sid = l.sid WHERE   l.[name] <> 'sa'   OR l.name IS NULL ORDER BY j.[name] PRINT @SQL;
I can execute the statements that come from that manually and everything works... However, if I change that PRINT @SQL to an EXECUTE @SQL, it gives me errors: Msg 203, Level 16, State 2, Line 15 The name 'EXEC MSDB.dbo.sp_update_job @job_name = 'job1', @owner_login_name = 'sa'; EXEC MSDB.dbo.sp_update_job @job_name = job2', @owner_login_name = 'sa'; EXEC MSDB.dbo.sp_update_job @job_name = 'job3', @owner_login_name = 'sa'; ' is not a valid identifier. Is this because you can't do an EXECUTE inside an EXECUTE?  Is there any way to do what I'm trying to do which is enumerate all SQL jobs that don't have an owner of 'sa' and update them so they do? Number2 - (John Nelson) Microsoft MVP (2009) - System Center Configuration Manager http://number2blog.com

View Complete Post

More Related Resource Links

Need to execute ALTER DATABASE inside stored procedure


SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?


Passing Parameters to Execute SQL task which is inside a For Loop Container



I have a requirement.

In this I will get a value of 50 ids from one table.

I have an sp which will take ids as input parameter and insert values into another table.

My Problem is I am having a for loop container with me and inside that i have execute sql task in which I have stored procedure.

How can i map the id parameter to the execute sql task variable whichi is inside a forloop.




Thanks, A2H

Using MVVM and commands inside of a datatemplate, the command wont execute.

Inside of my main view i have a datatemplate showing off all the items in an observable collection. now inside of that i want to set commanding on a radio button.

outside the datatemplate this works
<Button Height="23" Name="btnUpdate" Command="{Binding UpdateCommand}"

Using sp_grantlogin inside a stored procedure that uses EXECUTE AS.


Hello to all you oh mighty of the mightiest programming gods!

I am a novice in need of help.

I have a stored procedure



PROCEDURE [dbo].[vm_upd_user_rights]




EXECUTE AS inside a login trigger (or a reasonable alternative)


Here's the scenario: we use a 3rd party application that has a really awful idea of security. It uses Windows authentication, but requires direct database (and table) access, and generally doesn't work properly without full db_owner database access. We also run a number of other applications that use Windows Authentication, and the collateral damage is that they have way more privileges than needed to run.

In an effort to improve this, if even only slightly, I'm trying to limit these excessive permissions to just this one application. Now, I know what you're thinking already - "Application roles!" I've looked into this, but as best I can tell, an application role is a database principal, and not a server principal. This presents a problem, as this application needs to access multiple databases at once.

I've found that issuing EXECUTE AS LOGIN = 'dedicated_application_login' behaves exactly the way I would like, but only when issued in an ad-hoc batch. If I place it in a trigger or procedure, it seems to only affect execution of the module, and doesn't permanently change the security context of the session. This prevents me from checking the application name in a login trigger and running EXECUTE AS if everything checks out - I can do it, but it won't really have any meaningful effect. I'm well aware this is awful security as well, but i

HttpContext.Items and Server.Transfer/Execute

The problem was basically that when a Server.Transfer call was applied the script container (and also various ClientScriptProxy script embedding routines) would potentially fail to load up the specified scripts.

want to execute url without showing that page or url to client.


hello friends,

I need ur help,what i want to do is to execute a url ("To send a text message to client") on client registration but without showing that url page, in short executing url without displaying any page or url to the client.

please help and thanks in advance.

UI on the Fly: Use the .NET Framework to Generate and Execute Custom Controls at Run Time


Creating UI controls on the fly can be accomplished via run-time code generation. And there are lots of reasons to do so. Generating these controls once and then reusing them as needed is more efficient than generating the controls each time. Read on.

Morgan Skinner

MSDN Magazine December 2005

Automatically Execute Code at certain time from website


Is there a way to trigger a piece of code to execute at a certain time once a week?

I basically want my website to send out a reminder email every sunday. I have a function called SendReminder() and I would like to know if it is posible for it to run by itself because at the minute I have to press a button to run it.


Question about user used to execute code from app_code


 Hello. I would like to know something about code execution  in App_Code folder.

Under wich user executed code from app_code.

I'm using impersonate in web.config

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

ASP Development Server doesn't appear to execute JQuery?


Experienced a really strange problem today. I added a jquery script to my MVC application to collapse sections of a View (via divs).

Pretty standard stuff, he showed me the script working before hand and it works.

However any time I would run my MVC app via Visual Studio the script would not work. I changed browsers, re-imported the code from source control and basically me and my co-worker banged our heads together for some time trying to figure out why the exact same code was running different on two machines.

Publishing the MVC app to our web server and running worked for both machines but for some bizarre reason when I ran it on my VS it never did.

One thought that came to mind is that by VS uses the cassini default lightweight webserver for debugging, whereas my co-worker is using IIS.

This seems to suggest then that the ASP Development server does not support JQuery. Is this true and if not, what could the problem be?

Execute a function for specific time (not at specific time)


I have a function that does large amount of processing.

I just want that if it executes in specific time (say 10 min) then it should return "execution successful".

But if now then i want to stop the execution of that function and return "unsuccessful" and rollback my transactions.

Returning and rollback is fine.

But i dont know how to stop the execution of a function at specific time.

I am using asp.net with C#.

Any ideas would be really appreciated.


How it's possible to execute this code in C # WPF

Hi I have this code but I do not know how to do him in WPF   IF EXISTS (SELECT * FROM YourTable WHERE Key = 123)    PRINT 'EXISTS' ELSE    PRINT 'Dont EXISTS'   There is My Code in c#         private void button2_Click_1(object sender, RoutedEventArgs e)           {             DataSet ds6 = new DataSet();             SqlConnection con6 = new SqlConnection("Data Source=PC;Initial Catalog=Veo;Integrated Security=True");             SqlCommand cmd = new SqlCommand("Select * from [MHGROUP].CUSTOM3", con6);             SqlDataAdapter sqlDa6 = new SqlDataAdapter();             sqlDa6.SelectCommand = cmd;             sqlDa6.Fill(ds6);             string mySelectedValue = comboBox2.Text;             int newid;             newid = Convert.ToInt32(mySelectedValue);  

Execute Package Task password issue

I have a parent and child package. Both are stored on the file system and both are password protected. My problem is that when I call the child package from the parent using execute package task, I get prompted for the child package's password. Now the PackagePassword property of the Execute Package Task is supposed to take care of that. But for some reason the password stored in the Execute Package Task is not getting passed to the child package.  I've re-entered the PackPassword property several times, but it did not help.  I even tried loading the password from a config file, but that did not make any difference.  Can anyone help?

Execute SSIS Package PART based on Some Condition

Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

Execute system stored procedure

This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend