miércoles, 29 de agosto de 2018

Publishing SSRS

Locally deploy you can Run this script as DBAFire.bat file:

@echo off
::Script Variables
SET LOGFILE="C:\DeploySSRS\RSScripterlog.txt"
SET REPORTSERVER=http://10.153.3.199/ReportServer
SET RS="RS.EXE"
SET TIMEOUT=60
::Clear Log file
IF EXIST %logfile% DEL %logfile%
::Run Scripts
 %rs% -i "C:\DeploySSRS\Commonscript.rss" -s "%REPORTSERVER%" -e Mgmt2010
ECHO Finished Load at TE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%
PAUSE

martes, 29 de mayo de 2018

SQL Comma separated string to rows and columns

DECLARE @Data NVARCHAR(MAX) = 'Jessica Espinosa, Gerente de Nomina, REG. PAT. L0165468104, R.F.C. WKA160429DD8'
DECLARE @Delimiter NVARCHAR(5) = ','
DECLARE @Table TABLE ( Data NVARCHAR(MAX), SequentialOrder INT IDENTITY(1, 1))

DECLARE @TextXml XML;
SELECT @TextXml = CAST('<d>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Data, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '"', '&quot;'), '''', '&apos;'), @Delimiter, '</d><d>') + '</d>' AS XML);

INSERT INTO @Table (Data)
SELECT Data = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(T.split.value('.', 'nvarchar(max)'))), '&amp;', '&'), '&lt;', '<'), '&gt;', '>'), '&quot;', '"'), '&apos;', '''')
FROM @TextXml.nodes('/d') T(Split)

SELECT
(SELECT Data FROM @Table WHERE SequentialOrder=1) [Signature1],
(SELECT Data FROM @Table WHERE SequentialOrder=2) [Signature2],
(SELECT Data FROM @Table WHERE SequentialOrder=3) [Signature3],
(SELECT Data FROM @Table WHERE SequentialOrder=4) [Signature4]

SELECT * FROM @Table