sql server hints and tips. consolidating ssis tips until they outgrow it

01SqlAndSsisHints.md

# table of contents

• oracle
• sqlserver
• date arithmetic
• difference between two dates
• joins
• pivot rows into a single column delimited value using stuff
• set operations
• triggers
• type conversion
• misc
  • troubleshooting tips
  • zensheet
• ssis and dts
• ssrs

tagTest: hello world

02OracleHints.md

#Oracle hints

##misc

###dynamic tables

google: external tables

if you can't normalize the file name, use alter table TABLE_NAME location ('report-20140302.csv')

pro-tip: you'll need to use dynamic sql to use DDL in a stored proc/package.

to troubleshoot, use (as sys or better) select * from dba_external_locations

###object state

how to avoid pain when you update an object and forget about it's dependencies. better way to avoid this is to use table apis rather than dml.

select count(*) "No of invalid objects" from user_objects where status <> 'VALID';

###server info

select * from v$instance;
SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

###scripting template

SET DEFINE OFF
SET SERVEROUTPUT ON
DECLARE
  varname VARCHAR2;
BEGIN
  dbms_output.put_line('begin: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  -- do amazing shit...
  dbms_output.put_line('finish: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
END;
/

##general schema scripts without the cruft that sql developer wants to put in

SELECT RPAD(COLUMN_NAME ,50) || 
DECODE( DATA_TYPE,'VARCHAR2',DATA_TYPE ||'(' ||DATA_LENGTH || ')',
        'NUMBER',DATA_TYPE ||'(' ||DATA_PRECISION || decode(DATA_SCALE,0,')', ',' || DATA_SCALE || ')'),
        DATA_TYPE)
 || DECODE(NULLABLE,'N','          NOT NULL','') || ',' FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE_FOO'
ORDER BY COLUMN_ID;

you'll need to add create table and Run Script instead of Run Statement

##troubleshooting sql server to oracle connectivity

1. is there a linked server?
2. test the connection (in right click menu)
3. if it fails go to cmd prompt a. tnsping ORACLE_OBJECT.WORLD b. go to directory of parameter file c. confirm TNSNAME tnsnames.ora
4. select * from openquery(ORACLE_LinkedServerName, 'SELECT * FROM ORACLE_Object');
5. select * from ORACLE_LinkedServer..ORACLE_SCHEMA.ORACLE_OBJECT

source: http://sqlblog.com/blogs/john_paul_cook/archive/2009/07/31/linked-server-vs-openquery-for-handling-data-type-conversions.aspx

Table of Contents

##setup new users in oracle

###define a profile i.e. service accounts only need to change password every year

CREATE PROFILE service_accounts LIMIT
   FAILED_LOGIN_ATTEMPTS 3
   PASSWORD_LIFE_TIME UNLIMITED

###create user

create user *USERID* identified by *PASSWORD*
default tablespace *TABLESPACE_DATA*
temporary tablespace *temp*

###create role

create role *ROLE* NOT IDENTIFIED

###create object

table of contents

##running sprocs in Oracle

DECLARE
  P_ASOFDATE DATE;
BEGIN
  P_ASOFDATE := '9-MAY-13';

  FIZZBUZZ( -- fizzbuzz is your sproc with a parameter of p_asofdate
    P_ASOFDATE => P_ASOFDATE
  );
END;    

don't forget by default oracle doesn't emit any feedback unless you enable SET SERVEROUTPUT ON and have some dbms_output.put_line

table of contents

##Offset a date in Oracle

use case: midas plus dates

-- 15082 is our midas plus date example, whose  epoch is 31st of December 1971
SELECT TO_CHAR(TO_DATE('31-DEC-1971') + 15082) AS foo 
FROM dual;

table of contents

##find objects

system table and views are always in CAPS.

select * from all_objects where object_name like '%STUFF_IN_CAPS%'

table of contents

##how to update a table with joins (oracle)

UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);

source: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join

table of contents

###grant access to object

###new user creates synonym to object

tips:

• create package, they allow you to give users access to several sprocs and datatypes rather than explictly as individuals grants.
• synonym allow you to hide the details of the object owner, this is allows you to change them without having everyone update their code.

table of contents

##profiles

select profile, resource_type, resource_name, limit from dba_profiles order by 1,2,3;

table of contents

##locked accounts

select username, account_status, lock_date
from dba_users
where account_status != 'OPEN'
order by username;

table of contents

##joins

very detailed document about the many ways you can use a join

table of contents

##dump source of views

assuming you're running in this sql developer

set long 100000;
select text from all_views where view_name ='YOURMUM';

hit f5

##datatypes

create table foo ( floater float(126) null, numero number(27,10) null);
insert into foo values (1234567890.1234567890,1234567890.1234567890);
insert into foo values (1234567890.12345678901234567890,1234567890.12345678901234567890);
insert into foo values (1234567890.123456789012345678901234567890,1234567890.123456789012345678901234567890);
insert into foo values (1234567890.1234567890123456789012345678901234567890,1234567890.1234567890123456789012345678901234567890);
select * from foo;
drop foo;

----------------------------------------+--------------------
FLOATER                                 |NUMERO
----------------------------------------+--------------------
1234567890.123456789					|1234567890.123456789
1234567890.1234567890123456789			|1234567890.123456789
1234567890.1234567890123456789012345679	|1234567890.123456789

03SqlHints.md

#SQL Server #A-E

##CASE WHEN

CASE foo 
    WHEN 'bar' THEN 'yes'
    ELSE 'mej' THEN 'no
END

table of contents

##How to use CTEs

;WITH numbers(n) as (
SELECT ROW_NUMBER() OVER (ORDER BY object_id) 
FROM sys.objects
)
SELECT * FROM numbers WHERE numbers.n > 5

more examples: http://www.sqllion.com/2010/08/common-table-expressions-cte/

table of contents

###calculate days between dates This will count weekends and is ignorant of bank hols. Based on this answer: http://stackoverflow.com/a/252533/105282

  DECLARE @StartDate DATETIME
  DECLARE @EndDate DATETIME
	SET @StartDate = '2013/03/31'
	SET @EndDate = GETDATE() 
	
	SELECT  DATEDIFF(day, @StartDate, @EndDate) as NotInclusive
			,DATEDIFF(day, @StartDate, @EndDate) +1 As Inclusive

table of contents

##difference between two dates

select datediff(year|month|day|hour|minute|second, min(dateField), max(dateField)) from foo;

##add more time

select dateadd(year|month|day|minute|second, dateField)

###example factory clock checkin

with foo(empid, checkin, inout) as (
select 1, '2013-09-08 08:19', 'i' 
union all
select 1, '2013-09-08 12:19', 'o'
union all
select 1, '2013-09-09 07:00', 'i' 
union all
select 1, '2013-09-09 15:00', 'o'
union all
select 1, '2013-09-10 09:00', 'i' 
union all
select 1, '2013-09-10 17:00', 'o'

)

select empid, convert(varchar(10), checkin, 120) as dateOnly, COUNT(*), datediff(MINUTE, MIN(checkin),MAX(checkin)) as minutes
from foo
group by empid, convert(varchar(10), checkin, 120) ;

table of contents

#F-J ##Finding dependencies (caveats!)

###tl;dr

####SQL Server

select object_name(id), [text] 
from sys.syscomments 
where [text] like '%tfn_foobar%'

• https://www.simple-talk.com/sql/t-sql-programming/exploring-your-database-schema-with-sql/
• http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

table of contents

##add new rows when identity id is enabled

set insert_identity table_to_override on
insert table_to_override(id,foo) values(31337,'bar');
set insert_identity table_to_override off

table of contents

##Joins

Adding any left join criteria to where clause make it behave like an inner join.

table of contents

###Joins as Venn diagrams

• INNER JOIN - Insection of A+B
• LEFT OUTER JOIN - A and insection of A+B

visual representations: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

table of contents

###set operations

####UNION

[1,2,3] UNION [4,5,6] = [1,2,3,4,5,6]

####INTERSECT

[1,2,3] INTERSECT [3,4,5] = [3]

####EXCEPT

[1,2,3] EXCEPT [1,2,3,4] = [4]

table of contents

#K-O

##linked servers

SELECT name, COALESCE(remote_name,'N/A') AS remote_name, product, data_source, [catalog] 
FROM sys.servers s
  LEFT JOIN sys.linked_logins l
		ON s.server_id = l.server_id
WHERE provider = 'SQLNCLI' 
ORDER BY data_source

to create a server use sp_addlinkedserver: http://msdn.microsoft.com/en-us/library/ms190479.aspx

to create a server use sp_addlinkedsrvlogin: http://msdn.microsoft.com/en-us/library/ms189811.aspx

table of contents

##misc

• sql server
• select * from sys.tables and sys.views
• select * from sys.column where object_id=object_id(N'schema.table')
• sp_helptext 'table|view|sproc'
• select * from sys.columns where object_id = (select object_id from sys.tables where name='foo')
• CTRL-T text mode
• CTRL-D grid mode
• Renaming databases (does not change files) ALTER DATABASE oldName MODIFY NAME = newName
• oracle
• emulating recordsets using cursors - http://www.mkyong.com/oracle/oracle-stored-procedure-cursor-example/

table of contents

##How to find object references

To find all references to user defined sproc called usp_Foo.

select object_schema_name(object_id)
  , object_name(object_id)
	, [definition]  
from sys.sql_modules 
where [definition] like '%usp_Foo%'

table of contents

#P-T ##Right padding

Pad characters to right to a fixed width of 20 chars

WITH exampleCTE AS (

SELECT 'FOOBAR' AS PadThai
	UNION ALL
SELECT 'FIZZBUZZ' AS PadThai
)

SELECT LEFT(PadThai + Replicate('x', 20), 20) 
FROM exampleCTE

##Query msdb for packages

SELECT FLD.foldername + '\' + PCK.NAME AS FullPath
	,'v' + CONVERT(VARCHAR(10), vermajor) + '.' + CONVERT(VARCHAR(10), verminor) + '.' + CONVERT(VARCHAR(10), verbuild) AS Version
	,DATALENGTH(PCK.packagedata) AS PackageSizeInBytes
FROM msdb.dbo.sysssispackages pck
INNER JOIN msdb.dbo.sysssispackagefolders FLD ON pck.folderid = FLD.folderid
	AND FLD.foldername = 'APPLICATIOn'

##Pivot rows into a single column delimited value using for xml path

;WITH foo(parameter) AS ( -- 1 CTE
  SELECT '1' AS parameter
  UNION ALL
	SELECT '2' AS parameter
	UNION ALL
	SELECT '3' AS parameter
)
SELECT STUFF(
			( -- 2 subselect
				SELECT '|' + parameter -- '|' is your preferred delimiter
				FROM foo
				FOR XML PATH('')
			)
		,1 , 1, '') -- 3 stuff

###Break down of query

1 - cte, gives us test data 2 - subselect can be broken down as

• a standard single column select statement.
• using for xml path('') give a munged list, so we prepend '|' to delimit them 3 - finally we use stuff to delete the first delimiter

pro-tip: use rtrim to remove padding on integers smaller than convert char(n).

syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

bol

table of contents

##Selecting from one table and insert into another

source: http://blackwasp.co.uk/SQLSelectInsert.aspx

###Predefined table

insert into truedat
(foo, bar)
select fizz, buzz
from shiznits

###Creates table based on select (useful for temp tables)

select fizz, buzz
into truedat
from shiznits

table of contents

##Troubleshooting The specified @job_id (blah) does not exist in SQL Job Agent.

use msdb
exec sp_help_targetserver 'FOO'

if the unread_instructions value is high i.e. double digits then the most likely culprit is a missing proxy account on the target server. to confirm use this query:

select name from msdb.dbo.sysproxies
EXCEPT
select name from TARGETSERVER.msdb.dbo.sysproxies

table of contents

##Subselects

select p.code as name
   , (select options + '|' from config c where c.productid = __p.productid__ for xml path('')) as options
from product p

further reading: http://www.databasejournal.com/features/mssql/article.php/3464481/Using-a-Subquery-in-a-T-SQL-Statement.htm

table of contents

###Troubleshooting access Oracle/SSIS interop

1. Verify TNSNAMES is correct
2. Confirm Connectivity in SQL Developer (usually locked accounts will appear as a pop up when you try to connect).
3. Run stored procedure in SQL Developer
4. Confirm credentials are correct in package configuration file
5. Run SSIS package (that launches sproc)
6. Copy package configuration to server
7. Run SSIS package on the server
8. Give up go home

table of contents

##Type Conversion ###INT to VARCHAR

DECLARE @foo AS INT
SET @foo = 31337
SELECT CONVERT(VARCHAR(5), @foo)

###INT to DECIMAL

DECLARE @foo AS INT
SET @foo = 50

SELECT CONVERT(DECIMAL(5,2),@foo)/100

table of contents

##Triggers

Remember INSERTED in triggers is atomic i.e. one row per trigger. Don't try to join it on itself, otherwise you'll get unwanted data. Always join against the table that is being triggered.

table of contents

##Testing if you've already loaded data

    IF EXISTS(SELECT * FROM WHERE FOO='BAR')
       BEGIN
         SELECT 'I CAN HAZ DATA!'
       END
    ELSE
       BEGIN
         SELECT 'OMG NO DATA!'
       END

#U-Z

04SsisHints.md

##SSIS

####How to map a query to an ado object

Create a SQL Task and populate the following tabs

#####General tab

ResultSet: Full result set SQLStatement: Your sql query

#####Result Set tab

Result Name: 0 Variable Name: User::objVariable1

####Locking variables the right way

Not sure if this is a standard way or requires some stuff setup by our SSIS guru

Script Task

        (...)
        public void Main()
        {
            Variables vars = null;

            Dts.VariableDispenser.LockForRead("User::strVariable1");
            Dts.VariableDispenser.LockForWrite("User::strVariable2");
            Dts.VariableDispenser.GetVariables(ref vars);

            // add your code here...
            
            vars.Unlock();

            Dts.TaskResult = (int)ScriptResults.Success;

            
        }

####How to load datatable into an objectVariable that is shreadable in a ForEach Loop Container (ADO Enumerator)

TL;DR embed your table in a DataSet i.e.

    // we'll assume the following Script and Package variables exist
    // dt is a loaded DataTable
    // vars["User::TableToShread"] is variable reference to package variable of Object type
    
    DataSet ds = new DataSet();
    ds.Tables.Add(dt);
    vars["User::TableToShread"].Value = ds;

###How to shred an objcetVariable in a Script Task

using System.Data.OleDb;

DataTable dt= new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::objVariable"].Value);

foreach (DataRow row in dt.Rows)
{
   //insert what you want to do here
}

####Locking variables in a Script Component

    VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
    variableDispenser.LockForWrite("User::lolwut");
    IDTSVariables100 vars;
    variableDispenser.GetVariables(out vars);

    vars["User::lolwut"].Value = Row.RotflCopterColumn.ToString();

    vars.Unlock();

####Dealing with ...cannot convert between unicode and non-unicode string data types.

Add a Data Conversion task as a close as possible to your DFT source. Convert affected columns to Unicode string [DT_WSTR].

####Reusing a connection from the ConnectionManager

First off, try to use ADO if possible instead of OLEDB because you'll need to start adding references and casting objects. See references below.

Code

ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
System.Data.SqlClient.SqlDataReader rdr;

bool fireAgain = true;
public void Main()
{
    cm = Dts.Connections["ADONET.WOOT"];
    sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(null);
    sqlComm = new System.Data.SqlClient.SqlCommand("SELECT foo FROM bar", sqlConn);                           
    rdr = sqlComm.ExecuteReader();

    try
    {
        rdr.Read();                       
        Dts.Events.FireInformation(0, "", "foo: " + rdr[0].ToString(), "", 0, ref fireAgain);
    }
    finally
    {        
        rdr.Close();
        cm.ReleaseConnection(sqlConn);
    }           
    ... etc

References

• http://blogs.msdn.com/b/mattm/archive/2012/04/25/8889077.aspx?Redirected=true
• http://toddmcdermid.blogspot.co.uk/2011/05/use-connections-properly-in-ssis-script.html

####SQL Task, Result sets and Oracle select statements

The assumption is that you only want a single item value like count, sum etc

General Tab

ResultSet: Single row

Direct Input: SELECT TO_CHAR(TRUNC(SYSDATE)) FROM DUAL

Result Set Tab

Result Name: 0

Variable Name: User::strVariable1

If you need something to be dynamic i.e. SELECT TO_CHAR(?) FROM DUAL, you're better off using expressions to create a SQL variable: "SELECT TO_CHAR(" + @[User::ValueOfSysDate] + ") FROM DUAL"

####Troubleshooting problems with Data Flow Tasks to an Oracle Destination

Confirm the data types of your Oracle columns using Advanced Editor > Input and Output Properties > OLE DB Destination Input > External columns are string data types. By default Derived Columns component wants to confirm strings into Unicode strings. This will cause BIDS to carp about incorrect data types.

####Running DTS package and pass a value to a global variable

dtsrun /S<sqlserver> /E /N"PackageName" /A<GlobalVar>:8="31337"

/A needs a datatype from the following list

Data Type TypeID
---------+------
Integer   3
Date      7
String    8
Boolean   11
Decimal   14
Integer 
(8-byte)  20

####DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

despite your attempts to delay validation for connections and dft data sources. ssis still wants to be a dick and will not let you run your package (hurr) on the server unless you use expressions in your connection string. don't bother using cute String.Format format strings in your variables i.e. foo={0} it ain't gonna work.

####Excel breakage in 64bit environments

insert some pithy text complaining about how shit it is to work in an editor that is 32bit and server environment that is 64bit.

32bit connection strings

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=lolwat.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1'

64bit connection strings

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=lolwat.xls;Extended Properties="Excel 12.0;HDR=NO;IMEX=1"

The key points are the change in provide from Jet to ACE. And Extended properties.

Finally if you need to debug using the Excel source connector in a Data Flow Transformation task, change toggle your project's Debugging Environment in 64bit setting to False.

05SsrsHints.md

##SSRS

###Row count

=CountRow("DataSetName")

###Zebra lines

Add expr to BackgroundColor property of repeating row:

=IIF(RowNumber(Nothing) Mod 2 = 0,"Transparent","#cdfcbe")

table of contents