Joel Greijer

  select     day as date_bk ,     day as date ,     cast ( day as datetime ) as date_time ,     cast ( day as timestamp ) as date_timestamp ,     format_date ( '%Y-%m-%d' , day ) as date_string ,     extract ( year from day ) AS year ,     extract ( quarter from day ) AS quarter ,     'Q' || extract ( quarter from day ) AS quarter_string ,     extract ( month from day ) AS month ,     format_date ( '%B' , day ) AS month_string ,     format_date ( '%b' , day ) AS month_abbr_string ,     extract ( week from day ) AS week_sun ,     cast ( format_date ( '%W' , day ) as integer ) AS week_mon ,     extract ( isoweek from day ) AS iso_week ,     'W' || format ( '%02d' , extract ( isoweek from day )) AS iso_week_string ,         extract ( day from day ) AS day_of_mon...

;WITH unicodeDataSource AS (   SELECT N'Hello' AS Text , N'English' AS Language UNION ALL   SELECT N'Здравствуйте' AS Text, N'Russian' AS Language UNION ALL   SELECT N'こんにちは' AS Text, N'Japanese' AS Language UNION ALL   SELECT N'Hallå' AS Text, N'Swedish' AS Language  ) SELECT *,    iif( s.Text = cast(cast(s.Text AS varchar(100)) AS nvarchar(100))      ,1,0) AS CastWasSuccessful FROM unicodeDataSource s Result Text Language CastWasSuccessful Hello English 1 Здравствуйте Russian 0 こんにちは Japanese 0 Hallå Swedish 1

var metadataConnection = SchemaManager.CreateConnectionNode( "db_metadata" , RootNode.Connections[ "db" ].RenderedConnectionString); List< string > includedSchemas = new List< string >{ "schema1" }; List< string > includedTables = null ; var metadataSchema = metadataConnection.GetDatabaseSchema( includedSchemas, includedTables, ImportOptions.None); foreach ( var t in metadataSchema.TableNodes) { //perform biml magic }

declare    @quitWithSuccess tinyint = 1,   @quitWithFailure tinyint = 2,   @gotoNextStep tinyint = 3,   @gotoStep tinyint = 4

There are some hoops you need to jump through to get a multi-statement transaction committed via powershell. Default time-out is set to only one minute, so using the code below you can increase it. $timeout = New-Object System.TimeSpan -ArgumentList @ (5,0,0) # five hours $options = [System.Transactions.TransactionScopeOption] :: Required $scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @ ( $options , $timeout ) try { #Multiple queries, loops or whatever fun here, inside transaction Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName1 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName2 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName3 ; $scope .Complete() #we tell transactionscope to commit when using e...

/* Checksum() isnt very good for detecting data changes. Use hashbytes() instead Example of output from script below: -361975519341300.801789 */ select avg(cast(cast(hsh as bigint) as decimal(25,0))) as truncatedHash from ( select cast(HASHBYTES('SHA1', [StringField1]+ [StringField2]+ [StringField3]) as binary(20)) as hsh FROM dbo.TableToBeInvestigated ) as hashedData