MS SQL 2005 Configuration ???

[Jonathan Wood]

Greetings,

I'm fairly new to ASP.NET/ADO.NET and SQL. I worked out a few issues with
the help of some nice folks here.

Now, I'm starting a clean Web site project but am having trouble right off
again.

I'm trying to create a new database using MS SQL Server 2005. I entered
localhost as the Server name, selected Use Windows Authentication, and
entered a new name for my database. When I click OK, I get the following
error:

Microsoft Visual Studio
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)

As far as I know, localhost does not indicate a remote connection. I
eventually connected to a database previously with SQL Express Edition, so
I'm guessing this might have to do with my SQL configuration.

I'm using Visual Studio 2005 on a new C#/ASP.NET project. I'm running
Windows XP Professional w/SP2. Sorry, but virtually every suggestion I get
will probably need to include instructions on how to do--I'm quite new to
this.

Any help greatly appreciated!

 

[RobinS]

Open the Surface Area Configuration for SQLServer and
select "configuring the Services & Connections",
select "Remove Connections" under "Database Engine".
Select "Local and Remote connections".
I have mine set to accept both TCP/IP and named pipes.

Remote connection does not mean a connection from another
computer; it means a connection from outside of SQLserver,
like from a website or application, even if they are running
on the same machine.

Robin S.

 

[Jonathan Wood]

Thanks but my screens must look different than yours.

I have a command under my SQL Server 2005 menu called "SQL Surface Area
Configuration". So I selected that.

That opened a window where the closest thing to "configuring the Services &
Connections" was "Surface Area Configuration for Services and Connections".

That opens another window, but I don't see anything about "Remove
Connections" or "Database Engine". It has a tree list with the two roots
being "SQLEXPRESS" and "SQL Server Browser". I noticed the SQL Server
Browser was disabled. I managed to enable it but that did not resolve the
issue.

Do you know where I went wrong? It says SQL Server 2005--why is the first
item SQLEXPRESS? How can I confirm I have SQL 2005 installed?

Thanks again.

 

[Jonathan Wood]

One other queston:

The reason I don't just use SQLEXPRESS is because my web host doesn't
support it.

If I use SQLEXPRESS on my development machine, I guess I can create some
sort of script for recreating the data on the server. Do you know if that
will cause any problems if the script is converting from SQLEXPRESS and MS
SQL Server? If not, that might be the easiest approach.

 

[RobinS]

It sounds like you are running SQLExpress, not SQLServer.
I don't have SQL Express, so I can't replicate what you're seeing.

When I open my Surface Area Configuration and select
"Surface Area Configuration for Services and Connections",
it brings up another screen with a listbox on the left-hand
side.

The first entry in my listbox is the name of my SQLServer
instance (yours may be SQLExpress). (Also displayed on
that level of the tree are Integration Services and
SQL Server Browser). When I open my SQLServer branch
of the tree, I see Database Engine, Reporting Services,
SQLServer Agent, and FUll-TextSearch.

Under DatabaseEngine, I have two options: Service and
Remote Connections. The latter is the one you're looking for.

Try opening the one in your tree list that says SQLExpress.
I would think that corresponds to my SQLServer instance.

As for how to confirm that SQLServer2005 is installed, I know
you can have both the full version and the Express version
installed at the same time. When I hit Start/Programs, I see
an entry for "Microsoft SQL Server 2005" which has links to
all the server stuff like SQLServer Management Studio, SQLServer
Business Integration Development Studio, Configuration, etc.
Do you see that on your machine?

You can also look at the running services on your machine
and see if you can tell from there. Open your control panel,
open Administrative Tools, open Services and see what you
see. I've got 8 different SQLServer entries -- the actual
server (it shows my server name), Active Directory helper,
Agent, Browser, FullText Search, Integration Services,
Reporting Services, and VSS Writer. What do you see?

Hope this helps.

Robin S.
--------------------------------------------------

 

[RobinS]

If you use SQLExpress on your development machine, you can easily
copy the files over to your web server and attach them to the
SQLServer instance. You don't need to do anything to them, or
write any scripts, unless you are just updating the tables,
rather than creating them the first time.

With regular SQLServer, I have to detach those files before
moving them. I don't know if you need to do that with SQLExpress
or not, but if you have a configuration manager, it wouldn't
hurt to try.

In SQLServer, where I see the list of databases, I can just
right-click on one, do ShowAllTasks, and there's a Detach
Database option.

Good luck.
Robin S.
---------------------------------------

 

[Jonathan Wood]

RobinS,

It sounds like you are running SQLExpress, not SQLServer.
I don't have SQL Express, so I can't replicate what you're seeing.

I'm really not sure why you say that. Why would I have Microsoft SQL Server
in my menu as I described if I only have SQL Express?

In SQLServer, where I see the list of databases, I can just
right-click on one, do ShowAllTasks, and there's a Detach
Database option.

Right.

It looks like I have MS SQL Server. I believe I installed that before for VS
2003, which I understood did not come with a free version of the server. At
any rate, SQL Express is working fine on my machine and so I will use it for
testing if I can convert the data to MS SQL Server when I put it on my Web
host sever.

Thanks.

 

[RobinS]

RobinS,


I'm really not sure why you say that. Why would I have Microsoft SQL
Server in my menu as I described if I only have SQL Express?


Right.

It looks like I have MS SQL Server. I believe I installed that before for
VS 2003, which I understood did not come with a free version of the
server. At any rate, SQL Express is working fine on my machine and so I
will use it for testing if I can convert the data to MS SQL Server when I
put it on my Web host sever.

Thanks.

You should be able to run both versions on your computer at the same time.
I don't know why your SQLServer (not express) doesn't show in your server
list, unless it's not running, which is a distinct possibility.

If you want to see if it's even in the Services on your computer:

Start/Settings/Control Panel
double-click Administrative Tools
double-click Services
see if SQL Server is in the list, and if it's running.

At any rate, you can copy the SQL Express files over to the server and
attach them using the "production" instance running on the server when you
are ready to put it into production.

Hope this helps.

Robin S.

 

[Jonathan Wood]

RobinS,

If you want to see if it's even in the Services on your computer:

Start/Settings/Control Panel
double-click Administrative Tools
double-click Services
see if SQL Server is in the list, and if it's running.

This list shows:

SQL Server (SQLEXPRESS)
SQL Server Active Directory Helper
SQL Server Browser
SQL Server VSS Writer

And I thought I determined SQL Server was not running in the previous
discussions.

Either way, it doesn't matter. I don't know why that has to be so
complicated but I've moved on. I'll use SQLEXPRESS for testing on my
system--it appears to be working.

At any rate, you can copy the SQL Express files over to the server and
attach them using the "production" instance running on the server when you
are ready to put it into production.

Right.

Thanks.

 

[RobinS]

You don't have "big" SQLServer installed, or it would show up in the
Services, even if it were not running. You only have SQL Express installed.
But that will work great for you. Good luck with your project.

Robin S.
------------------------------------------