Problems when SQL Server applies BCP to export data

laukai

BCP tool to export the results obtained during the execution of stored procedures
Error message:
SQLState = 37000, NativeError = 156
Error = [Microsoft] [ODBC SQL Server Driver] [SQL Server] There is a syntax error near the keyword 'where'.

Situation description:
The above error only appeared on the client's machine, and it did not appear on my own machine or my colleague's machine, or even on my home machine.

Error BCP statement: bcp "exec vms.dbo. Vehicle operation_proc" queryout "e:\una\1\driverrecover.xml" -w -T

The view statements used:
CREATE VIEW dbo. Yesterday's driving record_v AS
SELECT DISTINCT
      Date, serial number, route name, license plate number, driver serial number, flight attendant serial number, departure time, arrival time, creator,
      Creation time
FROM dbo. Vehicle driving records
WHERE (SUBSTRING (CONVERT (char, date, 100), 7, 4) = YEAR (DATEADD ([day],-1,
      GETDATE ()))) AND (SUBSTRING (CONVERT (char, date, 100), 1, 2)
      = MONTH (DATEADD ([day],-1, GETDATE ()))) AND (SUBSTRING (CONVERT (char, date,
      100), 4, 2) = DAY (DATEADD ([day],-1, GETDATE ())))

Stored procedure statements used:
create proc vehicle operation_proc as select 1 as tag,
       null as parent,
       null as [root! 1 !! element],
       null as [steerrecover! 2! number],
       null as [steerrecover! 2! date],
       null as [steerrecover! 2! line],
       null as [steerrecover! 2! busnumber],
       null as [steerrecover! 2! drivernumber],
       null as [steerrecover! 2! stewardnumber],
       null as [steerrecover! 2! leavetime],
       null as [steerrecover! 2! arrivetime],
       null as [steerrecover! 2! creator],
       null as [steerrecover! 2! createtime],
       null as [steerrecover! 2! mendir],
       null as [steerrecover! 2! fixtime],
       null as [ticket! 3! ticketnumber],
       null as [ticket! 3! leavestation],
       null as [ticket! 3! arrivestation],
       null as [ticket! 3! price],
       null as [ticket! 3! amount]
from
vms.dbo. Yesterday's driving record_v a
union
select 2,
        1,
        null,
a. serial number,
a. date,
a. route name,
a. license plate number,
a. driver serial number,
a. Flight attendant serial number,
a. departure time,
a. arrival time,
a. creator,
a. creation time,
a. Modified by,
a. modification time,
null,
null,
null,
null,
null
from
vms.dbo. Yesterday's driving record_v a
union
select 3,
        2,
        null,
a. serial number,
a. date,
a. route name,
a. license plate number,
a. driver serial number,
a. Flight attendant serial number,
a. departure time,
a. arrival time,
a. creator,
a. creation time,
a. Modified by,
a. modification time,
b. serial number,
b. departure station,
b. arrive at the site,
b. price,
b. votes
from
vms.dbo. Yesterday's driving record_v a left join vms.dbo.Ticketing record b on a. Serial number = b. Driving record Serial number
order by [steerrecover! 2! number], [ticket! 3! ticketnumber]

for xml explicit



I ask for your help! !! !!

hhuaixiaozi

Is there any error in direct execution in Query Analyzer?

laukai

An error occurred while executing BCP. Whether in query analyzer or DOS

kxiaper

Since there is a syntax error near the keyword'where'
Try to remove it
WHERE (SUBSTRING(CONVERT(char, date, 100), 7, 4) = YEAR(DATEADD([day],-1,
      GETDATE()))) AND (SUBSTRING(CONVERT(char, date, 100), 1, 2)
      = MONTH(DATEADD([day],-1, GETDATE()))) AND (SUBSTRING(CONVERT(char, date,
      100), 4, 2) = DAY(DATEADD([day],-1, GETDATE())))

laukai

Thank you, let me see if I have a chance to try it.