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

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

Staying with the SQL Agent during Execution

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
I need to create a stored procedure that triggers off a particular job in the SQL server 2005 agent, waits for the job to complete and then returns back the control to the calling application with the job execution results. As far as triggering off the SQl server agent is concerned, I can use the sp_start_job procedure. But what is the best way to wait for the job to complete execution (Because the sp_start_job proc returns back the control immediately after starting the job). I could use the sp_help_job proc in a loop and wait for the value of the "current_execution_status" column to change from 1 to 4...but is there a better way of doing this? Appreciate inputs here. - Thanks, Manmeet

View Complete Post

More Related Resource Links

.NET Matters: Ordered Execution With ThreadPool


This month we demonstrate how you can use the ThreadPool to support ordered execution without having to build custom thread pools yourself.

Stephen Toub

MSDN Magazine February 2009

sql server agent - job schedule 22022 error


Hi ! I have scheduled a job in sql server 2008 to send birthday e-mails. I run the script and it looks wroking but in agent schedule it doesn't. I am getting the below error; what is the problem?

TITLE: Microsoft.SqlServer.Smo
Start failed for Job 'Sending_transferdb_birthdate_e-mails'. 
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&Link

Count Query Execution


I have a class that being used to connect with the DB. Now I want to count how many times each web request executes the queries, but I've no idea where to store the counted value. I mean, Session wont, ViewState wont work as site also have webservices. What else I can use ?

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

Doubt in sp execution

Hi,  i am executing sp in one of my database it is taking 4 minutes to complete execution. where as i am executing same sp in different database it is taking only 1 millisecond to complete execution. i have checked the table settings such as index and table data also everything is same. now i want to find where is the problem. what are steps to find such kind of problems.

Slow query execution

Hello, I have a strange problem that suddenly happened. I have a Windows 7 Ultimate OS running SSMS 2008. When I run a query or an stored proc against a server (the server is remote and passes through a VPN tunnel) it can take me 40 seconds to get the query to complete. But when I run a similar query against another server located at the same location (go through the same tunnel) I get the results in 1 second.  I select about 400 rows on both servers. The slow response comes from a server that runs sql 2005 and the fast response is sql 2008. I asked a friend that works in the same office with me and uses the same connection as I do to run the same queries and he gets the result in 1 second. Does anyone has any ideas what can cause such problems?  I tried to remove and reinstall all the sql server components from my computer but nothing helped? Thanks, Oded Tal

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

sql server agent roles and permissions

hi does any one have idea of what kind of roles are needed for creating a SQL server agent job and why those roles are needed?? Please Reply ASAP Because i don't have sysadmin rights.........so that other developers can use the same login for creating the jobs Thanks in advance

Error starting SQL Server Agent - Could not load the DLL xpstar90.dll. Reason: 126

AD Service account password was changed.  Now the SQL Server Agent (MSSQLSERVER) will not start even after updating the password.  Have no access into the databases.  Log files say "Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)."  Service Account has Full rights on system.  Any help in solving the problem would be great.

SQL Agent Jobstep output file contains automatically generated header

Hi,   I've created a stored procedure that creates a script to create a number of objects within the database (based on what existing objects are in the database).  From Management Studio, this works fine, and the output is exactly as I want it.   I'm now trying to create a job that will execute this stored procedure, and deposit the results into a file somewhere on the server.  When the job runs, the script is created in the correct place and is essentially ok.   However, there are a couple of questions I'd like to ask.   Why does SQL Server Agent put a header at the top of the output file?  I was hoping to be able to use that output file 'as is' and execute it automatically to recreate my objects when required.  (Obviously, I can manually remove the header, but this is an inconvenience in this situation).  How do I stop it?   Also, when executed from SSMS, the output is correctly line-spaced.  But, the output from the scheduled job adds an extra line between each line of text, which is, again, inconvenient.  Why does it do this, and how can I prevent this (again, without manually editting the output)?   Any thoughts and help greatly appreciated.   Cheers Simon

Install on New Windows7 - Getting Invalid SQL Server Agent credentials

Hi: I have a Developer License for MSSQL Server 2008.  Have just upgraded to Windows 7 Professional Edition.  When installing MS SQL Server, the setup shows that I have an instance of SQL Server Express already installed.  I am given a choice between Default Instance and Named Instance.  First time I chose named instance and then used the name of my machine - RBS1.  I received the following message:  The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service. Went back and tried Default instance and got same answer.  Can't proceed to install.  Any help in this greatly appreciated. roger  

Wierd error during execution of SerializeReply of IDispatchMessageFormatter

I am trying to develop a WCF REST app as explained in http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/04640a45-1a37-4722-8ed7-9f75c155dc14 (which serves both XML and JSON at the same time). the error is in the return statement of the following code: Public Function SerializeReply(ByVal messageVersion As System.ServiceModel.Channels.MessageVersion, ByVal parameters() As Object, ByVal result As Object) As System.ServiceModel.Channels.Message Implements System.ServiceModel.Dispatcher.IDispatchMessageFormatter.SerializeReply Dim formatter As IDispatchMessageFormatter = Me.xmlFormatter If OperationContext.Current.OutgoingMessageProperties.ContainsKey(WebBodyFormatMessageProperty.Name) Then Dim webBody As WebBodyFormatMessageProperty = DirectCast(OperationContext.Current.OutgoingMessageProperties(WebBodyFormatMessageProperty.Name), WebBodyFormatMessageProperty) If webBody IsNot Nothing AndAlso webBody.Format = WebContentFormat.Json Then formatter = Me.jsonFormatter End If End If Return formatter.SerializeReply(messageVersion, parameters, result) End Function It gives the error as "The given key was not present in the dictionary." further going deeper the stack trace looks like the following: Encountered unexpected namespace 'http://schemas.microsoft.com/ws/2005/05/envelope/none'. The namespace must be empty. Parameter name: ns

Improper execution plan with scalar function

Run the following sql in an empty db(in my case, sql 2008): create table t (id int) go declare @i int set @i = 1 while @i <= 1000 begin insert t values (@i) set @i = @i + 1 end go create function f(@i int) returns int as begin return (select count(*) - 10000+@i from t) end go Then run the following sql a, it's slow, used 6 seconds, yet sql b is very fast. Their execution plans are totally different. Can sql a run as fast as sql b? If not, why? -- sql a declare @i int set @i = 500 select * from t where id = (select dbo.f(@i)) -- sql b select * from t where id = (select dbo.f(500)) Thanks!

Sporadic .NET application crash with "Fatal Execution Engine Error"

I have a .NET application that runs for several hours.  It is a multi-threaded app that does some calculations and saves the results to the database.   On one machine in particular, it has been crashing recently with the following exception recorded in the Event Log: .NET Runtime version 2.0.50727.4206 - Fatal Execution Engine Error (6C9D7B6E) (80131506) Faulting application AnalyzerConsole.exe, version, time stamp 0x4c76d28b, faulting module mscorwks.dll, version 2.0.50727.4206, time stamp 0x4bf4c6a7, exception code 0xc0000005, fault offset 0x0011627d, process id 0x%9, application start time 0x%10. Sometimes it runs for hours without crashing, but usually it will crash eventually. I have captured a dump, and this is the call stack: > ntdll.dll!_DbgBreakPoint@0()   mscorwks.dll!WatsonLastChance()  + 0x133 bytes   mscorwks.dll!EEPolicy::LogFatalError()  + 0x23c bytes   mscorwks.dll!EEPolicy::HandleFatalError()  + 0x47 bytes   mscorwks.dll!CLRVectoredExceptionHandlerPhase3()  + 0x10dae2 bytes   mscorwks.dll!CLRVectoredExceptionHandlerPhase2()  + 0x20 bytes   mscorwks.dll!CLRVectoredExceptionHandler()  + 0xb7 bytes   mscorwks.dll!CPFH_RealFirstPassHandler()  + 0x8ac bytes   mscorwks.dll!_COMPlusFrameHandler()  + 0xb9 bytes   ntdll.d
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