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,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.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,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.|||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...
>> 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.
>

JET Anonymous Merge: Can It Be Done?

Is it possible to set up
Publisher: SQL Server 2000
Subscriber: Access 97
Merge replication
Anonymous pull subscriptions
Over the Internet via VPN
I've read the BOL and other on-line artices, but don't quite get it.
Links to specific examples?
Thanks
Nope, you might be able to do a push if you could map a drive. The problem
is the linked server definition to enable access as a database you need to
use if you are replicating to a access database. Review the sample chapter
download in the nwsu link for my book for more info on how to replicate to
access.
The problem is in your term anonymous pull. For a pull to work you must have
SQL Server or MSDE installed on the remote machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107726821.588526.272280@.l41g2000cwc.googlegr oups.com...
> Is it possible to set up
> Publisher: SQL Server 2000
> Subscriber: Access 97
> Merge replication
> Anonymous pull subscriptions
> Over the Internet via VPN
> I've read the BOL and other on-line artices, but don't quite get it.
> Links to specific examples?
> Thanks
>