Disk space information in SQL Server 2005.

As a normal sql user we are not able to get the disk space information by

executing 'xp_fixeddrive' extended stored procedure.

We are able to get the result using sa user and windows authenticated user.

Any help will be appreciated.

You may get the information you seek by querying: sys.sysfiles,

or perhaps using the dynamic management view: sys.dm_db_file_space_usage

Refer to Books Online for more information about either of them.

|||the xp_fixeddrives can get the free space in the disk(drivers). it is different to sys.sysfiles.

|||do you have the permission to run this extend procedure? any error informaiton that you got when you used a normall sql user?

I think it caused by the permission. .when you login as sa or windows administrator account , it belongs to sysadmin group by default.

Disabling the AUTOCOMMIT option in MSSQLServer

Hello,

Is there anyway.. we can disable AUTOCOMMIT option in MSSQL server
while executing the SQL queries via SQL ANALYZERbegin transaction|||On 8 Jun 2006 08:01:15 -0700, Uncle Sam wrote:

>Hello,
>Is there anyway.. we can disable AUTOCOMMIT option in MSSQL server
>while executing the SQL queries via SQL ANALYZER

Hi Sam,

In Query Analyzer (for SQL Server 2000): Tools / Options / Connection
Properties / Set implicit_transactions to ON. This affects only new
connections.
Also in Query Analyzer: Query / Current coonnection pproperties / Set
implicit_transactions ON to affect the current connection.

In SQL Server Management Studio (for SQL Server 2005): Tools / Options /
Query Execution / SQL Server / ANSI / SET IMPLICIT_TRANSACTIONS ON (for
new connections), and Query / Query Options / Execution / ANSI / SET
IMPLICIT_TRANSACTIONS ON (for the current connection).

--
Hugo Kornelis, SQL Server MVP

Disabling Foreign Key Constraint in Trigger

Hey,
Can anybody tell me how to disable a Foreign Key contraint from executing
while a Trigger is executing on DELETE event?
Best regards,
BillDRI are checked berofe the AFTER TRIGGERS, so if it is a problem the trigger
will not be fired.
AMB
"AST" wrote:

> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>
>|||Hey AMB,
The table [HistoryDetails] has a FK on the column HistoryID that is being
used to enforce integrity with the table[History].
I am not sure if you are stating you can't disable the FK or if I have to
modify the context of the Trigger?
Best regards,
Bill
The Trigger is defined as:
/****** Object: Trigger dbo.TRIGGER_CascDeleteOnHistoryDetails Script
Date: 5/24/2002 12:14:53 AM ******/
CREATE TRIGGER [TRIGGER_CascDeleteOnHistoryDetails] ON dbo.History
FOR DELETE
AS
DELETE HistoryDetails FROM deleted, HistoryDetails WHERE deleted.HistoryID =
HistoryDetails.HistoryID
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:4662B0B5-25C5-4217-84F0-B75E5ECCF604@.microsoft.com...
> DRI are checked berofe the AFTER TRIGGERS, so if it is a problem the
trigger
> will not be fired.
>
> AMB
>
> "AST" wrote:
>
executing|||That will make quite a mess:
1) Disabling a Foreign Key constraint is Data Definition Language, and can
only be executed by the table owner and members of the symin fixed server
roles and the db_owner and db_ddladmin database roles, and is not
transferable. So you have to give your users a large number of unnecessary
permissions.
2) Disabling a foreign key works for all connections, not just the current
one. So while you disable it in your trigger, someone else might need it.
What exactly are you trying to achieve? This is definitely not the right
way.
Jacco Schalkwijk
SQL Server MVP
"AST" <no_reply@.please.com> wrote in message
news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>|||Hey Jacco,
What I am trying to achieve is the following:
I have 2 tables [History], [HistoryDetails] where the Primary Table
[History] mantains detailed data in the [HistoryDetails] table. The key
relationship is History.HistoryID=> HistoryDetails.HistoryID.
I have place a constraint on the [HistoryDetails] table such that a record
cannot be added unless the HistoryID is existing in the [History] table.
I had also created a trigger to delete all relational records in the
[HistoryDetails] table when the Primary Key record in the [History] table
was deleted.
This is where I run into problems because the execution of the trigger fails
due to the FK Constraint.
Any suggestions would be very welcome?
Best regards,
Bill
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uEUZEs6EFHA.2700@.TK2MSFTNGP14.phx.gbl...
> That will make quite a mess:
> 1) Disabling a Foreign Key constraint is Data Definition Language, and can
> only be executed by the table owner and members of the symin fixed
server
> roles and the db_owner and db_ddladmin database roles, and is not
> transferable. So you have to give your users a large number of unnecessary
> permissions.
> 2) Disabling a foreign key works for all connections, not just the current
> one. So while you disable it in your trigger, someone else might need it.
> What exactly are you trying to achieve? This is definitely not the right
> way.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AST" <no_reply@.please.com> wrote in message
> news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
executing
>|||Drop the trigger and use the ON DELETE CASCADE option on the foreign
key constraint.
David Portas
SQL Server MVP
--|||Hey David,
I don't think this is supported in SQL 7 and I have to support both SQL7 and
SQL2000
I tried running the SQL to add this constraint in SQL7 and it fails and
doesn't seem to recognize anthing to do with cascaded deletes?
Best regards,
Bill
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
> Drop the trigger and use the ON DELETE CASCADE option on the foreign
> key constraint.
> --
> David Portas
> SQL Server MVP
> --
>|||AST
Well, you have to drop a FK constraint and to allow deletion within a
trigger.
"AST" <no_reply@.please.com> wrote in message
news:emTg1W8EFHA.624@.TK2MSFTNGP09.phx.gbl...
> Hey David,
> I don't think this is supported in SQL 7 and I have to support both SQL7
and
> SQL2000
> I tried running the SQL to add this constraint in SQL7 and it fails and
> doesn't seem to recognize anthing to do with cascaded deletes?
> Best regards,
> Bill
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
>

Disabled sp_oacreate in sql 2000?

Hi all,
I have an issue where a SQL server (2000 SP3a) is not executing
external objects. I have granted permissions to all sp_oa* stored
procedures as well as xp_cmdshell; I also ensured read/execute access
is enabled for Everyone on wscript.exe/dll. I am running the following
as 'sa':
DECLARE @.pid int, @.hr int, @.path varchar(500)
SET @.path = 'd:\public\\script\job.wsf'
EXEC @.hr = sp_OACreate 'WScript.Shell', @.pid OUT
EXEC @.hr = sp_OAMethod @.pid, 'Run', NULL, @.path
EXEC @.hr = sp_OADestroy @.pid
Command completes successfully, but the script never executes. I
have tryed with other objects and same result. I can see in Profiler
the script executes fine, but Windows Script Host never gets invoked.
This works on one server but not another; a third party hosting
facility installed the OS and DB but I'm not sure what they have
disabled, etc. or how to enable this. I know they've played with
security policies and registry settings but don't know what fixes this
issue.
Any help is greatly appreciated. Have a super day!
> Rich NorgaardDoes it work when you are logged in with sysadmin permissions? I'm thinking the proxy account
config...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Rich" <richnorgaard@.hotmail.com> wrote in message
news:447699bb.0310151115.268b1c5c@.posting.google.com...
> Hi all,
> I have an issue where a SQL server (2000 SP3a) is not executing
> external objects. I have granted permissions to all sp_oa* stored
> procedures as well as xp_cmdshell; I also ensured read/execute access
> is enabled for Everyone on wscript.exe/dll. I am running the following
> as 'sa':
> DECLARE @.pid int, @.hr int, @.path varchar(500)
> SET @.path = 'd:\public\\script\job.wsf'
> EXEC @.hr = sp_OACreate 'WScript.Shell', @.pid OUT
> EXEC @.hr = sp_OAMethod @.pid, 'Run', NULL, @.path
> EXEC @.hr = sp_OADestroy @.pid
> Command completes successfully, but the script never executes. I
> have tryed with other objects and same result. I can see in Profiler
> the script executes fine, but Windows Script Host never gets invoked.
> This works on one server but not another; a third party hosting
> facility installed the OS and DB but I'm not sure what they have
> disabled, etc. or how to enable this. I know they've played with
> security policies and registry settings but don't know what fixes this
> issue.
> Any help is greatly appreciated. Have a super day!
> > Rich Norgaard

Disable Job Still Executing

I have data archive process that disables the backup job then enables the
job when the process has completed. One thing the job still executes as
schedule. I tried disabling the schedule as well as the job which did not
help.
I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
Thanks in advance
BruceHi Bruce
You should be ok with just disabling the schedule with a command such as
EXEC msdb..sp_update_jobschedule @.job_name = 'My Backup Job',
@.name ='Nighly
Backup @. 22:00',
@.enabled = 0
Make sure that the job history shows that the jobs were run and that when
you display the steps, the step to disable the job was run. You may also wan
t
to log this to a file.
John
"Bruce" wrote:

> I have data archive process that disables the backup job then enables the
> job when the process has completed. One thing the job still executes as
> schedule. I tried disabling the schedule as well as the job which did not
> help.
> I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
> Thanks in advance
> Bruce
>
>

Disable Job Still Executing

I have data archive process that disables the backup job then enables the
job when the process has completed. One thing the job still executes as
schedule. I tried disabling the schedule as well as the job which did not
help.
I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
Thanks in advance
BruceHi Bruce
You should be ok with just disabling the schedule with a command such as
EXEC msdb..sp_update_jobschedule @.job_name = 'My Backup Job',
@.name ='Nighly
Backup @. 22:00',
@.enabled = 0
Make sure that the job history shows that the jobs were run and that when
you display the steps, the step to disable the job was run. You may also want
to log this to a file.
John
"Bruce" wrote:
> I have data archive process that disables the backup job then enables the
> job when the process has completed. One thing the job still executes as
> schedule. I tried disabling the schedule as well as the job which did not
> help.
> I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
> Thanks in advance
> Bruce
>
>