Insert header Record

Question

Post reply

Insert header Record

Ranga846

Valued Member

Points: 64
More actions
November 10, 2004 at 12:39 pm

#61367

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
&nbsp
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
&nbsp
  WHEN a.AgencyNumber = 2   THEN CONVERT(NUMERIC(3,0),850
&nbsp
  WHEN a.AgencyNumber = 130
  OR   a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
&nbsp
  ELSE CONVERT(NUMERIC(3,0),NULL
&nbsp
  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
&nbsp + ' 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)
&nbsp

---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
&nbsp + ' 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
&nbsp
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
&nbsp
  WHEN a.AgencyNumber = 2   THEN CONVERT(NUMERIC(3,0),850
&nbsp
  WHEN a.AgencyNumber = 130
  OR   a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
&nbsp
  ELSE CONVERT(NUMERIC(3,0),NULL
&nbsp
  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
&nbsp + ' 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)
&nbsp

---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
&nbsp + ' 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
&nbsp
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
&nbsp
  WHEN a.AgencyNumber = 2   THEN CONVERT(NUMERIC(3,0),850
&nbsp
  WHEN a.AgencyNumber = 130
  OR   a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
&nbsp
  ELSE CONVERT(NUMERIC(3,0),NULL
&nbsp
  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
&nbsp + ' 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)
&nbsp

---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
&nbsp + ' 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
&nbsp
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
&nbsp
  WHEN a.AgencyNumber = 2   THEN CONVERT(NUMERIC(3,0),850
&nbsp
  WHEN a.AgencyNumber = 130
  OR   a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
&nbsp
  ELSE CONVERT(NUMERIC(3,0),NULL
&nbsp
  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
&nbsp + ' 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)
&nbsp

---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
&nbsp + ' 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

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

raym SSCrazy Points: 2213 More actions · Answer 3

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..

raym SSCrazy Points: 2213 More actions · Answer 4

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

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