can not connect to msde

isheikh · #1 (**permalink**) February 4th, 2004, 09:34 AM

Hi ,

I installed MSDE from my ofice 2002 CD. I did not set up my password and my user id is still at default "sa". I am trying to connet to my database just as mentioned in wrox book "begining ASP 3.0".I just can not connect to MSDE from my ASP page and keep geting this error.

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

**********

My code is asp to connect is

objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=iffo;Data Source=local;Initial File Name=D:\Program Files\Microsoft SQL Server\MSSQL\Data\iffo.mdf"

************

I wrote this code exactly as it was given in this wrox book, only thing I am not sure here is that I don't know what that Catalog means, I am assuming it is also the name if the database

thanks

Imar · #2 (**permalink**) February 4th, 2004, 10:33 AM

Did the book say you needed to set Initial File Name to the name of the.MDF file for a SQL Server connection?

Take a look here to see how your connection should look: http://www.able-consulting.com/MDAC/...erForSQLServer

Basically, what you need are: Data Source and Initial Catalog. Data Source should be set to the named instance of your MSDE installation. So, if your server is called YourServer, and you named the instance of MSDE YourInstance, your Data Source should be YourServer\YourInstance. The Initial Catalog is the name of the database you're trying to connect to.

The part about Initial File Name can be removed.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

isheikh · #3 (**permalink**) February 5th, 2004, 12:12 AM

Hi Thanks for your reply. I am about to give up on this MSDE. I tried removing "Initial File Name" like you suggested but it still did not work. I tried the examples given in the link that you sent, but it did not work. Following code is direct from Wrox book, I downloaded it from their web site...

<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>Testing our connection</TITLE>
</HEAD>
<BODY>

<%
Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2

Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

Dim strDatabaseType
'Choose one of the following two lines, and comment out the other
strDatabaseType = "Access"
'strDatabaseType = "MSDE"

'Now we use this selection to open the connection in the appropriate way
If strDatabaseType = "Access" Then
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=C:\datastores\Movie2000.mdb;" & _
               "Persist Security Info=False"
Else
  objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _
               "User ID=sa;Initial Catalog=Movie;" & _
               "Initial File Name=C:\MSSQL7\Data\Movie2000.mdf"
End If

objRS.Open "Movies", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

While Not objRS.EOF
  Response.Write objRS("Title") & "<BR>"
  objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>

</BODY>
</HTML>

Imar · #4 (**permalink**) February 5th, 2004, 04:56 AM

From what Chapter is this code? This doesn't look right. You'll need to exchange the "Initial File Name" with a Data Source (the server). You may want to send this to Wiley Tech Support, so they can add this to the errata section for the book.

To find out the name of your MSDE installation, go to your program files folder and then look in the Microsoft SQL Server folder. You'll see a folder name with a $ in its name. The part after the $ is the instance name of your MSDE installation. For example, mine is at:

D:\Program Files\Microsoft SQL Server\MSSQL$VSDOTNET

so my MSDE instance is called VSDOTNET. To connect to the MSDE you'll need to use MachineName\InstanceName. Both parts are required. So, in my case, that would be MyServer\VSDOTNET

Mu connectionstring for the Northwind database should then look like this:

Provider=sqloledb;Data Source=MyServer\VSDOTNET;Initial Catalog=NorthWind;User Id=myUsername;Password=myPassword

for an MSDE installation with SQL Server Authentication. For Integrated Security, you should use:

Provider=sqloledb;Data Source=MyServer\VSDOTNET;Initial Catalog=NorthWind;Integrated Security=SSPI

One of these two connection string should work. If they don't, take a look here: http://support.microsoft.com:80/supp.../Q264/6/91.ASP. It will help you create a connection string with a UDL file.

Cheers

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

isheikh · #5 (**permalink**) February 5th, 2004, 10:45 PM

I looked under folder Program Files\Microsoft SQL Server, I do not have any folder that has $ in its name. In folder MSSQL I have folders Binn,log,data, install. Even they do not have any folder that has $ in its name.

It is chapter 12 in this book where they talk about using OLE to connect to the MSDE. At the same place they also talk about how instead of typing the connecting string you can make a UDL file in note pad to get the string. When I make the UDL file like they said the string that I get is :

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=iffo;Data Source=local;Initial File Name=D:\Program Files\Microsoft SQL Server\MSSQL\Data\iffo.mdf"

same that I mentioned before......

Imar · #6 (**permalink**) February 6th, 2004, 04:04 AM

The Initial File Name is used to connect a MDF file to SQL Server, and then directly open it. I wasn't aware of this, but it's supposed to work.

However, it looks like you don't have a valid MSDE installation. I am not sure if the Office edition is different from other installations, but you may want to try the latest version:
http://www.microsoft.com/downloads/d...DisplayLang=en

Make sure you read the Readme, especially the part about installing the MSDE. You need to use some command line stuff to get your installation and Named Instance to be setup correctly.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

isheikh · #7 (**permalink**) February 6th, 2004, 09:32 PM

Thanks for your help. I finally got it runnning. I installed msde from the link that youn sent and installed it with the following command:

setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL

One thing I found the reason I do not have a folder with $ sign in the name becasue this happen only if you want to name your instance of msde. if you are taking the default name then you do not get that folder, default instance name is always name of your machine, You can have only one instance with the default name per machine.

I belive that MSDE does not have foreign keys option..right?

thanks

Imar · #8 (**permalink**) February 7th, 2004, 05:03 AM

Right. I have always installed the MSDE as a Named Instance, so I wasn't aware about the default instance possibility.

Anyway, glad it worked out.

What do you mean with "foreign key option"?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

isheikh · #9 (**permalink**) February 7th, 2004, 10:28 AM

You know like Primary key ..there is a Foreign key in some database to connect one table to other table

Imar · #10 (**permalink**) February 7th, 2004, 11:42 AM

Well, that's supported by MSDE, just as it is with SQL Server.

Because you don't have a graphical interface, it's not as easy to configure. Take a look at the Books Online for more info.

http://msdn.microsoft.com/library/de...start_4fht.asp

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.