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.

date: Tue, 15 Apr 2008 14:46:15 -0400   author:   Brian Piotrowski

Re: Running a Job / Parse Issues   

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 
news:DAEA3686-1D8C-4EE2-B83E-606653BB2D4D@microsoft.com...
> 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.

date: Sun, 20 Apr 2008 18:46:56 -0400   author:   Sylvain Lafontaine sylvain aei ca (fill the blanks, no spam please)