March 1, 2012 at 5:24 am
Hi guys
I have a procedure which extracts information to a text file.
But one issue I have is, records being extracted to the text file, from the staging table, need to indicate what type of service is required, either Trace or Collect.
I was just wondering how to input this word into the extract.
Thank You
P.S. Here is my procedure.
USE [TM37_1_2_ONLINE]
GO
/****** Object: StoredProcedure [dbo].[ACCOUNTSEXTRACT_OUT] Script Date: 09/02/2009 15:27:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ACCOUNTSEXTRACT_OUT] AS
DECLARE @v_CommandLineVARCHAR(1000),
@v_CommandLine2VARCHAR(1000),
@v_ErrorINT,
@v_ErrorMessageVARCHAR(250),
@v_ErrorTextVARCHAR(2000),
@v_FiledVARCHAR(1),
@v_FileNameVARCHAR(50),
@v_DirectoryVARCHAR(500),
@forenameNVARCHAR(60),
@v_CountINT,
@rowcountINT,
@surnameNVARCHAR(60),
@idint,
@rowcountedint,
@AccountsReferencenvarchar(30),
@TelephoneNumberbigint,
@Balancenumeric (10,2),
@Addressnvarchar(50),
@Readyfordcanvarchar(5),
@Goneawayflagdatetime,
@ProcessDateDatetime,
@Statuscodenvarchar(30),
@StatusReasonnvarchar(30),
@Titlenvarchar(5)
SET @v_Directory = 'C:\DCA Challenge'
SET @v_Count = 0
SET @v_FileName = 'DCACustomers'+(SELECT SUBSTRING(CONVERT(VARCHAR(24), GETDATE(),120),1,4))
+(SELECT SUBSTRING(CONVERT(VARCHAR(24), GETDATE(),120),6,2))
+(SELECT SUBSTRING(CONVERT(VARCHAR(24), GETDATE(),120),9,2))
+'.txt'
SET @v_CommandLine = 'echo '
+ ''+ (SELECT SUBSTRING(CONVERT(VARCHAR(24), GETDATE(),120),1,4))
+ ''+ (SELECT SUBSTRING(CONVERT(VARCHAR(24), GETDATE(),120),6,2))
+ ''+ (SELECT SUBSTRING(CONVERT(VARCHAR(24), GETDATE(),120),9,2))
--write header information to rapid interface file
SET @v_CommandLine = @v_CommandLine + ' >> "'+ @v_Directory + @v_FileName + '"'
EXEC @v_Error = xp_cmdshell @v_CommandLine, NO_OUTPUT
IF (@v_Error != 0) BEGIN
SET @v_ErrorMessage = 'Error writing to file: ' + ERROR_MESSAGE()
RAISERROR (@v_ErrorMessage, 16, 1)
END
DECLARE TCur CURSOR FOR
SELECT
AccountsReference,
Title,
Forename,
Surname,
Address,
TelephoneNumber,
Balance
from
DCAEXTRACT_OUT;
BEGIN TRY
-- run cursor and feed data into file
OPEN TCur
FETCH NEXT FROM TCur INTO
@AccountsReference,
@Title,
@Forename,
@Surname,
@Address,
@TelephoneNumber,
@ReadyForDCA
WHILE (@@fetch_status = 0) BEGIN
SET @v_CommandLine = 'echo '+ ISNULL(CAST(@AccountsReference AS nVARCHAR(30)),'')
+','+ ISNULL(@Title,'')
+','+ ISNULL(@Forename,'')
+','+ ISNULL(@Surname,'')
+','+ ISNULL(@Address,'')
+','+ ISNULL(@TelephoneNumber,'')
+','+ ISNULL (@Balance,'')
+','+ ISNULL (@ReadyforDCA,'')
SET @v_CommandLine = @v_CommandLine + '>> "'+@v_Directory+@v_FileName+'"'
EXEC @v_Error = xp_cmdshell @v_CommandLine, NO_OUTPUT
IF (@v_Error != 0) BEGIN
SET @v_ErrorMessage = 'Error writing to file: ' + ERROR_MESSAGE()
PRINT '@v_ErrorMessage: ' + @v_ErrorMessage
RAISERROR (@v_ErrorMessage, 16, 1)
END
SET @v_Count = @v_Count + 1
FETCH NEXT FROM TCur INTO
@AccountsReference,
@Title,
@Forename,
@Surname,
@Address,
@TelephoneNumber,
@ReadyForDCA
END -- cursor loop end
END TRY BEGIN CATCH
SET @v_ErrorText = 'after loop :Error writing to file: ' + ERROR_MESSAGE()
PRINT '@v_ErrorText: '+ @v_ErrorText
RAISERROR (@v_ErrorText, 16,1)
END CATCH
CLOSE TCur
DEALLOCATE TCur
SET @v_CommandLine = 'echo '+ ISNULL(CAST(@v_Count AS INT),'')
SET @v_CommandLine = @v_CommandLine + ' >> "'+ @v_Directory + @v_FileName + '"'
EXEC @v_Error = xp_cmdshell @v_CommandLine, NO_OUTPUT
IF (@v_Error != 0) BEGIN
SET @v_ErrorMessage = 'Error writing to file: ' + ERROR_MESSAGE()
RAISERROR (@v_ErrorMessage, 16, 1)
END
March 1, 2012 at 5:40 am
Yuk. Don't like that proc at all. Looks like it was written by a procedural programmer unfamiliar with T-SQL concepts like 'sets' 🙂
Have you thought about writing your data to a temp table and then using BCP to output the contents in a single step? You should always be thinking like this with T-SQL, when presented with apparent row-by-row solutions.
March 1, 2012 at 6:05 am
I have used bcp before, and it is pretty simple to extract to a file,
However this is a code that was assigned to me for a specific task to use.
Thats why I asked. Other than that I would have used a simple bcp command.
March 1, 2012 at 4:09 pm
If your table already have a field with the type of service, only need to add it to the cursor and to your output.
But If you have to calculate the type of service based on the value of other field, you can do something like this:
WHILE (@@fetch_status = 0)
BEGIN
SET @TypeOfService = CASE WHEN @ReadyforDCA='Y' THEN 'TRACE' ELSE 'COLLECT' END
SET @v_CommandLine = 'echo '+ ISNULL(CAST(@AccountsReference AS nVARCHAR(30)),'')
+','+ ISNULL(@Title,'')
+','+ ISNULL(@Forename,'')
+','+ ISNULL(@Surname,'')
+','+ ISNULL(@Address,'')
+','+ ISNULL(@TelephoneNumber,'')
+','+ ISNULL (@Balance,'')
+','+ ISNULL (@ReadyforDCA,'')
+','+ @TypeOfService
Also I have realized that in the declaration of the cursor you don't include the field [ReadyForDCA], but in the fetch yo do. Is it working right?
DECLARE TCur CURSOR FOR
SELECT
AccountsReference,
Title,
Forename,
Surname,
Address,
TelephoneNumber,
Balance
---
OPEN TCur
FETCH NEXT FROM TCur INTO
@AccountsReference,
@Title,
@Forename,
@Surname,
@Address,
@TelephoneNumber,
@Balance,
@ReadyForDCA
Best regards.
March 1, 2012 at 7:06 pm
ram_kamboz2k7 (3/1/2012)
I have used bcp before, and it is pretty simple to extract to a file,However this is a code that was assigned to me for a specific task to use.
Thats why I asked. Other than that I would have used a simple bcp command.
Since you're already using xp_CmdShell, I see no reason why it would matter how the job was accomplished. Further, BCP would blow the doors off the cursor method.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply