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

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

This also?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stevendeveloperAuthor Commented:
Yes it works fine
0
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+' ...
0
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

0

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
0
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.
0
Kevin CrossChief Technology OfficerCommented:
Glad you have had safe travels and that we could help you. Cheers and happy coding!
0
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
0
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.