How do i know whether the connection is open with the sqlserver database while work in the

How do i know whether the connection is open with the sqlserver database while work in the application?

You should always open a connection explicity so if the Open method does not fail, you can assume it is open. It's best to wrap your connections in using statements like the following.

public int GetUserIdFromUserName(string userName)
{
int userId = -1;
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["knovoloCMS"].ConnectionString))
{
sqlConnection.Open();
try
{
String SqlDeleteString = @."
SELECT id FROM Users WHERE Username = @.Username
";
using (SqlCommand sqlCommand = new SqlCommand(SqlDeleteString, sqlConnection))
{
sqlCommand.Parameters.Add("@.Username", SqlDbType.VarChar).Value = userName;
userId = (int)sqlCommand.ExecuteScalar();
}
}
catch (Exception ee)
{

}
}
return userId;
}

|||

I generally use it like;

using (SqlConnection sqlConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["knovoloCMS"].ConnectionString))

{

if (sqlConnection.state != open)

sqlConnection.open();

......

}

How do I install MSDE after SQLServer 2000?

Hello,
I have SQLServer 2000 installed on a PC and I want to install
MSDE as well.
The attempted MSDE installation immediately fails with the
message:
"The instance name specified is invalid"
I looked at the autorun.inf file and I see that there is no name
specified for the new MSDE installation. Presumably it takes the
default machine name? The default machine name is already taken
by the existing SQL Server installation.
What are the correct lines to add to the MSDE autorun.inf file
for the default MSDE server name and sa account password?
After I have MSDE working is it possible to connect to it? I
would like to develop an ASP.NET web application that uses MSDE.
Ideally I would like to use something like Enterprise Manager
but to manage MSDE, at the very least I need something like
Query Analyser to work.
I note that there are some ASP.NET web apps in development that
emulate both Enterprise Manager and Query Analyser for MSDE. Has
anyone used these tools?
If necessary I can install MSDE on an other machine as I have a
LAN running here. I would prefer not to do that.
Hi,
Try executing the setup with Instance name:-
Setup.exe INSTANCENAME=<name> SAPWD=<sapassword >
Thanks
Hari
MCDBA
"Zenobia" <6.20.zenobia@.spamgourmet.com> wrote in message
news:atcid0l4i7qnu18vc7udj2ihmhru0neef5@.4ax.com...
> Hello,
> I have SQLServer 2000 installed on a PC and I want to install
> MSDE as well.
> The attempted MSDE installation immediately fails with the
> message:
> "The instance name specified is invalid"
> I looked at the autorun.inf file and I see that there is no name
> specified for the new MSDE installation. Presumably it takes the
> default machine name? The default machine name is already taken
> by the existing SQL Server installation.
> What are the correct lines to add to the MSDE autorun.inf file
> for the default MSDE server name and sa account password?
> After I have MSDE working is it possible to connect to it? I
> would like to develop an ASP.NET web application that uses MSDE.
> Ideally I would like to use something like Enterprise Manager
> but to manage MSDE, at the very least I need something like
> Query Analyser to work.
> I note that there are some ASP.NET web apps in development that
> emulate both Enterprise Manager and Query Analyser for MSDE. Has
> anyone used these tools?
> If necessary I can install MSDE on an other machine as I have a
> LAN running here. I would prefer not to do that.
>
|||On Wed, 23 Jun 2004 08:50:23 +0100, Zenobia
<6.20.zenobia@.spamgourmet.com> wrote:

>Hello,
>I have SQLServer 2000 installed on a PC and I want to install
>MSDE as well.
>The attempted MSDE installation immediately fails with the
>message:
>"The instance name specified is invalid"
>I looked at the autorun.inf file and I see that there is no name
>specified for the new MSDE installation. Presumably it takes the
>default machine name? The default machine name is already taken
>by the existing SQL Server installation.
>What are the correct lines to add to the MSDE autorun.inf file
>for the default MSDE server name and sa account password?
>After I have MSDE working is it possible to connect to it? I
>would like to develop an ASP.NET web application that uses MSDE.
>Ideally I would like to use something like Enterprise Manager
>but to manage MSDE, at the very least I need something like
>Query Analyser to work.
>I note that there are some ASP.NET web apps in development that
>emulate both Enterprise Manager and Query Analyser for MSDE. Has
>anyone used these tools?
>If necessary I can install MSDE on an other machine as I have a
>LAN running here. I would prefer not to do that.
Apologies for wasting your time. I needed to add some lines to
the setup.ini file:
++++++++++++++++++++++++++++++++++++++++
[Options]
InstanceName="instance_name"
SAPWD="sa_password"
SecurityMode=SQL
++++++++++++++++++++++++++++++++++++++++
See:
http://msdn.microsoft.com/library/de...stsql_84xl.asp
http://support.microsoft.com/default...99&Product=sql
++++++++++++++++++++++++++++++++++++++++
But if anyone's reading. I'd appreciate help on utilities for
editing and creating MSDE databases and...
Now that MSDE is running I can't even see it on my machine. I
now have 2 Sqlservr.exe processes running, which have 2
different memory usages (16M and 8M). But why can't I see the
new database instance in my Tray. Is that normal. Surely, if I
have a process running, I should have an icon in my tray
allowing me to stop and start it?
I running XP pro SP1.
|||Zenobia,
--For the MSDE install issue
Try to run the setup in command line specifying a instance name, as
mentioned in the below syntax:
<path>...\msde\setup INSTANCENAME="<instance_name>" /L*v
c:\<path>\msdeInstallation.log
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Zenobia" <6.20.zenobia@.spamgourmet.com> wrote in message
news:atcid0l4i7qnu18vc7udj2ihmhru0neef5@.4ax.com...
> Hello,
> I have SQLServer 2000 installed on a PC and I want to install
> MSDE as well.
> The attempted MSDE installation immediately fails with the
> message:
> "The instance name specified is invalid"
> I looked at the autorun.inf file and I see that there is no name
> specified for the new MSDE installation. Presumably it takes the
> default machine name? The default machine name is already taken
> by the existing SQL Server installation.
> What are the correct lines to add to the MSDE autorun.inf file
> for the default MSDE server name and sa account password?
> After I have MSDE working is it possible to connect to it? I
> would like to develop an ASP.NET web application that uses MSDE.
> Ideally I would like to use something like Enterprise Manager
> but to manage MSDE, at the very least I need something like
> Query Analyser to work.
> I note that there are some ASP.NET web apps in development that
> emulate both Enterprise Manager and Query Analyser for MSDE. Has
> anyone used these tools?
> If necessary I can install MSDE on an other machine as I have a
> LAN running here. I would prefer not to do that.
>
|||>>>> > After I have MSDE working is it possible to connect to it?
Ofcourse.It will be just like a SQLServer instance.
[vbcol=seagreen]
MSDE is devoid of any GUI but you can always use EM/QA from other editions
to manage.
[vbcol=seagreen]
Aaron has a collection.Please search www.aspfaq.com
Dinesh
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:#zwrcyPWEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Zenobia,
> --For the MSDE install issue
> Try to run the setup in command line specifying a instance name, as
> mentioned in the below syntax:
> <path>...\msde\setup INSTANCENAME="<instance_name>" /L*v
> c:\<path>\msdeInstallation.log
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Zenobia" <6.20.zenobia@.spamgourmet.com> wrote in message
> news:atcid0l4i7qnu18vc7udj2ihmhru0neef5@.4ax.com...
>
|||On Wed, 23 Jun 2004 14:21:43 +0530, "Dinesh T.K"
<tkdinesh@.nospam.mail.tkdinesh.com> wrote:

>Ofcourse.It will be just like a SQLServer instance.
>MSDE is devoid of any GUI but you can always use EM/QA from other editions
>to manage.
>
>Aaron has a collection.Please search www.aspfaq.com
Thanks.
After running server network utility I installed:
Named Pipes
TCP/IP
for my new MSDE installation aka machine_name\instance_name
After re booting it now shows to be 'running' in my system tray
as an option of my SQL Server and I have connected to it using
Enterprise Manager and QA.
Are these the 2 protocols I should install or will only one do?
|||Zenobia,
[vbcol=seagreen]
Its fine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Zenobia" <6.20.zenobia@.spamgourmet.com> wrote in message
news:7vlid016ddkp0mdfpkvstd9kdgkoroe6ls@.4ax.com... [vbcol=seagreen]
> On Wed, 23 Jun 2004 14:21:43 +0530, "Dinesh T.K"
> <tkdinesh@.nospam.mail.tkdinesh.com> wrote:
editions
> Thanks.
> After running server network utility I installed:
> Named Pipes
> TCP/IP
> for my new MSDE installation aka machine_name\instance_name
> After re booting it now shows to be 'running' in my system tray
> as an option of my SQL Server and I have connected to it using
> Enterprise Manager and QA.
> Are these the 2 protocols I should install or will only one do?
>
sql

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
PeterYou would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegroups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>|||Thanks Michael - I appreciate your help.sql

How do I insert text into empty xml element in sql server 2005?

Hello
I have the following xml data stored in an xml datatype colmun in sql
server 2005:
<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>
I'd like to be able to update the the xml so that there is text within
the pagetext element, i.e.
...
<pagetext>
Some content goes here.
</pagetext>
...
I've tried to achieve this using the query below:
update tbl_tree SET theTree.modify(' replace value of
(//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
sql:variable("@.someContent")')
but it doesn't work. Is this because you can't 'modify' an empty
element such as '<pagetext/>'
I thought about inserting a node such as '<pagetext>Some content goes
here.</pagetext>'- this would solve the problem only until such time as
the element has its contents removed at which point it will become
<pagetext/> again.
Is there a way to do this?
Any help very much appreciated.
Peter
You would need an IF_DML statement that unfortunately did not make it into
SQL Server 2005. Please send an email to sqlwish at microsoft.com with your
use case and request.
Here is a workaround: You first update all those pagetext elements that have
no content with an insert and then do your normal replace value of.
declare @.x xml;
set @.x = N'<webpage id="935623B3-F72D-45EE-AF88-47022F101184">
<createdate>Sep 6 2005 11:04AM</createdate>
<title>Themes</title>
<icon>app/16/p/text_align_left</icon>
<pagetext />
</webpage>';
declare @.focusID uniqueidentifier;
set @.focusID = '935623B3-F72D-45EE-AF88-47022F101184';
declare @.someContent nvarchar(50);
set @.someContent= N'this is a test';
--I'd like to be able to update the the xml so that there is text within
--the pagetext element, i.e.
SET @.x.modify('insert text {"x"}
into /webpage[@.id=sql:variable("@.focusID")][1]/pagetext[not(text())][1]');
select @.x;
SET @.x.modify('replace value of
(/webpage[@.id=sql:variable("@.focusID")][1]/pagetext/text())[1] with
sql:variable("@.someContent")');
select @.x
Best regards
Michael
<firechaser@.talk21.com> wrote in message
news:1127893569.480367.18870@.g14g2000cwa.googlegro ups.com...
> Hello
> I have the following xml data stored in an xml datatype colmun in sql
> server 2005:
> <webpage id="935623B3-F72D-45EE-AF88-47022F101184">
> <createdate>Sep 6 2005 11:04AM</createdate>
> <title>Themes</title>
> <icon>app/16/p/text_align_left</icon>
> <pagetext />
> </webpage>
> I'd like to be able to update the the xml so that there is text within
> the pagetext element, i.e.
> ...
> <pagetext>
> Some content goes here.
> </pagetext>
> ...
> I've tried to achieve this using the query below:
> update tbl_tree SET theTree.modify(' replace value of
> (//*[@.id=sql:variable("@.focusID")][1]/title/text())[1] with
> sql:variable("@.someContent")')
> but it doesn't work. Is this because you can't 'modify' an empty
> element such as '<pagetext/>'
> I thought about inserting a node such as '<pagetext>Some content goes
> here.</pagetext>'- this would solve the problem only until such time as
> the element has its contents removed at which point it will become
> <pagetext/> again.
> Is there a way to do this?
> Any help very much appreciated.
> Peter
>
|||Thanks Michael - I appreciate your help.

How do I give a user access to SQL Server?

Hi all

I tried using my administrator account to add SQLServer as an ODBC Connection, but for some reason when it tries authenticating it fails and the reason is that the user is not trusted to use that connection. Yet on my account i've created on my domain works perfectly.

I've added a new user in SQL Enterprise manager, but i still get that error.

How do I set the user to be trusted to use the connection?

Any and as many assists on this would be great.

Kr33

[edit] - Hmm, actually, I may be misunderstanding the question. I'm going to move this question into the Data Access forum which hopefully can provide better support:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Sung

|||its better here in the Sql server security section|||

there are two types of login in SQL server

1. the NT login which is used when your server is configured

to use "windows Only" authentication

2. and The SQL server login which is used when your server is configured

to used mixed mode authentication. example of which is the SA login. this is usefull

if your accepting user that not authenticated through windows such as linux user

To configure you server to be on mixed mode authentication. open SQL Enterprise manager

expand the treeview until you reach the desired server. Right click the server. clcik on properties

clcik on security. Then choose "sql server and windows" in the authentication option

note that you can use SA only when you turn on this option

|||Whats your connection string ? If you are not sure about changing the authentication type, you can have a look at my screencasts on my site which will show you how to administer your SQL Server for the change of the authentication mode.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql

How do I get my Data into SqlServer?

I have a development machine that I have prototyped with access. I don't know how to get the access data into Sql Server on the Internet. Can you help me? I'm not sure where to start. Thank you for any help.

just_for_forums wrote:

I don't know how to get the access data into Sql Server on the Internet.

I am not sure what you are asking? Are you trying to build a web application which will interact with your SQL Server, adding data to a table? Or are you trying to import records from Access into SQL Server? or, something else altogether?|||

tmorton wrote:

just_for_forums wrote:

I don't know how to get the access data into Sql Server on the Internet.

I am not sure what you are asking? Are you trying to build a web application which will interact with your SQL Server, adding data to a table? Or are you trying to import records from Access into SQL Server? or, something else altogether?

I have my web application already. I have everything working. The data is in Microsoft Access on my development machine. I need it in SQL Server on the Internet. I just need to know how to get the data in the table rows to SQL Server. The web application will then be adjusted so that it points to the Internet where data is housed (and where my web pages are).

Thanks for help.

|||

just_for_forums wrote:

The data is in Microsoft Access on my development machine. I need it in SQL Server on the Internet. I just need to know how to get the data in the table rows to SQL Server.

OK then, so you need to do a one-time import of your data from Microsoft Access into your SQL Server.

Which version of Access are you using? And which version of SQL Server?

As far as I know, there are 2 different ways to go about this. You can use the Upsizing Wizard within Access to push the data into SQL Server. Or you can use Data Transformation Services (DTS) within SQL Server to pull your data from Access.|||Thanks. I will look into it. I just so used to an Intranet and having access to all the pieces. I just have access 97. sql server on the Internet is 2000.|||I found that if I right-clicked on my table and exported it, I could export it to an ODBC data source.

How do I get a user's domain?

I need to provide a UI to get the information to add a windows login to a SqlServer database. The CREATE LOGIN Sql statment requires the user name as "DomainName\UserName". I can get a list of users in XML using the following code:

public static XmlDocument GetAllADDomainUsers(string DomainPath)
{
string domain;
XmlDocument doc = new XmlDocument();
doc.LoadXml("<users/>");
XmlElement elem;

DirectoryEntry searchRoot;

ArrayList allUsers = new ArrayList();

if (DomainPath.Length == 0)
{
DirectoryEntry entryRoot = new DirectoryEntry("LDAP://RootDSE");
domain = entryRoot.Properties["defaultNamingContext"][0].ToString();
}
else
domain = DomainPath;

searchRoot = new DirectoryEntry("LDAP://" + domain);

DirectorySearcher search = new DirectorySearcher(searchRoot);
search.Filter = "(&(objectClass=user)(objectCategory=person))";
search.PropertiesToLoad.Add("samaccountname");
search.PropertiesToLoad.Add("distinguishedname");
search.Sort.PropertyName = "samaccountname";
search.Sort.Direction = SortDirection.Ascending;

SearchResult result;
SearchResultCollection resultCol = search.FindAll();
if (resultCol != null)
{
for(int counter=0; counter < resultCol.Count; counter++)
{
result = resultCol[counter];
if (result.Properties.Contains("samaccountname"))
{
elem = doc.CreateElement("user");
doc.DocumentElement.AppendChild(elem);
elem.SetAttribute("name", (String)result.Properties["samaccountname"][0]);
elem.SetAttribute("distinguishedName", (String)result.Properties["distinguishedname"][0]);
}
}
}
return doc;
}

This works for listing the names but how do I get the NetBIOS domain name for a selected user as required by SqlServer? I have tried using TranslateName from secur32.dll. That works on some machines but for some reason on other machines, it returns a blank. Is there another way?

Thanks for your help,
Rob

Is this the right one you are looking for? Try System.Environment.UserDomainName .|||

System.Environment.UserDomainName gets the domain of the current user. However, I need to be able to get the domain of a user that could come from any of multiple domains instead of the current user and I also need to support version 1.1 of .Net Framwork which makes it more difficult...unless I'm missing something.

Any ideas?

Thanks,
Rob

How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks on
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. Is
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.
This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.
|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:

> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>
>

How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks on
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. Is
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:
> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
> >I know you can use xp_fixeddrives to find the free space left in the disks
> >on
> > the sql server box.
> >
> > I trying to write a procedure where I can set a threshold in the DB for
> > each
> > disks and once we reach it send me an email. Now I have the Disk Size as
> > hard coded, the problem with this is that if we changes disks or use this
> > procedure on another box running SQL Server, it not going to be accurate.
> > Is
> > there anyway to find total disk size using some XP's like xp_fixeddrives.
> > Thanks.
>
>|||My team had the same problem, except that in SQL 2005 we didn't want to allow
OLE automation at all. We ended up instead creating a stored procedure that
used VB (System.IO.DriveInfo) to get the total free space and total size to
help determine total disk space. Then we merely call the stored procedure on
a regular basis.
"yodarules" wrote:
> Thanks guys,
> Since the user who needs to use these procedures is noy sysadmin, I'm
> explicitly granting execute permissions in the 4 SP's being used. Hoep
> that;s not a big issue.
>
> "Jasper Smith" wrote:
> > sp_diskspace
> > http://www.sqldbatips.com/displaycode.asp?ID=4
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> > http://www.sqldbatips.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> > news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
> > >I know you can use xp_fixeddrives to find the free space left in the disks
> > >on
> > > the sql server box.
> > >
> > > I trying to write a procedure where I can set a threshold in the DB for
> > > each
> > > disks and once we reach it send me an email. Now I have the Disk Size as
> > > hard coded, the problem with this is that if we changes disks or use this
> > > procedure on another box running SQL Server, it not going to be accurate.
> > > Is
> > > there anyway to find total disk size using some XP's like xp_fixeddrives.
> > > Thanks.
> >
> >
> >

How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks o
n
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. I
s
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:

> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>
>|||My team had the same problem, except that in SQL 2005 we didn't want to allo
w
OLE automation at all. We ended up instead creating a stored procedure that
used VB (System.IO.DriveInfo) to get the total free space and total size to
help determine total disk space. Then we merely call the stored procedure o
n
a regular basis.
"yodarules" wrote:
[vbcol=seagreen]
> Thanks guys,
> Since the user who needs to use these procedures is noy sysadmin, I'm
> explicitly granting execute permissions in the 4 SP's being used. Hoep
> that;s not a big issue.
>
> "Jasper Smith" wrote:
>|||My team had the same problem, except that in SQL 2005 we didn't want to allo
w
OLE automation at all. We ended up instead creating a stored procedure that
used VB (System.IO.DriveInfo) to get the total free space and total size to
help determine total disk space. Then we merely call the stored procedure o
n
a regular basis.
"yodarules" wrote:
[vbcol=seagreen]
> Thanks guys,
> Since the user who needs to use these procedures is noy sysadmin, I'm
> explicitly granting execute permissions in the 4 SP's being used. Hoep
> that;s not a big issue.
>
> "Jasper Smith" wrote:
>