SQL Server Management Studio Cannot Open SQL Express Files

microsoft.public.sqlserver.tools

28 Jan 2009 9:11 PM

EagleRed@HighFlyingBirds.com

I am writing an application on a Vista machine with SQL Server 2005 Developer
Edition installed using VS2005 Team Architect. I also have SQL Express
installed. I have a requirement to develop and deploy a Web service that
connects to a SQL Express database.

Here is the problem. If I create the database in VS2005 using the
Membership wizard, I can create the database in the App_Data directory and I
can work with it in the Server Explorer. However, I cannot open the files in
the Management Studio to perform any administrative functions like creating
logons and setting permissions.

If I create the DB in Management Studio I cannot work with it in VS2005.

In both case I get access denied errors on the files in the alternate
environment. Since I have the main Management Studio running on the machine
I cannot install the SQL Express Management Studio.

How can I resolve this so I can work with the database in VS2005 and in
Management Studio?

Any pertinent input is greately appreciated.

Thanks,
Eagle

28 Jan 2009 9:38 PM

Ekrem_Ã–nsoy

May there be two separate instances are installed on your computer?

I suggest you to open SQL Server Configuration Manager and see how many SQL
Server instances are installed and you can see what instance of SQL Server
is being used by VS from VS's Tools->Options->Database Tools->Data
Connections.

Your databases that you create may be attached to the instance "X" and the
instance that you connect from SSMS may be instance "Y" and because
databases can be attached only to one instance, you experience this problem.

Note:
When you have SSMS installed on your computer, you don't need SSMSE (Express
edition of SSMS) because SSMSE is a light version of SSMS which comes with
not-for-free SQL Server editions.

--
Ekrem Ã–nsoy

"Eagle***@HighFlyingBirds.com"
<EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message

Show quoteHide quote

news:FEFDB265-5936-473D-B764-D8049639F018@microsoft.com...
>I am writing an application on a Vista machine with SQL Server 2005
>Developer
> Edition installed using VS2005 Team Architect. I also have SQL Express
> installed. I have a requirement to develop and deploy a Web service that
> connects to a SQL Express database.
>
> Here is the problem. If I create the database in VS2005 using the
> Membership wizard, I can create the database in the App_Data directory and
> I
> can work with it in the Server Explorer. However, I cannot open the files
> in
> the Management Studio to perform any administrative functions like
> creating
> logons and setting permissions.
>
> If I create the DB in Management Studio I cannot work with it in VS2005.
>
> In both case I get access denied errors on the files in the alternate
> environment. Since I have the main Management Studio running on the
> machine
> I cannot install the SQL Express Management Studio.
>
> How can I resolve this so I can work with the database in VS2005 and in
> Management Studio?
>
> Any pertinent input is greately appreciated.
>
> Thanks,
> Eagle

Are all your drivers up to date? click for free checkup

28 Jan 2009 10:26 PM

EagleRed@HighFlyingBirds.com

In VS2005 I am connected to the SQLExpress instance. I acually have 3
instances of SQL Server on my machine (I am doing some crazy stuff).
Currently I can work with the DB in SSMS but not in VS. I actually have
other projects, both VS2005 and VS2008, that work with the local instance.
When I open those projects I see the databases in the Server explorer and I
can work with them even though the ->Options->Database Tools->Data

> Connections output shows I am connected to SQLEXPRESS at the time in VS

Good news, I just created a new connection to the DB using the
machine\SQLEXPRESS\DBName reference to the DB and it seems to be working. I
will see how it goes and post back one way or another.

Thanks very much for the input in any case.

HAVE A BLESSED DAY!!!!

Thanks,
Eagle.

Show quoteHide quote

"Ekrem Ã–nsoy" wrote:

> May there be two separate instances are installed on your computer?
>
> I suggest you to open SQL Server Configuration Manager and see how many SQL
> Server instances are installed and you can see what instance of SQL Server
> is being used by VS from VS's Tools->Options->Database Tools->Data
> Connections.
>
> Your databases that you create may be attached to the instance "X" and the
> instance that you connect from SSMS may be instance "Y" and because
> databases can be attached only to one instance, you experience this problem.
>
> Note:
> When you have SSMS installed on your computer, you don't need SSMSE (Express
> edition of SSMS) because SSMSE is a light version of SSMS which comes with
> not-for-free SQL Server editions.
>
> --
> Ekrem Ã–nsoy
>
>
>
> "Eagle***@HighFlyingBirds.com"
> <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message
> news:FEFDB265-5936-473D-B764-D8049639F018@microsoft.com...
> >I am writing an application on a Vista machine with SQL Server 2005
> >Developer
> > Edition installed using VS2005 Team Architect. I also have SQL Express
> > installed. I have a requirement to develop and deploy a Web service that
> > connects to a SQL Express database.
> >
> > Here is the problem. If I create the database in VS2005 using the
> > Membership wizard, I can create the database in the App_Data directory and
> > I
> > can work with it in the Server Explorer. However, I cannot open the files
> > in
> > the Management Studio to perform any administrative functions like
> > creating
> > logons and setting permissions.
> >
> > If I create the DB in Management Studio I cannot work with it in VS2005.
> >
> > In both case I get access denied errors on the files in the alternate
> > environment. Since I have the main Management Studio running on the
> > machine
> > I cannot install the SQL Express Management Studio.
> >
> > How can I resolve this so I can work with the database in VS2005 and in
> > Management Studio?
> >
> > Any pertinent input is greately appreciated.
> >
> > Thanks,
> > Eagle
>

29 Jan 2009 12:17 AM

EagleRed@HighFlyingBirds.com

I have got things kind of working now. I cannot execute the stored
procedures in SSMS. When I execute stored procs in VS that should return a
result set, it is not shown. Also, when I publish VS does not have
permission to write the DB files to the publish media.

Any ideas on this.

Thanks,
Eagle

Show quoteHide quote

"Eagle***@HighFlyingBirds.com" wrote:

> In VS2005 I am connected to the SQLExpress instance. I acually have 3
> instances of SQL Server on my machine (I am doing some crazy stuff).
> Currently I can work with the DB in SSMS but not in VS. I actually have
> other projects, both VS2005 and VS2008, that work with the local instance.
> When I open those projects I see the databases in the Server explorer and I
> can work with them even though the ->Options->Database Tools->Data
> > Connections output shows I am connected to SQLEXPRESS at the time in VS
>
> Good news, I just created a new connection to the DB using the
> machine\SQLEXPRESS\DBName reference to the DB and it seems to be working. I
> will see how it goes and post back one way or another.
>
> Thanks very much for the input in any case.
>
> HAVE A BLESSED DAY!!!!
>
> Thanks,
> Eagle.
>
> "Ekrem Ã–nsoy" wrote:
>
> > May there be two separate instances are installed on your computer?
> >
> > I suggest you to open SQL Server Configuration Manager and see how many SQL
> > Server instances are installed and you can see what instance of SQL Server
> > is being used by VS from VS's Tools->Options->Database Tools->Data
> > Connections.
> >
> > Your databases that you create may be attached to the instance "X" and the
> > instance that you connect from SSMS may be instance "Y" and because
> > databases can be attached only to one instance, you experience this problem.
> >
> > Note:
> > When you have SSMS installed on your computer, you don't need SSMSE (Express
> > edition of SSMS) because SSMSE is a light version of SSMS which comes with
> > not-for-free SQL Server editions.
> >
> > --
> > Ekrem Ã–nsoy
> >
> >
> >
> > "Eagle***@HighFlyingBirds.com"
> > <EagleRedHighFlyingBirds***@discussions.microsoft.com> wrote in message
> > news:FEFDB265-5936-473D-B764-D8049639F018@microsoft.com...
> > >I am writing an application on a Vista machine with SQL Server 2005
> > >Developer
> > > Edition installed using VS2005 Team Architect. I also have SQL Express
> > > installed. I have a requirement to develop and deploy a Web service that
> > > connects to a SQL Express database.
> > >
> > > Here is the problem. If I create the database in VS2005 using the
> > > Membership wizard, I can create the database in the App_Data directory and
> > > I
> > > can work with it in the Server Explorer. However, I cannot open the files
> > > in
> > > the Management Studio to perform any administrative functions like
> > > creating
> > > logons and setting permissions.
> > >
> > > If I create the DB in Management Studio I cannot work with it in VS2005.
> > >
> > > In both case I get access denied errors on the files in the alternate
> > > environment. Since I have the main Management Studio running on the
> > > machine
> > > I cannot install the SQL Express Management Studio.
> > >
> > > How can I resolve this so I can work with the database in VS2005 and in
> > > Management Studio?
> > >
> > > Any pertinent input is greately appreciated.
> > >
> > > Thanks,
> > > Eagle
> >

29 Jan 2009 1:49 AM

Aaron Bertrand [SQL Server MVP]

> I have got things kind of working now. I cannot execute the stored
> procedures in SSMS.

What does this mean? I just got a similar message from a colleague. "I get
an error when I do x." Okay, that's a great start, but help me help you.
What on earth is "x" and what is the EXACT error message you get (or a
better explanation of what "cannot" means)?

> When I execute stored procs in VS that should return a
> result set, it is not shown.

I'm confused. Are you trying to execute stored procedures from SSMS or
Visual Studio? Why both?

29 Jan 2009 3:02 PM

EagleRed@HighFlyingBirds.com

I set the connection in VS2005 to TIMON\SQLEXPRESS where TIMON is the machine
name. I have a connection in the Server explorer on this instance.
When I build or publish the Web service I get the following errors:

Error 5 Unable to copy file
"F:\Projects\US-Christian-Chamber\NET20\USCCCSolution1\USCCCService\App_Data\USCCCDB.mdf"
to "bin\App_Data\USCCCDB.mdf". Access to the path
'F:\Projects\US-Christian-Chamber\NET20\USCCCSolution1\USCCCService\App_Data\USCCCDB.mdf' is denied. USCCCService

Error 6 Unable to copy file
"F:\Projects\US-Christian-Chamber\NET20\USCCCSolution1\USCCCService\App_Data\USCCCDB_log.ldf"
to "bin\App_Data\USCCCDB_log.ldf". Access to the path
'F:\Projects\US-Christian-Chamber\NET20\USCCCSolution1\USCCCService\App_Data\USCCCDB_log.ldf' is denied. USCCCService

I get these errors in various scenarios that I have previously described.
Sorry, that this is so messy, but I have tried several approaches all to end
with the same errors.

With regard to your question:

I'm confused. Are you trying to execute stored procedures from SSMS or
Visual Studio? Why both?

I am just trying to check the correctness of the stored procedures, sprocs,
and to debug them. I want to be able to check the execution of the sprocs
from both VS and SSMS because I am working with the data store in both. I
need to be able to perform admin tasks in SSMS, like setting permissions,
that I cannot do in VS. I want to debug the sprocs, if needed, that I have
to do in VS. In SSMS when I right click on a sproc to get the context menu,
the "Execute" option does not appear.
When I do the same on a sproc in a non-SQLExpress connection it does.

Furthermore, the service is written in C# and the sprocs will be called from
the code which will require a connection string to open the connection. I
want to be sure that I get the connection string correct with the desired
credentials.

Once again, I appreciate the input in that progress has been made on this.
However, I am not yet whole.

Thanks,
Eagle

Show quoteHide quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > I have got things kind of working now. I cannot execute the stored
> > procedures in SSMS.
>
> What does this mean? I just got a similar message from a colleague. "I get
> an error when I do x." Okay, that's a great start, but help me help you.
> What on earth is "x" and what is the EXACT error message you get (or a
> better explanation of what "cannot" means)?
>
> > When I execute stored procs in VS that should return a
> > result set, it is not shown.
>
> I'm confused. Are you trying to execute stored procedures from SSMS or
> Visual Studio? Why both?
>
>

29 Jan 2009 3:39 PM

Aaron Bertrand [SQL Server MVP]

> I set the connection in VS2005 to TIMON\SQLEXPRESS where TIMON is the machine
> name. I have a connection in the Server explorer on this instance.
> When I build or publish the Web service I get the following errors:
>
> Error 5 Unable to copy file
> "F:\Projects\US-Christian-Chamber\NET20\USCCCSolution1\USCCCService\App_Data\U
> SCCCDB.mdf"
> to "bin\App_Data\USCCCDB.mdf". Access to the path
> 'F:\Projects\US-Christian-Chamber\NET20\USCCCSolution1\USCCCService\App_Data\U
> SCCCDB.mdf' is denied. USCCCService

It sounds like either you don't have rights to F:\ or the files are in use.
My bet is the latter (you can't just overwrite the files if they're already
attached to a running SQL Server instance).

> from both VS and SSMS because I am working with the data store in both. I
> need to be able to perform admin tasks in SSMS, like setting permissions,
> that I cannot do in VS. I want to debug the sprocs, if needed, that I have
> to do in VS. In SSMS when I right click on a sproc to get the context menu,
> the "Execute" option does not appear.

Open a new query window in that database and type:

EXEC dbo.ProcedureName;

If the name is too long to type, you can drag the name of the procedure onto
the query window.

FWIW, I have found very little value in "debugging" a stored procedure, as
they are nowhere near the complexity of object-oriented code. Even if you
have GOTO etc. you can probably get by with judicious use of a @debug
parameter and PRINT/RAISERROR. If you get the new version of SSMS, it
includes debugging tools. Since the debugging is not complete and you can
not get access to the call stack, see temporary table contents, etc., this
is not all that valuable to me. Plus you cannot debug remotely and it
requires specific permissions which not all users have. The debug button is
the first thing I remove from the toolbar every time I install a new
instance of the SQL Server 2008 management studio. Not because I find it
useless but because I used Query Analyzer for a long time, and the new debug
icon gets clicked by accident far too often, because it is identical to
Query Analyzer's "execute" button. YMMV.

> When I do the same on a sproc in a non-SQLExpress connection it does.

Using the same client tool (e.g. you're talking about SSMS and not VS in
both cases)? And what does "it does" mean? It executes, or you see an
Execute option? When it does not appear, is it grayed out or is it really
not there?

Also, if you have a non-SQLExpress instance of SQL Server on your machine,
why are you trying to do all of these advanced things against an Express
instance?