Xp_sendmail Execution Is Inconsistant In DTS

Mar 5, 2004

I have a DTS package that runs once a week. There is a xp_sendmail SQL task at the end that sends emails to our field sales people with a file attachment based on the results of the DTS package. Simple enough.

The problem I have is that the sendmail chokes when the mail task is executed at the end of the package - sometimes. And when it does fail, the DTS step with the sendmail can be executed separately without a problem.

The error message the gets generated is:

MAPI_E_NETWORK_ERROR80040115-2147221227

This does not make sense. If there was a network problem I should still get a message when executing the step that failed 30 seconds prior...not the case.

The other odd thing is that this package has been running fine for about 3 mo with no troubles, but our mail server may have undergone some changes that I am not aware of so...anyway if anyone has any info or has come across this oddness please feel free to share with me what you know.

I am a t a loss here with this one.

Thanks in advance to anyone else with a dented skull that has an answer!

RLM

View 1 Replies

Sysaltfiles And Sysfiles Inconsistant

Aug 2, 2007

I succesfully did a MSSQL "file" restore of production to a different node yesterday. But failed to apply any transaction logs, it complained that one of the files have not been restored. On further investigation I found that one of the files are missing in sysfiles, but the file is in sysaltfiles.

This SQL statement does not return the same number files.
SELECT * FROM <DB>..sysfilesSELECT * FROM master..sysaltfiles WHERE dbid= DB_ID('<DB>')

sp_helpdb '<DB>' gives the same result as sysfiles.

Any idea on how to fix this ?

Thanks
Tiaan

View 7 Replies View Related

Going Crazy With Inconsistant Batch Duration

Jul 17, 2007

First; thanks for looking.

On SQL Server 2000. IBM X series, dual 2GHz Xeon processors, 3GB RAM, RAID 5 DB array and separate Mirrored drive for OS, Windows 2000 Server.

Note: I should have moved the log files to the mirrored volume - I only just noticed it wasn't like that ... oops.:o It only has the OS and backup files (different logical volumes).

Problem: I have a Bill of Material system. It has a batch (Stored Procedure) that explodes Order Line Items. Each line has about 40 components.

The SP has 5 major steps to explode the Assembly, figure out colors/sizes to get the actual part numbers, assign quantities, update flags on the line item table (that has 3,500,000 rows but is not a clustered index - fill factor 85% on the unique autonumber index I use for the updates). The SP is jam packed with User Function calls, sub-Stored Procedures, and (yes) some cursors (on small declared temp tables created for each Order Line processed).

The execution plan for the whole batch of 100 is humongous - like 1000 pages, however nearly all steps are about 16 - 40 milliseconds to execute.

It normally takes 13 seconds to process 100 line items (3900 assembly rows created). However; about every 20th run, it takes 2 or 3 minutes ON THE VERY SAME LINE ITEMS. No programming changes! Run back-to-back, or with a minute or two delay. Then running it again, back to 13 seconds. Sometimes it takes 25 or so seconds, but that oddball 3 minutes is a killer.

I put some timing INSERTs into a temporary log file to localize the problem.

So; here's the REALLY weird thing. The part that experiences the intermittent 2 minute delay is a VERY SIMPLE call to a SP that INSERTs to the tbBATCHs table and gets the identity and passes it back with an Output variable. The tbBATCHs table only has about 8 columns. Nobody else uses it so it's not locking. It has an "If Exists" selection following the Insert to see if another batch is in progress not yet flagged as complete, but's not the issue because it's never true (hard coded values for testing forces this).

I have a Standby Server (duplicate config of main server) so I ran it there. Same issue.

If I loop 100 times on the tbBATCHs INSERT SP, it is pretty quick (like 2 seconds for all 100).

It's never on the first run, so I think it may be some sort of Log File catch-up.

Also; I DO NOT have Transaction Logging in place yet.

Ideas?

View 5 Replies View Related

Inconsistant Row Counts When Selecting Data By Dates HELP!

Feb 28, 2000

Hi,

I currently think Im going mad!!

I have a large table (2+Million records, 2000+ new per day). If I want to count the records in one month, I could do :-

Select Count(*) from Table1 where Added_Date between '1999-12-01'
and '1999-12-31'

or

Select Count(*) from Table1 where Added_Date >= '1999-12-01' and
Added_Date <= '1999-12-31'

For these two above, I get the same result.

HOWEVER!!

If I then do:-

Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00'
and '1999-12-31 23:59:59'
or use the >= and <= operators, the result is larger.

If I then do :-

Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00.000' and '1999-12-31 23:59:59.999'
or use the >= and <= operators, the result is larger still but i believe this including more rows than there actually is?.

I can get over the problem programatically using Convert to truncate to a day, month or year however, I don't understand why the results are different.

Any Ideas as to why this is happening, A'm I being stupid!!!!!!!

(PS Added_Date has a non_clustered index which has been rebuilt twice and DBCC finds no errors)

Many thanks,

Peter Farley

View 1 Replies View Related

Inconsistant Query Results With Count(*)... Bad Index?

Jul 20, 2005

I have a table that seems to have a bad index. When I do the followingquery I get inconsistant and needless to say incorrect results.select count(*) from mytable where mycolumn = 1If I remove the index from "mycolumn" the query works correctly. If Iadd the index back (even with a new name etc...) it doesn't workright.Has anyone ran into this? or does anyone know how I can fix thisproblem?It seems that removing the index is not really removing everythingbecause when I add a new one I get this same problem... btw, this isisolated to this column on this table. all other indexes within thedatabase are fine.Any help would be appreciated.Thanks,dharper

View 1 Replies View Related

Sp_trace_generateevent - Arguments Inconsistant With Trace Column Types

Jan 21, 2008

The documentation for sp_trace_generateevent states that the following arguments are available:

Arguments
[ @eventid=] event_id
Is the ID of the event to turn on. event_id is int, with no default. The ID must be one of the event numbers from 82 through 91, which represent user-defined events as set with sp_trace_setevent.
[ @userinfo= ] 'user_info'
Is the optional user-defined string identifying the reason for the event. user_info is nvarchar(128), with a default of NULL.
[ @userdata= ] user_data
Is the optional user-specified data for the event. user_data is varbinary(8000), with a default of NULL.

@userinfo is stored in the textdata column, which after inspecting sys.trace_columns is of type text with a max size of 1073741824.

@userdata is stored in the binarydata column whose type is image with a max size of 1073741824.

I am attempting to build a stack trace of sorts that will capture detailed information about the run-time code executed when an error (contained within a try/catch) occurred.

The sql returned from dm_exec_sql_text goes well beyond 8000 and is getting truncated.

I haven't found any hot-fixes for SQL Server 2005 that addresses this issue. Has anyone else bumped into this limitation or found any explanation on the inconsistency between the stored procedure��s arguments and the column types that the values are stored in?

T-SQL sp_generate_traceevent
http://msdn2.microsoft.com/en-us/library/ms177548.aspx

Trace Columns
select * from sys.trace_columns

View 1 Replies View Related

Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp

Aug 23, 2007

after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?

View 4 Replies View Related

Execution Plans &<&> Proportionate Execution Times

Dec 7, 2005

Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.

View 10 Replies View Related

Execution Procedure Stored During Execution Of The Report .

Aug 3, 2007

Hello :

How to execute a procedure stored during execution of the report, that is before the poster the data.

Thnak you.

View 4 Replies View Related

Execution In Scheduled Job Vs Direct Execution

Nov 29, 2004

Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table

If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.

I used the same record, same parameters, and the same statements to call the stored procedure.

Any idea?