Re: ConnectionString using DSN not working

Over the years application designers have tried to create generic
applications that can work regardless of which backend database engine is
accessed. This assumes that each DBMS is the same--supports the same SQL
dialect (or at least a common subset), other functionality, admin processes,
connection strategies and other aspects. For the most part they don't. For
example, Oracle, SQL Server and JET all use different approaches to stored
procedures and SQL. All have different admin functions (like backup,
restore, user account management, security), different performance tuning
approaches and on and on.

This means that when you use an "OSFA" (one-size-fits-all) generic interface
like ODBC or OLE DB (and there are others) you must go to great lengths to
take advantage of the specific features you pay for when you buy SQL Server
or other SQL engines.

When you use SqlClient or OracleClient your code talks directly to the SQL
Server-specific TDS data stream or the Oracle-specific data stream.
Performance of ODBC and OLE DB is not nearly as good nor as flexible. If you
want to access all of the features of any serious DBMS engine, you can't
depend on ODBC or OLE DB to expose them.

Using SqlClient or other provider-specific interface, changing the
connection string to address the new targeted DBMS is something that can and
is done quite often as long as you design in the approach. Yes, the
TableAdapter and other wizards don't make this easy, but it can be done.
Consider that the application properties are accessible via code--they
include the connection string used to access the DBMS. As long as the schema
and rights of the target DBMS match the development system your application
should work fine.

hth
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

"Fendi Baba" <effendi@xxxxxxxxxxxxxx> wrote in message
news:1177212508.584011.106300@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thank everyone. One of the concern we have is the fact that using not
using ODBC means connecting to te server direct. This works out nicely
for development environment but when we move to production where the
database server is in a server famr, we have to change the config file
to change to the server accordingly. Is there a way we can "make" this
server change automated? My administrators would definitely appreciate
that.

Regards



.

Relevant Pages

  • Re: Cost of ownership: MV vs. SQL Server
    ... within the Pick/MV DBMS, whereas it is external to SQL Server. ... Server is a database server - Pick/MV is a database/application server. ... Pick/MV DBMS over SQL Server for new application development using ...
    (comp.databases.pick)
  • Re: Connecting to new SQL Server - database engine no local server
    ... SQL Server Integration Serives ... SQL Server Database Engine may not be installed during SQL Server Setup. ... In the SQL Server 2005 Surface Area configuration tool the server is configured for Remote Connections. ...
    (microsoft.public.sqlserver.setup)
  • Re: Highlander entitlement??
    ... what means "a server service running"? ... - if the Blackfish is really a DB server, ... Accessing a SQL server DB like a ISAM DB is one of the best ways to ... of course this is only if the SQL engine ...
    (borland.public.delphi.non-technical)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)
  • Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 C
    ... I also checked the individual patch levels for the .NET drivers, SQL Server ... The SQL Server is fully patched, however Windows Update reported that the OS ... Lock pages in memory -- I guess you might have taken care of it as well. ...
    (microsoft.public.sqlserver.clustering)