@@RowCount after Truncate

@@Rowcount with Truncate

Aug 10 2012 12:00AM by Jeetendra   

---

@@ROWCOUNT returns the number of affected rows by last statement. Below example illustrate the same

1.CREATE TABLE #t (id int)

2.INSERT INTO #t(id) values(1)

3.DELETE   #t

4.SELECT @@ROWCOUNT

Here we will get @@ROWCOUNT AS 1
But same did not work for truncate

1.CREATE TABLE #t (id int)

2.INSERT INTO #t(id) values(1)

3.TRUNCATE table   #t

4.SELECT @@ROWCOUNT

Here we will get @@ROWCOUNT AS 0
It is because truncate removes page allocation not as individual row, so it will not return row count value.
one important point is, if number of affected row is more then 2 billion, we need to use rowcount_big.

CTRL + R not working

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/07/14/sql-server-v-next-denali-getting-ctrl-r-to-work-again.aspx

As a true sign of my insanity, I keep trying - expecting a different outcome with the same input. Then I notice the status bar changing as I fruitlessly try to make the results pane go away. On first press of Ctrl+R, I see this in the bottom left corner:

 

And when I press it again, along with the beep, the status bar changes to this:

 

Okay, so now we're onto something - it seems this change was intentional. So off I go to Tools / Options / Keyboard / General to see what's what. In the middle box, select Window.ShowresultsPane, change the "Use new shortcut in:" dropdown to SQL Query Editor (Global did not work for me), put your cursor in the "Press shortcut keys:" box and hit CTRL + R. You should see the following:

SSIS Execute SQL Task and RaiseError

SSIS Execute SQL Task and RaisError (Using a OLEDB Connection)

This is a relatively short post about raising an error in T-SQL and having SSIS not recognize that error.  If you have an SSIS package that calls a SQL Task and that T-SQL for whatever reason ends up raising an error the SSIS package that called the T-SQL may complete with the step without error.    There may be two separate reasons why this is not working as expected. 
The first reason why it may not be working has to do with an apparent bug is SQL Server 2005 SP2. (It looks like it was a bug in 2008 as well) There are a number of good posts on this, but I believe you can fix this by installing SP 2 Updates.  The first step is to make sure you know what version of SQL Server you are on. (http://support.microsoft.com/default.aspx/kb/321185)  The second step is to upgrade to a SQL Server build that has this error corrected. (http://support.microsoft.com/kb/937137/)   I have chosen to installed the most recent Cumlative Update that was released on August 18th 2008, it will bring you to version 09.00.3282.00.
About the SP2 Error:
http://www.windows-tech.info/15/2c8831412be41b1f.php
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828

The second reason has to do wiht the RaisError() call itself.  The Error Level set in the RaisError statement needs to be set to a level of 11 or higher.  That's it, it's that simple. Below is a simple example of testing this process.
Procedure:
Create proc [dbo].[failure_proc]
as
BEGINRaisError('Steves Error',18,1)
END

EXCEL :: Reduce Excel file size

The "UsedRange" does not correctly get reset causing a lot of bloat. You can reset this very simply with 1 line of VBA code.

ActiveSheet.UsedRange

Open the Developer tab in Excel, insert a Module, add the following macro, and run it

Public Sub ResetUsedRange()
    ActiveSheet.UsedRange
End Sub

You could also tie this macro to an Excel keyboard shortcut for quicker access using the Application.OnKey event

Access database engine stopped the process because you and another user are attempting to change the same data

The following text was found in the "Notes" column of tblPPSFOSED.Notes

#Error

The Fix
Manually Compact & Repair, then SQL Update the row

UPDATE tblPPSFOSED SET Notes = '' WHERE PPsFOSEDID = 1644