It's hard to be specific with an advice without knowing how the procedure is built.

but generally speaking - you can include any value of any variable you're interested in while...

It's not about catching errors, it's about displaying the status of the process in the message tab without disrupting the execution.

After the execution is completed you may scroll through the...

Use RAISERROR with severity 0 (not actually raising any error, just printing a message) to display the state of affairs inside the procedure at any stage of execution.

That's how it...

• This reply was modified 6 years, 2 months ago by Sergiy.
• This reply was modified 6 years, 2 months ago by Sergiy.

Is it a part of a transaction opened somewhere in the application code?

a transaction which includes UPDATE and/or DELETE statements in line with that SELECT.

Because you have an "X" lock,...

I cannot see how <blocking-process> stands on the way of <blocked-process>.

Both queries apply shared locks, so, they should not block each other.

Can you find what is waitresource in the 1st...

Yes, it works, it's easy enough to write code to prove it:

;with tbltest as (select cast('ab012' as varchar(5)) as TestNumber) select * from tblTest where TestNumber = cast(20012 as...

Here is the full scenario:

You run the following query:

select * from tblTest

where TestNumber = 20012

The query output window displays the following error: "Conversion failed when converting the varchar value...

It's not a problem for the number used in the question.

And there is no indication how a number "1234" would be correctly represented in tblTest. May be '01234' is not...

I don't think it's a bug.

When you misuse partitioning and create daily or weekly partitions you end up with hundreds or thousands separate indexes created for every partition. Overhead of...

Numeric values take at least 5 bytes: starting with 4 bytes  of metadata (defining the numeric type) followed by as many as necessary bytes of the integer representation of the...

And try to get rid of daily partitioning. With clustering on date it only adds to overheads without providing any advantage.

There is no point having a clustered index on date and id a non clustered index on date - they duplicate each other.

On the old version of the server you...

He kinda did. Right here

Any other explanation why the splitting ends...

With a Calendar table in place it seems to be easier:

select DS.*, C.Date, EOM,
CASE WHEN ds.StartDate > C.Date then ds.StartDate ELSE c.Date END SplitBegin, 
case when EOM...

Set End_Date = ISNULL (Deceased_date, End_Date)