SSIS Parameter cannot be derived from sub-select queries

If you’re trying to use a parameter in a sub query using the SSIS SQL task you may encounter this error :

Edit the SSIS Execute SQL Task and check the ByPassPrepare option
Currently it is set to false, and we generate the error above.


Set the Set the ByPassPrepare option to True:
 
The task will now execute successfully !!

List All columns of a Table with their data type

USE AdventureWorks2008
GO 
SELECT t.name, 
t1.name,
t.name,
t1.name,
c.name,
c.max_length,
c.precision,
c.collation_name
FROM sys.TABLES t JOIN sys.COLUMNS c ON t.object_id = c.object_id JOIN sys.types t1 
ON c.user_type_id = t1.user_type_id WHERE t.name = 'Employee'

 

Download Code

How to check the installed version of powershell

 

The Easy Way, in the powershell prompt type:

PS D:\> $host.Version

Major  Minor  Build  Revision
-----  -----  -----  ------
2      0      -1     -1

Nice Free Script Generator - SQL Script Generator

SQL Script Generator Features

system_health session incidentally dropped

The session can be recreated using the file u_tables.sql (the script is at the bottom of the file) that is located in :

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Install\

Perfmon Counter poster (cheat sheet)

 

I really like this one from Quest Software.

List disabled Jobs and their schedule

To List the disabled Jobs and their relative schedules , you can run the following query:

USE msdb
GO
SELECT
 ss.schedule_id ,
 J.name,
 ss.name,
 J.enabled [Job Enabled ?],
 ss.enabled [Schedule ENABLED ?]
FROM sysjobs J
JOIN sysjobschedules s
 ON J.job_id = s.job_id
JOIN sysschedules ss
 ON s.schedule_id = ss.schedule_id
WHERE J.enabled = 0

The schedule_id can be used directly with sp_update_schedule, as from SQL 2008 schedules can be managed independantly of jobs.