Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 19th, 2006, 03:56 PM
Friend of Wrox
 
Join Date: Apr 2005
Location: Fort Walton Beach, FL, USA.
Posts: 190
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQLServerExpress not allow remote connection


Help Please:

I'm writing a small web service and for testing am using the Northwind db. Testing fails due to the following message:

-------------------------
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
-------------------------

The (entire) code shown below uses the Northwind db, which I have just downloaded and installed.

I cannot find a way to change the default settings for the SQL Server to allow remote connections. Anyone suggest a way that I can do that? I am using SQL 2005 Express.

The error is not the conn string. Both conn definitions above (commented out) do not work, it’s the SQL Server default settings.


-------------------------
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient

<WebService(Namespace:="http://www.FTA.net/customers", Description:="FTA web service")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicPr ofile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.Des ignerGenerated()> _
Public Class Customers
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function GetCustomers() As DataSet
        Dim conn As SqlConnection
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataSet As DataSet
        Dim cmdString As String = "SELECT * FROM Customers"
        'conn = New SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI")
        'conn = New SqlConnection("Server=localhost; uid=sa;pwd=;database=Northwind")
        myDataAdapter = New SqlDataAdapter(cmdString, conn)
        myDataSet = New DataSet
        myDataAdapter.Fill(myDataSet, "Customers")
        Return myDataSet
    End Function
End Class
-------------------------

Thanks, VV


  #2 (permalink)  
Old October 19th, 2006, 04:04 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi VictorVictor,

Take a look at the "Other Issues" section near the bottom of this article:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
  #3 (permalink)  
Old October 20th, 2006, 12:15 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Fort Walton Beach, FL, USA.
Posts: 190
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar:
Interesting. Thanks for the direction to your article.

I followed the procedure your article specified and enabled both named pipes and TCP/IP but still no joy. Same error msg.

Two other things I tried:

(1) I changed the name of the DataSource from local to my machine’s name and SQLServer: YOUR-D9E47C800B\SQLEXPRESS.
When I use that string in the following After: conn statement I get the same error as before but with a different number. See below.

Before: conn = New SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI")
After: conn = New SqlConnection("Data Source=(D9E47C800B\SQLEXPRESS);Initial Catalog=Northwind;Integrated Security=SSPI")

Error msg:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

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

(2) I changed the name of the Server from localhost to my machine’s name and SQLServer: YOUR-D9E47C800B\SQLEXPRESS.
When I use that string in the other, following After conn statement I get a different error. See below:

Before: conn = New SqlConnection("Server=localhost; uid=sa;pwd=;database=Northwind")
After: conn = New SqlConnection("Server=D9E47C800B\SQLEXPRESS; uid=sa;pwd=;database=Northwind")

Error msg:
System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

I am heartened by the remark that a connection was successful. So, it looks like it doesn’t like any form of ‘local’ for a db name. I have no clue on what it means about no process on the other end.

VV



  #4 (permalink)  
Old October 20th, 2006, 01:08 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

You probably don't have an SA account with a blank password, as you don't get one by default. So, the last connection strings are probably not going to work.

But are you sure Northwind is connected to your SQL Express installation? By default it's not installed anymore. If it's not, you need a different connection string that attaches your database automatically

Do you have a copy of "sql server management studio express"? That makes managing SQL Server a lot easier.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
  #5 (permalink)  
Old October 20th, 2006, 04:25 PM
Friend of Wrox
 
Join Date: Apr 2005
Location: Fort Walton Beach, FL, USA.
Posts: 190
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar:

Thanks for the quick reply. Sorry it's taking so long but ongoing business has high priority.

Yes, I have sql server management studio express and am using it to create a new db. BTW, Northwind was not connected but now it is. Still unable to connect to it tho...

I have the following string in my little webserver code and suspect that the url: www.FTA.net which does not actually exist may be what is preventing me from connecting. I am not sure tho because I believe the url only represents a unique string and is not
actually accessed. But I'm not sure about that. You might set me straight on that.

<WebService(Namespace:="http://www.FTA.net/customers", Description:="FTA web service")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicPr ofile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.Des ignerGenerated()> _
Public Class Customers ...
...class definition follows, and is unchanged from earlier code.

So, I have spend much of today creating a SQL express database using the sql server management studio express facility. And Wow, what an exercise in frustration. I have successfully created 5 tables as the dbo, and have attempted to enter a record in the first one. It will not accept the data. Keeps giving an error messages as follows:

No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.

There is nothing wrong with the data. The field calls for a varchar(10) and I am keying in (without quotes) "Active".

If I make that file one that will accept Null and then null it out, SQL objects to some other field, and on and on...

Almost ready to throw out SQL 2005 Express and re-install SQL 2000.

VV









  #6 (permalink)  
Old October 21st, 2006, 03:11 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi VictorVictor,

I don't think the URL has anything to do with this. It looks like a configuration /security issue to me.

If you want to use SQL Server Express, why don't you just add a database to the App_Data folder and then use a connectionstring like this:
Code:
<connectionStrings>
  <add name="YourName" connectionString="Data Source=(local)\SqlExpress;
    AttachDbFilename=|DataDirectory|\YourDatabase.mdf;Integrated Security=True;
    User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>
That should work much better than trying to set up SA accounts on the database.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
  #7 (permalink)  
Old October 23rd, 2006, 03:38 PM
Friend of Wrox
 
Join Date: Apr 2005
Location: Fort Walton Beach, FL, USA.
Posts: 190
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar:

Took your advice as follows:

Made an ordinary simple aspx page and used the web.config manager to establish a connection to the Northwnd sql database which I attached to the project in the data folder.
Ran it, it worked fine.

Took the exact same web.config, database, connection string, etc, and used them in a simple web service.
Ran it, did not work.

Below appear the web service code, the error msg, and listing extracts showing the connection string.

This is my first web service and I'm trying to do it without spending hours going thru a book on web services. Seems like my shortcut approach is not going to cooperate, so I am going to turn to line 1, page 1 of the book and follow it. Hopefully, all will become clear as I go thru the book.

Thanks for your help.

VV


  #8 (permalink)  
Old October 23rd, 2006, 03:41 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

I don't see any code or error messages below.

But, did you create the web service in the exact asp.net project as the aspx page? That is, did you create a brand new website and then added a Web service to it?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
  #9 (permalink)  
Old October 23rd, 2006, 03:41 PM
Friend of Wrox
 
Join Date: Apr 2005
Location: Fort Walton Beach, FL, USA.
Posts: 190
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oops, forgot to attach the listings:


Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient

<WebService(Namespace:="http://www.FTA.net/customers", Description:="FTA web service")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicPr ofile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.Des ignerGenerated()> _
Public Class Customers
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function GetCustomers() As DataSet
        Dim conn As New SqlConnection
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataSet As DataSet
        Dim cmdString As String = "SELECT * FROM Customers"
        conn.ConnectionString = "<%$ ConnectionStrings:NORTHWNDConnectionString %>"
        myDataAdapter = New SqlDataAdapter(cmdString, conn)
        myDataSet = New DataSet
        myDataAdapter.Fill(myDataSet, "Customers")
        Return myDataSet
    End Function

End Class



System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
   at System.Data.Common.DbConnectionOptions.GetKeyValue Pair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
   at System.Data.Common.DbConnectionOptions.ParseIntern al(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(Strin g connectionString, Hashtable synonyms, Boolean useOdbcRules)
   at System.Data.SqlClient.SqlConnectionString..ctor(St ring connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateC onnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetCo nnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionStri ng_Set(String value)
   at System.Data.SqlClient.SqlConnection.set_Connection String(String value)
   at Customers.GetCustomers() in C:\Choctaw\WebSite8\App_Code\Customers.vb:line 19




        <add name="NORTHWNDConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=&quot;C:\SQL Server 2000 Sample Databases\NORTHWND.MDF&quot;;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />


        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"


    <connectionStrings>
        <add name="NORTHWNDConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=&quot;C:\SQL Server 2000 Sample Databases\NORTHWND.MDF&quot;;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>


  #10 (permalink)  
Old October 23rd, 2006, 03:42 PM
Friend of Wrox
 
Join Date: Apr 2005
Location: Fort Walton Beach, FL, USA.
Posts: 190
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No, they were two separate projects.

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remote Desktop Connection hari raghav Windows Server 3 June 9th, 2008 12:46 PM
SQL remote connection gcharri SQL Server 2000 0 March 26th, 2008 03:42 PM
sqlserverexpress raktol SQL Server 2005 3 October 8th, 2006 12:11 PM
Remote database connection tdaustin Classic ASP Databases 0 November 28th, 2005 11:28 PM
creating a remote connection Adam H-W SQL Server 2000 1 February 10th, 2005 08:33 AM



All times are GMT -4. The time now is 08:15 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.