Insert header Record
-
l've got this procedure that l wrote. l have three problems. It runs for too long on a small record set.l suspect my error handling technique is the cause. Whats the best way of applying error handling and trap the records with errors and inset them in an exceptions table.
The other thing is how do l bcp the file to a specific directory and insert the header reocord in the exported file. l've tried xp_cmdshell with the echo statement no luck please assist.
IF Object_Id('prcInsertNewMatters') Is Not Null
DROP PROC prInsertNewMatters
GO
CREATE PROCEDURE prcInsertNewMatters
AS
SET NOCOUNT ON
/*------------------------------------------------------------------------------------*
| Name: dbo.prcInsertNewMatters |
| Author: Raymond M |
| Date: 09-11-2004 |
|-------------------------------------------------------------------------------------|
| Purpose: Insert CI Daily Newmatters |
| Input Parameters: None |
|-------------------------------------------------------------------------------------|
| Narration : This Procedure populates tbNewmatters with new matters on a daily basis|
| Writes Into tbLoadAudit the Table Name,Rows inserted & the Server Name |
| |
| Modifications: 09-11-2004 - Raymond M |
*-------------------------------------------------------------------------------------*/
DECLARE @IntErrorCode INT
SELECT @IntErrorCode = @@ERROR
DECLARE @ROWS INT
DECLARE @TotalBalance NUMERIC(13,2)
---Declare the variables to contain the BCP command
DECLARE @FileName VARCHAR(50),
@bcpCommand VARCHAR(2000)
DECLARE @Today VARCHAR(10)
SET @Today = REPLACE(CONVERT(CHAR(10),'2004-11-05',120 ),'-','/')
BEGIN TRANSACTION
IF @IntErrorCode = 0
BEGIN
TRUNCATE TABLE tbNewMatters
--insert new matters
INSERT INTO tbNewMatters
(
CustomerReference
,AccountReference
,OverDueBal
,TransactionDate
,Currency
,[Language]
,AttorneyCurrent
,ReasonCode
,TakeonPrevReasonCode
,InstalmetAmount
)
SELECT
DISTINCT
/** Check if the Ci client has an ABIL ClientNo & Populate the Field
Otherwise generate one from custcode
**/
CASE
WHEN c.IdentificationNumber IS NULL
THEN SUBSTRING(CONVERT(VARCHAR(10),a.CUSTCODE),3,5
 
ELSE c.CLIENTNUMBER
END
AS CustomerReference
,CONVERT(VARCHAR(15) , a.CustCode ) AS AccountReference
,CONVERT(NUMERIC(13,2) , a.Arrears ) AS OverDueBal
/** To get Date Format of YYYY/MM/DD **/
,REPLACE(
CONVERT(
CHAR(10),a.AccountOpenDate,120 ),'-','/'
) AS TransactionDate
,CONVERT(VARCHAR(3) ,'ZAR' ) AS Currency
,CONVERT(VARCHAR(3) ,'EN' ) AS Language
,LTRIM(LTRIM(CONVERT(CHAR(4),NULL))) AS AttorneyCurrent
/** Translate Flags to ABIL Flags **/
,CASE
WHEN a.AgencyNumber = 1 THEN CONVERT(NUMERIC(3,0),143
 
WHEN a.AgencyNumber = 2 THEN CONVERT(NUMERIC(3,0),850
 
WHEN a.AgencyNumber = 130
OR a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
 
ELSE CONVERT(NUMERIC(3,0),NULL
 
END AS ReasonCode
,CONVERT(NUMERIC(3,0) ,Null ) AS TakeonPrevReasonCode
,CONVERT(NUMERIC(13,2) ,a.Instalments ) AS InstalmentAmount
FROM
tbDevD2TNewClient a (NOLOCK) LEFT OUTER JOIN
tbClient c (NOLOCK)
ON a.IDNumber = c.IdentificationNumber
WHERE
/** Format Date into the required yyyy/mm/dd **/
REPLACE(
CONVERT(CHAR(10),a.DateReceived,120 ),'-','/') = @Today
AND a.AgencyNumber <> 0
/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
/** Update The ClientNumber.Add 6000000 to the 5 character
CustomerReference to Generate the ABClientNumber
**/
UPDATE tbNewMatters
SET CustomerReference = CONVERT(INT,CustomerReference) + 6000000
WHERE LEN(CustomerReference) <= 5
---Get The Rowcount to Create an entry for the header
SET @ROWS = @@ROWCOUNT
PRINT '<<<<<<< Procedure Completed SuccessFully>>>>> ' + ' ' + CONVERT(VARCHAR,GETDATE(),111) + ' '+
CONVERT(VARCHAR,@ROWS
 
+ ' Rows Inserted'---Calculate the OverdueBalance to Create an entry for the header
SET @TotalBalance = (SELECT SUM(OverDueBal) FROM tbNewmatters)
SELECT @IntErrorCode = @@ERROR
END
--Create the Temp Table to Contain The HeaderDetails
IF OBJECT_ID('HeaderDetails') IS NOT NULL
DROP TABLE HeaderDetails
CREATE TABLE HeaderDetails
(
HeaderDetails VARCHAR(8000)
 
---Insert Header Details
INSERT INTO HeaderDetails
SELECT
CONVERT(VARCHAR,GETDATE(), 111 )
+ ','+ CONVERT(VARCHAR ,@ROWS )
+ ','+ CONVERT(VARCHAR ,@TOTALBALANCE )
PRINT '<<<<< Procedure header Details inserted SuccessFully >>>' + ','+ ' '+ CONVERT(VARCHAR,@ROWS)
+ 'Rows Inserted Into Header Details File'+ ','
+ CONVERT(VARCHAR,@TOTALBALANCE
 
+ ' OverdueBalance'/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
--Move Files to Arat\c:\Dir\*.txt" " \\Fungi\mapped network Drive\dir\archive
EXEC Master..xp_cmdShell 'MOVE "\\Arat\c:\Dir\*.txt" "\\Fungi\C:\Dir\Archive\"'
--Delete Old files
EXEC Master..xp_cmdShell 'DEL "\\Arat\c:\Load\dir\*.txt"'
--Set the Format for the output file
SET @FileName =
REPLACE('c:\CLT__'+CONVERT(char(10),GETDATE(),120)+'.txt','/','-')
--Generate the Bcp Command
SET @bcpCommand ='bcp "SELECT * FROM Project_CStage..tbNewMatters" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P sa -c'
EXEC master..xp_cmdshell @bcpCommand
PRINT 'File Extracted to C:\Tallyman Daily Loads ' + ' ' + CONVERT(VARCHAR,GETDATE(),111)
IF @IntErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @IntErrorCode
Hi
Need some insight. Need fresh ideas
How do l bcp the data out to a specific location ie c:\DailyFiles\
How do l insert the header record into exported textfile ???? Tried xp_cmdshell with echo had no luck
is this the best way to handle errors....? How do l insert failed records into an exception table
Move my files from local server to another machine. My move and del statements not working well
IF Object_Id('prcInsertNewMatters') Is Not Null
DROP PROC prInsertNewMatters
GO
CREATE PROCEDURE prcInsertNewMatters
AS
SET NOCOUNT ON
/*------------------------------------------------------------------------------------*
| Name: dbo.prcInsertNewMatters |
| Author: Raymond Mahlangu |
| Date: 09-11-2004 |
|-------------------------------------------------------------------------------------|
| Purpose: Insert CI Daily Newmatters |
| Input Parameters: None |
|-------------------------------------------------------------------------------------|
| Narration : This Procedure populates tbNewmatters with new matters on a daily basis|
| Writes Into tbLoadAudit the Table Name,Rows inserted & the Server Name |
| |
| Modifications: 09-11-2004 - Raymond Mahlangu (Perfromance Tuning) |
*-------------------------------------------------------------------------------------*/
DECLARE @IntErrorCode int
Select @IntErrorCode = @@error
DECLARE @ROWS INT
DECLARE @TotalBalance Numeric(13,2)
DECLARE @Today VARCHAR(10)
SET @Today = REPLACE(CONVERT(CHAR(10),'2004-11-05',120 ),'-','/')
BEGIN TRANSACTION
IF @IntErrorCode = 0
BEGIN
TRUNCATE TABLE tbNewMatters
--insert new matters
INSERT INTO tbNewMatters
(
CustomerReference
,AccountReference
,OverDueBal
,TransactionDate
,Currency
,[Language]
,AttorneyCurrent
,ReasonCode
,TakeonPrevReasonCode
,InstalmetAmount
)
SELECT
DISTINCT
/** Check if the Ci client has an ABIL ClientNo & Populate the Field
Otherwise generate one from custcode
**/
CASE
WHEN c.IdentificationNumber IS NULL
THEN SUBSTRING(CONVERT(VARCHAR(10),a.CUSTCODE),3,5
 
ELSE c.CLIENTNUMBER
END
AS CustomerReference
,CONVERT(VARCHAR(15) , a.CustCode ) AS AccountReference
,CONVERT(NUMERIC(13,2) , a.Arrears ) AS OverDueBal
/** To get Date Format of YYYY/MM/DD **/
,REPLACE(
CONVERT(
CHAR(10),a.AccountOpenDate,120 ),'-','/'
) AS TransactionDate
,CONVERT(VARCHAR(3) ,'ZAR' ) AS Currency
,CONVERT(VARCHAR(3) ,'EN' ) AS Language
,LTRIM(LTRIM(CONVERT(CHAR(4),NULL))) AS AttorneyCurrent
/** Translate Flags to ABIL Flags **/
,CASE
WHEN a.AgencyNumber = 1 THEN CONVERT(NUMERIC(3,0),143
 
WHEN a.AgencyNumber = 2 THEN CONVERT(NUMERIC(3,0),850
 
WHEN a.AgencyNumber = 130
OR a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
 
ELSE CONVERT(NUMERIC(3,0),NULL
 
END AS ReasonCode
,CONVERT(NUMERIC(3,0) ,Null ) AS TakeonPrevReasonCode
,CONVERT(NUMERIC(13,2) ,a.Instalments ) AS InstalmentAmount
FROM
tbDevD2TNewClient a (NOLOCK) LEFT OUTER JOIN
tbClient c (NOLOCK)
ON a.IDNumber = c.IdentificationNumber
WHERE
/** Format Date into the required yyyy/mm/dd **/
REPLACE(
CONVERT(CHAR(10),a.DateReceived,120 ),'-','/') = @Today
AND a.AgencyNumber <> 0
/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
/** Update The ClientNumber.Add 6000000 to the 5 character
CustomerReference to Generate the ABClientNumber
**/
UPDATE tbNewMatters
SET CustomerReference = CONVERT(INT,CustomerReference) + 6000000
WHERE LEN(CustomerReference) <= 5
---Get The Rowcount to Create an entry for the header
SET @ROWS = @@ROWCOUNT
PRINT '<<<<<<< Procedure Completed SuccessFully>>>>> ' + ' ' + CONVERT(VARCHAR,GETDATE(),111) + ' '+
CONVERT(VARCHAR,@ROWS
 
+ ' Rows Inserted'---Calculate the OverdueBalance to Create an entry for the header
SET @TotalBalance = (SELECT SUM(OverDueBal) FROM tbNewmatters)
SELECT @IntErrorCode = @@ERROR
END
--Create the Temp Table to Contain The HeaderDetails
IF OBJECT_ID('HeaderDetails') IS NOT NULL
DROP TABLE HeaderDetails
CREATE TABLE HeaderDetails
(
HeaderDetails VARCHAR(8000)
 
---Insert Header Details
INSERT INTO HeaderDetails
SELECT
CONVERT(VARCHAR,GETDATE(), 111 )
+ ','+ CONVERT(VARCHAR ,@ROWS )
+ ','+ CONVERT(VARCHAR ,@TOTALBALANCE )
PRINT '<<<<< Procedure header Details inserted SuccessFully >>>' + ','+ ' '+ CONVERT(VARCHAR,@ROWS)
+ 'Rows Inserted Into Header Details File'+ ','
+ CONVERT(VARCHAR,@TOTALBALANCE
 
+ ' OverdueBalance'/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
--Move Files to Arat\c:\Dir\*.txt" " \\Fungi\mapped network Drive\dir\archive
EXEC Master..xp_cmdShell 'MOVE "\\Arat\c:\Dir\*.txt" "\\Fungi\C:\Dir\Archive\"'
--Delete Old files
EXEC Master..xp_cmdShell 'DEL "\\Arat\c:\Load\dir\*.txt"'
---BCP the Data to a textfile and concatenate the Date and extension
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
--Set the Format for the output file
SET @FileName = REPLACE('c:\CLT__'+CONVERT(char(10),GETDATE(),120)+'.txt','/','-')
--Generate the Bcp Command
SET @bcpCommand = 'bcp "SELECT * FROM Project_CStage..tbNewMatters" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P sa -c'
EXEC master..xp_cmdshell @bcpCommand
PRINT 'File Extracted to C:\Tallyman Daily Loads ' + ' ' + CONVERT(VARCHAR,GETDATE(),111)
IF @IntErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @IntErrorCode
Hi
Need some insight. Need fresh ideas
How do l bcp the data out to a specific location ie c:\DailyFiles\
How do l insert the header record into exported textfile ???? Tried xp_cmdshell with echo had no luck
is this the best way to handle errors....? How do l insert failed records into an exception table
Move my files from local server to another machine. My move and del statements not working well
IF Object_Id('prcInsertNewMatters') Is Not Null
DROP PROC prInsertNewMatters
GO
CREATE PROCEDURE prcInsertNewMatters
AS
SET NOCOUNT ON
/*------------------------------------------------------------------------------------*
| Name: dbo.prcInsertNewMatters |
| Author: Raymond Mahlangu |
| Date: 09-11-2004 |
|-------------------------------------------------------------------------------------|
| Purpose: Insert CI Daily Newmatters |
| Input Parameters: None |
|-------------------------------------------------------------------------------------|
| Narration : This Procedure populates tbNewmatters with new matters on a daily basis|
| Writes Into tbLoadAudit the Table Name,Rows inserted & the Server Name |
| |
| Modifications: 09-11-2004 - Raymond Mahlangu (Perfromance Tuning) |
*-------------------------------------------------------------------------------------*/
DECLARE @IntErrorCode int
Select @IntErrorCode = @@error
DECLARE @ROWS INT
DECLARE @TotalBalance Numeric(13,2)
DECLARE @Today VARCHAR(10)
SET @Today = REPLACE(CONVERT(CHAR(10),'2004-11-05',120 ),'-','/')
BEGIN TRANSACTION
IF @IntErrorCode = 0
BEGIN
TRUNCATE TABLE tbNewMatters
--insert new matters
INSERT INTO tbNewMatters
(
CustomerReference
,AccountReference
,OverDueBal
,TransactionDate
,Currency
,[Language]
,AttorneyCurrent
,ReasonCode
,TakeonPrevReasonCode
,InstalmetAmount
)
SELECT
DISTINCT
/** Check if the Ci client has an ABIL ClientNo & Populate the Field
Otherwise generate one from custcode
**/
CASE
WHEN c.IdentificationNumber IS NULL
THEN SUBSTRING(CONVERT(VARCHAR(10),a.CUSTCODE),3,5
 
ELSE c.CLIENTNUMBER
END
AS CustomerReference
,CONVERT(VARCHAR(15) , a.CustCode ) AS AccountReference
,CONVERT(NUMERIC(13,2) , a.Arrears ) AS OverDueBal
/** To get Date Format of YYYY/MM/DD **/
,REPLACE(
CONVERT(
CHAR(10),a.AccountOpenDate,120 ),'-','/'
) AS TransactionDate
,CONVERT(VARCHAR(3) ,'ZAR' ) AS Currency
,CONVERT(VARCHAR(3) ,'EN' ) AS Language
,LTRIM(LTRIM(CONVERT(CHAR(4),NULL))) AS AttorneyCurrent
/** Translate Flags to ABIL Flags **/
,CASE
WHEN a.AgencyNumber = 1 THEN CONVERT(NUMERIC(3,0),143
 
WHEN a.AgencyNumber = 2 THEN CONVERT(NUMERIC(3,0),850
 
WHEN a.AgencyNumber = 130
OR a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
 
ELSE CONVERT(NUMERIC(3,0),NULL
 
END AS ReasonCode
,CONVERT(NUMERIC(3,0) ,Null ) AS TakeonPrevReasonCode
,CONVERT(NUMERIC(13,2) ,a.Instalments ) AS InstalmentAmount
FROM
tbDevD2TNewClient a (NOLOCK) LEFT OUTER JOIN
tbClient c (NOLOCK)
ON a.IDNumber = c.IdentificationNumber
WHERE
/** Format Date into the required yyyy/mm/dd **/
REPLACE(
CONVERT(CHAR(10),a.DateReceived,120 ),'-','/') = @Today
AND a.AgencyNumber <> 0
/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
/** Update The ClientNumber.Add 6000000 to the 5 character
CustomerReference to Generate the ABClientNumber
**/
UPDATE tbNewMatters
SET CustomerReference = CONVERT(INT,CustomerReference) + 6000000
WHERE LEN(CustomerReference) <= 5
---Get The Rowcount to Create an entry for the header
SET @ROWS = @@ROWCOUNT
PRINT '<<<<<<< Procedure Completed SuccessFully>>>>> ' + ' ' + CONVERT(VARCHAR,GETDATE(),111) + ' '+
CONVERT(VARCHAR,@ROWS
 
+ ' Rows Inserted'---Calculate the OverdueBalance to Create an entry for the header
SET @TotalBalance = (SELECT SUM(OverDueBal) FROM tbNewmatters)
SELECT @IntErrorCode = @@ERROR
END
--Create the Temp Table to Contain The HeaderDetails
IF OBJECT_ID('HeaderDetails') IS NOT NULL
DROP TABLE HeaderDetails
CREATE TABLE HeaderDetails
(
HeaderDetails VARCHAR(8000)
 
---Insert Header Details
INSERT INTO HeaderDetails
SELECT
CONVERT(VARCHAR,GETDATE(), 111 )
+ ','+ CONVERT(VARCHAR ,@ROWS )
+ ','+ CONVERT(VARCHAR ,@TOTALBALANCE )
PRINT '<<<<< Procedure header Details inserted SuccessFully >>>' + ','+ ' '+ CONVERT(VARCHAR,@ROWS)
+ 'Rows Inserted Into Header Details File'+ ','
+ CONVERT(VARCHAR,@TOTALBALANCE
 
+ ' OverdueBalance'/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
--Move Files to Arat\c:\Dir\*.txt" " \\Fungi\mapped network Drive\dir\archive
EXEC Master..xp_cmdShell 'MOVE "\\Arat\c:\Dir\*.txt" "\\Fungi\C:\Dir\Archive\"'
--Delete Old files
EXEC Master..xp_cmdShell 'DEL "\\Arat\c:\Load\dir\*.txt"'
---BCP the Data to a textfile and concatenate the Date and extension
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
--Set the Format for the output file
SET @FileName = REPLACE('c:\CLT__'+CONVERT(char(10),GETDATE(),120)+'.txt','/','-')
--Generate the Bcp Command
SET @bcpCommand = 'bcp "SELECT * FROM Project_CStage..tbNewMatters" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P sa -c'
EXEC master..xp_cmdshell @bcpCommand
PRINT 'File Extracted to C:\Tallyman Daily Loads ' + ' ' + CONVERT(VARCHAR,GETDATE(),111)
IF @IntErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @IntErrorCode
Hi
Need some insight. Need fresh ideas
How do l bcp the data out to a specific location ie c:\DailyFiles\
How do l insert the header record into exported textfile ???? Tried xp_cmdshell with echo had no luck
is this the best way to handle errors....? How do l insert failed records into an exception table
Move my files from local server to another machine. My move and del statements not working well
IF Object_Id('prcInsertNewMatters') Is Not Null
DROP PROC prInsertNewMatters
GO
CREATE PROCEDURE prcInsertNewMatters
AS
SET NOCOUNT ON
/*------------------------------------------------------------------------------------*
| Name: dbo.prcInsertNewMatters |
| Author: Raymond Mahlangu |
| Date: 09-11-2004 |
|-------------------------------------------------------------------------------------|
| Purpose: Insert CI Daily Newmatters |
| Input Parameters: None |
|-------------------------------------------------------------------------------------|
| Narration : This Procedure populates tbNewmatters with new matters on a daily basis|
| Writes Into tbLoadAudit the Table Name,Rows inserted & the Server Name |
| |
| Modifications: 09-11-2004 - Raymond Mahlangu (Perfromance Tuning) |
*-------------------------------------------------------------------------------------*/
DECLARE @IntErrorCode int
Select @IntErrorCode = @@error
DECLARE @ROWS INT
DECLARE @TotalBalance Numeric(13,2)
DECLARE @Today VARCHAR(10)
SET @Today = REPLACE(CONVERT(CHAR(10),'2004-11-05',120 ),'-','/')
BEGIN TRANSACTION
IF @IntErrorCode = 0
BEGIN
TRUNCATE TABLE tbNewMatters
--insert new matters
INSERT INTO tbNewMatters
(
CustomerReference
,AccountReference
,OverDueBal
,TransactionDate
,Currency
,[Language]
,AttorneyCurrent
,ReasonCode
,TakeonPrevReasonCode
,InstalmetAmount
)
SELECT
DISTINCT
/** Check if the Ci client has an ABIL ClientNo & Populate the Field
Otherwise generate one from custcode
**/
CASE
WHEN c.IdentificationNumber IS NULL
THEN SUBSTRING(CONVERT(VARCHAR(10),a.CUSTCODE),3,5
 
ELSE c.CLIENTNUMBER
END
AS CustomerReference
,CONVERT(VARCHAR(15) , a.CustCode ) AS AccountReference
,CONVERT(NUMERIC(13,2) , a.Arrears ) AS OverDueBal
/** To get Date Format of YYYY/MM/DD **/
,REPLACE(
CONVERT(
CHAR(10),a.AccountOpenDate,120 ),'-','/'
) AS TransactionDate
,CONVERT(VARCHAR(3) ,'ZAR' ) AS Currency
,CONVERT(VARCHAR(3) ,'EN' ) AS Language
,LTRIM(LTRIM(CONVERT(CHAR(4),NULL))) AS AttorneyCurrent
/** Translate Flags to ABIL Flags **/
,CASE
WHEN a.AgencyNumber = 1 THEN CONVERT(NUMERIC(3,0),143
 
WHEN a.AgencyNumber = 2 THEN CONVERT(NUMERIC(3,0),850
 
WHEN a.AgencyNumber = 130
OR a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
 
ELSE CONVERT(NUMERIC(3,0),NULL
 
END AS ReasonCode
,CONVERT(NUMERIC(3,0) ,Null ) AS TakeonPrevReasonCode
,CONVERT(NUMERIC(13,2) ,a.Instalments ) AS InstalmentAmount
FROM
tbDevD2TNewClient a (NOLOCK) LEFT OUTER JOIN
tbClient c (NOLOCK)
ON a.IDNumber = c.IdentificationNumber
WHERE
/** Format Date into the required yyyy/mm/dd **/
REPLACE(
CONVERT(CHAR(10),a.DateReceived,120 ),'-','/') = @Today
AND a.AgencyNumber <> 0
/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
/** Update The ClientNumber.Add 6000000 to the 5 character
CustomerReference to Generate the ABClientNumber
**/
UPDATE tbNewMatters
SET CustomerReference = CONVERT(INT,CustomerReference) + 6000000
WHERE LEN(CustomerReference) <= 5
---Get The Rowcount to Create an entry for the header
SET @ROWS = @@ROWCOUNT
PRINT '<<<<<<< Procedure Completed SuccessFully>>>>> ' + ' ' + CONVERT(VARCHAR,GETDATE(),111) + ' '+
CONVERT(VARCHAR,@ROWS
 
+ ' Rows Inserted'---Calculate the OverdueBalance to Create an entry for the header
SET @TotalBalance = (SELECT SUM(OverDueBal) FROM tbNewmatters)
SELECT @IntErrorCode = @@ERROR
END
--Create the Temp Table to Contain The HeaderDetails
IF OBJECT_ID('HeaderDetails') IS NOT NULL
DROP TABLE HeaderDetails
CREATE TABLE HeaderDetails
(
HeaderDetails VARCHAR(8000)
 
---Insert Header Details
INSERT INTO HeaderDetails
SELECT
CONVERT(VARCHAR,GETDATE(), 111 )
+ ','+ CONVERT(VARCHAR ,@ROWS )
+ ','+ CONVERT(VARCHAR ,@TOTALBALANCE )
PRINT '<<<<< Procedure header Details inserted SuccessFully >>>' + ','+ ' '+ CONVERT(VARCHAR,@ROWS)
+ 'Rows Inserted Into Header Details File'+ ','
+ CONVERT(VARCHAR,@TOTALBALANCE
 
+ ' OverdueBalance'/** Short Delay Before Next Statement Executes **/
WAITFOR DELAY '00:00:05'
--Move Files to Arat\c:\Dir\*.txt" " \\Fungi\mapped network Drive\dir\archive
EXEC Master..xp_cmdShell 'MOVE "\\Arat\c:\Dir\*.txt" "\\Fungi\C:\Dir\Archive\"'
--Delete Old files
EXEC Master..xp_cmdShell 'DEL "\\Arat\c:\Load\dir\*.txt"'
---BCP the Data to a textfile and concatenate the Date and extension
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
--Set the Format for the output file
SET @FileName = REPLACE('c:\CLT__'+CONVERT(char(10),GETDATE(),120)+'.txt','/','-')
--Generate the Bcp Command
SET @bcpCommand = 'bcp "SELECT * FROM Project_CStage..tbNewMatters" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P sa -c'
EXEC master..xp_cmdshell @bcpCommand
PRINT 'File Extracted to C:\Tallyman Daily Loads ' + ' ' + CONVERT(VARCHAR,GETDATE(),111)
IF @IntErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @IntErrorCode
Ranga
-
On your error handling question, you might want to read this:
http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here's one way (of several) how to merge two files (ie header and data) into one...
-- generate headerfile
exec master..xp_cmdshell 'echo Tally of Sysobjects >> c:\header.txt'
-- generate datafile
exec master..xp_cmdshell 'bcp "select count(*) from master..sysobjects" queryout c:\data.txt -c -T'
-- concatenate by using copy commands + operator
exec master..xp_cmdshell 'copy c:\header.txt + c:\data.txt c:\finalOutput.txt'
/Kenneth
-
This works but problem is that it generates a '|' like character when you merge the two files.How do l get rid of it. The Data file looks fine bu the header record file si the one that carries the funny character..
-
Tally of Sysobjects
1267
This is what the merged file looks like. That funny character causes errors when you try and re import that file into sql
-
What kind of errors? The funny char is there alright, but I could bcp it into a table without problems or errors.
Anyways, to get rid of it, you can use the binary switch on the copy command.
-- concatenate by using copy commands + operator
exec master..xp_cmdshell 'copy /b c:\header.txt + c:\data.txt c:\finalOutput.txt'
That should fix it.
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply