@@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

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

Compare two SQL Server tables

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.

UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs successfully, which a join clause or a WHERE condition doesn't normally do.  It also allows you to very quickly see which rows are missing in either table, which only a FULL OUTER JOIN will do, but of course we all know to avoid those at all costs (right?) -- a full outer join is about as “unrelational” as you can get.  (every column returned is potentially Null and must be wrapped in a COALESCE function).  Best of all, the UNION is quick and easy and short.

The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2.  But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want.  We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.

So, here's an example, assuming we are comparing tables A and B, and the primary key of both tables is ID:

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
  FROM A
  UNION ALL
  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
  FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID