Re: Transform Data/Parameter (?) syntax error

#1

I do not have Win2003 here but what is the statement you are trying in the
Source ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Nate Russell" <nathan_russell (AT) REMOVETHISdatacard (DOT) com> wrote

Quote:

When I hit the Parameters button in Source page of
Transform Data Task, I get a "Syntax error or access
violation" from "Microsoft OLE DB Provider for SQL
Server". I have set an output parameter I wish to use in
a previous Execute SQL Task (which must complete first),
and am using the ? as a placeholder in my where clause on
my Source page (of Transform Data).

The Transform Data Task is between two Microsoft SQL
Server 2000 SP3 databases. They are running Windows
Server 2003, thus the MDAC is version 2.8 that is
integrated into Windows Server 2003.

#2

It is just a select statement. There is nothing wrong
with its syntax. The where clause looks like

"where sourcetimestamp between ? and current_timestamp"

It is the ? parameter placeholder that is causing the
syntax error. For instance, if I use "where
sourcetimestamp between currenttimestamp-30 and
current_timestamp", then I get no syntax error.

The impression I've gotten is that parameter support was
added into OLEDB around the time SQL Server 2000 came
out. I think the problem may be that the MDAC 2.8
version of OLEDB that comes with Windows Server 2003 is
having problems supporting parameters. Even if I upgrade
to SQL Server 2000 SP3, because the MDAC components in
that service pack are based around MDAC 2.7, I think the
MDAC 2.8 files are not getting updated. On last check,
you still cannot download MDAC 2.8 (it's only included in
Windows Server 2003), so I don't know if there is any fix.

I am hoping some expert is out there with a fix as
opposed to a workaround, such as dynamically changing the
select with ActiveX. Any suggestions?

Quote:

-----Original Message-----
I do not have Win2003 here but what is the statement you
are trying in the
Source ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Nate Russell" <nathan_russell (AT) REMOVETHISdatacard (DOT) com
wrote in message
news:097901c35aa9$f926d370$a601280a (AT) phx (DOT) gbl...
When I hit the Parameters button in Source page of
Transform Data Task, I get a "Syntax error or access
violation" from "Microsoft OLE DB Provider for SQL
Server". I have set an output parameter I wish to use
in
a previous Execute SQL Task (which must complete
first),
and am using the ? as a placeholder in my where clause
on
my Source page (of Transform Data).

The Transform Data Task is between two Microsoft SQL
Server 2000 SP3 databases. They are running Windows
Server 2003, thus the MDAC is version 2.8 that is
integrated into Windows Server 2003.

.

#3

Can you test the package on a < 2.8 version of MDAC server ?
That would rule out the MDAC issue.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Nate Russell" <Nathan_Russell (AT) REMOVETHISdatacard (DOT) com> wrote

Quote:

It is just a select statement. There is nothing wrong
with its syntax. The where clause looks like

"where sourcetimestamp between ? and current_timestamp"

It is the ? parameter placeholder that is causing the
syntax error. For instance, if I use "where
sourcetimestamp between currenttimestamp-30 and
current_timestamp", then I get no syntax error.

The impression I've gotten is that parameter support was
added into OLEDB around the time SQL Server 2000 came
out. I think the problem may be that the MDAC 2.8
version of OLEDB that comes with Windows Server 2003 is
having problems supporting parameters. Even if I upgrade
to SQL Server 2000 SP3, because the MDAC components in
that service pack are based around MDAC 2.7, I think the
MDAC 2.8 files are not getting updated. On last check,
you still cannot download MDAC 2.8 (it's only included in
Windows Server 2003), so I don't know if there is any fix.

I am hoping some expert is out there with a fix as
opposed to a workaround, such as dynamically changing the
select with ActiveX. Any suggestions?

-----Original Message-----
I do not have Win2003 here but what is the statement you
are trying in the
Source ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Nate Russell" <nathan_russell (AT) REMOVETHISdatacard (DOT) com
wrote in message
news:097901c35aa9$f926d370$a601280a (AT) phx (DOT) gbl...
When I hit the Parameters button in Source page of
Transform Data Task, I get a "Syntax error or access
violation" from "Microsoft OLE DB Provider for SQL
Server". I have set an output parameter I wish to use
in
a previous Execute SQL Task (which must complete
first),
and am using the ? as a placeholder in my where clause
on
my Source page (of Transform Data).

The Transform Data Task is between two Microsoft SQL
Server 2000 SP3 databases. They are running Windows
Server 2003, thus the MDAC is version 2.8 that is
integrated into Windows Server 2003.

.

#4

I tested it. Works fine on non MDAC 2.8/Windows Server
2003 Source. I'm not sure how to file a bug with
Microsoft on this one, but it would be good since it's
hard to convince a customer to use this solution when
they know it might break if they ever upgrade to MDAC 2.8
(provided it is available eventually for download).

Quote:

-----Original Message-----
Can you test the package on a < 2.8 version of MDAC
server ?
That would rule out the MDAC issue.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Nate Russell" <Nathan_Russell (AT) REMOVETHISdatacard (DOT) com
wrote in message
news:0cdd01c35b5d$47efcd50$a401280a (AT) phx (DOT) gbl...
It is just a select statement. There is nothing wrong
with its syntax. The where clause looks like

"where sourcetimestamp between ? and current_timestamp"

It is the ? parameter placeholder that is causing the
syntax error. For instance, if I use "where
sourcetimestamp between currenttimestamp-30 and
current_timestamp", then I get no syntax error.

The impression I've gotten is that parameter support
was
added into OLEDB around the time SQL Server 2000 came
out. I think the problem may be that the MDAC 2.8
version of OLEDB that comes with Windows Server 2003 is
having problems supporting parameters. Even if I
upgrade
to SQL Server 2000 SP3, because the MDAC components in
that service pack are based around MDAC 2.7, I think
the
MDAC 2.8 files are not getting updated. On last check,
you still cannot download MDAC 2.8 (it's only included
in
Windows Server 2003), so I don't know if there is any
fix.

I am hoping some expert is out there with a fix as
opposed to a workaround, such as dynamically changing
the
select with ActiveX. Any suggestions?

-----Original Message-----
I do not have Win2003 here but what is the statement
you
are trying in the
Source ?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Nate Russell" <nathan_russell (AT) REMOVETHISdatacard (DOT) com
wrote in message
news:097901c35aa9$f926d370$a601280a (AT) phx (DOT) gbl...
When I hit the Parameters button in Source page of
Transform Data Task, I get a "Syntax error or
access
violation" from "Microsoft OLE DB Provider for SQL
Server". I have set an output parameter I wish to
use
in
a previous Execute SQL Task (which must complete
first),
and am using the ? as a placeholder in my where
clause
on
my Source page (of Transform Data).

The Transform Data Task is between two Microsoft SQL
Server 2000 SP3 databases. They are running Windows
Server 2003, thus the MDAC is version 2.8 that is
integrated into Windows Server 2003.

.

.

#5

On 5-Aug-2003, "Nate Russell" <nathan_russell (AT) REMOVETHISdatacard (DOT) com> wrote:

Quote:

Re: Transform Data/Parameter (?) syntax error

Posted privately in response to private mail.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Transform Data/Parameter (?) syntax error

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Transform Data/Parameter (?) syntax error - 08-05-2003 , 01:41 AM

Re: Transform Data/Parameter (?) syntax error - 08-05-2003 , 09:24 AM

Re: Transform Data/Parameter (?) syntax error - 08-05-2003 , 09:33 AM

Re: Transform Data/Parameter (?) syntax error - 08-05-2003 , 02:24 PM

Re: Transform Data/Parameter (?) syntax error - 08-05-2003 , 03:21 PM