Create Text File from T-SQL Getting Error

[ssecca]

Below is the code I wish to use to create a text file directly from SQL. It, however keeps giving me the following error...

[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream
[Microsoft][ODBC SQL Server Driver]Function sequence error

This same basic code has worked for me at other sites. Can anyone help find the problem. Any clues would be appriciated.

CREATE PROCEDURE _dsp_ProcessImportData
@import_Name as varchar(25)
AS

--Import data to holding Table
--initiate appropriate DTS Package
SET NOCOUNT ON

delete from dbo._Lexis_Temp_Hold

exec master.dbo.xp_cmdshell
'dtsrun /S H-PARKINT_-2 /E /N Lexis_Import_3'

--Read records from temp Import into cursor
--Process transformations
--Import into _Import_Hold
DECLARE @ClntCode varchar(10)
DECLARE @MatCode varchar(16)
DECLARE @EntryEmplCode varchar(6)
DECLARE @DIEmlpCode varchar(6)
DECLARE @BLEmplCode varchar(6)
DECLARE @AUEmplCode varchar(6)
DECLARE @HardSoft varchar(10)
DECLARE @CostCode varchar(6)
DECLARE @PhaseCode varchar(4)
DECLARE @TaskCode varchar(8)
DECLARE @CurrencyCode varchar(3)
DECLARE @EntryDate varchar(6)
DECLARE @TranDate varchar(6)
DECLARE @HoldDate Datetime
DECLARE @BaseQty float
DECLARE @BillQty float
DECLARE @Amt float
DECLARE @CallInfoReq varchar(30)
DECLARE @CallSourceExt varchar(30)
DECLARE @CallDuration int
DECLARE @CallPhoneNo varchar(30)
DECLARE @Ref varchar(30)
DECLARE @PreBillComments int
DECLARE @NarrText varchar(200)
DECLARE @BatchNo int
DECLARE @Batch_Date datetime

--REad from
DECLARE @Record_Type varchar(50)
DECLARE @GroupID int
DECLARE @StartDate char(8)
DECLARE @EndDate char(8)
DECLARE @Account varchar(50)
DECLARE @Client varchar(50)
DECLARE @User_id varchar(50)
DECLARE @UserName varchar(50)
--50
DECLARE @Service varchar(50)
DECLARE @TypeOfCharge varchar(50)
DECLARE @Quantity int
DECLARE @QuanityType varchar(50)
DECLARE @ContractGrossAmount decimal
DECLARE @ContractAdj decimal
DECLARE @ContractNetAmt decimal
DECLARE @OverTheCap decimal
DECLARE @TransGrossAmt decimal
DECLARE @TransDiscount decimal
DECLARE @TransNetAmt decimal
DECLARE @TotalOnLineCharges decimal
DECLARE @OtherCharges decimal
DECLARE @CurrentPeriodCredits decimal
DECLARE @Taxes decimal
DECLARE @TotalCharges decimal

DECLARE @cnt as int
set @cnt = 0

--Get Batch No & Date
set @Batch_Date = getdate()
exec @batchNo =_DBH_getnext_FromSequencer @import_Name,1

DECLARE OneRec cursor for
select *
from _Lexis_Temp_Hold

OPEN OneRec
FETCH NEXT FROM OneRec
INTO @Record_Type,
@GroupID,
@StartDate,
@EndDate,
@Account,
@Client,
@User_id,
@UserName,
@Service,
@TypeOfCharge,
@Quantity,
@QuanityType,
@ContractGrossAmount,
@ContractAdj,
@ContractNetAmt,
@OverTheCap,
@TransGrossAmt,
@TransDiscount,
@TransNetAmt,
@TotalOnLineCharges,
@OtherCharges,
@CurrentPeriodCredits,
@Taxes,
@TotalCharges
SET @cnt = @cnt +1

Create table #Import_Errors
(
Row_ID smallint
Identity(1,1)
Primary key clustered,
Error_Description varchar(100),
OnRow smallint
)

-- Perform transformation and insert into destination
--Read data form Holding table
WHILE @@FETCH_STATUS = 0
BEGIN



-- Transform data
if @client = '****NO CLIENT ID SPECIFIED****'
BEGIN
INSERT INTO #IMPORT_ERRORS (Error_Description,OnRow)
VALUES('Client/Matter # Unspecified ',@cnt )

--Print 'Client/Matter Unspecified for Row ' + cast(@cnt as varchar(4)) + ' Of the imported file'
END
ELSE
BEGIN
SET @ClntCode = left(@Client,6)
SET @MatCode = substring(@Client,8,5)
SET @DIEmlpCode = Right(@Client,4)
SET @CostCode = 7
SET @PhaseCode = 'X-X'
SET @TaskCode = 'X-X'
SET @CurrencyCode = 'USD'
SET @EntryDate = right(CONVERT (VArchar(8), getdate(), 112),6)
SET @TranDate = right(convert(varchar(8),cast(@EndDate as datetime),112),6)
SET @BaseQty = @Quantity
SET @Amt = 100 --add amount = to @TotalCharges
SET @CallInfoReq = 'N'
SET @NarrText = 'BN=' + cast(@batchno as varchar(4)) + '; ' + @Username + '; ' + @Service + '; ' + @TypeOfCharge

--Import to import hold table
Insert into _Import_Hold (Client_Code,
Matter_Code,
DI_Empl_Code,
Cost_Code,
Phase_Code,
Task_Code,
Currency_Code,
Entry_Date,
Tran_Date,
Base_Qty,
Amount,
Call_InfoReqd,
Narrative_Text,
BatchNo,
BatchDate,
Source
)
VALUES ( @ClntCode,
@MatCode,
@DIEmlpCode ,
@CostCode,
@PhaseCode,
@TaskCode,
@CurrencyCode,
@EntryDate,
@TranDate,
@BaseQty,
@Amt,
@CallInfoReq,
@NarrText,
@batchNo,
@Batch_Date,
@import_Name
)
END

FETCH NEXT FROM OneRec
INTO @Record_Type,
@GroupID,
@StartDate,
@EndDate,
@Account,
@Client,
@User_id,
@UserName,
@Service,
@TypeOfCharge,
@Quantity,
@QuanityType,
@ContractGrossAmount,
@ContractAdj,
@ContractNetAmt,
@OverTheCap,
@TransGrossAmt,
@TransDiscount,
@TransNetAmt,
@TotalOnLineCharges,
@OtherCharges,
@CurrentPeriodCredits,
@Taxes,
@TotalCharges
SET @cnt = @cnt +1
END
CLOSE OneRec
DEALLOCATE OneRec

Print @batchno
Print @Batch_Date

select * from #Import_Errors

DROP Table #Import_Errors

exec _dsp_CreateASCII_File @batchno,1
GO