stored procedure to export data

Question

Post reply

stored procedure to export data

Jerry Abamonte

SSC Veteran

Points: 209
More actions
February 23, 2007 at 9:46 am

#171530

I have a big problem, I need to export data to a flat file using a stored procedure (not my first choice, but my client has sql 2k5 workgroup edition which does not support SSIS integration services). So I wrote with help from others this procedure. when I parse this in query analyzer everything checks out, but when I execute the procedure i get an error on the "open customerlist". The error is: Error converting data type varchar to numeric.
Can someone (who is much better at this than I am) take a look at this and point out my mistake!
the procedure is listed below
DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord SYSNAME;
DECLARE @RecordData    SYSNAME;
DECLARE @FileName      SYSNAME;
DECLARE @FILE          SYSNAME;
SET @FILE = 'C:\CUSTOMERQUERY.TXT'
SET @FileName = CAST(@file as SYSNAME);
SET @HeaderRecord = 'CUSTNMBR CUSTNAME RATETPID CRLMTAMT CUSTBLNC AGPERAMT_1 TTLSLYTD TTLSLLYR LSTTRXDT LASTPYDT';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT
DECLARE CustomerList CURSOR FOR
SELECT
CAST(RM00103.CUSTNMBR AS CHAR(10))+ CAST(CUSTNAME AS CHAR(26))+ CAST(RATETPID AS CHAR(10))+
CAST(CRLMTAMT AS NUMERIC(19,2))+ cast(CUSTBLNC as numeric(19,2))+ cast(AGPERAMT_1 as numeric(19,2))+
cast(TTLSLYTD as numeric(19,2))+ cast(TTLSLLYR as numeric(19,2))+
convert(varCHAR(10), LSTTRXDT,101)+ CONVERT(varCHAR(10), LASTPYDT,101)
FROM RM00101 inner Join RM00103 on rm00101.custnmbr = rm00103.custnmbr
WHERE Lsttrxdt >= dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
OPEN CustomerList
FETCH NEXT FROM CustomerList INTO @RecordData
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
    EXEC master..xp_cmdshell @CommandString, NO_OUTPUT
    FETCH NEXT FROM CustomerList INTO @RecordData
END
CLOSE CustomerList
DEALLOCATE CustomerList
GO

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ken McKelvey SSCoach Points: 18484 More actions · Answer 1

Concatenating nunerics to varchars will not work. I suspect all your code like:

CAST(CRLMTAMT AS numeric(19,2))

should be like:

STR(CRLMTAMT, 19, 2)

It may be better to use the BCP utility.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Does SQL 2K5 WorkGroup have either BCP or OSQL? The reason I ask is that the cursor method will take a comparitively very long time... for every line you write to the file using that method, the file is opened, the file is written to, the file is closed... once for each line written... not real good on efficiency.

But I need to know, does SQL 2K5 WorkGroup have either BCP or OSQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jerry Abamonte SSC Veteran Points: 209 More actions · Answer 3

I have no idea. I am working on getting the client to upgrade to SQL server 2005 standard and then I can use SSIS integration to create the packages and export the data. by the by, here is how I fixed the stored procedure:

CONVERT(CHAR(21),CAST(QUANTITY AS NUMERIC(19,2)))+