none
Unable to execute insert statement by SQL Server Agent - SQL Server 2008 R2

    Question

  • Hi all,

    I feel a bit confuse in the SQL Server Agent of SQL Server 2008 R2, please give a hand or comment.

    I have a store proc, which contain 2 part:

    1) Gather information and form a SQL string, then insert into the temp table by the following format: EXEC (@strSQL); 

    2) Retrieve information from the temp table mention in 1, then use a cursor to loop all records inside and send a email to specific user(email address is part of the information pre-set or gathered in (1))

    I found if i open the Management Studio and Execute it as the following format: EXEC StoreprocName Parm1, Parm2, Parm3 ; 

    it work fine as expected.

    But once I put this command as a step in the schedule Job (type of the step = Transact-SQL script), whatever i use:

    1) EXEC StoreprocName Parm1, Parm2, Parm3;

    or

    2) StoreprocName Parm1, Parm2, Parm3;

    It return noting ---- No records insert into the temp table and also no email sent.

    I can see that the insert statement placed in as content while View History and report SUCCESS in the step, but I found NO records in the temp table.

    After I export the log and pull the Insert Statement and replace ALL <c/> as "," and EXECUTE it in the Management Studio, it insert and return 1 ROW which is expected but DIFFERENT from the result given by the Schedule Job.

    After some test I did, by passing different PRINT in different section. I found that the store proc execute completely, but only the insert statement mentioned in (1) isnt execute at all. Because the cursor depends on the records in the temp table, as the insert statement didnt execute properly and the temp table is empty, cursor return nothing and no followup process take effect.

    But May I ask why and whats happening please? Why the insert statement can be execute properly and return result as expected, but not in the schedule job?

    Please comment and Advise, many thanks.


    Thursday, June 21, 2012 4:29 AM

Answers

  • At last, I resolved this issue.

    This issue happen because of the SQL Server Agent cannot read "Filtered" tables (which is created by MSCRM).

    Refer to my SQL, due to I gather information based on the "FilteredIncident" (as it contain the local time zone and most detail information i need), the SQL Server Agent unable to read, and so to insert data based on it. So that the temp table remain empty always and Cursor retrieve no data to work on.

    The answer should be, I should be:

    1) I should use the "Incident" table, instead of "FilteredIncident"; And

    2) use the following SQL statement to retrieved and convert the createdon field from default UTC into local time zone

     dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), createdon)


    I wish this post will help the others may having the same problem or similar problem as me.

    Thank you very much for all your time, if don't have the ideas and testing way you all give me, I will not be able to come to this end and resolved it. thank you very much for the reply and effort in my case.


    • Marked as answer by Ksun_Chan Tuesday, June 26, 2012 8:24 AM
    • Edited by Ksun_Chan Tuesday, June 26, 2012 8:29 AM
    Tuesday, June 26, 2012 8:24 AM

All replies

  • Hi all,

    I feel a bit confuse in the SQL Server Agent of SQL Server 2008 R2, please give a hand or comment.

    I have a store proc, which contain 2 part:

    1) Gather information and form a SQL string, then insert into the temp table by the following format: EXEC (@strSQL); 

    2) Retrieve information from the temp table mention in 1, then use a cursor to loop all records inside and send a email to specific user(email address is part of the information pre-set or gathered in (1))

    I found if i open the Management Studio and Execute it as the following format: EXEC StoreprocName Parm1, Parm2, Parm3 ; 

    it work fine as expected.

    But once I put this command as a step in the schedule Job (type of the step = Transact-SQL script), whatever i use:

    1) EXEC StoreprocName Parm1, Parm2, Parm3;

    or

    2) StoreprocName Parm1, Parm2, Parm3;

    It return noting ---- No records insert into the temp table and also no email sent.

    I can see that the insert statement placed in as content while View History and report SUCCESS in the step, but I found NO records in the temp table.

    After I export the log and pull the Insert Statement and replace ALL <c/> as "," and EXECUTE it in the Management Studio, it insert and return 1 ROW which is expected but DIFFERENT from the result given by the Schedule Job.

    After some test I did, by passing different PRINT in different section. I found that the store proc execute completely, but only the insert statement mentioned in (1) isnt execute at all. Because the cursor depends on the records in the temp table, as the insert statement didnt execute properly and the temp table is empty, cursor return nothing and no followup process take effect.

    But May I ask why and whats happening please? Why the insert statement can be execute properly and return result as expected, but not in the schedule job?

    Please comment and Advise, many thanks.




    Thursday, June 21, 2012 2:30 AM
  • Start from checking insert trigger on that table. Also, is it possible that error occurred, but was trapped and never properly reported.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 4:34 AM
  • Hi Maomi N, thanks for your reply, may I ask how can I check the insert trigger please? Because I didnt setup and triggers by myself, I dont sure if it is default setup by SQL Server itself....Or May I ask is that my insert method is not support ? I insert the temp table by another set of data by a sub-SQL, is that the schedule job not support insert in this way?

    many thanks


    • Edited by Ksun_Chan Thursday, June 21, 2012 4:39 AM
    Thursday, June 21, 2012 4:36 AM
  • I am not sure what do you mean by temp table. The temp tables are only visible to connection that creates them.

    In any case, try running SQL Server profiler's trace and then run your job. You will see everything what is send to SQL Server.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 5:12 AM
  • The temp table was prepared before the store proc execute.

    The store proc will truncate the table before it insert the new data.

    I am sorry, may I ask more about how could I "running SQL Server profiler's trace" please? I am well in this section; many thanks.

    Thursday, June 21, 2012 6:12 AM
  • In SSMS go to Tools menu and chose the first option, then run a trace. Don't forget to stop it using stop button after you're done (and only then close the window).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 6:15 AM
  • Thanks Naomi N, I go to have a try now, thanks you very much
    Thursday, June 21, 2012 6:17 AM
  • Hi Naomi N, I tried the profiler trace, but I found some problem inside....

    the profiler trace contain a lot of data, and keep refreshing while to opened it. I tried to config the filter option but I have no idea how to start it...

    May I ask if you could give me some information to follow to use the profiler trace please? So I will be able to track the transaction for the job only, but not other transactions.....I feel so sorry about bother you or if I asked a stupid question.

    many thanks

    Thursday, June 21, 2012 6:56 AM
  • In the profiler go to the second page (Events Selection) and select only a few events you may be interested in (I usually keep SP Batch Completed and RC Completed). Also, you can filter. Say, select the TextData column and select Values LIKE 

    and type %myTempTableName%

    So, you will only see statements where your table was referenced. You can also filter based on the name of your application if it's part of the connection string.

    Also, start a trace right before running your job and turn it off immediately once it's completed (by pressing stop button on the toolbar).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 21, 2012 12:56 PM
  • Hi Naomi N, thanks for your reply and advise, I will try it right now.
    Friday, June 22, 2012 1:23 AM
  • Hi Ksun_Chan,

    Could you share the source code and more detail for troubleshooting? 

    Regards,
    Tom

    Friday, June 22, 2012 3:18 AM
  • Hi, Tom, thanks for your reply.

    here is the script of the SP:

    USE [mylocal_MSCRM]
    GO
    /****** Object:  StoredProcedure [dbo].[mylocalCaseFollowupAlert]    Script Date: 06/21/2012 17:32:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[mylocalCaseFollowupAlert]

    @EmailID nvarchar(1), --Email Type ID, please refer table new_mylocalEmail for details.
    --@StateCode nvarchar(50), --State Code of the case defined in CRM: 0 = Active, 1 = Resolved, 2 = Cancelled
    @strAge nvarchar(50), --Ticket Age, the difference between case createdon and current datetime 
    @strSrvLvCode nvarchar(50) --Service Level of the case defined in CRM: 1 = Class 1, 2 = Class 2

    As
    DECLARE @intEmailID integer;
    DECLARE @strEmailDesc nvarchar(500);
    DECLARE @strEmailSubject nvarchar(500);
    DECLARE @strOrgEmailSubject nvarchar(500);
    DECLARE @strEmailBody nvarchar(MAX);
    DECLARE @strOrgEmailBody nvarchar(MAX);

    DECLARE @strRecipientEmail nvarchar(50);
    DECLARE @strCclist varchar(1000);

    DECLARE @strSolution nvarchar(500);
    DECLARE @strOwner nvarchar(50);
    DECLARE @strTitle nvarchar(500);
    DECLARE @strDescription nvarchar(500);
    DECLARE @strCustomer nvarchar(500);
    DECLARE @strTicketNumber nvarchar(50);
    DECLARE @strEmail nvarchar(500);
    DECLARE @strName nvarchar(500);

    --KC20120618 ---- Start on: 20120618
    DECLARE @strSrvLv nvarchar(50);
    DECLARE @strState nvarchar(50);
    DECLARE @strStatus nvarchar(50);
    DECLARE @dCreaton datetime;
    DECLARE @strModifyBy nvarchar(50);
    DECLARE @strCaseAge nvarchar(50);

    DECLARE @strStateCode nvarchar(50);

    DECLARE @AEmail nvarchar(500);
    DECLARE @BEmail nvarchar(500);
    DECLARE @CEmail nvarchar(500);

    DECLARE @strEngineers nvarchar(1000);

    DECLARE @strEmailBody2 nvarchar(MAX);
    DECLARE @strOrgEmailBody2 nvarchar(MAX);

    DECLARE @strEmailBody3 nvarchar(MAX);
    DECLARE @strOrgEmailBody3 nvarchar(MAX);
    DECLARE @flgFirst char(1);

    DECLARE @flgContainData char(1);


    --DECLARE @strSQL nvarchar(4000);

    SET @AEmail = 'A@mylocal.com.hk';
    SET @BEmail = 'B@mylocal.com.hk';
    SET @CEmail = 'C@mylocal.com.hk';

    SET @strEngineers = '''A Alcantara''' + ',' + '''Hin Wu''' + ',' + '''TB Leung''' + ',' + '''System Engineering''';

    SET @flgFirst = 'Y';
    SET @flgContainData = 'N';
    ----

    DECLARE @strNoRecordSendRemark nvarchar(1000);
    DECLARE @intSendCnt int;

    SET @intSendCnt = 0;
    SET @strNoRecordSendRemark = 'No record meet the requirment of corresponding Email ID; No Email sent.';



    --Reterive Email Setting
    DECLARE EmailCur CURSOR FOR

    SELECT EMailID, Description, Subject, Body, Body2, Body3, Cclist, StateCode
    FROM new_mylocalEmail
    WHERE EmailID = @EmailID

    OPEN EmailCur
    FETCH NEXT FROM EmailCur INTO @intEmailID, @strEmailDesc, @strOrgEmailSubject, @strOrgEmailBody, @strOrgEmailBody2,@strOrgEmailBody3, @strCclist, @strStateCode

    CLOSE EmailCur;
    DEALLOCATE EmailCur;
    ----

    --Prepare Email Structure Data

    --Trancate The temp Table
    --TRUNCATE TABLE new_EmailStrucTemp
    ----

    --Insert & Prepare Data
    DECLARE @strSQL nvarchar(MAX);

    SET @strSQL = 'INSERT INTO new_EmailStrucTemp (new_Solution,Title,Description,CustomerIdName,TicketNumber,InternalEMailAddress,FullName,contractservicelevelcodename,statecodename,statuscodename,createdon,modifiedbyname,age) ';
    SET @strSQL = @strSQL + 'SELECT ISNULL(A.new_Solution,'''') AS new_Solution,B.Title,B.Description,B.CustomerIdName,B.TicketNumber,C.InternalEMailAddress,C.FullName,A.contractservicelevelcodename,A.statecodename,A.statuscodename,A.createdon,A.modifiedbyname,CONVERT(VARCHAR(50),DATEDIFF(HOUR,A.createdon,GETDATE())) As Age ';
    SET @strSQL = @strSQL + 'FROM ';
    SET @strSQL = @strSQL + '(';
    SET @strSQL = @strSQL + 'SELECT ';
    SET @strSQL = @strSQL + 'IncidentId, ISNULL(FollowupBy,'''') AS FollowupBy, ISNULL(new_Solution,'''') AS new_Solution, OwnerId,contractservicelevelcodename,statecodename,statuscodename,createdon,modifiedbyname,ISNULL(DATEDIFF(HOUR,createdon,GETDATE()),0) As Age,statecode ';
    SET @strSQL = @strSQL + 'FROM FilteredIncident ';


    SET @strSQL = @strSQL + 'WHERE statecode IN (' + @strStateCode + ') ';

    IF (@EmailID = 1)
    BEGIN
    SET @strSQL = @strSQL + 'AND DATEDIFF(DAY,followupby,GETDATE()) = 0 ';
    END


    IF (@strSrvLvCode > 0)
    BEGIN
    SET @strSQL = @strSQL + 'AND contractservicelevelcode IN (' + @strSrvLvCode + ') ';
    END

    IF (@strAge > 0)
    BEGIN
    SET @strSQL = @strSQL + 'AND ISNULL(DATEDIFF(HOUR,createdon,GETDATE()),0) >= ' + @strAge + ' ';
    END

    IF (@EmailID = 3)
    BEGIN
    SET @strSQL = @strSQL + 'AND (DATEDIFF(DAY,modifiedon,GETDATE()) = 0 OR DATEDIFF(DAY,createdon,GETDATE()) = 0) '
    END



    SET @strSQL = @strSQL + ') A ';
    SET @strSQL = @strSQL + 'JOIN IncidentBase B ON A.IncidentId = B.IncidentId ';
    SET @strSQL = @strSQL + 'JOIN ';
    SET @strSQL = @strSQL + '(';
    SET @strSQL = @strSQL + 'SELECT * FROM SystemUser WHERE 1 = 1 ';


    IF (@EmailID = 3)
    BEGIN
    SET @strSQL = @strSQL + 'AND fullname IN (' + @strEngineers + ')';
    END


    SET @strSQL = @strSQL + ') C ON A.OwnerId = C.SystemUserId ';
    SET @strSQL = @strSQL + 'ORDER BY A.statecodename ASC,A.statuscodename ASC,A.createdon ASC,A.modifiedbyname ASC';

    --PRINT @strSQL;
    PRINT 'A';

    EXEC (@strSQL);
    ------

    PRINT 'B';

    SET @strEmailSubject = @strOrgEmailSubject;

    --Reterive Case Content
    DECLARE CaseCur CURSOR FOR

    SELECT 
    new_Solution,
    Title,
    Description,
    CustomerIdName, 
    TicketNumber,
    InternalEMailAddress,
    FullName,
    contractservicelevelcodename,
    statecodename,
    statuscodename,
    createdon,
    modifiedbyname,
    Age
    FROM new_EmailStrucTemp




    OPEN CaseCur

    FETCH NEXT FROM CaseCur INTO @strSolution, @strTitle, @strDescription, @strCustomer, @strTicketNumber, @strEmail, @strName, @strSrvLv , @strState ,@strStatus ,@dCreaton ,@strModifyBy ,@strCaseAge 

    PRINT 'C';

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

    SET @flgContainData = 'Y';

    PRINT 'D';

    --SET @strEmailBody = @strOrgEmailBody;

    --Alert Email to Case Owner
    IF (@EmailID = 1)
    BEGIN
    SET @strEmailBody = @strOrgEmailBody;

    --Reformat the Email Subject
    SET @strEmailSubject = REPLACE(@strEmailSubject,'<CaseNumber>',@strTicketNumber);
    SET @strEmailSubject = REPLACE(@strEmailSubject,'<CaseTitle>',@strTitle);
    ----

    --Reformat the Email Body
    SET @strEmailBody = REPLACE(@strEmailBody,'<Recipient>',@strName);
    SET @strEmailBody = REPLACE(@strEmailBody,'<CaseNumber>',@strTicketNumber);
    SET @strEmailBody = REPLACE(@strEmailBody,'<CaseTitle>',@strTitle);
    SET @strEmailBody = REPLACE(@strEmailBody,'<CaseCustomer>',@strCustomer);
    SET @strEmailBody = REPLACE(@strEmailBody,'<CaseDescription>',@strDescription);
    SET @strEmailBody = REPLACE(@strEmailBody,'<CaseSolution>',@strSolution);
    ----

    --Recipient Email
    SET @strRecipientEmail = @strEmail
    ----

    --GOTO SendEmail;


    --Send The Email
    EXEC msdb.dbo.sp_send_dbmail
    --@recipients = @strRecipientEmail,
    @recipients = 'ksun.chan@mylocal.com.hk',
    --@copy_recipients = @strCclist,
    @body = @strEmailBody,
    @body_format = 'HTML',
    @subject = @strEmailSubject;
    ----


    END
    --------

    --KC20120618 ---- Start on: 20120618
    --Alert Email to A, Summary of Daily Case Ownered by Engrs
    IF (@EmailID = 3)
    BEGIN
    --Recipient Email = A
    SET @strRecipientEmail = @AEmail;
    ----

    --Setup & Reset Email Body
    IF (@flgFirst = 'Y')
    BEGIN
    SET @strEmailSubject = REPLACE(@strEmailSubject,'<Today>', CONVERT(VARCHAR(12),GETDATE(),106));

    SET @strEmailBody = @strOrgEmailBody;
    SET @strEmailBody = REPLACE(@strEmailBody,'<Today>', CONVERT(VARCHAR(12),GETDATE(),106));

    SET @flgFirst = 'N';
    END

    SET @strEmailBody2 = @strOrgEmailBody2;
    SET @strEmailBody3 = @strOrgEmailBody3;
    ----

    --Setup Email and Case Content
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Title>',@strTitle);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Case Number>',@strTicketNumber);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Class>',@strSrvLv);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Status>',@strState);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Status Reason>',@strStatus);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Created On>',@dCreaton);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Last Update By>',@strModifyBy);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Age>',@strCaseAge);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Owner>',@strName);
    --SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Class>',@strTitle);


    SET @strEmailBody = @strEmailBody + @strEmailBody2;

    --PRINT @strEmailBody;

    --PRINT LEN(@strEmailBody);

    END
    ------
    --KC20120618 ---- End on: 20120619

    --KC20120618 ---- Start on: 20120620
    --Case Escalation Alert
    IF (@EmailID = 4)
    BEGIN
    IF (@flgFirst = 'Y')
    BEGIN
    SET @strEmailSubject = REPLACE(@strEmailSubject,'<Today>', CONVERT(VARCHAR(12),GETDATE(),106));
    SET @strEmailSubject = REPLACE(@strEmailSubject,'<Class>','Class ' + @strSrvLvCode);

    SET @strEmailBody = @strOrgEmailBody;

    --For Class 1 Case 
    IF (@strAge = 12) AND (@strSrvLvCode = 1)
    BEGIN 
    --Recipient Email = C
    SET @strEmailBody = REPLACE(@strEmailBody,'<Receipt>','C');
    SET @strRecipientEmail = @CEmail;
    ----
    END

    IF (@strAge = 24) AND (@strSrvLvCode = 1)
    BEGIN 
    --Recipient Email = A
    SET @strEmailBody = REPLACE(@strEmailBody,'<Receipt>','A');
    SET @strRecipientEmail = @AEmail;
    ----
    END

    IF (@strAge = 72) AND (@strSrvLvCode = 1)
    BEGIN 
    --Recipient Email = B
    SET @strEmailBody = REPLACE(@strEmailBody,'<Receipt>','B');
    SET @strRecipientEmail = @BEmail;
    ----
    END
    ----

    --For Class 2 Case 
    IF (@strAge = 24) AND (@strSrvLvCode = 2)
    BEGIN 
    --Recipient Email = C
    SET @strEmailBody = REPLACE(@strEmailBody,'<Receipt>','C');
    SET @strRecipientEmail = @CEmail;
    ----
    END

    IF (@strAge = 72) AND (@strSrvLvCode = 2)
    BEGIN 
    --Recipient Email = A
    SET @strEmailBody = REPLACE(@strEmailBody,'<Receipt>','A');
    SET @strRecipientEmail = @AEmail;
    ----
    END

    IF (@strAge = 336) AND (@strSrvLvCode = 2)
    BEGIN 
    --Recipient Email = B
    SET @strEmailBody = REPLACE(@strEmailBody,'<Receipt>','B');
    SET @strRecipientEmail = @BEmail;
    ----
    END
    ----

    SET @strEmailBody = REPLACE(@strEmailBody,'<Today>',CONVERT(VARCHAR(12),GETDATE(),106));
    SET @strEmailBody = REPLACE(@strEmailBody,'<Hrs>',@strAge);

    SET @flgFirst = 'N';

    END
    ----


    SET @strEmailBody2 = @strOrgEmailBody2;
    SET @strEmailBody3 = @strOrgEmailBody3;
    ----

    --Setup Email and Case Content
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Title>',@strTitle);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Case Number>',@strTicketNumber);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Class>',@strSrvLv);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Status>',@strState);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Status Reason>',@strStatus);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Created On>',@dCreaton);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Last Update By>',@strModifyBy);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Age>',@strCaseAge);
    SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Owner>',@strName);
    --SET @strEmailBody2 = REPLACE(@strEmailBody2,'<Class>',@strTitle);


    SET @strEmailBody = @strEmailBody + @strEmailBody2;


    END
    --KC20120618 ---- End on:20120620


    SET @intSendCnt = @intSendCnt + 1;

    FETCH NEXT FROM CaseCur INTO @strSolution, @strTitle, @strDescription, @strCustomer, @strTicketNumber, @strEmail, @strName, @strSrvLv , @strState ,@strStatus ,@dCreaton ,@strModifyBy ,@strCaseAge 
    --FETCH NEXT FROM CaseCur INTO @strSolution, @strTitle, @strDescription, @strCustomer, @strTicketNumber, @strEmail, @strName 
    END

    CLOSE CaseCur;
    DEALLOCATE CaseCur;
    ----

    PRINT 'E';

    IF (@flgContainData = 'Y')
    BEGIN
    --SET @strEmailBody = @strEmailBody + @strEmailBody2 + @strEmailBody3;

    --Setup Email Content and Subjects For EmailID = 3 (Daily Case Owned by Engrs)
    IF (@EmailID = 3) OR (@EmailID = 4)
    BEGIN

    PRINT 'F';

    SET @strEmailBody = REPLACE(@strEmailBody,'<NoOfCases>', @intSendCnt);

    SET @strEmailBody = @strEmailBody + @strEmailBody3;

    --Send The Email
    EXEC msdb.dbo.sp_send_dbmail
    --@recipients = @strRecipientEmail,
    @recipients = 'ksun.chan@mylocal.com.hk',
    --@copy_recipients = @strCclist,
    @body = @strEmailBody,
    @body_format = 'HTML',
    @subject = @strEmailSubject;
    ----
    PRINT 'G';

    END

    END





    --Write Log to capture the process summary
    InsertLog:

    IF (@intSendCnt = 0)
    BEGIN
    --Insert Log
    INSERT INTO new_EmailLog (EmailID, Description, Remark, ExecutionDateTime)
    VALUES (@intEmailID, @strEmailDesc, @strNoRecordSendRemark, GETDATE())
    ----
    END
    ELSE
    BEGIN
    --Insert Log
    INSERT INTO new_EmailLog (EmailID, Description, EmailTitle, Recipient, CcList, ExecutionDateTime)
    VALUES (@intEmailID, @strEmailDesc, @strEmailSubject, @strRecipientEmail, @strCclist, GETDATE())
    ----
    END
    ----

    -------------------------------------------

    The logic is that, the SP will having 3 parameters, they are email ID, ticket age and service lv code. with these Parameters, the SP will format an SQL statement the gather information as criterion and insert these data into a temp table(this temp table is a physical table created in mylocal instance) ---- new_EmailStrucTemp.

    Based on the information collected in "new_EmailStrucTemp", a cursor will be create and query from "new_EmailStrucTemp". by different email type and situation(i.e: the parameters) emails will be sent to different people under different ideas.

    This SP work well and return expected result if I open the SSMS and input as "EXEC mylocalCaseFollowupAlert 4,12,1"; there will be 1 record in new_EmailStrucTemp and 1 email be sent.

    But after I put "EXEC mylocalCaseFollowupAlert 4,12,1"(or mylocalCaseFollowupAlert 4,12,1) into the SQL Job Agent as a step(type = T-SQL), it return nothing. No records will be insert in new_EmailStrucTemp and as a result, cursor having no data and no email sent.

    After the investigation and testing today, I very sure that, the problem should be in between the SQL Job Agent and "EXEC (@strSQL);". Because I did try "PRINT @strSQL" to pull the SQL statement out from the JOB and place the statement into the JOB, as a step, to replace "EXEC mylocalCaseFollowupAlert 4,12,1"; The job return success with no error, but still, no records can be insert into new_EmailStrucTemp  at all, and that is not expected.

    And I dont know what happen of it at all........

    besides that,I found that, if i placing SQL as a step in the job. it doesnt work at all.

    I tried a very simple update statement, just update a field of a table with where condition, after I start the job, no records take effect; But as my SP, if i bring to SQL to SSMS, put it on the worksheet and give it a F5, it work fine and affect 15 reocrds, which is expected.....

    its that a bug for SQL server job agent? or My statements having some mistake? but If my statement having mistake, why it work fine when I pull to SMSS....?

    below is the Stateement:

    UPDATE A

    SET

    new_age = DATEDIFF(MINUTE,createdon,GETDATE()) / 60

    WHERE Statecode = 0

    Friday, June 22, 2012 3:38 AM
  • Hi Naomi N, I tried to captured the information from the server profile and filter as your said, it filter well and display the batch information in the log, but still it display the Insert Statement correctly but no records insert, it return reads = 36, writes = 0.

    But after I copy and paste the SQL captured from the log,into SSMS, it work fine and return 1 row inert success........

    I really have no idea what this going on...would you mind give me some more comment or advise please?

    I am doubt if there are something wrong in the SQL server agent or the server, I will reboot the server tonight, hope this can work after the reboot....but I dont sure whats casing this at all

    Friday, June 22, 2012 9:19 AM
  • Do you have any error handler in your code? I think in the job this statement is somehow failing (say, permissions issue). You need to trap and log this error.

    Also, check SQL Server log files and Windows Log files - may be a clue is in these files.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, June 22, 2012 9:24 AM
  • I dont have any error handling, since I only trying for simple insert or update statement , it also not working at all.....the job owner is administrator.
    Friday, June 22, 2012 9:58 AM
  • Still, if the simple INSERT statement doesn't insert row in the Job, but inserts it in SSMS, it means some error is produced - you need to view SQL Server logs.

    I also suggest to introduce the error handing in your stored procedure.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, June 22, 2012 1:18 PM
  • At last, I resolved this issue.

    This issue happen because of the SQL Server Agent cannot read "Filtered" tables (which is created by MSCRM).

    Refer to my SQL, due to I gather information based on the "FilteredIncident" (as it contain the local time zone and most detail information i need), the SQL Server Agent unable to read, and so to insert data based on it. So that the temp table remain empty always and Cursor retrieve no data to work on.

    The answer should be, I should be:

    1) I should use the "Incident" table, instead of "FilteredIncident"; And

    2) use the following SQL statement to retrieved and convert the createdon field from default UTC into local time zone

     dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), createdon)


    I wish this post will help the others may having the same problem or similar problem as me.

    Thank you very much for all your time, if don't have the ideas and testing way you all give me, I will not be able to come to this end and resolved it. thank you very much for the reply and effort in my case.


    • Marked as answer by Ksun_Chan Tuesday, June 26, 2012 8:24 AM
    • Edited by Ksun_Chan Tuesday, June 26, 2012 8:29 AM
    Tuesday, June 26, 2012 8:24 AM