I ma trying to move up from Accsss to SQL server but am in a bit of a pickle with the connection string.
Also, when testing on my local machine where SQL server 2000 is installed - where should the databse be stored? In sql programme files or on INETPUB?
Many thanks in advance..
Init

i created a folder on my development machine's C: drive, i store log and dbase files in there. When connecting to SQL Server you need to specify computername of the computer that runs the "Instance" of SQL that hosts your Dbase.

I guess I am alittle spoiled working for a big company, i connect through a DSN to SQL server. We store the instance name in a DSN on the webserver and it works great, Coldfusion is even better, it gives you a menu in the webbased administrator to connect to your databases, you can even browse server if you run access databases. When i work at home on my asp production website then i also connect to my sql server through a dsn.
it is the easiest way in DWMX to connect to any database.

is your database converted yet ?
if you must use a connection string then show what you allready have and i'll try to help you out further.

with regards,

Dennis van Galen
DMXzone Manager
FAQ, Tutorial and Extension Manager

How to set up an OLE DB connection to SQL Server: www.drdev.net/article10.asp


Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/tdsfdemo/Shop.htm


Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

Edited by - oeastwick on 16 Feb 2003 00:09:47

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
OLE DB connection is better than using a DSN, I don't know why people still use DSN's and ODBC, it just adds an extra layer to an OLE connection and reduces performance.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Maybe because the bigger corporations will only allow you access through a certain way. Anyway, that's a good article, think i'll show it to the "techheads" at our company.

with regards,

Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Edited by - djvgalen on 16 Feb 2003 00:21:13

Here's some more on ODBC and OLE DB:

Use of ODBC databases (Access in particular)
File-based databases, particularly Access, are not well suited for use on a production web site. Even with just a small, lightly used database, problems can arise. Our recommendation is that our customers use SQL Server databases, and that they connect to the using the OLE-DB driver instead of ODBC. Besides being more reliable, the SQL Server OLE-DB driver is also much faster than the ODBC version.

If you are using Access, FoxPro, or SQL Server via a System DSN, your database connection goes through the "OLE-DB Provider for ODBC Databases." Accessing a database involves going through four API layers: ADO -&gt; OLE-DB -&gt; OLE-DB Provider for ODBC Databases -&gt; ODBC Driver.

By switching to SQL Server and specifying it's OLE-DB driver, the OLE-DB Provider for ODBC Databases can be eliminated from the process. Now your database queries will go through just three API layers: ADO -&gt; OLE-DB -&gt;OLE-DB Driver.

Source: www.digitalcanvas.com/support/asp_coding.php3


I think the main reason people use ODBC and DSN's is because that's what they have allways used. Also, most of the books, tutorials and examples I've seen show how to connect with a DSN and make no mention of OLE DB.

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/tdsfdemo/Shop.htm


Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

Edited by - oeastwick on 16 Feb 2003 00:46:23

Many thanks for the replies guys.
I followed the OLE DB instruction.
The UDL file works and the test is successful, however when I paste the string into MX I get 'Login failed for user 'BX\IUSR_USER'.
I have been to the snap ins and changed the log on for SQL server and SQL server ad helper to "Local System account" is this correct?
Thanks again.
Init

Hmmm, not sure too sure about that one.

You can leave the database files in the SQL Server default, or any other directory on your local PC and you should be able to set up the connection using Windows Integrated Security.

The username and password you need to connect to a SQL Server are the ones set up in SQL Server itself, not the annonymous user account for web acces information that this: BX\IUSR_USER appears to be.

Am I right in assuming that your computers network name is BX?



Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/tdsfdemo/Shop.htm


Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

Owen, Many thanks for your help.
I have now connected by making an account IUSR_USER
In the server behaviours the tables appear and the data comes up in the test.
Now however, when I put it to the testing server and preview the page I get this lot.
The same comes up when I use live view.

"System.ArgumentException: Unknown connection option in connection string: provider.
at System.Data.SqlClient.ConStringUtil.ParseStringIntoHashtable(String conString, Hashtable values)
at System.Data.SqlClient.ConStringUtil.ParseConnectionString(String connectionString)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at DreamweaverCtrls.DataSet.NewDbConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()
--------------------------------------------------------------------------------
System.Exception: The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()

Thanks again.