Connection Help

[anon47]

First we host our own servers so the code below is what I am use to but we are wanting to upgrade to sql express 2005 from access I am trying to figure what my connection string is for the sql? Any help would be great.

dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Cs\Database\Data.mdb;" & _
"Jet OLEDB:System Database=C:\Cs\Database\Secured.mdw", _
"ID", "123456"

 

[gmmastros]

http://www.connectionstrings.com/?carrier=sqlserver2005

Scroll down to: SQL Native Client OLE DB Provider

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon47]

OK I got it up to this far but can anyone tell me why the end statement error?

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/clients/test/index.asp, line 18

Server=Server\SQLEXPRESS Database=Data Trusted_Connection=True;

 

[anon47]

Sorry here is all of the error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/clients/test/index.asp, line 18

Server=SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=True;
------------------------^

 

[gmmastros]

Without seeing the code, I would guess that you are missing an end quote or possibly a closing parenthesis. If you show us the actual code, we would be able to help you better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon47]

OK I have got this far
<%
Provider="SQLNCLI;Server=SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=yes;"
'Server="SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=True;"
sql = "SELECT * FROM [Debtors]"
rs.open sql, conn ' <--- this is line 21
%>

Error I am getting
Microsoft VBScript runtime error '800a01a8'

Object required: ''

/clients/test/index.asp, line 21

 

[gmmastros]

set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString =  "Provider=SQLNCLI;Server=SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=yes;"

Conn.Open
sql = "SELECT * FROM [Debtors]"

rs.open SQL, Conn

This is untested, but I think it should work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah. Just a heads up...

Many of the sql queries you use with Access will also work with SQL Server, but there are some differences. The most common problem developers face concerns dates. In Access, you surround your dates with the # symbol. In SQL Server, you need to use a single-quote ' instead.

In my opinion, this is the correct forum for your question. However, if you have problems with your queries, then you should ask the question in this forum:
forum183



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon47]

Perfect thanks a bunch.

Now I get this login error. On access I needed a user and pass to login but I am assuming on SQL the trusted string will do with windows authinacation?

Microsoft SQL Native Client error '80004005'

Cannot open database "GE_Data" requested by the login. The login failed.

/clients/test/index.asp, line 21

 

[anon47]

Got gmmastros had to set the permissions and now I have to make sure its secure the way I have it.

 

[gmmastros]

Yes.

Trusted_Connection=yes;
This uses windows authentication. I suspect that you have not set up your database to allow connections in this way. Let me explain.

There are 3 authentication modes available with SQL Server (actually 3). There is SQL Authentication, Windows authentication and (BOTH). Using the interface for sql server, you can allow or disallow sql authentication.

Then, you will need to set up logins within the database. If you are using windows authentication, you would add logins that correspond to your windows accounts (either individually or as groups).

If you decide to use SQL Authentication, you will need to make sure your sql instance allows for it. In the SQL Server Management Studio...

Right click your server.
Click Properties.
In the 'select a page' section, click on Security.
Under 'Server authentication' make sure you have 'SQL Server and Windows Authentication mode' selected.

Then, you'll need to create a login.

In the Object Explorer, drill down to (Server name) -> Security -> Logins
Right click 'Logins' and select 'New Login'.
Create a new login on this page.
Under 'User Mapping', make sure you give the login access to the database that you want to use.

If you decide to use SQL Authentication, you will need to modify the connection string. You'll need to remove the Trusted_Connection=yes; and add the user name and password.

One big difference between Access and SQL Server concerns security. I once heard someone say that Access defaults to full permissions and SQL Server defaults to no permissions. There is some truth in that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon47]

Thanks for the info I am still learning the new concept. I did read that windows auth is more secure? What is your take on that?

 

[gmmastros]

>> I did read that windows auth is more secure?

I've read the same thing. In my opinion, if you enforce strong passwords, then SQL Authentication is secure enough.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon47]

10-4 thanks for every thing