Hi All,

we are upgrading our environment from windows 2000 to windows 2003 and from
SQL 2000 Sp3A to SP4. We have an asp application using SQL 2000 ,commerce
server 2002 and biztalk server.

As part of the upgrade i'm trying to run a batch file(contains critical
daily job) from machine running commerce server 2002 to access a DB on
another server(SQL Server) which is windows 2003 standard SQL 2K Sp4. i
modified the required config settings. I'm using dsn-less connection string.
I get the below error

Run time error ‘-2147467259(80004005)’:

[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified

The connection string in the commerce server manager is

Provider=MSDASQL.1;Persist Security Info=True;Extended
Properties="Driver={SQL Server};SERVER=<Servername\instancename>;DATABASE= <db
name>;UID=<user name>;PWD=<password>'

I tried below options with no success.
1. persistsecurityinfo set to false
2. tried running as different user
3. checked the MDAC versions(2003 ones running MDAC 2.8 and commerce server
machines(windows 2000) 2.71)
4. Double checked all the config settings.

The same setting works fine in our QA environment. This is the dev
environment and i cant get this one working.

Any help is highly appreciated.
Thanks for your time.

First thing to check would the UID (User ID) and the PWD (password). Are
these two a Windows Login Account or a SQL-Server Account and in the later
case, has the Mixed Authentication Mode been enabled on the SQL-Server? If
this is a Windows Account, this won't work this way as you must use the
Integrated Security instead.

The second thing to check would be the presence of a firewall blocking the
communication. As you are using an InstanceName, you must also make sure
that the SQL-Server Browsing Service is running and that the UDP port 1434
is not blocked.

Finally, this is a strange connection string for an ASP application: instead
of directly using the OLEDB provider of SQL-Server, it use the ODBC driver
through the MSDASQL OLEDB provider. This way of doing thing should only be
done for databases for which there is no OLEDB provider available; which is
clearly not the case for SQL-Server. However, this is probably not what's
causing your connection problem.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"JG" <JG (AT) discussions (DOT) microsoft.com> wrote