BCP Parameters INPUT: UNCPathAndLogTable - UNC Path And LogTable OF Parameter file
E.DIN Editor • June 9, 2020
Note: Parameters INPUT: UNCPathAndLogTable - UNC Path And LogTable OF Parameter file
RELATED SPROC: spManualRun PROCESSES THE PARAMETERS IN THIS FILE
RELATED SPROC: spManualRun PROCESSES THE PARAMETERS IN THIS FILE
UPDATED: 06/01/2020 TJC created for Automation
TESTING: EXECUTE [myDatabase].dbo.[spParamFileCheck]
@UNCPathAndLogTable=N'\\Server02\FTP\In\InParameters.csv';
@UNCPathAndLogTable=N'\\Server02\FTP\In\InParameters.csv';
RETURN OUTPUT: 0 GOOD RETURN, SSIS JOB CONTINUES
-1 NEUTRAL RETURN, SSIS JOB STOPS
ERROR, SSIS JOB STOPS
***************************************************************************************************/
CREATE PROCEDURE dbo.[spParamFileCheck]( @UNCPathAndLogTable varchar(512) = NULL)
AS
BEGIN
SET NOCOUNT ON;
declare @cmd varchar(8000), @CID INT = 0, @FileRecordCount int = 0, @CurrentDate Datetime = GETDATE(), @ErrorMessage varchar(max);
declare @dir table(FullName varchar(512));
-1 NEUTRAL RETURN, SSIS JOB STOPS
ERROR, SSIS JOB STOPS
***************************************************************************************************/
CREATE PROCEDURE dbo.[spParamFileCheck]( @UNCPathAndLogTable varchar(512) = NULL)
AS
BEGIN
SET NOCOUNT ON;
declare @cmd varchar(8000), @CID INT = 0, @FileRecordCount int = 0, @CurrentDate Datetime = GETDATE(), @ErrorMessage varchar(max);
declare @dir table(FullName varchar(512));
IF (isnull(@UNCPathAndLogTable, '') = '') BEGIN
SET @UNCPathAndLogTable = N'\\Server01\FTP\In\InParameters.csv';
END;
SET @UNCPathAndLogTable = N'\\Server01\FTP\In\InParameters.csv';
END;
BEGIN TRY
SET @cmd='exec xp_cmdshell ''dir
SET @cmd='exec xp_cmdshell ''dir
/b /s''';
INSERT @dir(FullName) EXEC(@cmd);
SELECT @FileRecordCount = COUNT(*) FROM @dir WHERE FullName is not null;
IF (@FileRecordCount = 0) BEGIN
PRINT '-1';
RETURN -1;
END;
SELECT @FileRecordCount = COUNT(*) FROM @dir WHERE FullName is not null;
IF (@FileRecordCount = 0) BEGIN
PRINT '-1';
RETURN -1;
END;
/*** LOGGING THE JOB PARAMETERS ***/
IF (object_id('tempdb..#ParametersFile') is not null) DROP TABLE #ParametersFile;
CREATE TABLE #ParametersFile (Column1 varchar(50), Column2 varchar(50), Column3 varchar(50), Column4 varchar(50));
SET @cmd = 'bulk insert #ParametersFile from
IF (object_id('tempdb..#ParametersFile') is not null) DROP TABLE #ParametersFile;
CREATE TABLE #ParametersFile (Column1 varchar(50), Column2 varchar(50), Column3 varchar(50), Column4 varchar(50));
SET @cmd = 'bulk insert #ParametersFile from
WITH (FIELDTERMINATOR = '','' )';
EXEC(@cmd);
END TRY
BEGIN CATCH /* CATCH ERROR BUT DO NOT RAISERROR, RETURN -1 AS STOP JOB CODE */
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
-- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
EXEC(@cmd);
END TRY
BEGIN CATCH /* CATCH ERROR BUT DO NOT RAISERROR, RETURN -1 AS STOP JOB CODE */
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
-- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
SELECT @ErrorMessage = 'spParamFileCheck: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
PRINT '-1 ' + @ErrorMessage;
RETURN -1;
END CATCH;
PRINT '-1 ' + @ErrorMessage;
RETURN -1;
END CATCH;
BEGIN TRY
--SELECT TOP 1 * FROM #ParametersFile WITH(NOLOCK) WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0;
SELECT @FileRecordCount = COUNT(Column1)
FROM #ParametersFile WITH(NOLOCK)
where RTRIM(ISNULL(Column1, '')) <> ''
and RTRIM(ISNULL(Column2, '')) <> ''
and RTRIM(ISNULL(Column3, '')) <> ''
and isnumeric(Column3) > 0
and isnumeric(Column4) > 0;
--SELECT TOP 1 * FROM #ParametersFile WITH(NOLOCK) WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0;
SELECT @FileRecordCount = COUNT(Column1)
FROM #ParametersFile WITH(NOLOCK)
where RTRIM(ISNULL(Column1, '')) <> ''
and RTRIM(ISNULL(Column2, '')) <> ''
and RTRIM(ISNULL(Column3, '')) <> ''
and isnumeric(Column3) > 0
and isnumeric(Column4) > 0;
IF (@FileRecordCount > 0) BEGIN
SELECT TOP 1 @CID = ce.CID
FROM myDatabase.dbo.ssisInputProcessing AS irc WITH (NOLOCK)
INNER JOIN myOtherDatabase.dbo.CopeceticItems AS ce WITH (NOLOCK) ON irc.Column1 = ce.Column1
AND irc.Column1 in (SELECT TOP 1 Column1 FROM #ParametersFile WITH(NOLOCK)
WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0);
FROM myDatabase.dbo.ssisInputProcessing AS irc WITH (NOLOCK)
INNER JOIN myOtherDatabase.dbo.CopeceticItems AS ce WITH (NOLOCK) ON irc.Column1 = ce.Column1
AND irc.Column1 in (SELECT TOP 1 Column1 FROM #ParametersFile WITH(NOLOCK)
WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0);
IF (ISNULL(@CID, 0) = 0) BEGIN /* REPORT ERROR: INCOMING PARAMETERS FILE HAS INCORRECT DATA TO PROCESS */
RAISERROR ('spParamFileCheck InParameters.csv was found but contains invalid client data. Column1.',15,1)
END;
RAISERROR ('spParamFileCheck InParameters.csv was found but contains invalid client data. Column1.',15,1)
END;
END ELSE BEGIN /* REPORT ERROR: INCOMING PARAMETERS FILE HAS NO DATA TO PROCESS */
RAISERROR ('spParamFileCheck InParameters.csv was found but contains no valid rows to import. missing parameters.',15,1)
END;
RAISERROR ('spParamFileCheck InParameters.csv was found but contains no valid rows to import. missing parameters.',15,1)
END;
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
--VALUES (@CID, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, 0);
--VALUES (@CID, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, 0);
END TRY
BEGIN CATCH
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
-- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
BEGIN CATCH
--INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
-- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
SELECT @ErrorMessage = 'InParameters Bulk Insert: ' + ERROR_MESSAGE() + ' Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
PRINT @ErrorMessage;
RAISERROR (@ErrorMessage,15,1);
RETURN -1;
END CATCH
PRINT '0'
RETURN 0;
PRINT @ErrorMessage;
RAISERROR (@ErrorMessage,15,1);
RETURN -1;
END CATCH
PRINT '0'
RETURN 0;
/* FUTURE: EXPORT THE INCOMING PARAMETERS FILE FROM THE INPUT FOLDER TO THE EXPORT FOLDER */
BEGIN TRY
declare @UNCPathAndFileOut varchar(512) = N'\\Server02\Output\InParameters.csv'
SET @SQL ='bcp "SELECT (Column1, Column2, Column3, CAST(Column4 AS VARCHAR(1)) AS Column4 from #ParametersFile " queryout '
BEGIN TRY
declare @UNCPathAndFileOut varchar(512) = N'\\Server02\Output\InParameters.csv'
SET @SQL ='bcp "SELECT (Column1, Column2, Column3, CAST(Column4 AS VARCHAR(1)) AS Column4 from #ParametersFile " queryout '
-t"|" -c -T ';
EXEC xp_cmdshell @SQL ;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Cmdshell Export: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
print @ErrorMessage;
RAISERROR (@ErrorMessage,15,1);
RETURN;
END CATCH
EXEC xp_cmdshell @SQL ;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Cmdshell Export: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
print @ErrorMessage;
RAISERROR (@ErrorMessage,15,1);
RETURN;
END CATCH
Title: Base64 Encoding With Simple Salt (and decoding)
XPCMD_SHELL Move & Load Data from Directory
***** My Valid Data Monitor **** XML OUT *** HTML EMAIL DELIVERY *****IF OBJECT_ID('tempdb..#tCustomerID') IS NOT NULL DROP TABLE #tCustomerID select distinct mm.CustomerID Into #tCustomerID from myDatabase..myTable (nolock) mm left join myDatabase.dbo.myOtherTable (nolock) mo on mm.refID = mo.id left join myDatabase.dbo.myOtherTableDetail (nolock) mod on mo.id = mod.myOtherTableid and mm.SearchValue = mod.SearchValue where mm.refID is not null and mm.quantity is not null and mod.SearchValue is nullIF Exists (Select 1 From #tCustomerID)BEGIN DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)SET @xml = CAST(( SELECT [CustomerID] AS 'td' FROM #tCustomerID ORDER BY CustomerID FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))SET @body ='
'--SELECT @body exec msdb.dbo.sp_send_dbmail @profile_name = 'dbmailProfile' ,@recipients = 'usergroup@mydomain.com' ,@subject = 'My Valid Data Monitor' ,@body = @body ,@body_format = 'HTML'END
My Valid Data Monitor
| CustomerID |
|---|
The new season is a great reason to make and keep resolutions. Whether it’s eating right or cleaning out the garage, here are some tips for making and keeping resolutions.
There are so many good reasons to communicate with site visitors. Tell them about sales and new products or update them with tips and information.
Write about something you know. If you don’t know much about a specific topic that will interest your readers, invite an expert to write about it.
Turn Up! Not every microphone has the same base volume for transmitting your voice through your computer to whoever or whatever is on the other end of the exchange. Some microphones have a higher volume as compared to others, and some microphones have a volume that is so low that the person on the other […]
The post How to Turn Up Mic Volume in Windows 10 – Appuals.com appeared first on TekPreacher.
--UNIQUE AND RANDOM TRANSACTION NAMES (FOR HIGH VOLUME TRANSACTIONS PER SECOND)DECLARE @randomString VARCHAR(255) = CONVERT(varchar(255), NEWID());SELECT @randomString = replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','') + RIGHT(@randomString,10);PRINT ''+ CONVERT(VARCHAR,LEN(@randomString)) + ' CHARS / OF MAXIMUM 32 CHARS FOR LENGTH OF TRANSACTION NAMES'SELECT @randomStringBEGIN TRANSACTION @randomStringSELECT replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','');SELECT CONVERT(VARCHAR,getdate(),112) + LEFT(REPLACE(CONVERT(VARCHAR,getdate(),114),':',''),6);SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(33),SYSDATETIMEOFFSET(),126), 1, 8), '-', '') + SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126),'T',''),'.',''),':',''),9,DATALENGTH(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126)))COMMIT TRANSACTION @randomStringOUTPUT-- 24 CHARS / OF MAXIMUM 32 CHARS FOR LENGTH OF TRANSACTION NAMES-- 202005141039592610925CAA <- THIS IS THE ONE-- 20200514103959-- 20200514103959-- 202005141039598866412-0500