BCP not exporting data only NULL

Bcp query out is new to me I believe I have the correct syntax but the export is only that of a blank file what am I doing incorrectly.   The files must all be only one line of data, the files are created but again with no data exported.


 DECLARE moldline CURSOR LOCAL STATIC FOR
 
 
 select wo,Line from  EspiMoldExport


open moldline
WHILE 1 = 1
   BEGIN
      FETCH NEXT FROM moldline INTO @conum,@coline
        if @@FETCH_STATUS <> 0
         BREAK

        SET @filename = 'Mold_' + rtrim(ltrim(@conum)) + '_' + rtrim(ltrim(CAST(@coline AS VARCHAR(2))))  +'.csv'
      SET @FILENAME = isnull(@FILENAME, 'MISS')
        SET @elog   = 'bcp  "select * from SyteLine_POLY_App.dbo.EspiMoldExport where WO =''@conum'' and Line = ''@coline'' " queryout \\fp01\companyfiles\moldLogs\' + @filename + '  -c -t, -T -SESPIDATA'---elogload.csv
      PRINT @ELOG
        exec master..xp_cmdshell @elog
       select * from SyteLine_POLY_App.dbo.EspiMoldExport where wo = @conum and line = @coline
    END
  CLOSE moldline
  DEALLOCATE moldline
stevendeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Hey Steven,

Are u getting data with below query

select * from SyteLine_POLY_App.dbo.EspiMoldExport where wo = @conum and line = @coline

Open in new window

stevendeveloperAuthor Commented:
Yes I check that first.
Pawan KumarDatabase ExpertCommented:
select wo,Line from  EspiMoldExport

This also?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

stevendeveloperAuthor Commented:
Yes it works fine
Kevin CrossChief Technology OfficerCommented:
SET @elog   = 'bcp  "select * from SyteLine_POLY_App.dbo.EspiMoldExport where WO ='''+@conum+''' and Line = '''+@coline+''' " queryout \\fp01\companyfiles\moldLogs\' + @filename + '  -c -t, -T -SESPIDATA'---elogload.csv

Try like above.  If the @conum and @coline variables are numeric, you may have to wrap those with a CAST/CONVERT.

i.e. +CONVERT(VARCHAR(10), @conum)+

EDIT: if WO and Line also are numeric, you also can do this:
... WO ='+@conum+' and Line = '+@coline+' ...
Pawan KumarDatabase ExpertCommented:
Full updated code. Please try ..

DECLARE moldline CURSOR LOCAL STATIC FOR 
select wo,Line from  EspiMoldExport
open moldline
WHILE 1 = 1
   BEGIN
      FETCH NEXT FROM moldline INTO @conum,@coline
        if @@FETCH_STATUS <> 0 
         BREAK

        SET @filename = '\\fp01\companyfiles\moldLogs\' + 'Mold_' + rtrim(ltrim(@conum)) + '_' + rtrim(ltrim(CAST(@coline AS VARCHAR(2))))  +'.csv'

		/* BELOW CODE should be on 1 LINE */
		SET @query   = ' " select * from SyteLine_POLY_App.dbo.EspiMoldExport where WO = ''' + CAST(@conum AS VARCHAR(MAX)) + '''' + ' and Line = ' + '''' + CAST(@coline AS VARCHAR(MAX)) + ''' " '

		SET @bcp = ' EXEC master..xp_cmdshell '' bcp ' + @query + ' queryout ' + '"' + @filename + '"' + ' -c -T ' + ''''

		--PRINT ( @bcp )

		EXEC ( @bcp )

		SELECT * FROM SyteLine_POLY_App.dbo.EspiMoldExport WHERE wo = @conum and line = @coline
    END
CLOSE moldline
DEALLOCATE moldline

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevendeveloperAuthor Commented:
This worked for me thank you very much for the help.  Sorry for late reply I have been out of country
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help as always. :)

You can select the comment that helped and click submit. I have opened the question for you.
Kevin CrossChief Technology OfficerCommented:
Glad you have had safe travels and that we could help you. Cheers and happy coding!
Pawan KumarDatabase ExpertCommented:
Same as old one..

Accepted answer: 800 points for Pawan Kumar's comment #a42356719
Assisted answer: 200 points for Kevin Cross's comment #a42356609
Kevin CrossChief Technology OfficerCommented:
Agree that there should he accepted answers here. My comment first identified issue with parameters and potential conversion based on data types, but Pawan's comment shows how to implement in original code. Therefore, 50/50 split or as below:

Accepted answer: 600 points for Pawan Kumar's comment https:#a42356719
Assisted answer: 400 points for Kevin Cross's comment https:#a42356609
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.