ODBC Error

I'm getting an error message I've never seen before when I installed my
application on a client's machine.
I've got seven machines at this location that all work fine, when I install
on a new machine, I can connect through ODBC Administrator; but, when I try
to connect through the program I get this error message:
[ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error
[ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionOpen(SECDoClientHandsh
ake()).
I've looked on KB and have not been able to find anything specific on this
problem.
Any ideas?
Thanks in advance.This might be the same issue reported in the KB article at the link
http://support.microsoft.com/defaul...b;en-us;Q322144
Regards,
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Dennis Powell" <dennis.powell@.monroe.XXXXXXXXX> wrote in message
news:OA6%23L0fYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> I'm getting an error message I've never seen before when I installed my
> application on a client's machine.
> I've got seven machines at this location that all work fine, when I
install
> on a new machine, I can connect through ODBC Administrator; but, when I
try
> to connect through the program I get this error message:
> [ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error
> [ODBC SQL Server Driver][TCP/IP
> Sockets]ConnectionOpen(SECDoClientHandsh
ake()).
> I've looked on KB and have not been able to find anything specific on
this
> problem.
> Any ideas?
> Thanks in advance.
>

ODBC drivers for sql server 2005, 64 bit

Hi,
I have a client server app that connects to sql server through ODBC, it
works fine when connecting to sql server 2005 32 bit, but when connecting to
sql server 64 bit from the client on Windows XP it behaves strangely, are
there separate set of odbc drivers that one has to use to connect to sql
server 2005 64 bit, even from 32 bit machine.
Thank you
VadimIt should be transparent to remote clients that the SQL Server is 64-bit.
The same client drivers can be used to connect to a 32 or 64-bit server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a client server app that connects to sql server through ODBC, it
> works fine when connecting to sql server 2005 32 bit, but when connecting
> to sql server 64 bit from the client on Windows XP it behaves strangely,
> are there separate set of odbc drivers that one has to use to connect to
> sql server 2005 64 bit, even from 32 bit machine.
> Thank you
> Vadim
>|||Thank you, Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:452AD768-B04F-4CD1-A54B-0C15E260ED3A@.microsoft.com...
> It should be transparent to remote clients that the SQL Server is 64-bit.
> The same client drivers can be used to connect to a 32 or 64-bit server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

ODBC drivers for sql server 2005, 64 bit

Hi,
I have a client server app that connects to sql server through ODBC, it
works fine when connecting to sql server 2005 32 bit, but when connecting to
sql server 64 bit from the client on windows xp it behaves strangely, are
there separate set of odbc drivers that one has to use to connect to sql
server 2005 64 bit, even from 32 bit machine.
Thank you
Vadim
It should be transparent to remote clients that the SQL Server is 64-bit.
The same client drivers can be used to connect to a 32 or 64-bit server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a client server app that connects to sql server through ODBC, it
> works fine when connecting to sql server 2005 32 bit, but when connecting
> to sql server 64 bit from the client on windows xp it behaves strangely,
> are there separate set of odbc drivers that one has to use to connect to
> sql server 2005 64 bit, even from 32 bit machine.
> Thank you
> Vadim
>
|||Thank you, Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:452AD768-B04F-4CD1-A54B-0C15E260ED3A@.microsoft.com...
> It should be transparent to remote clients that the SQL Server is 64-bit.
> The same client drivers can be used to connect to a 32 or 64-bit server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

ODBC drivers for sql server 2005, 64 bit

Hi,
I have a client server app that connects to sql server through ODBC, it
works fine when connecting to sql server 2005 32 bit, but when connecting to
sql server 64 bit from the client on windows xp it behaves strangely, are
there separate set of odbc drivers that one has to use to connect to sql
server 2005 64 bit, even from 32 bit machine.
Thank you
VadimIt should be transparent to remote clients that the SQL Server is 64-bit.
The same client drivers can be used to connect to a 32 or 64-bit server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a client server app that connects to sql server through ODBC, it
> works fine when connecting to sql server 2005 32 bit, but when connecting
> to sql server 64 bit from the client on windows xp it behaves strangely,
> are there separate set of odbc drivers that one has to use to connect to
> sql server 2005 64 bit, even from 32 bit machine.
> Thank you
> Vadim
>|||Thank you, Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:452AD768-B04F-4CD1-A54B-0C15E260ED3A@.microsoft.com...
> It should be transparent to remote clients that the SQL Server is 64-bit.
> The same client drivers can be used to connect to a 32 or 64-bit server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23MyV57ECHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a client server app that connects to sql server through ODBC, it
>> works fine when connecting to sql server 2005 32 bit, but when connecting
>> to sql server 64 bit from the client on windows xp it behaves strangely,
>> are there separate set of odbc drivers that one has to use to connect to
>> sql server 2005 64 bit, even from 32 bit machine.
>> Thank you
>> Vadim
>sql

ODBC Driver for SQLServer Everywhere/Compact from Access

So far I've been unable to connect to a test SQLServer Everywhere/Compact database with MS Access. I installed the new SQLServer 2005 Native Client to no avail. Has anyone done this?

If you are trying to sync the data between Access and the SQLServer Compact edition, you might need to download the synchronizer. The following link should provide you a quick overview and the access to the software. If this is not what you intended to do, please provide more details on what you are trying to accomplish:

http://www.microsoft.com/downloads/details.aspx?FamilyID=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&displaylang=en

Regards,

Riyaz

|||

In Access, I'm simply trying to link to a SQLServer CE database using an ODBC driver, as you can do with other SQLServer servers. I've been the route you suggested, hoping that in the process I'd discover the driver I need. But to install that synchronizer, I have to install IIS and SQLServer 2005, which goes beyond what I'm trying to do. And I'm not interested in syncronizing to a mobile device in any case.

I was hoping to use SQLServer CE as a substitute for the normal Access Jet engine, to take advantage of encryption and security that Access does not provide (or does poorly). I want to deploy a single-user Access application with an easily deployable secure database. I'm doing it now with just Access, but I'd like to make the data tables inaccessible except through my application.

Thanks for the suggestion.

|||You can only connect to an instance of a SQL Server 2005 Mobile Edition database when it is running on a Windows CE-based OS or Tablet PC (you can create a SQL Server 2005 Mobile database in Visual Studio and, I believe, SQL Server Management Studio as part of the development process). When SQL Server Everywhere becomes available, it should, as you've surmised, fit your needs. In the meantime, you can check out the development experience using SQL Mobile today...take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/sqleverywhere.asp?frame=true for some additional info. If you'd like to have a runtime prototype of your app, however, you'll want to try using SQL Server 2005 Express.|||

I do have SQL Server 2005 Everywhere installed, (SQL Server 2005 Compact Edition), and I've created a database from VS 2005 (after installing VS 2005 SP1 Beta) and so far have not been able to connect to that database from Access. There is a .dll distributed with Everywhere: sqlceoledb30.dll that has a description "OLEDB Provider" that I am hoping will work, but it is not installed as an ODBC driver.

It seems impossible that I'm the first to attempt this. Any help will be appreciated.

Office 2003 SP2, Win XP SP2, VS 2005 SP1 Beta

Thanks

|||

The naming is a little tricky here...SQL Server 2005 Mobile Edition is the 3.0 version of the product formerly referred to as SQL Server Compact Edition (or SQL CE). The product formerly referred to as SQL Server Everywhere now appears to have the moniker of SQL Server 2005 Compact Edition...this version is the only one in this family that can be used on the desktop. A CTP version of this product was released in late August and the RC1 version is now available at http://www.microsoft.com/downloads/details.aspx?FamilyId=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en

Given all of this, which version do you have? The RC1 file version appears to be 3.1 (the device only SQL Server 2005 Mobile Edition would be v3.0, I believe).

If you have the correct version but continue to have problems connecting, I'll move this thread into the SQL Server Compact Edition forum.

|||

If you read the last post I made, it's pretty clear that I have Compact Edition 3.1 RC1 Beta. I haven't ever installed the Mobile Edition. After installing CE, most references (like the installation directory) reflect the previous name (Everywhere).

I really want to link to a Compact Edition database through ODBC (as it happens, in Access). I can connect to it fine in VS 2005 VB.NET, so I don't think it's a lack of understanding.

Thanks, any help will be appreciated.

|||Thanks for claryifying the specific version that you have installed. Someone here in the SQL Server Compact Edition forum should be able to help you with the nuances of connecting to a local SQL Server CE database.|||

Although SQL CE 3.1 does expose an OLE DB provider, you cannot link to it via the regular ODBC link in Access. I have developed a set of tools that will help you with both converting to and from Access and also to edit the SDF on the desktop. These tools use the OLE DB provider at the low level and work with databases on the PC and on the device. You can try them here: http://www.primeworks-mobile.com

ODBC Driver for AS400

Hi,

I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.

Thanks,

VivekLook at www.hitsw.com. I've never used their drivers, but a friend of mine did and he praised their performance to the sky.

Regards,

hmscott

Hi,

I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.

Thanks,

Viveksql

ODBC Driver Error - Timeout Expired

Very common error but wired scenario. Every client machine get this error in morning. VB application works fine until evening but when everyone goes home after shut down the machine and they come back again in morning and try to run the application they get this error. Application runs fine, It can access data, pull data, view data but It can not write any data. (Other words can not enter any data).

Application again starts working fine after I copy database on different SQL server. For temporary solution I swap database from one SQL Server to other one day and back to original SQL server next day. Every morning it takes about 2 hours to copy database. Im doing this from last few days as working solution. FYI, I have 2 different VB application, each has their own database. One working fine and other started giving me problem, the one I described above.

Few thing I want to let you know:

Recently I changed the SQL server. After I changed I started having this problem. But other application working fine. So I dont think that could be a problem. (Both application basically same in terms of development and tools they use. VB and SQL Server, ODBC connection, Crystal Reports).

In old SQL server both database had daily backup on third party backup built on different server using Client Network Backup. After I changed the SQL server I never modify backup setting. So after I moved SQL server , every night backup was trying to connect to old SQL server and but It couldnt take the backup cause I changed the machine. Again if thats the problem both application should not work but one working fine other is giving me problem.

One more thing I want to mention here is I started having this problem when I left the SQL server copying database overnight. Means, I started copying database and I left the machine ON when I came in the morning copying database was done and I just click on the OK and close the window. Basically It has finished copying database in around 2 hours after I started and I close the window when I came back next morning.

Thats the few things Im thinking about but I dont know what kind of database setting this might have changed and how to reset again. Any help will appreciated.

Dose any one know how to combine .mdf (Primary data file) and .ndf (secondary data file) ?To answer your direct question, use DBCC SHRINKFILE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp) to empty the NDF file, then use ALTER DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp) to deactivate the NDF file. At that point, you can then simply delete the NDF file because it is no longer part of your database.

As a separate issue, I'd suspect that the third party backup is somehow causing your problem with users being unable to write data. I'm not sure how it is interfering. There are many third party backup solutions, with varying degrees of compatibility with SQL Server. Almost all of them work under their ideal conditions, almost all of them break down under other conditions.

Just as a point of curiousity, does the problem persist if you reboot NT on the machine that runs SQL Server? IF there is some problem with file locking, etc. that should clear up the problem for you a lot more quickly and simply than having to copy the database to another server. It would also give you some valuable information for trying to locate the real source of your problem.

-PatP|||Thanks for .ndf tip.

No, It wouldnt clear the problem if restart the NT machine. Just today I tried another solution, I dont know if its going to work but what I did is create the new database and import the data from the original database which already solved my .ndf file problem, now I have only one data file. I dont know if its going to work. I will find out tomorrow morning.

I also noticed one thing today someone created (there is couple of people have access to server) one more filegroup which is Primary_1. There was not any file in that group so I tried to delete that group but It would not delete It just kept freezing.

One question, If I created the new database with same name and import data using SQL server Import function where I choose Import all Objects. Is it going to have any problem in terms of data. I mean tables and views are all imported. It just changed 2 data file to one data file and also got rid of that Primary_1 file group. Just wondering if its going to make any difference in data. I mean I'm going to loose some data or anything like that. Application functions fine.

Thanks.sql

ODBC Data Source

I'm trying to import tables from COBOL thru Relativity client. With SQL Server 2000, I used Other (ODBC Data Source) to do this. I cannot find a comparible data source in SQL Server 2005. What do I do?

Where are you looking to find this data source?

Mike

|||In the Import wizard.|||

Thanks,

The Import wizard is not supported in SQL Express so this isn't the best place to get information about it. I'm going to move this thread into a forum where the folks who should be able to answer this question hang out.

Mike

|||Well, great! We use SQL Express. Won't my boss love this! Thanks for the info.|||

There's some very good reasons why SQLExpress is free. You don't get much with it

-Jamie

ODBC Connection Problem

Hello,
I have a user who is attempting to connect to SQL Server Enterprise 2000 SP3a running on Windows 2000 Server. The client has WIndows NT 4.0 with MDAC 2.8. The problem is that when I attempt to create the ODBC link , I receive the following error:
"connection failed
sqlstate '01000'
sql server error 770
Microsoft odbc sqlserver driver tcp/ip socket connection write
secencrypt data
Connection failed
sql state '08S01'
Sql server error 18 security error
SSL/Encryption is not forced on the Server and I have many other clients who have no issues in connecting.
If anyone has any suggestions, I would appreciate it.
Thanks
Brent
I am having the exact same problem. If you ever solve it please let me know.

ODBC Connection Problem

Hello,
I have a user who is attempting to connect to SQL Server Enterprise 2000 SP3a running on Windows 2000 Server. The client has WIndows NT 4.0 with MDAC 2.8. The problem is that when I attempt to create the ODBC link , I receive the following error:
"connection failed
sqlstate '01000'
sql server error 770
Microsoft odbc sqlserver driver tcp/ip socket connection write
secencrypt data
Connection failed
sql state '08S01'
Sql server error 18 security error
SSL/Encryption is not forced on the Server and I have many other clients who have no issues in connecting.
If anyone has any suggestions, I would appreciate it.
Thanks
Brent
Run cliconfg.exe. Is "force protocol encryption" checked?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Nope, not ticked.
-- Cindy Gross (MSFT) wrote: --
Run cliconfg.exe. Is "force protocol encryption" checked?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What's the EXACT error message you get and what are you doing when you get
it (secencrypt and 770 are not SQL messages)?
Are there messages in the SQL errorlog or any of the three Windows event
logs on the client or server?
Try each of the following to see if any succeed (if it's a default
instance, ignore the \InstanceName):
np:ServerName\InstanceName
tcp:ServerName\InstanceName
tcp:ServerName\InstanceName, port
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

ODBC Connection Problem

Hello,
I have a user who is attempting to connect to SQL Server Enterprise 2000 SP3
a running on Windows 2000 Server. The client has WIndows NT 4.0 with MDAC 2
.8. The problem is that when I attempt to create the ODBC link , I receive
the following error:
"connection failed
sqlstate '01000'
sql server error 770
Microsoft odbc sqlserver driver tcp/ip socket connection write
secencrypt data
Connection failed
sql state '08S01'
Sql server error 18 security error
SSL/Encryption is not forced on the Server and I have many other clients who
have no issues in connecting.
If anyone has any suggestions, I would appreciate it.
Thanks
BrentI am having the exact same problem. If you ever solve it please let me
know.
grummo
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message527750.html

ODBC Connection Issue on Client Site

Hi,

It might be a basic question but could you please help me on that?

Working Environment:

I have developed application on SQL Server 2005 and now wants to Run on Client Site, I am using access as a front end and SQL server 2005 back end through ODBC (System Dsn) which is working good. (user has been added on SQL Server and on Database)

Question:

1) Do I need to create new System Dsn Or other DSN (Keep in mind I have already create System DSN in Server) at each client site to access application from Server?

(If yes then I am trying to do that, it allow me to create (with required database which is on Server) and verify but doesn’t save on ODBC list so I can’t use that name to run appliation on client site)

I am using windows authentication.

Thanks for help in advance

It's done the issue was admin rights on the client's computer.

ODBC connection for client application to SQL Server 2005 Express installed on network computer

Hi All,

I've developed an application that connects to a SQL Server 2005 Express database. I created a DSN to connect to the database through ODBC. Currently, I am testing locally and everything works fine.

I would now like to install my application on another workstation and connect remotely to the database located on my development machine.

The client workstation does not have SQL Server 2005 Express installed on it because I would just like my application to connect remotely by creating the DSN and using ODBC. What I'm missing here are the database drivers. The "SQL Natice Client" is not available on this client workstation. How can I deploy the necessary drivers with my installation file so that I may create the required DSN name using the SQL Native Client driver?

Thanks!
Deployment of the SNAC can be found here:

http://msdn2.microsoft.com/en-us/library/ms131334.aspx

If you don′t use the new features of SQL Server 2005, you can also use the MDAC driver which is compatible with the Pre SQL 2005 features of SQL Server 2005.

My best practice is not to use the DSN rather than using direct connection strings which can be configured in configuration files.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply.

I can't seem to find the sqlncli.msi file on my drive. I have the Express edition installed. Is there somewhere else I can find this installer for the SNAC driver?

If not, which version of MDAC do I need, and how would I build my DSN? I agree with using connection strings instead of DSN's, unfortunately that is a decision I'm unable to change.

|||

SNAC: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc
it

Just get the newest MDAC, since it makes no difference if you get 2.7 or 2.8 but getting the newest will keep you away from old bugs :-)

The connectionstrings can be found on www.connectionstring.com (Where I also posted an example for the SNAC one :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Excellent,

I got it working using MDAC.

Thanks!

ODBC connection

Hi,
I would like to have a ODBC connection to a MSDE 2000 database on a Win 2003
server from a client PC. Can this be done?
On the client I can browse to the the SQL server, but I cannot connect to
it. Is there a way to connect and retreive the data ?
Arnold
What does it mean "cannot connect to it"? How do you do it, any error
message? Yes, there is way to connect to it, as long as you do it right.
The simplest way would be doing it from Control Panel->Administrative
Tools->ODBC Data Source and follows the wizard and make correct selection.
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:uFNlJ9vwHHA.3364@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I would like to have a ODBC connection to a MSDE 2000 database on a Win
> 2003 server from a client PC. Can this be done?
> On the client I can browse to the the SQL server, but I cannot connect to
> it. Is there a way to connect and retreive the data ?
> Arnold
>
|||Hi Norman,
I can do what you suggest on the server, but I cannot do it on a PC
connected to the same server.
If I connect from a PC I get a message that either the SQL database does not
exist, or the username and password is not OK.
When I type in exactly the same settings on the server it all works OK. (I
have not got the exact message for you since it is in Dutch)
So it seems that I am not allowed to connect to the database via ODBC on a
PC. Is that correct, or is there another way to do this ?
Arnold
"Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
news:e%23zxmswwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> What does it mean "cannot connect to it"? How do you do it, any error
> message? Yes, there is way to connect to it, as long as you do it right.
> The simplest way would be doing it from Control Panel->Administrative
> Tools->ODBC Data Source and follows the wizard and make correct selection.
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:uFNlJ9vwHHA.3364@.TK2MSFTNGP02.phx.gbl...
>
|||After Windows 2000 SP2, MSDE was blinded and its ports were shut off. This
prevents it from being attacked by the slammer virus? worm...-whatever. These
ports and protocols must be reenabled for the service to be visible on the
network. Yes, you can connect via ODBC but there are a lot of other
alternatives that are more efficient. Are you building a .NET application,
VB6 or what?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
> Hi Norman,
> I can do what you suggest on the server, but I cannot do it on a PC
> connected to the same server.
> If I connect from a PC I get a message that either the SQL database does
> not exist, or the username and password is not OK.
> When I type in exactly the same settings on the server it all works OK. (I
> have not got the exact message for you since it is in Dutch)
> So it seems that I am not allowed to connect to the database via ODBC on a
> PC. Is that correct, or is there another way to do this ?
> Arnold
> "Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
> news:e%23zxmswwHHA.4736@.TK2MSFTNGP04.phx.gbl...
>
|||OK, let us see what exactly you ran into:
1. Strat Control Panel->Administrative Tools->Data Sources(ODBC) applet; You
should be OK.
2. Click User DSN or SYSTEM DSN tab (Assume to create a System DSN). You
should be OK.
3. Click "Add Button" -> select "SQL Server" ->Click "Finish" to get you
into "Create a New Data Source to SQL Server" wizard. You should be OK.
4. Enter DSN name, Description and select the SQL Server from dropdown list
(you can also type SQL Server/instance name, too). You should be OK to go to
Next step.
5. Here is your turn to describe what you did on each step for next three
steps that leads you to failure.
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
> Hi Norman,
> I can do what you suggest on the server, but I cannot do it on a PC
> connected to the same server.
> If I connect from a PC I get a message that either the SQL database does
> not exist, or the username and password is not OK.
> When I type in exactly the same settings on the server it all works OK. (I
> have not got the exact message for you since it is in Dutch)
> So it seems that I am not allowed to connect to the database via ODBC on a
> PC. Is that correct, or is there another way to do this ?
> Arnold
> "Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
> news:e%23zxmswwHHA.4736@.TK2MSFTNGP04.phx.gbl...
>
|||Hello Bill, I will explain my challenge a little more:
I would like to connect a visio drawing to sharepoint. In Visio I have a
task flowchart that visually represents the same (project)tasks in
Sharepoint.
Visio has an excelent Database wizzard that perfectly connects the Visio
tasks to lets say Excel or Access, but I need a connection to the Sharepoint
SQL (MSDE) database for it.
Since I do not want to run the Visio application on the server, I need the
connection from a client to the SQL database on the server.
Do you have any more suggestions ?
Thanks in advance,
Arnold
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schreef in bericht
news:%23xzhe%23wwHHA.4568@.TK2MSFTNGP03.phx.gbl...
> After Windows 2000 SP2, MSDE was blinded and its ports were shut off. This
> prevents it from being attacked by the slammer virus? worm...-whatever.
> These ports and protocols must be reenabled for the service to be visible
> on the network. Yes, you can connect via ODBC but there are a lot of other
> alternatives that are more efficient. Are you building a .NET application,
> VB6 or what?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
>
|||Visio uses ODBC connection. You create ODBC connection to SQL Server exactly
the same way (the same wizard) as you do from Control Panel->Administrative
Tools->Data Source(ODBC).
To connect to SQL Server you need to:
1. Make sure the SQL Server can be connected from network. If you are not
sure, ask your network admin/database admin. If you installed the SQL
Server, you need to learn more beofre install it and use it.
2. Know the database name.
3. Ask the network admin/database admin for the SQL Server name, login
credential (if the SQL Server uses Windows security, make sure your windows
account has necessary access permission; if SQL server uses mixed security
mode, you can ask username/password instead). If you installed the SQL
Server, again, a bit more study on SQL Server security before using it.
The Wizard just does that: ask you SQL Server name, login credential, and
then database name. As long as you can supply them correctly, there
shouldn't any problem.
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:ena0PhywHHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hello Bill, I will explain my challenge a little more:
> I would like to connect a visio drawing to sharepoint. In Visio I have a
> task flowchart that visually represents the same (project)tasks in
> Sharepoint.
> Visio has an excelent Database wizzard that perfectly connects the Visio
> tasks to lets say Excel or Access, but I need a connection to the
> Sharepoint SQL (MSDE) database for it.
> Since I do not want to run the Visio application on the server, I need the
> connection from a client to the SQL database on the server.
> Do you have any more suggestions ?
> Thanks in advance,
> Arnold
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schreef in bericht
> news:%23xzhe%23wwHHA.4568@.TK2MSFTNGP03.phx.gbl...
>
|||Hello Norman, thanks for your reply.
My answers are in your reply:
"Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
news:%23xSVlAywHHA.1776@.TK2MSFTNGP03.phx.gbl...
> OK, let us see what exactly you ran into:
> 1. Strat Control Panel->Administrative Tools->Data Sources(ODBC) applet;
> You should be OK.
OK

> 2. Click User DSN or SYSTEM DSN tab (Assume to create a System DSN). You
> should be OK.
No problem

> 3. Click "Add Button" -> select "SQL Server" ->Click "Finish" to get you
> into "Create a New Data Source to SQL Server" wizard. You should be OK.

> 4. Enter DSN name, Description and select the SQL Server from dropdown
> list (you can also type SQL Server/instance name, too). You should be OK
> to go to Next step.
Also no problem. My name is : SERVER-FILE\MICROSOFT##SSEE
Server-File is the name of our Server, and MICROSOFT##SSEE is the standard
name Sharepoint give to the database.

> 5. Here is your turn to describe what you did on each step for next three
> steps that leads you to failure.
I can choose between Windows NT verification, or SQL server verification. If
I choose NT verification, I get this message (not an exact reply, since it
is in Dutch):
Connection failure:
SQL State 01000
SQL Server error 223
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failure:
SQL State: 08001
SQL Server error 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL-server does not exist, or
the login is refused (someting like that).
The same thing goed for a SQL server verification. I would not know that
username and pass to fill in here. The administrator login does not work
here.
If I do this on Server-file however, I do get a connection.
Arnold
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Norman,
I am the administratror and have all the rights to my network/database
admin.
I have not installed the server myself. A third IT party did that for us.
They also do not know why I cannot connect directly from a PC. They did not
put in a specific password. It is a standard installation.
Furthermore the SQL server works perfectly OK on the Win 2003 server. We are
using this for more than two years. The only problem I have is making a
connection from ODBC on a PC.
Arnold
"Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
news:uExHC2ywHHA.4308@.TK2MSFTNGP02.phx.gbl...
> Visio uses ODBC connection. You create ODBC connection to SQL Server
> exactly the same way (the same wizard) as you do from Control
> Panel->Administrative Tools->Data Source(ODBC).
> To connect to SQL Server you need to:
> 1. Make sure the SQL Server can be connected from network. If you are not
> sure, ask your network admin/database admin. If you installed the SQL
> Server, you need to learn more beofre install it and use it.
> 2. Know the database name.
> 3. Ask the network admin/database admin for the SQL Server name, login
> credential (if the SQL Server uses Windows security, make sure your
> windows account has necessary access permission; if SQL server uses mixed
> security mode, you can ask username/password instead). If you installed
> the SQL Server, again, a bit more study on SQL Server security before
> using it.
> The Wizard just does that: ask you SQL Server name, login credential, and
> then database name. As long as you can supply them correctly, there
> shouldn't any problem.
>
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:ena0PhywHHA.4228@.TK2MSFTNGP06.phx.gbl...
>
|||"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:%23H%23b1s6wHHA.1168@.TK2MSFTNGP02.phx.gbl...
> Hello Norman, thanks for your reply.
> My answers are in your reply:
> "Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
> news:%23xSVlAywHHA.1776@.TK2MSFTNGP03.phx.gbl...
> OK
>
> No problem
>
> Also no problem. My name is : SERVER-FILE\MICROSOFT##SSEE
> Server-File is the name of our Server, and MICROSOFT##SSEE is the standard
> name Sharepoint give to the database.
>
> I can choose between Windows NT verification, or SQL server verification.
> If I choose NT verification, I get this message (not an exact reply, since
> it is in Dutch):
> Connection failure:
> SQL State 01000
> SQL Server error 223
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> Connection failure:
> SQL State: 08001
> SQL Server error 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL-server does not exist, or
> the login is refused (someting like that).
It is obvious that the user account you used on the client PC, where you
want to create a ODBC connection to SQL Server, does not have access to the
SQL Server. Since you alread passed previous step (i.e. you have selected
the SQL Server name from a list), we are sure the SQL Server DOES exists and
can be seen by the client PC. So, you are facing SQL Server security
problem. No offence, I recommend you study a bit more on SQL Server
security, especially how to create SQL Server login, Database user and map
Windows/Domain user account to logins and users. If you kown a window/domain
user account can access the sql server/database, then use that account to
log on the PC. If you are to create System DSN, you need to use an admin
account.
Most likely, if you log on the PC as domain admin, you would be able to
access the database. Or, you could log on to the server computer, where the
SQL Server sits, as local admin, you should be able to get into the SQL
Server, then you can create necessary SQL Server logins/database users, and
map them to different computer users/groupd on your network/domain. Again,
do the due study before doing so, you do not want to open the SQL Server too
widely to put in risk.

> The same thing goed for a SQL server verification. I would not know that
> username and pass to fill in here. The administrator login does not work
> here.
If you choose SQL Server authentication, you have to make sure the SQL
Server's mixed security mode is enabled (it is disable by default). If not
enable, of course you can not use it. You must know a login's
username/password in pair, which is not the windows username/password,
whether it is admin or not.

> If I do this on Server-file however, I do get a connection.
> Arnold
>
>
sql

ODBC Connect Problem w/2005

I have attempted to establish ODBC connections w/ the Microsoft SQL
Native Client Version 09.00.1399 (from 2 different machines) and get the
following error:
Connection Failed:
SQLState: '08001'
SQL Server Error: 1326
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [1326]
Connectio Failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login Timeout Expired
Connection Failed:
SQLState: '08001'
SQL Server Error: 1326
Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure
may be caused by the fact that under the default setting SQL Server does
not allow remote connections.
* Win2003 Server 64bit; SQL Server 2005 x64
* SQL Server authentication
* Allow Remote connections is checked on the SQL Server
* The clients show driver ver 09.00.1399 in the ODBC Sources, the
Server's ODBC Sources shows ver 09.00.1399.00, although the SQL Server
2005 shows 09.00.1399.06.
thanks!
GregC
Hi,
Following things you need to check:
1. If SQL Browser is enabled and started.
2. If firewall is on, you need to put sql server and sql browser in
exception list. Regarding this, you may refer to following two method:
#1. Get IP and port of target SQL Server, suppose it is 123.123.123.123 and
1433 ->
Test connect using osql and target of Stcp:123.123.123.123,1433, like
so:
osql -Stcp:123.123.123.123,1433 -E
#2. Try telnet to port as well, if this fails it is firewall:
telnet 123.123.123.123 1433
Best regards,
Vincent Xu
Microsoft Online Partner Support
================================================== ====
Get Secure! - www.microsoft.com/security
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties,and confers no rights.
================================================== ====
--[vbcol=seagreen]
19:52:44 CST)[vbcol=seagreen]
TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!border2.nntp.dca.giganews.com!nntp.giganews. com!cyclone.austin.rr.com!
news.rr.com!tornado.texas.rr.com.POSTED!53ab2750!n ot-for-mail[vbcol=seagreen]

ODBC Connect Problem w/2005

I have attempted to establish ODBC connections w/ the Microsoft SQL
Native Client Version 09.00.1399 (from 2 different machines) and get the
following error:
Connection Failed:
SQLState: '08001'
SQL Server Error: 1326
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
connection to SQL Server [1326]
Connectio Failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login Timeout Expired
Connection Failed:
SQLState: '08001'
SQL Server Error: 1326
Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure
may be caused by the fact that under the default setting SQL Server does
not allow remote connections.
* Win2003 Server 64bit; SQL Server 2005 x64
* SQL Server authentication
* Allow Remote connections is checked on the SQL Server
* The clients show driver ver 09.00.1399 in the ODBC Sources, the
Server's ODBC Sources shows ver 09.00.1399.00, although the SQL Server
2005 shows 09.00.1399.06.
thanks!
GregCHi,
Following things you need to check:
1. If SQL Browser is enabled and started.
2. If firewall is on, you need to put sql server and sql browser in
exception list. Regarding this, you may refer to following two method:
#1. Get IP and port of target SQL Server, suppose it is 123.123.123.123 and
1433 ->
Test connect using osql and target of Stcp:123.123.123.123,1433, like
so:
osql -Stcp:123.123.123.123,1433 -E
#2. Try telnet to port as well, if this fails it is firewall:
telnet 123.123.123.123 1433
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
19:52:44 CST)[vbcol=seagreen]
TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!border2.nntp.dca.giganews.com!nntp.giganews.com!cyclone.austin.rr.com!
news.rr.com!tornado.texas.rr.com.POSTED!53ab2750!not-for-mail[vbcol=seagreen]sql

ODBC and SQLServer : does I need the client installed on ?

Hi,

Does I need to install the mssql client binaries to use ODBC to
connect to a mssql database ?

TYIA
rjp"rjp" <rjp.l@.laposte.net> wrote in message
news:9b7a7215.0402100934.5fe5b830@.posting.google.c om...
> Hi,
> Does I need to install the mssql client binaries to use ODBC to
> connect to a mssql database ?
> TYIA
> rjp

You should be able to use the latest version of MDAC - there's a link from
this page:

http://msdn.microsoft.com/data/

Simon|||rjp (rjp.l@.laposte.net) writes:
> Does I need to install the mssql client binaries to use ODBC to
> connect to a mssql database ?

ODBC comes with MDAC that comes with Windows, so you don't need to install
anything - if you are on a Windows box, that is. (And it's a decently
modern version of Windows. Windows 95 is not likely to have any MDAC.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ODBC and SQL 2000

We have a MFC app that uses ODBC to connect to the DB. Now let me get down
to the problem. We had a client which keeps saying Invalid object name
'Docs_Main'. And we have another client which works fine. Good ole SQL
profiler time to figure out what's going on. So we run our query and we get
this for the working client:
declare @.P1 int
set @.P1=84
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
And for the client not working we get
declare @.P1 int
set @.P1=NULL
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
The only difference appears to be in the set @.P1 = x statement. If that's
set to NULL does that cause problems, and why is that being set to NULL.
The program it's coming from is the same one.
Lance JohnsonWhy should the program explicitly set an output parameter in either case?
You would think it is an output parameter because you're interested in
seeing the output, not because you want to control the input.
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> We have a MFC app that uses ODBC to connect to the DB. Now let me get
down
> to the problem. We had a client which keeps saying Invalid object name
> 'Docs_Main'. And we have another client which works fine. Good ole SQL
> profiler time to figure out what's going on. So we run our query and we
get
> this for the working client:
> declare @.P1 int
> set @.P1=84
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> And for the client not working we get
> declare @.P1 int
> set @.P1=NULL
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> The only difference appears to be in the set @.P1 = x statement. If that's
> set to NULL does that cause problems, and why is that being set to NULL.
> The program it's coming from is the same one.
>
> Lance Johnson
>|||Well I don't really know what that @.P1 is for, but shouldn't it be the same
for all clients, meaning if one client has a number, another client would
have a number and not NULL. I've looked around for info on these prepare
statements so I could learn what's happening, but these aren't easily
findable and there's no mention in sql books online of them.
Lance Johnson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:egz6PuMnDHA.3288@.tk2msftngp13.phx.gbl...
> Why should the program explicitly set an output parameter in either case?
> You would think it is an output parameter because you're interested in
> seeing the output, not because you want to control the input.
>
>
> "Lance Johnson" <ljohnson@.docs.com> wrote in message
> news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> > We have a MFC app that uses ODBC to connect to the DB. Now let me get
> down
> > to the problem. We had a client which keeps saying Invalid object name
> > 'Docs_Main'. And we have another client which works fine. Good ole SQL
> > profiler time to figure out what's going on. So we run our query and we
> get
> > this for the working client:
> >
> > declare @.P1 int
> > set @.P1=84
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > And for the client not working we get
> >
> > declare @.P1 int
> > set @.P1=NULL
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > The only difference appears to be in the set @.P1 = x statement. If
that's
> > set to NULL does that cause problems, and why is that being set to NULL.
> > The program it's coming from is the same one.
> >
> >
> >
> > Lance Johnson
> >
> >
>|||> Well I don't really know what that @.P1 is for, but shouldn't it be the
same
> for all clients, meaning if one client has a number, another client would
> have a number and not NULL.
I really don't know enough about your design to agree with you.
> I've looked around for info on these prepare
> statements so I could learn what's happening, but these aren't easily
> findable and there's no mention in sql books online of them.
You could always google;
http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&q=sp_prepexec&btnG=Google+Search|||Hi Lance,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your conservation with Aaron, I understand that you would like to know what the stored
procedure sp_prepexec is and why it cannot appear with the same value on the same
parameter @.p1. Moreover, you want to avoid this and then make both your clients work fine.
Have I fully understood you, Lance? If there is anything I misunderstood, please feel free to
post it in the newsgroup to let me know.
In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that provides server
support for combining an execution plan preparation and executing it into one call. If you set
"Prepared" property in Application, the application might send this command into SQL Server.
From your description, however, I'm unsure of your SQL Server and Service Pack' s version,
whether or not you used some local temp tables and the data types in your table. Detailed
information that you can provide will make things clear and help us move closer to the causes
and resolutions.
However, I'm still eager to assist you with this issue according to my experience on the
SP_PREPEXEC.
First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed the "Preparing a
Statement That References a Missing Object Incorrectly" problem. You can download this
service pack via:
http://www.microsoft.com/sql/downloads/sp4.asp
Additionally, if you use some local temp tables, the Prepare will fail since we cannot perform
the SELECT statement because the object doesn't exist yet. In this case, you should use
global temp table or a permanent table.
If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it will start using
sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
SQLExecute commands from ODBC, which was done to eliminate round trips to the server
whenever possible.
This is not documented anywhere as this is an implementation done at the server level and
should be transparent to the user. If Prepare first and Execute later were somehow forced, the
Prepare would fail in SQL 2000 as well. In this case, please make sure that your server is able
to Prepare and Execute AT THE SAME TIME.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||We are currently using SQL 2000 with SP3a. We are not using temp tables.
The tables are regular tables. Let me know if you need more info in this
area. Following is the code that we are using. Some of it is not
explicitly obvious and this is not my code, so I'm just passing it along.
Let me know what else you might need to know.
BOOL CSW_UsersCtrl::DoWeTurnOnSecurity()
{
BOOL SecurityOn = FALSE;
try
{
CSOAPwareSettings theDB(g_DB_USERS);
theDB.m_strFilter = "Item_ID like '";
theDB.m_strFilter += DOCS_SECURITY_COUNT_ID;
theDB.m_strFilter += "'";
try{
theDB.Open();
}catch(CException* e){
DisplayErrorMessage("DoWeTurnOnSecurity #1",e);
return FALSE;
}
...more code but it's failing above
}
So the g_DB_Users is just the access to our database where the table exists.
And the error is coming from the catch statement so an error is being
generated from the call to open theDB.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:HwZRGjTnDHA.1804@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your conservation with Aaron, I understand that you would like to
know what the stored
> procedure sp_prepexec is and why it cannot appear with the same value on
the same
> parameter @.p1. Moreover, you want to avoid this and then make both your
clients work fine.
> Have I fully understood you, Lance? If there is anything I misunderstood,
please feel free to
> post it in the newsgroup to let me know.
> In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that
provides server
> support for combining an execution plan preparation and executing it into
one call. If you set
> "Prepared" property in Application, the application might send this
command into SQL Server.
> From your description, however, I'm unsure of your SQL Server and Service
Pack' s version,
> whether or not you used some local temp tables and the data types in your
table. Detailed
> information that you can provide will make things clear and help us move
closer to the causes
> and resolutions.
> However, I'm still eager to assist you with this issue according to my
experience on the
> SP_PREPEXEC.
> First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed
the "Preparing a
> Statement That References a Missing Object Incorrectly" problem. You can
download this
> service pack via:
> http://www.microsoft.com/sql/downloads/sp4.asp
> Additionally, if you use some local temp tables, the Prepare will fail
since we cannot perform
> the SELECT statement because the object doesn't exist yet. In this case,
you should use
> global temp table or a permanent table.
> If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it
will start using
> sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
> SQLExecute commands from ODBC, which was done to eliminate round trips to
the server
> whenever possible.
> This is not documented anywhere as this is an implementation done at the
server level and
> should be transparent to the user. If Prepare first and Execute later were
somehow forced, the
> Prepare would fail in SQL 2000 as well. In this case, please make sure
that your server is able
> to Prepare and Execute AT THE SAME TIME.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Lance,
Thank you for your feedback! Now it's clear to me that you are using SQL Server 2000 SP3a,
not using any temp tables and there are different users accessing to your DB.
Based on my knowledge, the issue is not related to the code you are using. I agree with you
we can pass it along. In my opinion, it's also not a server side issue.
As I mentioned before, SP_PREPEXEC first PREPARE for the execution plan (can be re-used
later) and then perform the EXECUTE in the future. All the parameters passing processed are
on the client.
That means the provider on the client produces the code (you trace in the original message)
and sends it to the server provider. Therefore, the problem may be located in provider
mismatch between client and server.
According to my experience, you should first check both clients' Microsoft Data Access
Components (MDAC) version to see if it matched the server's MDAC version. I think the
working client can be suited, but it's hard to make sure if the failing client's MDAC is also ok.
Here I provide you a tool from Microsoft to check the MDAC's version for your convenience:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-
14332EF092C9&displaylang=en
Please download it and I provide you step-by-step directions to help check the clients' MDAC
version:
1. Extract the executable file to the WORKING client you want to check
2. In the extracted folder, click cc.exe to run the MDAC check tool
3. In the "Component Check Dialog", select "perform analysis of machine and automatically
determine the release version". It's the default selected option.
4. Click OK on the Dialog and let the tool to check the client's MDAC version
5. Extract the executable file to the FAILING client you want to check and then follow the 2-4
steps above to check another MDAC version on your failing client.
After that, you can compare the version to see if the two clients had different MDAC.
To workaround the issue, I provide you with the following suggestion:
1. Download the working client's MDAC and apply it to the failing client to see if the problem
can be resolved
2. On the other hand, note that your server is SQL Server 2000 SP3, you can also apply MDAC
2.7 SP1 Refresh on your client to meet the provider match.
3. For downloading the MDAC, please reference the following address for detailed
information:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Lance, please apply my suggestion above and let me know if it helps you resolve your
problem. If there is anything more I can assist you with, please feel free to post it in the group.
Thank you for using MSDN newsgroup!
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||This was actually one of our first suspicions and we
installed MDAC 2.8 on this machine to make sure it was up
to date. I proceeded to run component checker on this
machine and all was OK it seemed. One more thing to help
in understanding this. The machine this runs on has MSDE
installed and it does the same thing when working with
our DB on that local MSDE or another MSDE/SQL from
another computer. So it's not between 1 particular
server, but for any MSDE server. Thanks for the
information you're provided so far. I hope we can
somehow figure out what's causing this.
Lance Johnson
>--Original Message--
>Hello Lance,
>Thank you for your feedback! Now it's clear to me that
you are using SQL Server 2000 SP3a,
>not using any temp tables and there are different users
accessing to your DB.
>Based on my knowledge, the issue is not related to the
code you are using. I agree with you
>we can pass it along. In my opinion, it's also not a
server side issue.
>As I mentioned before, SP_PREPEXEC first PREPARE for the
execution plan (can be re-used
>later) and then perform the EXECUTE in the future. All
the parameters passing processed are
>on the client.
>That means the provider on the client produces the code
(you trace in the original message)
>and sends it to the server provider. Therefore, the
problem may be located in provider
>mismatch between client and server.
>According to my experience, you should first check both
clients' Microsoft Data Access
>Components (MDAC) version to see if it matched the
server's MDAC version. I think the
>working client can be suited, but it's hard to make sure
if the failing client's MDAC is also ok.
>Here I provide you a tool from Microsoft to check the
MDAC's version for your convenience:
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=8F0A8DF6-4A21-4B43-BF53-
>14332EF092C9&displaylang=en
>Please download it and I provide you step-by-step
directions to help check the clients' MDAC
>version:
>1. Extract the executable file to the WORKING client you
want to check
>2. In the extracted folder, click cc.exe to run the MDAC
check tool
>3. In the "Component Check Dialog", select "perform
analysis of machine and automatically
>determine the release version". It's the default
selected option.
>4. Click OK on the Dialog and let the tool to check the
client's MDAC version
>5. Extract the executable file to the FAILING client you
want to check and then follow the 2-4
>steps above to check another MDAC version on your
failing client.
>After that, you can compare the version to see if the
two clients had different MDAC.
>To workaround the issue, I provide you with the
following suggestion:
>1. Download the working client's MDAC and apply it to
the failing client to see if the problem
>can be resolved
>2. On the other hand, note that your server is SQL
Server 2000 SP3, you can also apply MDAC
>2.7 SP1 Refresh on your client to meet the provider
match.
>3. For downloading the MDAC, please reference the
following address for detailed
>information:
>http://msdn.microsoft.com/library/default.asp?
url=/downloads/list/dataaccess.asp
>Lance, please apply my suggestion above and let me know
if it helps you resolve your
>problem. If there is anything more I can assist you
with, please feel free to post it in the group.
>Thank you for using MSDN newsgroup!
>
>Best regards,
>Billy Yao
>Microsoft Online Partner Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>|||Hi Lance,
Thank you for your update!
From your description, I know that you have already installed MDAC 2.8 on this machine.
However, I'm unsure what here "this machine" represents for. Does it mean the machine your
server and DB located, or mean the machine your problematic client located?
Looking through your original message in this post, I notice that there were two clients
connecting to the server and fetch the data from your DB. One worked fine and the other failed
with "Invalid object name 'Docs_Main' " error message.
As we discussed before, the provider issue might be not on the server side machine but on
the client side machine. Therefore, I provided the MDAC check tool for your convenience to
distinguish the MDAC version between these two clients. If the versions are different, this may
be the cause that one was successful and the other was failed.
It was recommended that you applied suitable (not latest) MDAC on the problematic client
side machine. This "suitable MDAC" means you can apply "the MDAC version on the working
client" to the failing client.
Another workaround you can perform is to force sp_unprepare explicitly. In this way,
SP_PREPEXEC will not prepare the execution plan and execute the script directly.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Sorry. Let me clarify what I mean by that machine. Our problematic client
(let's call it Laptop) has the MDAC 2.8 and we have MSDE installed on it.
It was having this problem, so we tried connecting it to another machine's
MSDE (let's call it Desktop). The same result occurred. So on Desktop we
connected to the 2 different MSDE's and it worked on both. So if the client
is connecting to a local MSDE the MDAC shouldn't (I believe) be getting in
the way. We connected to another machine's SQL just so we could run SQL
Profiler and get more info. Thanks much.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:XIC4jsjnDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your update!
> From your description, I know that you have already installed MDAC 2.8 on
this machine.
> However, I'm unsure what here "this machine" represents for. Does it mean
the machine your
> server and DB located, or mean the machine your problematic client
located?
> Looking through your original message in this post, I notice that there
were two clients
> connecting to the server and fetch the data from your DB. One worked fine
and the other failed
> with "Invalid object name 'Docs_Main' " error message.
> As we discussed before, the provider issue might be not on the server side
machine but on
> the client side machine. Therefore, I provided the MDAC check tool for
your convenience to
> distinguish the MDAC version between these two clients. If the versions
are different, this may
> be the cause that one was successful and the other was failed.
> It was recommended that you applied suitable (not latest) MDAC on the
problematic client
> side machine. This "suitable MDAC" means you can apply "the MDAC version
on the working
> client" to the failing client.
> Another workaround you can perform is to force sp_unprepare explicitly. In
this way,
> SP_PREPEXEC will not prepare the execution plan and execute the script
directly.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lance,
Thank you for your detailed explanation.
Now it's clear that the problem is addressed on the client provider- Laptop as all the clients
could connect the MSDEs except for this problematic one.
Although you apply the MDAC 2.8 on the problematic client, the symptom may also occur with
unexpected causes. The better way to work around this issue is to apply the same MDAC
version of the WORKING client to the f problematic client (Laptop), which ensures the Laptop's
provider to use the working MDAC that can connect to the server successfully.
Please re-check your working client's MDAC version and download THIS MDAC from:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Then uninstall the MDAC 2.8 on the problematic client (Laptop), and install the working client's
MDAC you've downloaded. Moreover, make sure the problematic client's
configuration/environment (mainly related to provider) is the same one as the working client.
Enable them identical if possible.
To uninstall the MDAC and reconfigure it, please refer to the following article:
307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
http://support.microsoft.com/?id=307255
Another workaround I mentioned in the previous message is to force sp_unprepare explicitly.
In this way, SP_PREPEXEC will not prepare the execution plan and perform execution directly.
Lance, please help narrow down the problem and apply my recommendation above to see if
this solves your problem. If there is anything more I can assist you with, please feel free to post
in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hey Lance,
For more information, you should close all applications that use ODBC in order for the MDAC
installation to succeed, as all later version of ODBC components will be installed when
applying new MDAC. Applications that use ODBC include Microsoft Internet Information Server
(IIS), Microsoft Systems Management Server, Microsoft Access, and Oracle database
applications.
The following articles help you troubleshoot the MDAC setup and list various versions of
MDAC for the SQL Server ODBC driver:
232060 HOWTO: MDAC Setup Troubleshooting Guide
http://support.microsoft.com/?id=232060
219293 INFO: ODBC, SQL Server, and Jet Versions Shipped with MDAC
http://support.microsoft.com/?id=219293
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can't seem to uninstall the current MDAC. This must refer to an older
Component Checker functionality because when I run my current one with that
/d flag it doesn't let me reconfigure or anything. Since I couldn't do
that, I installer 2.8 MDAC on one of my other machines and it seems to work
fine with my application. I'm a little bit hazy on this sp_unprepare stuff.
Do you have any links about this. We have never had to force this before so
we're not accustomed to doing this.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:PcqgbEqnDHA.2088@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your detailed explanation.
> Now it's clear that the problem is addressed on the client provider-
Laptop as all the clients
> could connect the MSDEs except for this problematic one.
> Although you apply the MDAC 2.8 on the problematic client, the symptom may
also occur with
> unexpected causes. The better way to work around this issue is to apply
the same MDAC
> version of the WORKING client to the f problematic client (Laptop), which
ensures the Laptop's
> provider to use the working MDAC that can connect to the server
successfully.
> Please re-check your working client's MDAC version and download THIS MDAC
from:
>
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
> Then uninstall the MDAC 2.8 on the problematic client (Laptop), and
install the working client's
> MDAC you've downloaded. Moreover, make sure the problematic client's
> configuration/environment (mainly related to provider) is the same one as
the working client.
> Enable them identical if possible.
> To uninstall the MDAC and reconfigure it, please refer to the following
article:
> 307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
> http://support.microsoft.com/?id=307255
> Another workaround I mentioned in the previous message is to force
sp_unprepare explicitly.
> In this way, SP_PREPEXEC will not prepare the execution plan and perform
execution directly.
> Lance, please help narrow down the problem and apply my recommendation
above to see if
> this solves your problem. If there is anything more I can assist you with,
please feel free to post
> in the group.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lance
Thank you for your update. Your feedback proves again that the cause of the problem is on
the problematic machine, not on any other machines.
The MDAC Component Checker worked fine on my side and could uninstall MDAC 2.8
according to KB 307255. It's really strange that it didn't let you reconfigure the version. Well, as
MDAC 2.8 did work fine on another machine, I suggest you re-install the same MDAC 2.8 on
the machine. The reason why we should do this is the pervious MDAC 2.8 may not be applied
properly on the client. Please re-apply it.
As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC) like sp_prepexec,
which the latter provides server support for combining an execution plan preparation and
executing it into one call.
There is no outer resource you can refer to because it's interal. Here I provide you a simple
sample to picture how to apply sp_unprepare explicitly:
/************************* Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these when opening a
default recordset (client cursor): forward only, read only, rowset size = 1 They must also use
SQLPrepare/SQLExecute (ODBC) or ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select *
from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec sp_unprepare @.P1
go
Please run the script above on the problematic client machine and the server machine to see
if it can work fine. If it works fine, you can apply this explicit sp_prepare, sp_execute and
sp_unprepare instead of sp_prepexec.
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Re-post the untidy sample
======================
/*************************
Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these
when opening a default recordset (client cursor): forward only, read only,
rowset size = 1
They must also use SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int
set @.P1=NULL
exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select * from titles where
title_id = @.P1'
exec sp_execute @.P1, 'MC2222'
exec sp_unprepare @.P1
go|||According to this link on component checker
Special Considerations for Windows 2000, Windows Me, and Later Versions of
Windows
You can use Component Checker on Windows 2000, Windows Me, and later
versions of Windows to identify the version of MDAC and identify problems
with the installation. However, the Reconfigure MDAC components menu option
does not appear when you run Component Checker on these versions of Windows,
even if you use the /d switch. Because MDAC is installed as part of the core
functionality in Windows 2000, Windows Me, and later versions of Windows, it
is impossible to remove MDAC from the operating system. It is also
impossible to reinstall the current version of MDAC without reinstalling the
entire operating system.
So since I'm running XP Pro on this laptop, I can't do this reconfigure. I
have re-run the MDAC installation and it hasn't seemed to help.
One more note. I couldn't get the query you specified to work but I was
able to get it to work using the sp_prepexec. So what does that mean. ODBC
is throwing the error and we have no idea why? The app is actually
connecting to another db before this one and that seems to work fine. So
where do we go from here. We really can't practically redo all of our
queries as you specified since we have a ton of those and this is only
happening on one client's machine.
Thanks for the info you have provided so far.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> Hi Lance
> Thank you for your update. Your feedback proves again that the cause of
the problem is on
> the problematic machine, not on any other machines.
> The MDAC Component Checker worked fine on my side and could uninstall MDAC
2.8
> according to KB 307255. It's really strange that it didn't let you
reconfigure the version. Well, as
> MDAC 2.8 did work fine on another machine, I suggest you re-install the
same MDAC 2.8 on
> the machine. The reason why we should do this is the pervious MDAC 2.8 may
not be applied
> properly on the client. Please re-apply it.
> As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
like sp_prepexec,
> which the latter provides server support for combining an execution plan
preparation and
> executing it into one call.
> There is no outer resource you can refer to because it's interal. Here I
provide you a simple
> sample to picture how to apply sp_unprepare explicitly:
> /************************* Using sp_prepare/sp_execute
> Note applications would not call these directly, but would only get these
when opening a
> default recordset (client cursor): forward only, read only, rowset size =1 They must also use
> SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
> (OLEDB)
> If the calls are not prepared, the query will simply be executed normally.
> ***************************/
> use pubs
> go
> declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
varchar(8)', N'select *
> from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
sp_unprepare @.P1
> go
> Please run the script above on the problematic client machine and the
server machine to see
> if it can work fine. If it works fine, you can apply this explicit
sp_prepare, sp_execute and
> sp_unprepare instead of sp_prepexec.
>
> Please feel free to post in the group if this solves your problem or if
you would like further
> assistance.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||I found my problem. We use system DSNs. However there was an errant entry
in the registry for User DSNs with that same DSN name. This entry did not
show up in User DSNs tab for ODBC so I didn't know it existed. There wasn't
much to it except the default DB, which was set to master. That'll teach me
to not look in profiler at what db is being queried.
Lance Johnson
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:uJxL659nDHA.2068@.TK2MSFTNGP09.phx.gbl...
> According to this link on component checker
> Special Considerations for Windows 2000, Windows Me, and Later Versions of
> Windows
> You can use Component Checker on Windows 2000, Windows Me, and later
> versions of Windows to identify the version of MDAC and identify problems
> with the installation. However, the Reconfigure MDAC components menu
option
> does not appear when you run Component Checker on these versions of
Windows,
> even if you use the /d switch. Because MDAC is installed as part of the
core
> functionality in Windows 2000, Windows Me, and later versions of Windows,
it
> is impossible to remove MDAC from the operating system. It is also
> impossible to reinstall the current version of MDAC without reinstalling
the
> entire operating system.
> So since I'm running XP Pro on this laptop, I can't do this reconfigure.
I
> have re-run the MDAC installation and it hasn't seemed to help.
> One more note. I couldn't get the query you specified to work but I was
> able to get it to work using the sp_prepexec. So what does that mean.
ODBC
> is throwing the error and we have no idea why? The app is actually
> connecting to another db before this one and that seems to work fine. So
> where do we go from here. We really can't practically redo all of our
> queries as you specified since we have a ton of those and this is only
> happening on one client's machine.
> Thanks for the info you have provided so far.
> Lance
>
> "Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
> news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> > Hi Lance
> >
> > Thank you for your update. Your feedback proves again that the cause of
> the problem is on
> > the problematic machine, not on any other machines.
> >
> > The MDAC Component Checker worked fine on my side and could uninstall
MDAC
> 2.8
> > according to KB 307255. It's really strange that it didn't let you
> reconfigure the version. Well, as
> > MDAC 2.8 did work fine on another machine, I suggest you re-install the
> same MDAC 2.8 on
> > the machine. The reason why we should do this is the pervious MDAC 2.8
may
> not be applied
> > properly on the client. Please re-apply it.
> >
> > As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
> like sp_prepexec,
> > which the latter provides server support for combining an execution plan
> preparation and
> > executing it into one call.
> >
> > There is no outer resource you can refer to because it's interal. Here I
> provide you a simple
> > sample to picture how to apply sp_unprepare explicitly:
> >
> > /************************* Using sp_prepare/sp_execute
> > Note applications would not call these directly, but would only get
these
> when opening a
> > default recordset (client cursor): forward only, read only, rowset size
=> 1 They must also use
> > SQLPrepare/SQLExecute (ODBC) or
> ICommandText::Prepare/ICommandText::Execute
> > (OLEDB)
> > If the calls are not prepared, the query will simply be executed
normally.
> > ***************************/
> > use pubs
> > go
> >
> > declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
> varchar(8)', N'select *
> > from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
> sp_unprepare @.P1
> > go
> > Please run the script above on the problematic client machine and the
> server machine to see
> > if it can work fine. If it works fine, you can apply this explicit
> sp_prepare, sp_execute and
> > sp_unprepare instead of sp_prepexec.
> >
> >
> > Please feel free to post in the group if this solves your problem or if
> you would like further
> > assistance.
> >
> >
> > Best regards,
> >
> > Billy Yao
> > Microsoft Online Partner Support
> > ----
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only. Thanks.
> >
> >
> >
> >
>|||Thank you Lance for your feedback.
DSNs settings are actually out of my consideration as it seemed from the trace of profile that
the problem appeared with provider issue. The same operation resulted in different value
passing to the same parameter @.p1 and this script was sent by client provider to server
provider.
I'm glad that you yourself find the cause and solve the problem. We learnt a lot from this
specific issue. Thank you for participating newgroup and sharing us so many information and
your own experience on the issue.
Best regards,
Billy Yao
Microsoft Online Partner Support

ODBC Administrator app fails only on SQL Server

Make sure this isn't a rights-issue
You could reinstall mdac 2.8 or sql server client.

>--Original Message--
>Hello,
>I am on an XP SP1 system and when I use the ODBC
Administrator application
>to establish a new SQL Server DSN the process fails.
>After launching the ODBC Administrator I push the "Add"
button and select
>"SQL Server" from the list of available drivers. When I
then push the
>"Finish" button I am immediately returned to the initial
ODBC Administrator
>window. That is, I am never presented with the dialog
box for configuring
>the SQL Server DSN. If I select a Sybase, MS Access or
Oracle DSN I do get
>the expected configuration dialog box. Under W2000 I get
a dialog box for
>configuring a SQL Server DSN and on a different XP SP1
system I also get the
>configuration dialog box. All of this suggets that
something has gone wrong
>on this particular XP SP1 box. Can anyone tell me what's
happened and how
>to fix this?
>Thanks very much for the help.
>Al Koch
>AlKoch@.MyRealBoxREMOVEALLTHESECHARS.com
>
>.
>
Thanks for the reply. It wasn't a right issue since I was logged in as an
Admin. However your suggestion to reinstall MDAC fixed the problem I a
curious though, are you aware of what might have triggered the loss of
(only) the SQL Server ODBC facility?
Thanks,
Al
|||I can only guess
-Sql2000 has been updated to SP3a
(Perhaps the old driver was one for sql server 7)
-WindowsXP had a fair share of updates as well.
-Perhaps a anti-virusprogram was running during install of
mdac/updates

>--Original Message--
>Thanks for the reply. It wasn't a right issue since I
was logged in as an
>Admin. However your suggestion to reinstall MDAC fixed
the problem I a
>curious though, are you aware of what might have
triggered the loss of
>(only) the SQL Server ODBC facility?
>Thanks,
>Al
>
>.
>
|||Thanks for the possible causes and thanks again for the help!
Al
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:307801c4b03d$6f50dac0$a301280a@.phx.gbl...
> I can only guess
> -Sql2000 has been updated to SP3a
> (Perhaps the old driver was one for sql server 7)
> -WindowsXP had a fair share of updates as well.
> -Perhaps a anti-virusprogram was running during install of
> mdac/updates
>

ODBC Administrator app fails only on SQL Server

Make sure this isn't a rights-issue
You could reinstall mdac 2.8 or sql server client.

>--Original Message--
>Hello,
>I am on an XP SP1 system and when I use the ODBC
Administrator application
>to establish a new SQL Server DSN the process fails.
>After launching the ODBC Administrator I push the "Add"
button and select
>"SQL Server" from the list of available drivers. When I
then push the
>"Finish" button I am immediately returned to the initial
ODBC Administrator
>window. That is, I am never presented with the dialog
box for configuring
>the SQL Server DSN. If I select a Sybase, MS Access or
Oracle DSN I do get
>the expected configuration dialog box. Under W2000 I get
a dialog box for
>configuring a SQL Server DSN and on a different XP SP1
system I also get the
>configuration dialog box. All of this suggets that
something has gone wrong
>on this particular XP SP1 box. Can anyone tell me what's
happened and how
>to fix this?
>Thanks very much for the help.
>Al Koch
>AlKoch@.MyRealBoxREMOVEALLTHESECHARS.com
>
>.
>Thanks for the reply. It wasn't a right issue since I was logged in as an
Admin. However your suggestion to reinstall MDAC fixed the problem I a
curious though, are you aware of what might have triggered the loss of
(only) the SQL Server ODBC facility?
Thanks,
Al|||I can only guess
-Sql2000 has been updated to SP3a
(Perhaps the old driver was one for sql server 7)
-WindowsXP had a fair share of updates as well.
-Perhaps a anti-virusprogram was running during install of
mdac/updates

>--Original Message--
>Thanks for the reply. It wasn't a right issue since I
was logged in as an
>Admin. However your suggestion to reinstall MDAC fixed
the problem I a
>curious though, are you aware of what might have
triggered the loss of
>(only) the SQL Server ODBC facility?
>Thanks,
>Al
>
>.
>|||Thanks for the possible causes and thanks again for the help!
Al
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:307801c4b03d$6f50dac0$a301280a@.phx.gbl...
> I can only guess
> -Sql2000 has been updated to SP3a
> (Perhaps the old driver was one for sql server 7)
> -WindowsXP had a fair share of updates as well.
> -Perhaps a anti-virusprogram was running during install of
> mdac/updates
>