.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

sp_send_dmail doesn't work in Agent job with query parameter

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server

I have the following query which needs to run on a schedule and email a list of all disabled jobs for the SQL Server instance (I've changed the server and email names for privacy):

 EXEC msdb.dbo.sp_send_dbmail
 @recipients = N'name@email.com'
 ,@body = N'The attachment shows the names of all SQL jobs that are currently disabled on SERVER1'
 ,@subject = N'Disabled Jobs on SERVER1'
 ,@query = N'SELECT [NAME] FROM [msdb].[dbo].[sysjobs]'
 ,@attach_query_result_as_file = 1

This query works fine when running from a query window.  however, when I try to run it from a SQL Agent job, I get the following error on the step output (username changed for privacy):

Executed as user: domain\user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

If I try other queries in the @query parameter, everything works fine when running from the job.  As soon as I try to query [msdb].[dbo].[sysjobs] though, the job fails.  I ran a profiler trace, and I found a more detailed error message that occurs during the execution of the job, unfortunately it involves an undocument SP:

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status

View Complete Post

More Related Resource Links

copy/y doesn't work in SQL Agent job step


I added some very simple copy file steps to a job.

I wanted to give them the ability to copy over an existing file, so tried to use the copy/y switch.  When I do this the job fails with a message:

Executed as user: MYCORP\XYZ. 
The process could not be created for step 1 of job 0xA7AC060B3A9B9443A42F68DCBD74EC07 
(reason: The system cannot find the file specified). 
The step failed.

Wassup with that?




Crawl doesn't work and Query server is not responding; Search works only for old (crawled) contents


We have following three servers with MOSS 2007 STD deployed in SharePoint server farm,


App01- Query Server

App02 - Index Server (We are using this server as dedicated web front end computer for crawling)

SPDBServer - SQL 2005


Search was working fine till 01/05/2008 and it stopped working after this date only for new contents meaning search is still working fine for the old contents which were added before 01/05/2008 but not for new contents added after 01/05/2008.


We have been working to find out reasons why did that happen? We have found following event ids on Query server,


Event ID:10042 - A new query machine, 'App01', has been added to the query rotation based on changes to farm topology.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

How To Remove a Query Parameter from a URL


for a string such as this:


how would I remove the parameter id 12?


adding parameter to query string


hi all,

how do i add a new parameter to an existing query string?


now i need to add a new parameter say, showsearch.

When I use 'publish', site doesn't work when i post data


Arrgghh, I am so tired of dealing with stuff like this

I've got a project, when I put up on another server I point two sites in IIS6 to it

i have

http://www.main.com  pointing to the folder the files are in


http://www.anotherofoursites.com/main pointing to that same folder in it's own "Virtual Application" or whatever IIS calls it when you put a folder into it's own 

If I just "Build" and push all the files up.. both work without issue and as expected...  but when I use "Publish" to precompile, the site works until I post, and then i get nailed with:

Server Error in '/' Application.
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Ajax Asyncfileupload doesn't work as soon as I put it in a control


Hi guys,

I can't get my <ajax:asyncfileupload> working and I don't see why...

When outside any panel or table it is working well, but as soon as I put it in a <asp:panel> or table I get a strange error which seems to be on client-side but I don't understand what's happen here. Here is the code and code-behind and Javascript.

Here's the error: 


Here's my front-code:

<%@ Page Language="VB" MasterPageFile="~/masterpages/mpSqueletton.master" AutoEventWireup="false" CodeFile="frmAdmGestSocietes.aspx.vb" Inherits="adm_frmAdmGestSocietes" title="Untitled Page" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>

<asp:Content ID="Content1" ContentPlaceHolderID="cphHead" Runat="Server">
<script type="text/javascript" language="javascript">    
        function uploadError(sender,args)
          document.getElementById("<%= lblStatus.ClientID %>").innerText = args.get_fileName(), "<span style='color:red;'>" + args.get_errorMes

AsyncPostBackTrigger doesn't work on server..


 I am new to asp.net and ajax. I have a problem while using update panel.
I have a tabcontainer with 3-4 tab panels. Inside one tab panel there is a cancel button. What i want to do is to when i hit cancel button in code behind i change active tab index to 0 so that tabcontainer shows first tab panel. I have update panel around tab container. This works at my localhost. But on server its behavior is very random. Sometimes it works sometime it doesn't. Unfortunately I am not able to debug it also. I don't know how to debug code on server. As on localhost it works correctly so I am kind of stuck. 

Asyncfileupload doesn't work in webusercontrol

I'm having a problem with a Webusercontrol that has an asyncfileupload control. My websusercontrol is being loaded in a placeholder control that's wrapped by an updatepanel.Due to asyncpostback, I have to load and clear the placeholder's controls. The webusercontrol works flawlessly, except the asyncfileupload, which doesn't want to upload at all. I can assure that the code is without error, because I used to have the webusercontrol running as an *.aspx site. I guess that the problem is the fact that the control has to be cleared and reloaded with every asyncpostback ?

In BCP what is work of 'query out'

In BCP what is work of 'query out' is there any example"SQLSERVER DBA" "INDIA"

logoff doesn't work through windows service

Dear Friends, I am facing this problem on Windows 2003 and 2008 Server. On XP it works fine. I have written and Windows Service that takes receives the command and executes tasks such as Logoff, Shutdown, etc. All the commands except Logoff works fine. I tried following ways to perform the Logoff. However it didnt work. Try 1:                 Process.Start("shutdown", "-l"); Try 2:     [DllImport("user32.dll")]     public static extern int ExitWindowsEx(int uFlags, int dwReason);     ExitWindowsEx(0, 0); Try 3:     Process _currentProcess = new Process();                _currentProcess.StartInfo = new ProcessStartInfo("shutdown", "-l -f");                _currentProcess.StartInfo.UseShellExecute = false;                 _currentProcess.Start(); Try 4:     Process.Start("logoff.exe", null); If I write a simple C# based applicatin (not Service) it works fine for all the tries that I have made. I am facing this problem only when I put this code

doesn't work (using IIS 6.0)

HiI'm getting errors with an application on our test web server, which has .NET 4.0 installed, when I input HTML into a form.  I get the usual errors of:A potentially dangerous Request.Form value was detected from the clientThis is being caused by the change in .NET 4.0 that disables switching off automatic validation for HTML input.I can fix this on my local development machine by adding the <httpRuntime requestValidationMode="2.0" /> directive to the <system.web> section of my root web.config, and .NET then honours the <pages validateRequest="false" /> directive that's in the same root web.config.  Strangely, I needed to restart IIS on my local machine (which is version 5.1) for this change to work.When I deploy the root web.config to our test server however, I'm still getting the validation errors.  I've tried using run > iisreset, stopping and starting IIS (which is version 6.0 on the test server), and I've even restarted the server to fully clear out .NET.  My application is definitely picking up the new root web.config (I've tested this), however the <httpRuntime requestValidationMode="2.0" /> directive seems to just be ignored.My application is configured as a  .NET 4.0 application on both my local machine and on the test server.  I've tried rebuilding the a

Workflow doesn't work anymore after renaming list

Hi All, I have created a list with the name "helpdesk test". After testing this list i renamed the list to "helpdesk". I did the rename by the list settings.  To this list i attached some workflows, with Sharepoint designer,  that have to start when a new item is created. All worked fine but after the rename the workflows don't work anymore. When i start the workflows manually from the list all works fine. Is there a way to rename a list and let the workflows work as before? Thanks in advance. Laurens.      

WCF WebGet method doesn't work from browser

I'm trying to operate a WCF Method from the browser, i opened a basic WCF Project and added the Webget attribute, my interface is this: [ServiceContract] public interface IService1 { [WebGet(UriTemplate = "GetData?value={value}", BodyStyle = WebMessageBodyStyle.Bare)] [OperationContract] string GetData(int value); [OperationContract] CompositeType GetDataUsingDataContract(CompositeType composite); // TODO: Add your service operations here } and i'm inserting the following URL in the Browser: http://localhost:3122/Service1.svc/GetData?value=3 i get "The webpage cannot be found" there must be missing something in my configuration , what do i have to add ? this is my configuration : <?xml version="1.0"?> <configuration> <system.web> <compilation debug="true" targetFramework="4.0" /> </system.web> <system.serviceModel> <behaviors> <serviceBehaviors> <behavior> <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment --> <serviceMetadata httpGetEnabled="true"/> <!-- To receive exception details in faults for debugging purposes, set the value below to true. Set to false before deployment to avoid disclosing

Form in iframe doesn't work.

I have put a form into an iframe. It is sent from another website to the php page on the site which has the iframe. Upon clicking the submit button nothing happens. Just a quick flash of the green page load bar. Why is this happening? I have spend hours on this...<%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation="true" CodeFile="adv_links_addbycustomer.aspx.vb" Inherits="users_adv_links_addbycustomer" title="" %> <html> <head runat="server"> <title></title> <script type="text/javascript"> function copy() { var sel = document.getElementById("DropDownList1"); var text = sel.options[sel.selectedIndex].value; // Assigning selected value of DropDownList to a hidden TextArea holdtext.innerText = text; // Creating text range from hidden TextArea's content CopiedTxt = holdtext.createTextRange(); // Copying the text range to clipboard CopiedTxt.execCommand("Copy"); // out = parent.document.getElementById("web_recip"); out = document.getElementById("Textbox6"); out.value = text + "

Redirecting a view doesn't work

I have the following class that I have all my controllers inherit from:  using System; using System.Collections.Generic; using System.Diagnostics.CodeAnalysis; using System.Linq; using System.Security.Principal; using System.Web; using System.Web.Mvc; using System.Web.Routing; using System.Web.Security; using LMWebUI.Models; using LMWebUI.Components; namespace LMWebUI.Controllers { public class BaseController : Controller { private UserSettings _currentUser = null; private HttpContextBase _currentContext = null; public UserSettings CurrentUser { get { if (_currentUser == null) _currentUser = UserHelper.GetUser(_currentContext); return _currentUser; } } public string CurrentController { get { return Utility.LastSplit(ToString(), '.'); } } public void SaveUser() { UserHelper.SaveSettings(this.HttpContext, CurrentUser); } protected override void Execute(RequestContext requestContext) { _currentContext = requestContext.HttpContext; if (Utility.FixNull(requestContext.HttpContext.Request.QueryString["noScript"], "") == "true") { CurrentUser.ScriptingMode = UserSettings.ScriptMode.NoScript; } else { if (CurrentUser.ScriptingMode == UserSettings.ScriptMode.ScriptDetection && Utility.ReadCookie(requestContext.HttpContext.Request.Cookies, "HasScri
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