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

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
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:

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

"EagleRed (AT) HighFlyingBirds (DOT) com" wrote:

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)?

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

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

"Aaron Bertrand [SQL Server MVP]" wrote:

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).

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.

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?