Tally table in SQL

I had a situation where I had to write out to a file where the  column3 ( string)  should be split and write to the next line if the length was greater than 50 . I used Tally table to do that.

WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)

,cte AS
(
SELECT
  v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50  + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Explanation ---

select * from cteTally
V
-----
1
1
1
1
1
1
1
1
1
1
Select * from cte
v
----
1
51
101
151
201
.
.
.
.
4951
(100 rows because of cross join)
finally use the place holder row value in the sub string

SELECT
  tbl1.col1   'Column1'
  ,tbl1.col2  'Column2'
  ,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50)  'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''

Another easy to understand example that I found on http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html
Generating a sequence of numbers from 1 to 20
DECLARE @Max AS INT = 20


;WITH CTE AS (
  SELECT 1 Num
  UNION ALL
  SELECT Num + 1 FROM CTE WHERE Num < @Max
)


SELECT * FROM CTE
 



----Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

---Generating a sequence of Dates starting with the current date & going till next 20 days


DECLARE @MaxDate AS DATETIME = GETDATE() + 20


;WITH CTE AS (
  SELECT GETDATE() Dates
  UNION ALL
  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)


SELECT * FROM CTE

Generate XML data

--XML data sample to be genereated

<?xml version="1.0"?>
-<serialization SerializationRoot="SBOInboundBatchCO">

-<SBOInboundBatchCO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO">

-<sboInboundBatchIO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO">
<creationDate Type="DATE">2017/05/02</creationDate>
<externalReferenceDate Type="DATE">2017/05/02</externalReferenceDate>

-<sboInboundMessageIOs ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO" IsIndexed="1">

-<element ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO">
<billingPeriodsequenceNumber>2</billingPeriodsequenceNumber>
<messageCodeBehaviourCode Type="STRING">CACISBOACKGMENT</messageCodeBehaviourCode>
<messageText Type="STRING"/>
<receivableGrpId Type="STRING">615466</receivableGrpId>
<responsiblePartyIdentifier Type="STRING">Y</responsiblePartyIdentifier>
</element>

</sboInboundMessageIOs>
<sboName Type="STRING">CACi BD Collections</sboName>
<totalMessageCount Type="NUMERIC">284</totalMessageCount>
</sboInboundBatchIO>
</SBOInboundBatchCO>
</serialization>

Used the following SQL script to get this output

declare @intTotMsgCount int , @xmlstr xml



　
 set @inttotMsgCount = (Select count(Account_number) from Imp_staging_referrals_claims_FollowUp where imp_fileId = @intfileId

and uploaded = 1 and deleted = 0)



　
 set @xmlstr=



　
 (select 'SBOInboundBatchCO' as '@SerializationROOT',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO' as '@ClassName',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO' as '@ClassName',

'DATE' as 'creationDate/@Type',

convert(varchar(10),cast(getdate() as date) ,111) as 'creationDate',

'DATE' as 'externalReferenceDate/@Type',

convert(varchar(10),cast(getdate() as date) ,111) as 'externalReferenceDate',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName', '1' as '@IsIndexed',




(
  select ' com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName',

'1' as 'billingPeriodsequenceNumber',

'STRING' as 'messageCodeBehaviourCode/@Type',

'CACISBOACKGMENT' as 'messageCodeBehaviourCode',

'STRING' as 'messageText/@Type',

'' as 'messageText',

'STRING' as 'receivableGrpId/@Type',

substring(Account_Number,2,len(Account_Number)-4) as 'receivableGrpId',

'STRING' as 'responsiblePartyIdentifier/@Type',

'Y' as 'responsiblePartyIdentifier'

from Imp_staging_referrals_claims_FollowUp

where imp_fileId = @intFileId

and Facility_Code = @intClientCode



　
 for xml path ('element') , elements,type




)
  for xml path ('sboInboundMessageIOs') , type

)

,'STRING' as 'sboName/@Type',

'CACi BD Collections' as 'sboName',

'NUMERIC' AS 'totalMessageCount/@type',

@intTotMsgCount as 'totalMessageCount'

for xml path('sboInboundBatchIO'), elements,type




)
  
for xml path('SBOInboundBatchCO'), type




)
  for xml path ('serialization') , type




)
  --AS COL_XML
  
select '<?xml version="1.0"?>' + cast(@xmlstr as varchar(max)) as COL_XML

SSIS error while project deployment from the local machine to the server.

While doing a SSIS project deployment on my staging server from my desktop I ran into the following error

"Please create a master key in the database or open the master key in the session before performing this operation, ( Microsoft SQL server, Error:15581)"

The staging SSISDB database was a restore copy of the production .

I tried to alter the master key with the pwd from the prod
USE [SSISDB]
OPEN master Key decryption by password ='password' --my prod password
ALTER Master Key ADD encryption by Service Master Key

But again got the following error -

Msg 15313, Level 16, State 1, Line 2
The key is not encrypted using the specified decryptor.
Msg 15581, Level 16, State 7, Line 3
Please create a master key in the database or open the master key in the session before performing this operation.

Tried to create a master key -
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.

tried to drop the master key -
use ssisdb
GO
drop master key

Got the following error
Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'MS_Cert_Proj_1' is encrypted by it.


Solution :
I backed up the SSIS Key on prod from the following

use ssisdb
GO

BACKUP MASTER KEY TO FILE = 'v:\backupkeys\SSISDBKey1
    ENCRYPTION BY PASSWORD = 'password' 

Then I copied the key file over to the test server in v:\backupkeys_staging\SSISDBKey1
use ssisdb
GO
RESTORE MASTER KEY FROM FILE = 'v:\backupkeys_staging\SSISDBKey1'
    DECRYPTION BY PASSWORD = 'password'  --prod password used to backup the key
    ENCRYPTION BY PASSWORD = 'newpassword' 
    FORCE  

Stored procedure with an OUTPUT parameter

CREATE procName1 (@intClientId int, @account varchar(50), @intCount int OUTPUT)
AS

SELECT @intCount = COUNT (*) FROM Clients where clientId = @intClientId

SELECT @intCount

GO


--procedure call
Declare @out int
EXECUTE procName1 999, 'ABCSDRF',@out OUTPUT
SELECT @out

SQL server side traces handy commands

select * from ::fn_trace_getinfo(0)    //list all the traces--where property = 5
--stops the tracet of raceid 2 EXEC sp_trace_setstatus 2 ,0

---restart the trace of traceid 2
EXEC sp_trace_setstatus 2 ,1
--deletes the trace of traceid 2 from the server EXEC sp_trace_setstatus 2 ,2