number to datetime

convert(datetime ,43040.379150544 )
Till Next Time

image to varchar

convert(varchar(max),convert(varbinary(max),<>))


Till Next Time

MSSQL: Export all packages from MSDB package store

Quick script to generate DTUTIL statements to export all SSIS package from the MSDB package store to the file system using DTUTIL:

;
WITH FOLDERS AS
(
-- Capture root node
SELECT
cast(PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.dbo.sysssispackagefolders PF
WHERE
PF.parentfolderid IS NULL
-- build recursive hierarchy
UNION ALL
SELECT
cast(F.FolderPath + '\\' + PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.dbo.sysssispackagefolders PF
INNER JOIN
FOLDERS F
ON F.folderid = PF.parentfolderid
)
, PACKAGES AS
(
-- pull information about stored SSIS packages
SELECT
P.name AS PackageName
, P.id AS PackageId
, P.description as PackageDescription
, P.folderid
, P.packageFormat
, P.packageType
, P.vermajor
, P.verminor
, P.verbuild
, suser_sname(P.ownersid) AS ownername
FROM
msdb.dbo.sysssispackages P
)
SELECT
-- assumes default instance and localhost
-- use serverproperty('servername') and serverproperty('instancename')
-- if you need to really make this generic
'DTUTIL /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\\' + P.PackageName + '" /En file;"F:\\Archief\\SSIS\\'+CONVERT(VARCHAR(50),GETDATE(),112)+'\\' + P.PackageName +'.dtsx";4 /Q' AS cmd
FROM
FOLDERS F
INNER JOIN
PACKAGES P
ON P.folderid = F.folderid
-- uncomment this if you want to filter out the
-- native Data Collector packages
WHERE
F.FolderPath <> '\Data Collector'
and F.FolderPath not Like 'Maintenance%'

Till Next Time

MSSQL Scripted indexes

Dimension tables:

/*
(re)creating all dimensional indexes
within a schema in one go
for dimension tables
20081219 JJM: First
darft
*/
declare @strSql01 varchar(4000)
declare @strSql02
varchar(4000)
declare @strTableName varchar(500)
declare @strColumnName
varchar(500)
declare @strIndexFileGroup varchar(500)
declare
@strSchemaName varchar(500)
set @strIndexFileGroup = 'INDEX'
set
@strSchemaName = 'dbo'
declare idx_cur cursor for
SELECT distinct
table_name, column_name FROM information_schema.columns
WHERE Table_Name
like 'Dim_%'
and Column_name like '%Code%'
and table_schema =
@strSchemaName
and table_name in (SELECT name FROM sys.objects WHERE name
like 'Dim_%'
AND type in (N'U')
) order by table_name
open
idx_cur
fetch next from idx_cur into @strTableName,@strColumnName
while
@@fetch_status=0
begin
set @strSql01 = 'IF EXISTS (SELECT * FROM
sys.indexes WHERE object_id =
OBJECT_ID(N''['+@strSchemaName+'].['
+@strTableName
+']'') AND name =
N''IX_'
+@strTableName +'_' +@strColumnName +''')
DROP INDEX
[IX_'
+@strTableName +'_' +@strColumnName +'] ON
['+@strSchemaName+'].['
+@strTableName
+'] WITH ( ONLINE = OFF
)'
print @strSql01
exec (@strSql01)
set @strSql02 = 'CREATE
NONCLUSTERED INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON
['+@strSchemaName+'].['
+@strTableName
+'] ([' + @strColumnName
+']
ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, '
+ 'SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, '
+'ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ['+ @strIndexFileGroup
+']'

print @strSql02
exec (@strSql02)
fetch next from idx_cur
into @strTableName,@strColumnName
end
close idx_cur
deallocate
idx_cur

Fact tables:

/*
(re)creating all dimensional indexes within a schema in one go
for facttables
20081219 JJM: First darft
*/
declare @strSql01 varchar(4000)
declare @strSql02 varchar(4000)
declare @strTableName varchar(500)
declare @strColumnName varchar(500)
declare @strIndexFileGroup varchar(500)
declare @strSchemaName varchar(500)

set @strIndexFileGroup = 'INDEX'
set @strSchemaName = 'dbo'

declare idx_cur cursor for
SELECT distinct table_name, column_name FROM information_schema.columns
WHERE Table_Name like 'Feit_%'
and Column_name like 'Dim_%'
and table_schema = @strSchemaName
and table_name in (SELECT name FROM sys.objects WHERE name like 'Feit_%'
AND type in (N'U')
) order by table_name

open idx_cur
fetch next from idx_cur into @strTableName,@strColumnName
while @@fetch_status=0
begin

set @strSql01 = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['+@strSchemaName+'].['
+@strTableName
+']'') AND name = N''IX_'
+@strTableName +'_' +@strColumnName +''')
DROP INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON ['+@strSchemaName+'].['
+@strTableName
+'] WITH ( ONLINE = OFF )'

print @strSql01
exec (@strSql01)

set @strSql02 = 'CREATE NONCLUSTERED INDEX [IX_'
+@strTableName +'_' +@strColumnName +'] ON ['+@strSchemaName+'].['
+@strTableName
+'] ([' + @strColumnName
+'] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, '
+ 'SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, '
+'ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ['+ @strIndexFileGroup +']'


print @strSql02
exec (@strSql02)

fetch next from idx_cur into @strTableName,@strColumnName
end

close idx_cur
deallocate idx_cur

Till Next Time