The exception message should help track down the problem. Is it something like 'execute permission denied' or 'insert denied on...'? Also, are you using any system stored procedures (e.g. xp_cmdshell)...

dmbaker (6/21/2012)

---

ISNULL(p.TrackingNumber,'') > ''

...may be wasteful in terms of processor cycles, but it makes it a bit clearer that the author was at least deliberately thinking about the NULL case....

Adding Database Users to the db_owner Role will work fine however this is a good time to start thinking about User-defined Database Roles. I like to only grant permissions, including...

DLathrop (6/20/2012)

---

Without seeing the whole conditional, it's hard to tell if the ISNULL is a waste because NULL is not a value. E.g.,

WHERE ship_by_date > @today

OR ( ...

Jeff Moden (6/19/2012)

---

Just a recommendation. I've found that using SET STATISTICS when a scalar or multi-line table valued UDF is involved will cause the UDF to look much worse...

Sachin Nandanwar (6/19/2012)

---

Thanks for the replies.

@Grant

Yes the whole optimization effort was done based on the readings of the execution plan.I have achieved significant gain in terms of the execution time(screenshot...

SP2 is very old. Is applying SP4+CU3+MS11-049 an option?

One more question about the data. How many rows are you calculating the Median over? Median being what it is, you have...

I am seeing some reports of this kind of issue with earlier builds of 2005. What build are you running?

SELECT SERVERPROPERTY('Edition') AS Edition,

...

Please post all messages from the SQL Server Error Log if you don't mind and have a moment. I would be interested to see what was written.

Yeah, that sounds like a bad build, either of the .NET framework or of SQL Server, or both. Please post back when you get the new server and let us...

nick947 (6/19/2012)

---

I either get a transport error (when the sql server service restarts) or I get this error message:

"Msg 6522, Level 16, State 2, Line 6

A .NET Framework error occurred...

SQL Server will buffer the trace results for performance reasons. Depending on what you set your trace file size to, and presumably what else is going on with the server...

I did not run it, but your code looks OK to me. One item that came to mind is that serialized SQLCLR objects in SQL Server 2005 have a max...

The code can be optimized if rewritten as:

Since NULL will not be greater than anything the call to ISNULL is wasteful.

edit: put code inside quote block to...

If you meant SSIS package, and not actually DTS (SQL 2000, 7.0):

kausmail (6/8/2012)

---

I have a DTS package that imports the data from csv file into the table everyday. I Now...