No output from BCP statement using Queryout

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

No output from BCP statement using Queryout

Author

Topic

TrevorDrakeson
Starting Member

5 Posts

Posted - 2009-01-02 : 14:40:18

I have spent the last few days combing the internet, looking for a solution to this issue.

Here is the Query:

Update XC_ET_F14Reject_A_CaptureDB.dbo.ItemCommonData SET iAdjustmentReason = 10 where iAdjustmentReason = 0
EXEC master..xp_cmdshell 'bcp "Select
SUBSTRING (acField4,1,9) as RT, SUBSTRING(acField3,1,18) as AcctNo,
SUBSTRING(sCustomFieldValues,67,8) as OrigProcDate,
SUBSTRING(sCustomFieldValues,97,8) as CutoffDate,
SUBSTRING(sCustomFieldValues,105,12) as cfDINISN,
SUBSTRING(sCustomFieldValues,79,18) as LeadCrAN,
SUBSTRING(sCustomFieldValues,65,2) as HoldStatus,
SUBSTRING(sCustomFieldValues,75,4) as Applid,
SUBSTRING(sCustomFieldValues,13,4) as Selector,
iAdjustmentReason as Adj,
SUBSTRING(sCustomFieldValues,245,6) as Branch
from XC_ET_F14Reject_A_CaptureDB.dbo.ItemCommonData
where SUBSTRING(sCustomFieldValues,65,2) = 99 and bREXCode <> 52 and fDeleted <> 1" queryout "C:\Sierra Xchange\XC_ET_F14Reject\Data\Patterns\ETHold.txt" -U sa -P abcd -c -t -S W03-Etrade-2350'

I have verified the remote connections and they are all configured and work correctly.

I have eliminated the chance that it is a Login issue with the 'sa' account.

When this query is run as a SQL Agent job or as a straight query, it gives no error. "Query executed successfully"

The only thing I have to go on is when I run this BCP statement from SQLcmd, I get a "Syntax error near 'queryout'"

Any help with this statement would be much appreciated

Trevor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 14:56:57

You can't run an UPDATE statement for a query in bcp. Bcp allows select queries only. If you just want to run the query in a batch file, then use sqlcmd.exe.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TrevorDrakeson
Starting Member

5 Posts

Posted - 2009-01-02 : 15:09:16

Thank you for the quick reply

But I do not have the UPDATE statement in the query:

Update XC_ET_F14Reject_A_CaptureDB.dbo.ItemCommonData SET iAdjustmentReason = 10 where iAdjustmentReason = 0
EXEC master..xp_cmdshell
'bcp "Select......

The BCP statement only has a Select and Query out

I did test your suggestion however.
Unfortunately I got the same result, no output, no txt file.

When I run the select statement I get eh values I want, but even if I remove the entire select statement and put

BCP select * from %table_name% queryout %path% I get the error: "syntax error near queryout"

Trevor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 15:12:13

Your original post doesn't reflect your last post. In your original post, there is no bcp command nor a select statement. Show us the actual code rather than abbreviating it. Remove the update statement to make this clear as that isn't part of the bcp command.

Also, you need to put double quotes around the query in the bcp command. Check BOL for details or run bcp /? from a cmd window.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TrevorDrakeson
Starting Member

5 Posts

Posted - 2009-01-02 : 15:36:55

Sorry tara

must have been something wrong with my original post but here is the entire statement again

copied from original post

EXEC master..xp_cmdshell

'bcp " Select
SUBSTRING (acField4,1,9) as RT, SUBSTRING(acField3,1,18) as AcctNo,
SUBSTRING(sCustomFieldValues,67,8) as OrigProcDate,
SUBSTRING(sCustomFieldValues,97,8) as CutoffDate,
SUBSTRING(sCustomFieldValues,105,12) as cfDINISN,
SUBSTRING(sCustomFieldValues,79,18) as LeadCrAN,
SUBSTRING(sCustomFieldValues,65,2) as HoldStatus,
SUBSTRING(sCustomFieldValues,75,4) as Applid,
SUBSTRING(sCustomFieldValues,13,4) as Selector,
iAdjustmentReason as Adj,
SUBSTRING(sCustomFieldValues,245,6) as Branch
from XC_ET_F14Reject_A_CaptureDB.dbo.ItemCommonData
where SUBSTRING(sCustomFieldValues,65,2) = 99 and bREXCode <> 52 and fDeleted <> 1" queryout "C:\Sierra Xchange\XC_ET_F14Reject\Data\Patterns\ETHold.txt" -U sa -P abcd -c -t -S W03-Etrade-2350'

The Double quotes (") are highlighted in Red and bold along with the single quotes that are present in the original post

BCP and SELECT are also emboldened and highlighted in brown

Update is not part of the BCP statement, and as stated earlier, I have run from CMD and the error is "Syntax near queryout"

I hope that clarifies

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 15:43:40

Remove all of the hard returns from the query. It needs to be in one long query statement.

Here's an example of one I wrote yesterday (renamed everything):


declare @sql nvarchar(4000), @cmd nvarchar(4000), @begin_date datetime, @end_date datetime

set @sql = ''
set @sql = @sql + 'select cast(i.aaa as varchar(20)) as aaa, '
set @sql = @sql + 'md.bbb as bbb, md.ccc, '
set @sql = @sql + 'sum(cast(ddd as bigint))/1024 as ddd1, '
set @sql = @sql + 'sum(cast(ddd as bigint))/1024 as ddd2, '
set @sql = @sql + 'count(distinct eee) as eee '
set @sql = @sql + 'from db1.dbo.asdf i '
set @sql = @sql + 'inner join db1.dbo.ghjk md on i.aaa = md.aaa where '
set @sql = @sql + 'db1.dbo.udf_bnm(qwe, few) > ''' 
set @sql = @sql + convert(varchar(30), @begin_date) + ''' and '
set @sql = @sql + 'db1.dbo.udf_bnm(qwe, few) < ''' 
set @sql = @sql + convert(varchar(30), @end_date) + ''' and '
set @sql = @sql + 'ttt = 2 '
set @sql = @sql + 'group by i.aaa, md.bbb, md.ccc'

set @cmd = 'bcp "' + @sql + '" queryout E:\DBA\SomeFile.csv -someserver\a -T -t, -c -r\r\n'

EXEC master.dbo.xp_cmdshell @cmd

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TrevorDrakeson
Starting Member

5 Posts

Posted - 2009-01-02 : 16:36:58

Tara

You rock

The correct statement was:

Update ET_XC_VM_PROD_A_CaptureDB.dbo.ItemCommonData SET iAdjustmentReason = 10 where iAdjustmentReason = 0
Declare @cmd nvarchar(4000)
SET @cmd = 'bcp "Select SUBSTRING (acField4,1,9) as RT, SUBSTRING(acField3,1,18) as AcctNo,SUBSTRING(sCustomFieldValues,67,8) as OrigProcDate, SUBSTRING(sCustomFieldValues,97,8) as CutoffDate, SUBSTRING(sCustomFieldValues,105,12) as cfDINISN, SUBSTRING(sCustomFieldValues,79,18) as LeadCrAN, SUBSTRING(sCustomFieldValues,65,2) as HoldStatus, SUBSTRING(sCustomFieldValues,75,4) as Applid, SUBSTRING(sCustomFieldValues,13,4) as Selector, iAdjustmentReason as Adj, SUBSTRING(sCustomFieldValues,245,6) as Branch from ET_XC_VM_PROD_A_CaptureDB.dbo.ItemCommonData where SUBSTRING(sCustomFieldValues,65,2) = 99 and bREXCode <> 52 and fDeleted <> 1" queryout "C:\Sierra Xchange\ET_XC_VM_PROD\Data\Patterns\ETHold.txt" -U sa -P abcd -c -t -S ET-XC-VM-PROD'
EXEC master..xp_cmdshell @cmd

I got the correct output and it works great now

it was the returns and the Declare statement that made it work

Thank you for your help

Trevor

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 16:39:38

Glad to help, sorry that I misread your original post earlier. When you separated it out and color coded it, I then saw what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TrevorDrakeson
Starting Member

5 Posts

Posted - 2009-02-24 : 14:09:44

Well I am back at this Darn BCP statement again

We have discovered, in our testing, that the application using the exported Text file works great if there is data to populate said TXT file. However, if there is no data for that query it will print a blank TXT file that will cause the application to crash.

So we have decided to populate that TXT file with Zeros (0) to ensure the application does not crash.

Our idea was to use a simple If/Else statement along with the BCP to populate the file but while the Select statement (which includes the IF statement) works fine on its own, when you add the BCP command the query fails.

Can you use IF/Then clauses with BCP??

Is there another way to populate this Text file with a row of Zero's if the query returns no data??

Your help would be great!?

Sean

The Query:

Update ET_XC_VM_PROD_A_CaptureDB.dbo.ItemCommonData SET iAdjustmentReason = 10 where iAdjustmentReason = 0
Declare @cmd nvarchar(4000) SET @CMD = 'bcp "SELECT SUBSTRING(ItemCommonData.acField4,1, 9) AS RT, SUBSTRING(ItemCommonData.acField3, 1, 18) AS AcctNo, ItemCommonData.sDIN, SUBSTRING(ItemCommonData.acField1, 1, 10) AS Amount, SUBSTRING(ItemCommonData.sCustomFieldValues, 67, 8) AS OrigProcDate, SUBSTRING(ItemCommonData.sCustomFieldValues, 97, 8) AS CutoffDate, SUBSTRING(ItemCommonData.sCustomFieldValues, 105, 12) AS cfDINISN, SUBSTRING(ItemCommonData.sCustomFieldValues, 79, 18) AS LeadCrAN, SUBSTRING(ItemCommonData.sCustomFieldValues, 65, 2) AS HoldStatus, SUBSTRING(ItemCommonData.sCustomFieldValues, 75, 4) AS Applid, SUBSTRING(ItemCommonData.sCustomFieldValues, 13, 4) AS Selector, ItemCommonData.iAdjustmentReason AS Adj, SUBSTRING(ItemCommonData.sCustomFieldValues, 245, 6) AS Branch FROM ItemCommonData INNER JOIN ItemControlInfo ON ItemCommonData.iDocId = ItemControlInfo.iDocId WHERE (SUBSTRING(ItemCommonData.sCustomFieldValues, 65, 2) = 99) AND (ItemCommonData.bREXCode <> 52) AND (ItemCommonData.fDeleted <> 1) ORDER BY ItemControlInfo.iblockid, ItemControlInfo.iPODOrderSequence IF (@@ROWCOUNT = 0) SELECT (000000000) AS RT, (000000000000000000) AS AcctNo, (000000000000), (0000000000) AS Amount, (00000000) AS OrigProcDate, (00000000) AS CutoffDate, (000000000000) AS cfDINISN, (000000000000000000) AS LeadCrAN, (00) AS HoldStatus, (0000) AS Applid, (0000) AS Selector, (00) AS Adj,(000000) AS Branch" queryout "C:\Sierra Xchange\ET_XC_VM_PROD\Data\Patterns\ETHold.txt" -U sa -P abcd -c -t -S ET-XC-VM-PROD'EXEC master..xp_cmdshell @cmd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources