SQL Spark

SSIS build config 1. Project -> Properties -> General -> TargetServerVersion = SQL Server 2022 2. Security -> Protection Level = DontSaveSensitive 3. Execution -> DelayValidation = True 4. When you create your packages in Visual Studio you can specify the Version Major, Minor, Build and associated descriptions in the package properties window. 5. SSIS Data Source connect to MS SQL How to check the Version and Build Number of an SSIS Package USE SSISDB GO --Check full version history of installed SSIS packages --NB only works for project deployment model SELECT folders.name [Folder Name] ,projects.name [Project Name] ,packages.name [Package Name] ,version_major [Version Major] ,version_minor [Version Minor] ,version_build [Version Build] ,project_version_lsn [Project LSN] ,object_versions.created_time [Installed] ,IIF(object_versions.object_version_lsn=projects.object_version_lsn,'Yes','No') [Latest Versio...

/********************************************* Description: Import xml file to table on SQL Server 2022-11-12 created Test.xml <ROOT> <RecordTotal><2></RecordTotal><2> <FileDate><2022-11-10></FileDate><2> <ITEM> <FIELD1>1<FIELD1> <FIELD2>test1<FIELD2> </ITEM> <ITEM> <FIELD1>2<FIELD1> <FIELD2>test2<FIELD2> </ITEM> </ROOT> *********************************************/ use ETL --select ('D:\ETL\Test.xml') declare @XML as XML, @hDoc AS INT, @SQL NVARCHAR (MAX) SELECT @XML = CAST(MY_XML AS xml) FROM OPENROWSET(BULK 'D:\ETL\Test.xml', SINGLE_BLOB) AS T(MY_XML) --select @XML TRUNCATE TABLE tb_Import EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML Insert into tb_Import SELECT 1 AS ExecID, 20220122 as ExecEffectiveDate, * FROM OPENXML(@hDoc, 'Root/Item') WITH ( RecordTot...

Database project - unresolved reference Database project - How to handle metadata scripts DACPAC deployment - How to ignore login and user DACPAC deployment - Table schema changed to NOT NULL

  USE master Declare @strFind varchar(max) Set @strFind = '%ma_cancadeal%' select distinct object_name(OBJECT_ID)  from sys.sql_modules  where definition like @strFind  order by object_name(OBJECT_ID)  declare @sql varchar(max) SELECT @sql = 'USE ? print ''?''; select ''?'' as DB_Name, object_Name(object_id),* from sys.sql_modules WHERE definition LIKE ''%'+@strFind+'%'''  EXEC sp_MSforeachdb @sql 

  -- Save File as .xdl and then open in SSMS  SELECT DeadloackDetail = CONVERT(xml, event_data).query('/event/data/value/child::*'), CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time FROM sys.fn_xe_file_target_read_file('F:\SQL13BIN\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null) WHERE object_name like 'xml_deadlock_report'

  1. Register SPN for SQL service account SetSPN -S MSSQLSvc/win2012r2-e424b.dynamic.sit.cloud.td.com:3341 D2-TDBFG\TCRBI084SQLS 2. Enable Delegate in Active Directory, go to SQL server account Properties, on "Delegation" tab, select "Trust this user for delegation to any service (Kerberos only)" 3. Verify SPN and delegate Get-ADUser -Identity TCRDM463SQLSS -Properties * After Enabled 4. confirm Authentication use K erberos select ServerName =@@ServerName, Servicename, service_account, status_desc from sys.dm_server_services select @@ServerName, auth_scheme from sys.dm_exec_connections where session_id =@@SPID 5. Create Linked Server use AD authentication, access based on account use linked server USE [master] GO /****** Object:  LinkedServer [DEV3]    Script Date: 11/9/2021 12:12:02 PM ******/ IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DEV3') BEGIN EXEC master.dbo.sp_addlinkedserver @server = N'DEV3...

  select * from msdb.dbo.sysmail_profile p join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id   join msdb.dbo.sysmail_account a on pa.account_id = a.account_id       join msdb.dbo.sysmail_server s on a.account_id = s.account_id