MS SQL OLE DB Provider 1

[liaml1]

Hello All,

I am making a piece of software and would like to connect to my MS SQL 2000 server (actually MSDE) using The OLE DB Provider for SQL SERver. I have so far been unsuccessful. I have read through the other threads and have tried the following:

1) Connecting via ODBC instead - This worked fine. I was able to connect to the DB Server with the user I created.

2) Enable both Windows and SQL Server login in the configuration of SQL server - This did nothing. Still could not connect with OLE DB.

3) Upgraded to SP 4. Still not change.

4) I also assigned the "Systems Administrator" role to the user that i created in MS SQL but that did nothing.

Just for the record the MSDE is on the same machine that I am prorgamming on and I have a MYSQL server installed on it to.

Here is the connection string that I am using:

Provider=SQLOLEDB.1;Password=catservNDC1;Persist Security Info=True;User ID=ndc;Initial Catalog=db_ndc;Data Source=DEVELOPER

Any advice would be appreciated.

Liam

 

[fredericofonseca]

see

http://www.connectionstrings.com

The correct connection will depend on whether you are using Windows authentication or SQL Server authentication.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[liaml1]

Thanks,

I have modified my connection string to this:

'Provider=sqloledb;Data Source=developer;Initial Catalog=db_ndc;User Id=ndc;Password=catservNDC1;'

Unfortunately it does not help. I still get an error saying:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access is denied.

Could it be some configurating problem with the MSDE?

Liam

 

[fredericofonseca]

try

'Provider=sqloledb;Data Source=developer;Initial Catalog=db_ndc;Integrated Security=SSPI;"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[liaml1]

Still no luck. I get the same error. I reverted back to using ODBC.

Liam

 

[fredericofonseca]

Hummm..

There is something wrong with your setup or with your program.


Please do the following.

create an empty text file anywhere you want. Rename it to .udl, and then open it.

It will show you a wizard.

On this wizard you can choose the provider, and all other information required to connect to your SQL server.
Information to supply will vary with the provider you choose.

Please try with both the ODBC driver and the OLEDB for SQL Server provider, and after testing the connection look at the contents of the file (using a text editor).
You should have information like the following.

--- With integrated security
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FACTUCLI;Data Source=FREDERICOXP1

--- With SQL Server user
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=frederico;Data Source=FREDERICOXP1


--- With ODBC datasource
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=frederico;Data Source=FREDERICOXP1

Also look at the options on your ODBC Data source and see which protocol is being used (pipes, TCP/IP other).

You may need to change the MSDE installation to allow for other protocol. See the manuals for command line options.


Basically if you can access it using ODBC then you can also do it through OLEDB, but you may need to correctly configure the connection type being used (e.g. network protocol), and this may be your problem.

Also you are not telling us what language you are using to connect to your SQL Server. This may also have some impact on the final solution based on the code you are using to connect.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[liaml1]

Thank for the info. I will try out what you suggested. I am using Delphi 7.0 to code and connect to the SQL DB.

Liam

 

[philhege]

Check out this post for an in-depth explanation of network connectivity for MSDE:

http://groups.google.com/group/micr...network+enabled&rnum=2&hl=en#e0422d84a3cf7cd9

HTH,



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[cmmrfrds]

Instead of using "developer" for the instance name of msde, try using "localhost" as the name. This is an alias for the instance of sql server on your pc. Could be the name is not correct due to case sensitivity or some other issue.

 

[liaml1]

This is what my SQL server Log file looks like:

2006-10-16 19:04:51.70 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-10-16 19:04:51.70 server Copyright (C) 1988-2000 Microsoft Corporation.
2006-10-16 19:04:51.70 server All rights reserved.
2006-10-16 19:04:51.70 server Server Process ID is 2240.
2006-10-16 19:04:51.70 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2006-10-16 19:04:51.71 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2006-10-16 19:04:51.75 server SQL Server configured for thread mode processing.
2006-10-16 19:04:51.75 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2006-10-16 19:04:51.78 spid2 Starting up database 'master'.
2006-10-16 19:04:52.01 spid5 Starting up database 'model'.
2006-10-16 19:04:52.01 server Using 'SSNETLIB.DLL' version '8.0.194'.
2006-10-16 19:04:52.07 spid2 Server name is 'DEVELOPER'.
2006-10-16 19:04:52.07 spid2 Skipping startup of clean database id 4
2006-10-16 19:04:52.07 spid2 Skipping startup of clean database id 5
2006-10-16 19:04:52.35 spid5 Clearing tempdb database.
2006-10-16 19:04:53.00 spid5 Starting up database 'tempdb'.
2006-10-16 19:04:53.20 spid2 Recovery complete.
2006-10-16 19:04:55.98 server SQL server listening on Shared Memory, Named Pipes.
2006-10-16 19:04:55.98 server SQL Server is ready for client connections
2006-10-16 19:05:00.26 spid51 Starting up database 'msdb'.
2006-10-16 19:05:01.48 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.



It says that the SQL server is listening on named pipes and shared memory but not TCP/IP. I already removed Named Pipes in the Network Cponfiguration utility and left only TCP/IP but the log still shows that I am not listening on the TCP/IP port.


Liam

 

[snowdreamer2000]

You probably figured it out by now. But just in case, the "Data Source" must be set to the "Servername\Instancename" for the MSDE engine.

You can find the name of the MSDE "Instance" by looking in the "Services". You should see a Service that begins with "MSSQL$". In the example Connection String below, the Service Name would be "MSSQL$I101MSDE"

Provider=SQLOLEDB.1;Password=sa;User ID=sa;Initial Catalog=Imaging101Demo;Data Source=WKST02\I101MSDE

Jacob Russo
Imaging101, Inc.

http://www.imaging101.com