Running a Job / Parse Issues

Hi All,

I'm having a problem with a job on an SQL 2000 Server that is linked to pull
data from an SQL 2005 server. When I enter my infromation into the Job Step
properties and parse the SQL, the system tells me it is fine. When I run
the code through query analyzer, it also works fine as well. However, when
the job is run, I am getting a syntax error on the file name because (I
think) it is in double quotes.

Here is the SQL I am trying to parse:

insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
(vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
+ CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
from bos_data a
where vin COLLATE DATABASE_DEFAULT not in
(select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")

Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
in both areas of the SQL.

Any ideas would be most appreciated.

Thanks!

Brian.

The SET QUOTED_IDENTIFIER option is probably set to OFF when you try to
parse this sql string. See
http://msdn2.microsoft.com/en-us/library/aa259228(SQL.80).aspx

Another possible solution would be to try replacing the double quote with
[], as in: ["SPS 4_0$AFON DTS Import Staging"] .

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Brian Piotrowski" wrote in message

> Hi All,
>
> I'm having a problem with a job on an SQL 2000 Server that is linked to
> pull data from an SQL 2005 server. When I enter my infromation into the
> Job Step properties and parse the SQL, the system tells me it is fine.
> When I run the code through query analyzer, it also works fine as well.
> However, when the job is run, I am getting a syntax error on the file name
> because (I think) it is in double quotes.
>
> Here is the SQL I am trying to parse:
>
> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
> (vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
> from bos_data a
> where vin COLLATE DATABASE_DEFAULT not in
> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import
> Staging")
>
> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import
> Staging" in both areas of the SQL.
>
> Any ideas would be most appreciated.
>
> Thanks!
>
> Brian.