asked on

Dreamwearver 8 - SQL Server 2005 Connections - No Tables Found

I should be grateful if someone would please solve my problem, as detailed below.

To set the scene, the following technologies are in play&

Local Machine: Windows 2000, Dreamweaver 8

Web Server: Windows Server 2000 (Advanced, SP4, sqlncli.msi applied)

SQL Server: Windows Server 2003, SQL Server 2005

Development Language: Classic ASP

The problem&

I am connecting to an SQL Server 2005 database using the in-built features of Dreamweaver 8, found under the Databases tab. I have applied the file, sqlncli.msi (downloaded from Microsoft), to the web server, which enables the use the necessary SQL Native Client drivers and providers.

I have successfully connected to the required database on the SQL 2005 server, using either the Custom Connection String&

MM_IMWebsite_STRING = "Provider=SQLNCLI;Server=myServer;Database=myDatabase;Uid=myID; Pwd=myPassword;"

Or the Data Source Name (DSN)&

MM_IMWebsite_DSN_STRING = "dsn=myDSNName Uid=myID; Pwd=myPassword;"

&Methods. There are no error messages and all connections test successfully; however, when I expand the connection entries under the Databases tab, everything seems ok, until I click on Tables. In both cases None is displayed.

For completeness, I should mention the following&

1. The Stored Procedures & Views are all visible/available in the 'DSN' connection, but only the Stored Procedures are visible/available in the Custom Connection String connection (Views displays None).

2. The System DSN has been created on the Web Server, using the SQL Native Client.

Can anyone assist me in accessing my SQL 2005 Databases correctly, such that Dreamweaver 8 can make use of the data stored in the tables?

Thanking you in advance,

Simon

saoirse1916

For testing purposes, create a new ASP file (index.asp, for example) and include the following code. Then run it and see if you can get any sort of valid results.

<%
Const DB_CONN_STRING = "dsn=myDSNName;Uid=myID;Pwd=myPassword;"
 
Dim getDataConn, getDataCmd, rsData, arrData, strSQL
 
strSQL = "SELECT TOP 10 * FROM yourTableName" 
 
Set getDataConn = Server.CreateObject("ADODB.Connection")
getDataConn.Open DB_CONN_STRING
Set getDataCmd = Server.CreateObject("ADODB.Command")
getDataCmd.ActiveConnection = getDataConn
getDataCmd.CommandText = strSQL
getDataCmd.CommandType = 1
 
Set rsData = getDataCmd.Execute
If Not (rsData.BOF AND rsData.EOF) Then
	arrData = rsData.GetRows()
End If
 
rsData.Close()
Set rsData = Nothing
Set getDataCmd = Nothing
getDataConn.Close()
Set getDataConn = Nothing
 
Response.Write("<html><head></head><body>")
 
If IsArray(arrData) = True Then
	Response.Write("<table width=" & chr(34) & "100%" & chr(34) & ">")
	For i = 0 to uBound(arrData,2)
		Response.Write("<tr>")
		For x = 0 to uBound(arrData,1)
			Response.Write("<td>" & arrData(x,i) & "</td>"))
		Next
		Response.Write("</tr>")
	Next
Else
	Response.Write("No Data Found")
End If
 
Response.Write("</body></html>")
%>

Open in new window

sdkman

ASKER

Thank you for your response saoirse1916.

I have carried out your request, and received the following error information...

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][SQL Native Client][SQL Server]The SELECT permission was denied on the object 'im_Articles', database 'IMWebsite', schema 'dbo'.
/TESTING/test3.asp, line 16

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 2.0.50727; InfoPath.1)

Page:
GET /TESTING/test3.asp

Time:
Tuesday, October 27, 2009, 3:31:01 PM

More information:
Microsoft Support

Regards,
Simon

ASKER CERTIFIED SOLUTION

saoirse1916

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdkman

ASKER

saoirse1916, you are a God amongst men! THe problem is resolved, and the tables are now available via Dreamweaver. I have also learned a thing or two about Db connection fault diagnosis too

Thank you very much for your time and support.

Kind regards,
Simon

sdkman

ASKER

saoirse1916 saved me a lot of time, and I still have my hair!!

saoirse1916

No problem, happy to help!