List all Database Constraints

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where Constraint_Name like '%Service%'
GO

ORA-00907 Missing Paranthesis error

PROBLEM:  SSIS package fails  with ORA-00907 error even though the query can be successfully parsed in the data flow task.

POSSIBLE SOLUTION:  Remove all of the comments from the query.

----------------------------------------------------------------------------------------

Sound stupid, I know, but try it anyway.

Determine Currently Running SQL Server Processes

1.1.1       Determine Currently Running SQL Server Processes

SELECT p.spid
,   right(convert(varchar,            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

If you need to see the SQL running for a given spid from the results, use something like this:

Declare @spid int,   @stmt_start int,   @stmt_end int,   @sql_handle binary(20) set @spid = XXX -- Fill this in select  top 1    @sql_handle = sql_handle,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 endfrom    master.dbo.sysprocesseswhere   spid = @spidorder by ecid SELECT    SUBSTRING(    text,                  COALESCE(NULLIF(@stmt_start, 0), 1),                  CASE @stmt_end                         WHEN -1                                THEN DATALENGTH(text)                         ELSE                                (@stmt_end - @stmt_start)                         END           )FROM ::fn_get_sql(@sql_handle)

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

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

The binding status was "DT_NTEXT". The data flow column type is "DBBINDSTATUS_UNSUPPORTEDCONVERSION".

The problem is that the data in that column is longer than 255 characters.  If the Jet driver (the code that reads the Excel file for SSIS) detects content in that column larger than that, it presents the data to SSIS as a DT_TEXT or DT_NTEXT data type.  SSIS has no control over that behaviour.  Changing the "data type" in Excel doesn't exist - you only change data "format"... and that won't help you here, because it's simply a matter of size.

What you have to do is accept that you're getting a DT_NTEXT in to SSIS, which is a "long" string type - a CLOB.  In order to "convert" that to a DT_WSTR, you'll need to use a Data Conversion or Derived Column to create a new column with that data.  Make sure the column you make has enough room for the incoming data...