by Arnaud Gastelblum | SQL Server, Tips & Tricks
In SQL Server Management Studio (SSMS for close friends) but also in a multitude of text editors (such as Notepad++ for example), you can make multiple selections.
I thought it was something everyone knew, but I realize that I often look like a magician every time I do it.
I’m delighted to pretend to be Harry Potter, but I think it’s time for this little game to stop!
Look by yourself how simple it is!
You should press the keys [ALT] + [Shift] simultaneously and move your cursor [Up] and / or [Down] to select your text.
Sometimes we have to surround our text with single quotes. (for example: to add multiple codes to an IN clause in a test query)
No need to add them one by one and make sure you do not have space at the end.
Here is the method:
Like mentioned above, this tip is not an exclusivity in SSMS, you can also do the same in many different text editor.
by Arnaud Gastelblum | SQL Server, Tips & Tricks
Dear BI Developer,
I’m pretty sure you would be happy to improve your productivity. If not, you should at least read this article to look like a superman (superwoman) developer.
When I start a new mission, the first thing I do is to set up SSMS (SQL Server Management Studio). And because I’m the kind of guy who acts like a Microsoft BI evangelist (and also for running), I replicate my configuration on my colleague’s machines.
In this example – CTRL + 4 – COUNT
Select statement you want to execute, Press CTRL and 4
SSMS will give you Nb impacted rows.
In BI (and not only!), it’s very important to test if we have unexpected behavior with our joins.
Does our INNER JOIN filter too much data? Or worst, does our join multiply our result set?
A quick and easy CTRL + 4 will ensure you to respect your grain.
How to configure Management Studio
Open Management Studio, Go to Tools > Option…
Under Environment > Keyboard > Query Shortcuts
You have a list of existing shortcuts. (I don’t change them, but I neither use them too).
You should now fill each text box with a query.
(See image and table below)
Queries are available on the next section
Do not forget to add a space after each query.
SELECT COUNT(1) AS Nb FROM
EXEC sp_executesql N' SELECT schemas.name ,tables.name ,columns.name ,types.name ,columns.max_length ,columns.is_nullable ,columns.is_identity FROM sys.tables tables INNER JOIN sys.schemas schemas ON schemas.schema_id = tables.schema_id INNER JOIN sys.all_columns columns ON columns.object_id = tables.object_id INNER JOIN sys.types types ON types.system_type_id = columns.system_type_id WHERE UPPER(RTRIM(LTRIM(tables.name))) = UPPER(RTRIM(LTRIM(REPLACE(REPLACE(@objname, '']'', ''''), ''['', '''')))) ORDER BY tables.object_id, columns.column_id',N'@objname nvarchar(776)', @objname =
Select a table and show a quick description of attributes (Name, Datatype, size, nullable, identity)
SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = qt.text ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme ,DatetimeSnapshot = GETDATE() FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp WHERE cp.plan_handle = er.plan_handle ) ec OUTER APPLY ( SELECT lead_blocker = 1 FROM master.dbo.sysprocesses sp WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND sp.blocked = 0 AND sp.spid = er.session_id ) lb WHERE er.sql_handle IS NOT NULL AND er.session_id != @@SPID ORDER BY er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id
by Arnaud Gastelblum | SQL Server, SSIS, Tips & Tricks
This query helps you to have a better view of your SSIS Packages executions.
When you run a “master job / package”, you don’t clearly see the execution time for your child packages in SSISDB Reports.
With this query, now it’s possible 🙂
USE SSISDB
GO
SELECT
package_name
,execution_id
,executable_id
,StartTime
,EndTime
,LastStatus
,execution_result
,RowIndex
,DATEDIFF(minute, StartTime, EndTime) AS 'execution_time[min]'
FROM
(
SELECT DISTINCT
executables.package_name AS package_name
,executables.execution_id AS execution_id
,executables.executable_id AS executable_id
,FIRST_VALUE(start_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY start_time ASC) AS StartTime
,FIRST_VALUE(end_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY end_time DESC) AS EndTime
,FIRST_VALUE(execution_result) OVER(PARTITION BY executables.package_name ORDER BY end_time DESC) AS LastStatus
,execution_result AS execution_result
,ROW_NUMBER() OVER(partition by executables.package_name ORDER BY end_time DESC) AS RowIndex
FROM [catalog].[executables]
LEFT JOIN [catalog].[executable_statistics] ON
executable_statistics.execution_id = executables.execution_id
AND executable_statistics.executable_id = executables.executable_id
--WHERE executables.package_name = 'YourPackage.dtsx'
) A
WHERE a.RowIndex = 1
ORDER BY StartTime ASC
by Arnaud Gastelblum | SQL Server, Tips & Tricks
You need to:
- Export your SSIS Environments (Variables)
- Replicate it on your new server
- Assign these variables to your new project
SSIS Catalog is not complex, but we don’t really want to spend our time to copy manually our configuration from one server to another one.
Often, we don’t have access to the production environment.
The SQL Code below helps you to create a script and automate all these very ennuying steps.
I found the base line of this code on internet: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/135173/
Jeff Jordan did a really nice work.
His code helps you to:
- Create your SSIS Catalog folder
- Create your SSIS Environment
- Add SSIS Variables in your SSIS Environment
I added some extra steps:
- Assign your SSIS Environment to your SSIS Project
- Assign your SSIS Environment Variables to your SSIS Project Variables
- + Some correction
- + A super nice ASCII message when everything is done.. Who can help you to have a smile and maybe a better life. (You should trust the power of smiling )
How does it works?
- Copy the SQL Script below and paste it into a new blank query in SSMS (Management studio) –> Connected to your existing server (The server who has your environment already configured / Not the new one)
- Configuration: Change the XXXX value in the query
- @folder : Folder Name (Your configured server)
- @env : Environment Name (Your configured server)
- @destination_folder_name : Folder Name (Your new server)
- @destination_environment_name : Environment Name (Your new server)
- @destination_project_name : Project Name (Your new server)
- Run your query and copy the result into a new blank query in SSMS. –> Connected to your new server
- FIRST OF ALL: You should deploy your SSIS Project!! With the Deployment wizard or Visual Studio
- Change the configuration information into the generated query
- Run the query
- Smile
-- Backup / Restore SSIS Catalog Environments
--
--http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/135173/
SET NOCOUNT ON;
DECLARE
-- ---------------------------------------------------------------------------
-- Configuration
-- ---------------------------------------------------------------------------
-- --------
-- Current environment (For extraction)
--
@folder sysname = 'XXXXX',
@env sysname = 'XXXXX',
-- --------
-- Destination environment (For Deployment)
--
@destination_folder_name sysname = N'XXXXX', -- Folder (SSIDB >> "FolderName")
@destination_environment_name sysname = N'XXXXX', -- Environment Name
@destination_project_name sysname = N'XXXXX', -- Project Name
-- ---------------------------------------------------------------------------
-- End Configuration
-- ---------------------------------------------------------------------------
@project_id int,
@reference_location char(1),
@folder_description nvarchar(1024),
@sql varchar(max) = '',
@name sysname,
@cr char(1) = char(10),
@tab char(4) = SPACE(4),
@ver nvarchar(128) = CAST(serverproperty('ProductVersion') AS nvarchar);
SET @ver = CAST(SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1) as int);
IF (@ver < 11)
BEGIN
RAISERROR ('This procedure is not supported on versions prior SQL 2012', 16, 1) WITH NOWAIT;
END;
IF NOT EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSISDB')
BEGIN
RAISERROR('The SSISDB database does not exist on this server', 16, 1) WITH NOWAIT;
END;
/* TO DO - get the folder, environment description-*/
SET @sql = '/*------------------------------------------------------------------------------------------' + @cr;
SET @sql += @tab + 'This script creates a script to generate and SSIS Environment and its variables.' + @cr;
SET @sql += @tab + 'Replace the necessary entries to create a new envrionment' + @cr;
SET @sql += @tab + '***NOTE: variables marked as sensitive have their values masked with ''''.' + @cr;
SET @sql += @tab + @tab + 'These values will need to be replace with the actual values' + @cr;
SET @sql += '------------------------------------------------------------------------------------------*/' + @cr +@cr;
SET @sql += 'DECLARE @ReturnCode INT=0, @folder_id bigint' + @cr + @cr;
SET @sql += '-- ---------------------------------------------' + @cr;
SET @sql += '--'+ @tab + 'Variable declarations, make any changes here' + @cr;
SET @sql += '-- ---------------------------------------------' + @cr;
SET @sql += 'DECLARE '+ @cr;
SET @sql += @tab + @tab + ' @folder sysname = ''' + @destination_folder_name + ''' /* this is the name of the new folder you want to create */' + @cr;
SET @sql += @tab + @tab + ',@env sysname = ''' + @destination_environment_name + ''' /* this is the name of the new environment you want to create */' + @cr;
SET @sql += @tab + @tab + ',@project sysname = ''' + @destination_project_name + ''' /* this is the name of the new project you want to create */' + @cr;
PRINT @sql;
/*
Generate the variable declarations at the "top" this makes it easier to replace/update the values
The variable names here map to the name of the variable being created
*/
IF OBJECT_ID('tempdb..#env_var') IS NOT NULL DROP TABLE #env_var
IF OBJECT_ID('tempdb..#cmd') IS NOT NULL DROP TABLE #cmd
-- ---------------------------------------------
-- Create Variables Declaration
-- ---------------------------------------------
SELECT
[env_var] =
@tab + @tab + ',@' + LEFT(ev.name +' ', CASE WHEN( LEN(ev.name) >= 40 ) THEN LEN(ev.name) ELSE 40 END ) + ' '
+ CASE WHEN( ev.base_data_type = 'nvarchar' )
THEN ev.base_data_type +'(600) '
ELSE ev.base_data_type
END + '= N''' + ISNULL(CONVERT(varchar(max), ev.value), '') + ''''
, [name] = ev.name
INTO #env_var
FROM [SSISDB].[catalog].[folders] f
INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id
INNER JOIN [SSISDB].[internal].[environment_variables] ev ON e.environment_id = ev.environment_id
WHERE
(f.name = @folder)
AND (e.name = @env)
;
/*
Yes, I am looping here. We don't know how many variables, sql_variant can be up to 8,000 bytes for the base type
and don't want to be limited trying to print varchar(max) to the output window
... so we're going to print them one at a time
*/
WHILE EXISTS (SELECT TOP 1 1 FROM #env_var)
BEGIN
SELECT TOP 1 @sql = env_var, @name = name FROM #env_var ORDER BY name;
PRINT @sql;
DELETE FROM #env_var WHERE name = @name;
END;
SET @sql = ';' + @cr + '/* Starting the transaction */' + @cr;
SET @sql += 'BEGIN TRANSACTION' + @cr;
SET @sql += @tab + 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder)' + @cr;
SET @sql += @tab + 'BEGIN' + @cr;
SET @sql += @tab + @tab + 'RAISERROR(''Creating folder: %s ...'', 10, 1, @folder) WITH NOWAIT;' + @cr;
SET @sql += @tab + @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_folder] @folder_name=@folder, @folder_id=@folder_id OUTPUT' + @cr;
SET @sql += @tab + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr;
SET @sql += @tab + 'END' + @cr + @cr;
SET @sql += @tab + 'RAISERROR(''Creating Environment: %s'', 10, 1, @env) WITH NOWAIT;' + @cr;
SET @sql += @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env' + @cr;
SET @sql += @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr + @cr;
SET @sql += @tab +'-- ------------------------------------------ '+ @cr
SET @sql += @tab + '-- Variable creation' + @cr;
SET @sql += @tab +'-- ------------------------------------------ '+ @cr
PRINT @sql;
-- ---------------------------------------------
-- Generate the variable creation
-- ---------------------------------------------
SELECT [cmd] = @tab + 'RAISERROR('' Creating variable: ' + ev.name + ' ...'', 10, 1) WITH NOWAIT;' + @cr
+ @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]' + @cr
+ @tab + @tab + '@variable_name=N''' + ev.name + '''' + @cr
+ @tab + @tab + ', @sensitive=' + CONVERT(varchar(2), ev.sensitive) + @cr
+ @tab + @tab + ', @description=N''' + ev.[description] + '''' + @cr
+ @tab + @tab + ', @environment_name=@env' + @cr
+ @tab + @tab + ', @folder_name=@folder' + @cr
+ @tab + @tab + ', @value=@' + ev.name + @cr
+ @tab + @tab + ', @data_type=N''' + ev.type + '''' + @cr
+ @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr
, [name] = ev.name
INTO #cmd
FROM [SSISDB].[catalog].[folders] f
INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id
INNER JOIN [SSISDB].[catalog].[environment_variables] ev ON e.environment_id = ev.environment_id
WHERE (f.name = @folder) AND (e.name = @env);
/*Print out the variable creation procs */
WHILE EXISTS (SELECT TOP 1 1 FROM #cmd)
BEGIN
SELECT TOP 1 @sql = cmd, @name = name FROM #cmd ORDER BY name;
PRINT @sql;
DELETE FROM #cmd WHERE name = @name;
END;
-- ------------------------------------------
-- Set Environment to Project
-- ------------------------------------------
SET @sql = 'RAISERROR(''Configure Project: %s ...'', 10, 1, @project) WITH NOWAIT;' + @cr + @cr
SET @sql += '-- ------------------------------------------ '+ @cr
SET @sql += '-- Set Environment to Project ' + @cr
SET @sql += '-- ------------------------------------------ '+ @cr
SET @sql += 'RAISERROR('' Set Environment (%s) as Reference ...'', 10, 1, @env) WITH NOWAIT;' + @cr
SET @sql += 'DECLARE @reference_id AS BIGINT'+ @cr
SET @sql += 'EXEC SSISDB.catalog.create_environment_reference'+ @cr
SET @sql += @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")'+ @cr
SET @sql += @tab + @tab + '@environment_name = @env, -- Environment Name '+ @cr
SET @sql += @tab + @tab + '@project_name = @project, -- Project Name '+ @cr
SET @sql += @tab + @tab + '@reference_type = ''R'','+ @cr
SET @sql += @tab + @tab + '@reference_id = @reference_id OUTPUT'+ @cr
PRINT @SQL
-- ------------------------------------------
-- Set Environment Variable to Project Variables
-- ------------------------------------------
IF OBJECT_ID('tempdb..#cmd2') IS NOT NULL DROP TABLE #cmd2
SET @sql = '-- ------------------------------------------ '+ @cr
SET @sql += '-- Set Environment Variable to Project Variables '+ @cr
SET @sql += '-- ------------------------------------------ '+ @cr
PRINT @sql
-- -----------------
-- OLD
-- -> Assign only project variables (From environment to project variables)
--SELECT
-- [cmd] =
-- 'RAISERROR('' Set Project variable ('+ ev.name +') ...'', 10, 1) WITH NOWAIT;' + @cr
-- + 'EXECUTE [SSISDB].[catalog].set_object_parameter_value '+ @cr
-- + @tab + @tab + '@object_type = 20,' + @cr
-- + @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")'+ @cr
-- + @tab + @tab + '@project_name = @project, -- Environment Name'+ @cr
-- + @tab + @tab + '@parameter_name = N'''+ ev.name +''', -- Project Variable Name'+ @cr
-- + @tab + @tab + '@parameter_value = N'''+ ev.name +''', -- Environment Variable Name'+ @cr
-- + @tab + @tab + '@object_name = N'''','+ @cr
-- + @tab + @tab + '@value_type = ''R'''+ @cr
-- ,[name] = ev.name
-- INTO #cmd2
-- FROM [SSISDB].[catalog].[folders] f
-- INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id
-- INNER JOIN [SSISDB].[catalog].[environment_variables] ev ON e.environment_id = ev.environment_id
-- WHERE (f.name = @folder) AND (e.name = @env);
SELECT
[cmd] =
'RAISERROR('' Set Project variable ('+ op.parameter_name +') ...'', 10, 1) WITH NOWAIT;' + @cr
+ 'EXECUTE [SSISDB].[catalog].set_object_parameter_value ' + @cr
+ @tab + @tab + '@object_type = '+ CONVERT(NVARCHAR(5), op.object_type) +',' + @cr
+ @tab + @tab + '@folder_name = @folder, -- Folder (SSIDB >> "FolderName")' + @cr
+ @tab + @tab + '@project_name = @project, -- Environment Name' + @cr
+ @tab + @tab + '@parameter_name = N'''+ op.parameter_name +''', -- Project Variable Name' + @cr
+ @tab + @tab + '@parameter_value = N'''+ op.referenced_variable_name +''', -- Environment Variable Name'+ @cr
+ @tab + @tab + '@object_name = N'''+ CASE WHEN op.object_name <> @env THEN op.object_name ELSE '' END +''',' + @cr
+ @tab + @tab + '@value_type = ''R''' + @cr
,[name] = op.parameter_name
INTO #cmd2
FROM SSISDB.catalog.folders
INNER JOIN SSISDB.catalog.projects ON projects.folder_id = folders.folder_id
INNER JOIN SSISDB.catalog.object_parameters op ON op.project_id = projects.project_id
--INNER JOIN SSISDB.catalog.environment_variables ON environment_variables.name = op.parameter_name
--INNER JOIN SSISDB.catalog.environments ON
-- environments.environment_id = environment_variables.environment_id
-- AND environments.folder_id = folders.folder_id
WHERE
folders.name = @folder
--AND environments.name = @env
AND projects.name = @env
AND op.referenced_variable_name IS NOT NULL
/*Print out the variable creation procs */
WHILE EXISTS (SELECT TOP 1 1 FROM #cmd2)
BEGIN
SELECT TOP 1 @sql = cmd, @name = name FROM #cmd2 ORDER BY name;
PRINT @sql;
DELETE FROM #cmd2 WHERE name = @name;
END;
/* finsih the transaction handling */
SET @sql = 'COMMIT TRANSACTION' + @cr;
--SET @sql += 'RAISERROR(N''Complete!'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' _____ _ _ _ _ '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''/ __ \ | | | | | | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| / \/ ___ _ __ ___ _ __ | | ___| |_ ___ __| | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| | / _ \| ''''_ ` _ \| ''''_ \| |/ _ \ __/ _ \/ _` | |'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N''| \__/\ (_) | | | | | | |_) | | __/ || __/ (_| |_|'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' \____/\___/|_| |_| |_| .__/|_|\___|\__\___|\__,_(_)'', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' | | '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'RAISERROR(N'' |_| '', 10, 1) WITH NOWAIT;' + @cr;
SET @sql += 'GOTO EndSave' + @cr + @cr;
SET @sql += 'QuitWithRollback:' + @cr;
SET @sql += 'IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' + @cr;
SET @sql += 'RAISERROR(N''Variable creation failed'', 16,1) WITH NOWAIT;' + @cr + @cr;
SET @sql += 'EndSave:' + @cr;
SET @sql += 'GO';
PRINT @sql;
