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
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Friday, November 30, 2018
Friday, December 1, 2017
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
<?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
Tuesday, January 10, 2017
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
"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
Labels:
backup key,
restore database,
restore key,
sql,
SSIS error,
SSISDB,
SSISDB master KEY
Thursday, October 16, 2014
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
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
Tuesday, March 12, 2013
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
--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
Labels:
Administration,
serverside trace,
sql,
sql profiler,
trace
Subscribe to:
Posts (Atom)