Job Agent - Syntax Errors

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,Pr oductionLotNo,
KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Compl eted,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.
My guess is that Agent is executing your code with below setting:
SET QUOTED_IDENTIFIER OFF
Above will interpret double-quotes as string delimiters instead of identifier delimiters. IMO, the
setting should be ON which is ANSI SQL compliant, what QA etc has and apparently even the "parse"
functionality seem to have it set differently.
Anyhow, you can try to use [square brackets] instead of double-quotes. Or add below in beginning of
your TSQL code:
SET QUOTED_IDENTIFIER ON
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.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,Pr oductionLotNo,
> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Compl eted,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.
|||That worked.
Thank you very much, Tibor!
/b;
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OoRcLb8nIHA.1036@.TK2MSFTNGP03.phx.gbl...
> My guess is that Agent is executing your code with below setting:
> SET QUOTED_IDENTIFIER OFF
> Above will interpret double-quotes as string delimiters instead of
> identifier delimiters. IMO, the setting should be ON which is ANSI SQL
> compliant, what QA etc has and apparently even the "parse" functionality
> seem to have it set differently.
> Anyhow, you can try to use [square brackets] instead of double-quotes. Or
> add below in beginning of your TSQL code:
> SET QUOTED_IDENTIFIER ON
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
> news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
>