C# Messagebox within SSIS

 

Add a script component to the error handler within the package and pass the error message as a readonly  variable.

Check the existence of indexes before trying to create them

Here is the standard template to check existence of an index within the database you are working on:

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

-- where MyTable and MyIndex are replaced with appropriate values

-- and 'IndexID' is the non-replaced string value actually used

   If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexID') Is Null

              CREATE INDEX index_name ON table_name (column1, column2, ...)

 -- for example:

       If IndexProperty(Object_Id('dbo.TEST'), 'IX_TEST_State', 'IndexID') Is  not Null

BEGIN

        CREATE INDEX [<IndexName>] ON [<DatabaseName>].[<Schema>].[<TableName>] 

        ([column1]) ,,,

END

But what if you're trying to create a stored proc to do this on a database other than where your stored proc exists.... like DB A is loaded every night by restoring a backup off another server and won't retain it's stored procedures when it's overwritten?  Unfortunately, the usual and common method documented above will not traverse to another database and will always report it was not found, thus trying to create the index anyway.  Here is a method that will query the other DB's internals without fail:

IF NOT EXISTS (

        SELECT si.name

        FROM DatabaseName>.sys.indexes si

        INNER JOIN <DatabaseName>.sys.objects so

        ON si.object_id = so.object_id

        WHERE si.name = '<IndexName>'

        AND so.name = '<TableName>'

)

BEGIN

        CREATE INDEX [<IndexName>] ON [<DatabaseName>].[<Schema>].[<TableName>] 

        ([column1]) ,,,

END

SQL Server Window Functions

Okay, so if you got this far then I assume you know that the picture above is not exactly what you're seeking.
Sometimes when you find something that works for you, it's better to just link to the source, like I've done here. The following is a  straight forward and easy to follow explanation of the Window Functions available in SQLServer:


https://www.sqlservertutorial.net/sql-server-window-functions/

Output values as single line CSV

declare @yourtable  table

([ID] int, [Date] datetime)

;

 

 

INSERT INTO @yourtable

([ID], [Date])

VALUES

(756, '2011-08-29 00:00:00'),

(756, '2011-08-31 00:00:00'),

(756, '2011-09-01 00:00:00'),

(756, '2011-09-02 00:00:00')

;

INSERT INTO @yourtable

([ID], [Date])

VALUES

(956, '2011-08-29 00:00:00'),

(956, '2011-08-31 00:00:00'),

(956, '2011-09-01 00:00:00'),

(956, '2011-09-02 00:00:00')

;

 

-- example 1-----------------------------------------------------------------

select

distinct t1.id,

STUFF((SELECT ', ' + convert(varchar(10), t2.date, 120)

   FROM @yourtable t2

   where t1.id = t2.id

   FOR XML PATH ('')), 1, 1, ''

)  AS date

from @yourtable t1;

 

 

-- query to return every table and column in order

select isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,max(SI.rows) as RecCount
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
from INFORMATION_SCHEMA.COLUMNS isc
join INFORMATION_SCHEMA.TABLES ist
on isc.TABLE_NAME = ist.TABLE_NAME
join SYSINDEXES SI
on ist.TABLE_NAME = OBJECT_NAME(SI.ID)
where isc.TABLE_CATALOG = 'xxxx'
and isc.TABLE_SCHEMA = 'dbo'
and ist.TABLE_TYPE = 'Base Table'
group by
isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
order by TABLE_NAME, ORDINAL_POSITION

-- query to determine to find all the keys in the database, which columns are involved, and report if they are combined keys (ColumnCount)


;with CTE_SingleKey as 
(select
'Single Column Key' as KeyType
,COUNT(scu.column_name) as ColumnCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name)
)
,CTE_MultiKey as 
(select
'Multi Column Key' as KeyType
,COUNT(scu.column_name) as ColumnsCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name) > 1
)

select mkey.*
,scu.COLUMN_NAME
from CTE_MultiKey mkey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on mkey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME

union

select skey.*
,scu.COLUMN_NAME
from CTE_SingleKey skey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on skey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME
order by
TABLE_NAME
,CONSTRAINT_NAME
,COLUMN_NAME

-- query to find all foreign keys and referenced tables/columns

SELECT obj.name AS FK_NAME,
schem.name AS [schema_name],
Table1.name AS [table],
Columns1.name AS [column],
table2.name AS [referenced_table],
Columns2.name AS [referenced_column]
FROM sys.foreign_key_columns fkCol
INNER JOIN sys.objects obj
ON obj.object_id = fkCol.constraint_object_id
INNER JOIN sys.tables Table1
ON Table1.object_id = fkCol.parent_object_id
INNER JOIN sys.schemas schem
ON Table1.schema_id = schem.schema_id
INNER JOIN sys.columns Columns1
ON Columns1.column_id = parent_column_id AND Columns1.object_id = Table1.object_id
INNER JOIN sys.tables table2
ON table2.object_id = fkCol.referenced_object_id
INNER JOIN sys.columns Columns2
ON Columns2.column_id = referenced_column_id
AND Columns2.object_id = table2.object_id

@Rank vs @Row_Number

So recently I was asked the difference between the two and my brain froze.  There are other phrases that come to mind, but the condition of frozen brain is more PC and I'm sticking to it.

Back on topic; SQL provides a few "Ranking Functions" that can be most helpful.  There is a subtle difference between @Rank and @Row_Number I will try to demonstrate here, starting with a table called "Inventory".  For purposes of demonstration, we will assume that we can have multiple records of the same inventory item at the same location with the same quantity.  Here is the Inventory table:

The following SQL exposes the difference in the two functions:

select recordid, productid, description, location, quantity
,rank() over (partition by produtctid order by quantity desc) as rank
,row_number() over (partition by productid order by quantity desc) as row_number
from inventory
Results:

Highlighted above are the @Rank and @Row_Number for ProductID 101 at Location 1. 
Notice, that for every Product 101 at Location 1 that @Row_Number increments by 1.
@Rank, in comparison, does not. Duplicates do not cause @Rank to increment.  On rows 1 and 2, the value of @Rank is 1 - these are the number 1 values.  ODDLY though, if we look on rows 3 and 4 (also duplicate records) we see that @Rank continues with a value of 3.  This is because there are 2 records in ahead of it.

Conclusion:  @Rank and @Row_Number have different outcomes when duplicate values are present for the Partition By and Order By clauses.  

All About SQL Joins

For purposes of demonstration, lets imagine we have a zoo.
The zoo database has a table of animals it keeps named Animals.
It also has a table of the soft stuffed animals it sells in the Products table.

You have been asked to provide the following:

Find each Animal where there is not a corresponding Product:

Select A.*, P.*
from Animal A
left join Product P
on A.Animal = P.Product
where P.Product is null


Find all the Animals where there is not a corresponding Product
AND all the Products where there is not a corresponding Animal

Select A.*, P.*
from Product P
full join Animal A
on P.Product = A.Animal
where A.Animal is null or P.Product is null


Find each Animal at the zoo where there is also a corresponding Product:


select A.*, P.*
from Animal A
join Product P
on A.Animal = P.Product




Find each Animal at the zoo and also each Product whether there is a match or not:

Select A.*, P.*
from Animal A
Full join Product P
on A.Animal = P.Product

String to delimited list Function

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

-- =============================================
-- Author:           blowersd
-- Create date:    20120821     
-- Description:   creates a quoted and delimited list from a string for use in a sql IN statement
--                          delimeter default is comma
-- USAGE:         SELECT DBO.FN_STRINGTOLIST('Ford,Lincoln,Mercury',',')
-- EXAMPLE:   Select * from Atable A where A.columnx in dbo.fn_stringtolist(@ReportDropDownParameter)
-- =============================================
ALTER FUNCTION DBO.FN_STRINGTOLIST (
    @PARMVARIABLE VARCHAR(4000)
    ,@DELIMITER VARCHAR(1) = ','
    )
RETURNS VARCHAR(4050)
AS
BEGIN
    DECLARE @OUTLIST VARCHAR(4050);

    SET @DELIMITER = ISNULL(@DELIMITER, ',');
    SET @OUTLIST = '''' + REPLACE(@PARMVARIABLE, ',', ''',''') + '''';

    RETURN @OUTLIST;
END;

Report database sizes and status

------------------------------Data file size----------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go
 
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), 
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB 
from sys.database_files  where type=0 group by type'
go

-------------------log size--------------------------------------
  if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go
 
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB 
from sys.database_files  where type=1 group by type'
go

--------------------------------database free size
  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))
 
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use [?];SELECT database_name = db_name()
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
    ,''unallocated space'' = ltrim(str((
                CASE 
                    WHEN dbsize >= reservedpages
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2) + '' MB'')
FROM (
    SELECT dbsize = sum(convert(BIGINT, CASE 
                    WHEN type = 0
                        THEN size
                    ELSE 0
                    END))
        ,logsize = sum(convert(BIGINT, CASE 
                    WHEN type <> 0
                        THEN size
                    ELSE 0
                    END))
    FROM sys.database_files
) AS files
,(
    SELECT reservedpages = sum(a.total_pages)
        ,usedpages = sum(a.used_pages)
        ,pages = sum(CASE 
                WHEN it.internal_type IN (
                        202
                        ,204
                        ,211
                        ,212
                        ,213
                        ,214
                        ,215
                        ,216
                        )
                    THEN 0
                WHEN a.type <> 1
                    THEN a.used_pages
                WHEN p.index_id < 2
                    THEN a.data_pages
                ELSE 0
                END)
    FROM sys.partitions p
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    LEFT JOIN sys.internal_tables it
        ON p.object_id = it.object_id
) AS partitions'
----------------------------------- 
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate 
create table #alldbstate 
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))
  
--select * from sys.master_files
 
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize
 
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'
 
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'
 
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'
 
select   
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace
from #dbsize d join #logsize l 
on d.Dbname=l.Dbname join #dbfreesize fs 
on d.Dbname=fs.name
order by Dbname

Reporting Metrics for SSRS

Here is a practical query that can be easily modified to provide SSRS Reporting Metrics and help find unused or misconfigured objects;  Modify the granularity by simply uncommenting the UserName lines of code below. 

USE REPORTSERVER; SELECT COUNT(RE.REPORTID) AS EXECUTION_COUNT ,CAT.NAME ,CAT.PATH ,SUM(TIMEDATARETRIEVAL) / COUNT(NAME) AS AVG_MS_DATA_RETRIEVAL ,SUM(TIMEPROCESSING) / COUNT(NAME) AS AVG_MS_PROCESSING ,SUM(TIMERENDERING) / COUNT(NAME) AS AVG_MS_RENDERING ,SUM(BYTECOUNT) / COUNT(NAME) AS AVG_BYTE_COUNT ,SUM([ROWCOUNT]) / COUNT(NAME) AS AVG_ROW_COUNT ,CAST(MIN(TIMESTART) AS DATE) AS FIRST_RUN ,CAST(MAX(TIMESTART) AS DATE) AS LAST_RUN --,RE.USERNAME FROM CATALOG CAT LEFT JOIN ( SELECT EL.REPORTID ,EL.TIMESTART ,EL.TIMEDATARETRIEVAL ,EL.TIMEPROCESSING ,EL.TIMERENDERING ,EL.BYTECOUNT ,EL.[ROWCOUNT] --,EL.USERNAME FROM EXECUTIONLOG EL) AS RE ON CAT.ITEMID = RE.REPORTID WHERE CAT.TYPE = 2 GROUP BY RE.REPORTID ,CAT.NAME ,CAT.PATH --,RE.USERNAME ORDER BY NAME ,PATH;

ERROR: User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

This happens when you are on the server and want to administer report content from the server.  In short, the problem is that the local user does not have permissions. It appears to be a bug in the how permissions get set up within SSRS.

RESOLUTION

Run IE, Firefox or whatever as administrator

Navigate to http://localhost/Reports - (Do not use http://<server name>/Reports)

From the Root Folder, Click on  "Folder Settings" as below



Click on New "Role Assignment" and add "Content Manager" to the person you want to administer your content

SSRS Divide by Zero Handling Alternative

So in creating an SSRS report recently which contained percent calculations in the totals I came across a divide by zero error I could not get around.  All the checks for null/0 in the world wouldn't get rid using the expression editor.




Here is the formula for the detail level where it works fine:


Margin = (Price-Cost)/Price


and checking for zero values


Margin = Iif((Price-Post)=0 or Price=0),0, (Price-Cost)/Price




But for totals, it throws an error




Margin = (Sum(Price) - Sum(Cost))/Sum(Price)


Margin = Iif(((Sum(Price)-Sum(Cost))=0 or Price=0)),0, (sum(Price)-sum(Cost))/sum(Price)




SHOULD be straight forward,,,,, but not.  It appears to be too complex in some way for the expression to be calculated and just returns errors.




So, and I'm borrowing here, I'm going to repost something simple but brilliant that was posted by William Mendoza on his blog site:




In the Menu; got to Report > Report Properties > Code and paste the code bellow

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal If denominator = 0 Then Return 0 Else Return numerator / denominator End If End Function
 
To call the function go to the the Textbox expresion and type:
=Code.Quotient(SUM(fields!FieldName.Value),SUM(Fields!FieldName2.Value))


 in this case I am putting the formula at the Group level so I am using sum. Otherwise it would be:
=Code.Quotient(fields!FieldName.Value,Fields!FieldName2.Value)


Thanks William for your post and help

Remove Spaces and Non Alpahnumeric Characters

SELECTRTRIM(CUSTNMBR)AS CUSTNMBR
,REPLACE(REPLACE(CUSTNMBR ,SUBSTRING(CUSTNMBR ,PATINDEX('%[^a-zA-Z0-9 ]%' ,CUSTNMBR) ,1) ,'') ,CHAR(32) ,'')AS NU_CUSTNMBR

,CUSTNAME


FROM RECORDSTABLE


WHERECUSTNMBR LIKE '%[^a-zA-Z0-9 ]%'

OR CHARINDEX(CHAR(32) ,RTRIM(CUSTNMBR)) > 0;

Excellent Query Analysis Tool

I'm always looking for new tools for SQL. 

Here is one that truly sets the standard for Query Execution Plan Analysis:

SQL SENTRY PLAN EXPLORER

The feature I like about it this best is that you can navigate your sql code and the analysis plan window stays in sync with where you are in the code.  Conversely, the code window is sync'd to the analysis plan. 

On top of this, the tool also provides tabular performance metrics of the various operations instead of the user having to hunt through the diagrams to find the problems. 

Best of all, there is a free version you can check out and decide if you want to get the reasonably priced pro-version.

There is too much in this tool for me to list all the great points about it.  I realize this sounds like a sales pitch, but after you check it out you will be impressed too.

SMS Tips and Tricks SQL

I don't often link to other posts - Yeah I know, a fatal bloggers mistake.  However, here is a a really nice set of tips: 

http://www.bidn.com/blogs/MMilligan/bidn-blog/3326/sql-server-management-studio-ssms-tips-and-tricks

Single Quotes in String Values

The following code includes an example of building a string value which has a single quote character in the text and also searching a field for a single quote character.

declare @test varchar(1000)

declare @i int

IF OBJECT_ID('tempdb..#testtable') IS NOT NULL  

    DROP TABLE #testtable

create table #testtable

(val varchar(200)

)

-- Intentionally misspelled

set @i = 1

set @test =  'Had this been a real emergency, you''re keester would already by fried'

 

while @i < 20

begin

  insert #testtable

  select cast(@i as varchar(1000))

  set @i = @i + 1

end

insert #testtable

select @test

-- find the one record 

select val

from #testtable

where val like '%''%'

Find instances of a string in all your stored procedures:

This is a great little trick I came across while we were moving from one server to another and had to change the prefix on all references to that SQL machine - but it work in any instance where you need to search through all your stored code:

USE <databasename>; go   SELECT routine_name               ,routine_definition FROM information_schema.routines WHERE routine_definition LIKE '%<my search string>%'   AND routine_type = 'PROCEDURE' ORDER BY routine_name;

SSRS - List User Parameter Selection

In SSRS,
How do I list the parameter the user selected in the parameter drop down?

Place a label on the header of your report and create an expression for the value:

 = "Days Selected " + Parameters!parm_Days_Past_Due.Label



What if multi-select is enabled for the parameter and the've chosen more than one value?
 ="Location Type= " + Join(Parameters!Location_Type.Label,",")

In this case, the join function treats the drop down label as an array object and concantonates all the values separated by a comma.  You can also use other characters to separate the list, but a comma is the most easily understood.