auto format of stored procedures?

hi,
i have many stored procedures, and i'm currently working on one with many
levels of nested IF's. it came from someone else, and i'm modifying it.
So...i'd love a utility, that i've seen in most other dev environments, that
will 'pretty print', or automatically format all the levels of a stored
procedure.
any one know of such a thing? i don't find much when googling!
thanks,
fred
You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
what is called 'Smart formatting' -though many may quibble about how 'smart'
it may be...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> hi,
> i have many stored procedures, and i'm currently working on one with many
> levels of nested IF's. it came from someone else, and i'm modifying it.
> So...i'd love a utility, that i've seen in most other dev environments,
> that will 'pretty print', or automatically format all the levels of a
> stored procedure.
> any one know of such a thing? i don't find much when googling!
> thanks,
> fred
>
|||thanks arnie, i'll take a look!
fred
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQDB%23CP4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
> what is called 'Smart formatting' -though many may quibble about how
> 'smart' it may be...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
> news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
>
sql

Auotmated Stored Procedure

Hi
Is there is any way to run the stored procedure automatically from master
database on daily at particular time.
Thanks is advance
NizhamYes, create a SQL Server Agent job with a TSQL jobstep which executes the pr
ocedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Junkmn" <junkmn@.gmail.com> wrote in message news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl..
.
> Hi
> Is there is any way to run the stored procedure automatically from master
> database on daily at particular time.
> Thanks is advance
> Nizham
>|||Yes. Create a job and schedule it to run at a specific time. From EM, you
can do this by using the Jobs node under Management. ( Make sure the SQL
Agent service is enabled & started )
Anith|||Thanks Tibor Karaszi
I hope you are expert on that If possible will you please send me the sample
code to run the stored procedure sp_XYZ to be run at 1.00 am
Regards
Nizham
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Yes, create a SQL Server Agent job with a TSQL jobstep which executes the
> procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Junkmn" <junkmn@.gmail.com> wrote in message
> news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl...|||It's much easier to use the GUI than to write the code for you. Have a look
at SQL Server Agent topics in Books Online. You can also see a brief
example here:
http://www.aspfaq.com/2403
"Junkmn" <junkmn@.gmail.com> wrote in message
news:O03vlkLMGHA.668@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor Karaszi
> I hope you are expert on that If possible will you please send me the
> sample code to run the stored procedure sp_XYZ to be run at 1.00 am
> Regards
> Nizham
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
>

Auditing Config

I need to query system tables for various config options. Anyone know if the auditing options(login-success/fail/all) are stored in a table and what that table might be?
Jeffexec sp_configure is the supported way to query this, or you can look at
master.dbo.spt_values
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Jeff Ericson" <jeff.ericson@.mbna.com> wrote in message
news:FB99D88D-62D5-44D2-8D01-9AC00EF28731@.microsoft.com...
> I need to query system tables for various config options. Anyone know if
the auditing options(login-success/fail/all) are stored in a table and what
that table might be?
> Jeff|||Couple of options
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Ericson" <jeff.ericson@.mbna.com> wrote in message
news:FB99D88D-62D5-44D2-8D01-9AC00EF28731@.microsoft.com...
I need to query system tables for various config options. Anyone know if
the auditing options(login-success/fail/all) are stored in a table and what
that table might be?
Jeff|||Oops sent that last post a bit quick
Easiest way would be
exec xp_loginconfig 'audit level'
Details are in BOL
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Ericson" <jeff.ericson@.mbna.com> wrote in message
news:FB99D88D-62D5-44D2-8D01-9AC00EF28731@.microsoft.com...
I need to query system tables for various config options. Anyone know if
the auditing options(login-success/fail/all) are stored in a table and what
that table might be?
Jeff|||the detail actually is stored in registry...
>--Original Message--
>I need to query system tables for various config
options. Anyone know if the auditing options(login-
success/fail/all) are stored in a table and what that
table might be?
>Jeff
>.
>|||Hi Jeff
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You can get the Audit Level in the database security option from the
registry table or by extensive stored procedure 'xp_loginconfig'.
From the 'Start' menu of you Windows system, click 'Run', and run
'regedit'. You will get the a registration key 'AuditLevel' at the location
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
I should explain more about this key.
If your Audit Level is set to:
'None', the value would be 0x00000000(0);
'Success', the value would be 0x00000001(1);
'Failure', the value would be 0x00000002(2);
'All', the value would be 0x00000003(3).
You can also use the following code to retrieve the value of the Audit
Level:
Use master
exec xp_loginconfig 'audit level'
go
You will get
name config_value
----
audit_level success
I hope this would answer your question. If you still have questions, please
feel free to post any new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the replies, I'll thry the extended SP, we don't have access to the registry(or the server (&%*(&()!!!!).

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:

> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automat
ic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with
2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that de
finition?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote
in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automat
ic
> task to run all time.
> Thank so much

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so much
You may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:

> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much
|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much

Audit Drop table

We would like to create a audit table for storing information if any
objects like tables / stored procedures are dropped.
Table would also contain information about time and the user name who
has dropped the object.
Any help would be great.

Thanks in advance.
Kamal"Kamal" <karora@.melstar.com> wrote in message
news:a4d6a1fb.0402060028.654b0f88@.posting.google.c om...
> We would like to create a audit table for storing information if any
> objects like tables / stored procedures are dropped.
> Table would also contain information about time and the user name who
> has dropped the object.
> Any help would be great.
> Thanks in advance.
> Kamal

One solution would be to create a trace for any SQL commands containing the
word DROP - see sp_trace_create and Profiler in Books Online. Or you could a
third-party audit tool, such as this one:

http://www.lumigent.com/products/entegra/entegra.htm

Simon

Attempt to catch error using @@ERROR in SQL 2000 is failing

We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.

UPDATE PERSONS

SET FLOORNR = dbo.FloorNR(OFFICELOCATION)

WHERE OFFICELOCATION IS NOT NULL

IF(@.@.ERROR <> 0 OR @.@.ROWCOUNT = 0)

BEGIN

RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait

END

However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.

How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.

Is it possible for you to try to handle some of those situations inside of the function and have it not return any values in the case of an error?|||We already handle a number of situations inside the functions. But once every week a user finds a new way to enter his office location and our procedure stops responding. At the moment, we have no way of controlling the input by our users, but we don't want to keep on expanding the procedure. In fact, we just want users to enter their office location in a standard way. We will change the application which handles that, but that change is still some months away. Untill then, we just want the catch any errors and log them.

Attachment file is invalid.

Hello,

I have a stored procedure with this syntax

declare @.filename varchar(100)

set @.filename='c:/A.pdf'

EXEC msdb.dbo.sp_send_dbmail

@.profile_name =@.p_profile

@.recipients = @.p_address,

@.body = @.p_body,

@.subject = @.p_subject,

@.file_attachments = @.filename

but when I execute it i have an error

Attachment file c:/A.pdf is invalid.

What's happen? Thanks in advance

hi,

It seems you are trying to load the file from invalid path. Change to

@.filename='C:\A.pdf' instead of 'c:/A.pdf'

Also make sure that A.pdf file is availed in C Drive.

|||Moving to the Transact-SQL forum.

Attach to SQL Server 2K5 Express Problem

I am building a web app using VS 2K5 with SQL Server Express as the backend.
The project and database are stored in the default location: My
Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also downloaded and
installed the SQL Server Management Studio Express to be able to manage the
database. The problem I have is that I am unable to attach the database. I
go through the process but the SSMSE won't allow me to navigate to where my
database is. I'm needing to import a table with a lot of data. Is this a
bug? Do I need to temporarily move my database file? Thanks.
hi Phill,
Phill wrote:
> I am building a web app using VS 2K5 with SQL Server Express as the
> backend. The project and database are stored in the default location:
> My Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also
> downloaded and installed the SQL Server Management Studio Express to
> be able to manage the database. The problem I have is that I am
> unable to attach the database. I go through the process but the
> SSMSE won't allow me to navigate to where my database is. I'm
> needing to import a table with a lot of data. Is this a bug? Do I
> need to temporarily move my database file? Thanks.
I do not currently have VS2005 installed, but with SSMSE I was able to
navigate to My Documents\Visual Studio 2005\WebSites\MyWebSiteName (the
\WebSites\MyWebSiteName folders have been actually created by me as, again,
I do not have VS2005 installed on that machine) both with a windows
administrator member (trusted connections) and the "traditional" "sa" SQL
Server login (mixed mode security)
I do not think VS2005 sets some sort of particular ACLs for that folder,
but I can be wrong here..
the account running my SQLExpress instance is LocalSystem..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||When you created the database in that location, you probably did it as a
user instance because that's the Visual Studio default. The user running
Visual Studio has permissions to that directory because he created it. You
are now trying to attach it to the main SQL Server instance instead of the
user instance, The main SQL Express instance is running as Network Service
and more than likely does not have permissions to open files in you
application directory. You can get around this by giving Network Service
read-write permission on the database and log files. Be aware that while
the files are attached to the main instance, your application using the user
instance connection will not be able to access them.
Check this article for an explanation of user instances:
http://msdn.microsoft.com/sql/expres...xpuserinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:1CF63F8E-902B-4AD7-A2F8-B7DA7233D7AE@.microsoft.com...
>I am building a web app using VS 2K5 with SQL Server Express as the
>backend.
> The project and database are stored in the default location: My
> Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also downloaded
> and
> installed the SQL Server Management Studio Express to be able to manage
> the
> database. The problem I have is that I am unable to attach the database.
> I
> go through the process but the SSMSE won't allow me to navigate to where
> my
> database is. I'm needing to import a table with a lot of data. Is this a
> bug? Do I need to temporarily move my database file? Thanks.
|||Thanks, I am now able to connect to the database. It didn't really do me any
good though. I want to import an Access table into it but there aren't any
tools to do so. No ODBC drivers, nothing. So how are we supposed to import
data into SSE2K? Thanks.
"Roger Wolter[MSFT]" wrote:

> When you created the database in that location, you probably did it as a
> user instance because that's the Visual Studio default. The user running
> Visual Studio has permissions to that directory because he created it. You
> are now trying to attach it to the main SQL Server instance instead of the
> user instance, The main SQL Express instance is running as Network Service
> and more than likely does not have permissions to open files in you
> application directory. You can get around this by giving Network Service
> read-write permission on the database and log files. Be aware that while
> the files are attached to the main instance, your application using the user
> instance connection will not be able to access them.
> Check this article for an explanation of user instances:
> http://msdn.microsoft.com/sql/expres...xpuserinst.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:1CF63F8E-902B-4AD7-A2F8-B7DA7233D7AE@.microsoft.com...
>
>
|||Use the upsizing Wizard in access to push the data into SSE
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F00DFB95-4D36-47CC-9A46-41A5A3577D60@.microsoft.com...[vbcol=seagreen]
> Thanks, I am now able to connect to the database. It didn't really do me
> any
> good though. I want to import an Access table into it but there aren't
> any
> tools to do so. No ODBC drivers, nothing. So how are we supposed to
> import
> data into SSE2K? Thanks.
> "Roger Wolter[MSFT]" wrote: