can't connect with OLEDB, but ODBC works

I have a local database that I'm having trouble selecting as a source in
DTS. I can connect just fine using "mymachinename", but if I try to use
(local) or an alias in Client Network Utility, I get the error:
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : [DBNETLIB][ConnectionOpen (Connect()).]
SQL Server does not exist or access denied.
I noticed that if I changed the driver to ODBC, then it works fine. Also, I
can connect using any name in Query Analyzer. However, I need to use OLEDB
and also the alias. I have Windows XP Pro and SQL 2000 Developer Edition w/
SP3. Here's all the different tests I have done with OLEDB:
Server Authentication Result
-- -- --
mymachinename Windows OK
mymachinename SQL login OK
myalias Windows SQL Server does not exist or access denied.
myalias SQL login SQL Server does not exist or access denied.
(local) Windows SQL Server does not exist or access denied.
(local) SQL login SQL Server does not exist or access denied.Can you make an OLE DB connection through a UDL? To create a UDL, just
right-click on your desktop and choose to create a new document. Name it
anything you want. Then rename the extension to .udl. Then double-click on
it. Choose your driver and other connection properties and test it. If it
works from there then most likely OLE DB is fine.
(local) is using shared memory and an alias is using whatever protocol is
defined in cliconfg.exe for that alias. When you get a successful
connection which protocol are you using? For a successful connection you
can do:
SELECT network_library, * from master..sysprocesses where spid = @.@.spid to
see your actual protocol.
It may be that the difference isn't odbc vs. ole db but rather tcp vs.
shared memory vs. named pipes. To force connectivity with each type of
protocol:
for tcp use: tcp:mymachinename
for named pipes use: np:mymachinename
for shared memory use: lpc: mymachinename
You can find more troubleshooting steps in:
328306 INF: Potential Causes of the "SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.