"Syntax error or access violation" with new (July 22 2003) driver

Hi,
I'm having troubles with the SQL Server ODBC driver 2000.81.9001.40 dated on
July 22 2003. It is incompatible with our custom application developed with
Centura SQLWindows/32 Version 1.5.1-PFT2. When running some queries, the
error message reported by the driver is
Error No: 20035
Error Text: [Microsoft][ODBC SQL Server Driver]Syntax error or access
violation
The only suspicious fact is that the command is broken by a CR-LF (new line)
combination. This code had worked for years without any problem, sending
such broken line commands
The SQL command sent is:
{call spEjeSelectRem ('vCtePedTem_ChecaPoliticas
20,20,159,1,935502,17700024,1200148,null,
1')}
Note there is a new-line sequence behind the last parameter
My questions are:
Can It be the cause of the above mentioned error?
Wat can I do, install the previous version?
The ODBC driver is dated July 22 2003. It was automatically downloaded by
Microsoft Automatic Update. How can I protect specifically from ODBC driver
version updates?
Thanks in advance
FaustinoOk,
I did the following:
-"Uninstall" MDAC by
dasetup.exe /u
(actually the uninstall procedure didn't work, but at least it leave the
ODBC in such inconsistent state that I could install a previous version)
-Installed the MDAC v.2.7 that I had before the bug appeared. The bug didn't
appear again.
However yesterday the Microsoft Update suggested to install the security
patch 823718 related to MDAC. I crossed my fingers and applied it. No
problem. SQL Server driver is now 2000.81.9041.40 and the bug didn't show
again.
Hope this helps somebody
Faustino
"faustino Dina" <fdina@.matusa.com.mx> wrote in message
news:eo%23$lJ%23aDHA.2016@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm having troubles with the SQL Server ODBC driver 2000.81.9001.40 dated
on
> July 22 2003. It is incompatible with our custom application developed
with
> Centura SQLWindows/32 Version 1.5.1-PFT2. When running some queries, the
> error message reported by the driver is
> Error No: 20035
> Error Text: [Microsoft][ODBC SQL Server Driver]Syntax error or access
> violation
> The only suspicious fact is that the command is broken by a CR-LF (new
line)
> combination. This code had worked for years without any problem, sending
> such broken line commands
> The SQL command sent is:
> {call spEjeSelectRem ('vCtePedTem_ChecaPoliticas
> 20,20,159,1,935502,17700024,1200148,null,
> 1')}
> Note there is a new-line sequence behind the last parameter
> My questions are:
> Can It be the cause of the above mentioned error?
> Wat can I do, install the previous version?
> The ODBC driver is dated July 22 2003. It was automatically downloaded by
> Microsoft Automatic Update. How can I protect specifically from ODBC
driver
> version updates?
> Thanks in advance
> Faustino
>

"SQL Native Client" / "SQL Server" drivers

When creating a system DSN via the ODBC Admistrator dialog...
What is the differecne between "SQL Native Client" and "SQL Server" drivers
listed in the "Create New Data Source" drivers list?
How do they differ?
"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> When creating a system DSN via the ODBC Admistrator dialog...
> What is the differecne between "SQL Native Client" and "SQL
> Server" drivers
> listed in the "Create New Data Source" drivers list?
> How do they differ?
From MSDN:
"SQL Native Client is a data access technology that is new to
Microsoft SQL Server 2005, and it is a stand-alone data access
application programming interface (API) that is used for both OLE
DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
driver into one native dynamic-link library (DLL) while also
providing new functionality above and beyond that supplied by the
Microsoft Data Access Components (MDAC). SQL Native Client can be
used to create new applications or enhance existing applications
that need to take advantage of new SQL Server 2005 features such
as multiple active result sets (MARS), user-defined data types
(UDT), query notifications, snapshot isolation, and XML data type
support."
- Arnie
|||Thanks, that helps... but what about the other one listed? ("SQL Server"
driver)
Which is the appropriate one to use in an IIS/Website scenario in terms of
data access?
"Arnie" wrote:

> "Coder23" <Coder23@.discussions.microsoft.com> wrote in message
> news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> From MSDN:
> "SQL Native Client is a data access technology that is new to
> Microsoft SQL Server 2005, and it is a stand-alone data access
> application programming interface (API) that is used for both OLE
> DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
> driver into one native dynamic-link library (DLL) while also
> providing new functionality above and beyond that supplied by the
> Microsoft Data Access Components (MDAC). SQL Native Client can be
> used to create new applications or enhance existing applications
> that need to take advantage of new SQL Server 2005 features such
> as multiple active result sets (MARS), user-defined data types
> (UDT), query notifications, snapshot isolation, and XML data type
> support."
> - Arnie
>
>
|||"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:A3AAA0F0-1898-4453-8D19-9613BA7FAA08@.microsoft.com...
> Thanks, that helps... but what about the other one listed?
> ("SQL Server"
> driver)
> Which is the appropriate one to use in an IIS/Website scenario
> in terms of
> data access?
>
The 'normal' SQL Server Driver should be fine unless you plan to
use some of the new features that were mentioned for SQL Server
2005. We use SQL Server 2000. I haven't tried to run the new
driver against it yet.
- Arnie

"SQL Native Client" / "SQL Server" drivers

When creating a system DSN via the ODBC Admistrator dialog...
What is the differecne between "SQL Native Client" and "SQL Server" drivers
listed in the "Create New Data Source" drivers list?
How do they differ?"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> When creating a system DSN via the ODBC Admistrator dialog...
> What is the differecne between "SQL Native Client" and "SQL
> Server" drivers
> listed in the "Create New Data Source" drivers list?
> How do they differ?
From MSDN:
"SQL Native Client is a data access technology that is new to
Microsoft SQL Server 2005, and it is a stand-alone data access
application programming interface (API) that is used for both OLE
DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
driver into one native dynamic-link library (DLL) while also
providing new functionality above and beyond that supplied by the
Microsoft Data Access Components (MDAC). SQL Native Client can be
used to create new applications or enhance existing applications
that need to take advantage of new SQL Server 2005 features such
as multiple active result sets (MARS), user-defined data types
(UDT), query notifications, snapshot isolation, and XML data type
support."
- Arnie|||Thanks, that helps... but what about the other one listed? ("SQL Server"
driver)
Which is the appropriate one to use in an IIS/Website scenario in terms of
data access?
"Arnie" wrote:

> "Coder23" <Coder23@.discussions.microsoft.com> wrote in message
> news:B50C82B2-70C8-4F28-89CA-248F039A048E@.microsoft.com...
> From MSDN:
> "SQL Native Client is a data access technology that is new to
> Microsoft SQL Server 2005, and it is a stand-alone data access
> application programming interface (API) that is used for both OLE
> DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
> driver into one native dynamic-link library (DLL) while also
> providing new functionality above and beyond that supplied by the
> Microsoft Data Access Components (MDAC). SQL Native Client can be
> used to create new applications or enhance existing applications
> that need to take advantage of new SQL Server 2005 features such
> as multiple active result sets (MARS), user-defined data types
> (UDT), query notifications, snapshot isolation, and XML data type
> support."
> - Arnie
>
>|||"Coder23" <Coder23@.discussions.microsoft.com> wrote in message
news:A3AAA0F0-1898-4453-8D19-9613BA7FAA08@.microsoft.com...
> Thanks, that helps... but what about the other one listed?
> ("SQL Server"
> driver)
> Which is the appropriate one to use in an IIS/Website scenario
> in terms of
> data access?
>
The 'normal' SQL Server Driver should be fine unless you plan to
use some of the new features that were mentioned for SQL Server
2005. We use SQL Server 2000. I haven't tried to run the new
driver against it yet.
- Arnie

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
--
Regards,
Andy O'Neill

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
Ken Wells
President
Smart Computing Solutions, LLC
All it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:

> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC
|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (o
r
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedur
e
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:

> I am unable to add/update/delete data in the SQL Server database from Acce
ss
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data
(or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't ge
t
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 o
n
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 databa
se
> application on PC-2 that needs to modify the SQL data on PC-1. I have tri
ed
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways
of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Acce
ss
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update
to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding update
s
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I trie
d
> to ask Microsoft (using my one free question) but my (valid) Product ID wa
s
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on o
ne
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill

"Prepare" in SQL Server through ODBC/ADO

Hi,
When using an interface like ADO or ODBC, there is a call named
"Prepare" which supposedly sent the statement to the server which in
turn should parse it, save the query plan and return a handle to the
client so subquent calls just refer to this handle/id and get faster
execution in batches... is this correct?
Well, my question is, when this Prepare or Parse is done, is there any
physical check being performed by SQL Server ? I mean, does it check
the column names, table existance, etc, or does it just check pure
syntax ?According to the ODBC 3.0 SDK, a SQLPrepare call to an ODBC driver sends the
statement to the data source for preparation and stores the returned access
plan identifier for later execution; or returns any errors including
SQLSTATE 42000 (Syntax error or access violation) and SQLSTATE 42S02 (Base
table or view not found). It also notes that some drivers do not return
errors after SQLPrepare is called, but rather when the statement is executed
later. So "SQLPrepare might appear to have succeeded when in fact it has
failed."
It would seem that you can't rely on a SQLPrepare call to return errors,
since not all drivers support that functionality via SQLPrepare.
Thanks,
Mike C.
<craigkenisston@.hotmail.com> wrote in message
news:1109726936.969442.224550@.o13g2000cwo.googlegroups.com...
> Hi,
> When using an interface like ADO or ODBC, there is a call named
> "Prepare" which supposedly sent the statement to the server which in
> turn should parse it, save the query plan and return a handle to the
> client so subquent calls just refer to this handle/id and get faster
> execution in batches... is this correct?
> Well, my question is, when this Prepare or Parse is done, is there any
> physical check being performed by SQL Server ? I mean, does it check
> the column names, table existance, etc, or does it just check pure
> syntax ?
>|||Thank you !!

"Oracle Not Available" error from SQL

We have a DTS package utilizing an ODBC connection to and Oracle database to run a few updates. I can TNSPING the Oracle instance but get the error Ora-01034 ORACLE not available when trying to run the package.
Any ideas?DTS packages can run as scheduled tasks, on the server itself, or from a workstation. They may or may not have the same NT Credentials. My guess would be that you're running the task from two different machines (a workstation and a server), where one has Oracle connectivity and the other does not.

-PatP|||Pat, Thanks for the reply.

This is all running from our SQL server and is scheduled from there. When i am looking at the DTS package, specifically the connection i can not even close it without getting the error.|||Have you rebooted NT since you installed the Oracle drivers on your server?

-PatP|||We have been utilizing the Microsoft ODBC for Oracle drivers.|||Have you tried running xp_cmdshell stored procedure from sql server with something like 'tnsping oracle_server'?

exec master..xp_cmdshell 'tnsping oracle_server_name'

what is the result? it could help to determine the source of problem

btw we have been using microsoft ole db provider for oracle for two years in dts packages and it's absolutely problemless. but only for selects. mojza

"List of Values Failure" error in CR

I have designed a Crystal report from an ODBC connection. But recently I was asked to change the connection to JDBC. The data is from an SQL database. I have set the database location to JDBC from crystal report program. Everytime I preview the resuls I get the following error message.

"Prompting failed with the following error message: 'List of Values failure: fail to get values. [Cause of error: Failed to open connection. Failed to open the connection. UNKNOWN.rpt']. Error source: prompt.dll Error code: 0x8004380D."

Please help.Goto Database and Set Location and set the path
Do verify Database also