display SQL Server: procedure

Showing posts with label procedure. Show all posts

Friday, March 30, 2012

How do I Modify a Stored Procedure?

I am using the sql server express manager and when I right click on
stored procedure I have a modify option. I use this, make my changes,
and hit save. But is always saves a separate .sql file and never
changes the original stored procedure. What am I doing wrong? How can
I modify the stored procedure in my database? Thank you for any help.
You have to execute the changing script, NOT saving it, which always
saves the script to a file.
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||Jens wrote:
> You have to execute the changing script, NOT saving it, which always
> saves the script to a file.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
So you are saying that when I hit modify, change the code, and then hit
Execute (!) and that automatically saves it?
|||needin4mation@.gmail.com wrote:
> Jens wrote:
> So you are saying that when I hit modify, change the code, and then hit
> Execute (!) and that automatically saves it?
>
It's not correct wording to say it's being saved. As Jens says, you'll
have to execute the new stored procedure to get it added to the
database, but that's not the same as saving it...:-). When you save it,
it's being saved as a regular sql file but that will not do anything to
the actual SP in the database.
Regards
Steen
|||right.

How do I Modify a Stored Procedure?

I am using the sql server express manager and when I right click on
stored procedure I have a modify option. I use this, make my changes,
and hit save. But is always saves a separate .sql file and never
changes the original stored procedure. What am I doing wrong? How can
I modify the stored procedure in my database? Thank you for any help.You have to execute the changing script, NOT saving it, which always
saves the script to a file.
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens wrote:
> You have to execute the changing script, NOT saving it, which always
> saves the script to a file.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
So you are saying that when I hit modify, change the code, and then hit
Execute (!) and that automatically saves it?|||needin4mation@.gmail.com wrote:
> Jens wrote:
> So you are saying that when I hit modify, change the code, and then hit
> Execute (!) and that automatically saves it?
>
It's not correct wording to say it's being saved. As Jens says, you'll
have to execute the new stored procedure to get it added to the
database, but that's not the same as saving it...:-). When you save it,
it's being saved as a regular sql file but that will not do anything to
the actual SP in the database.
Regards
Steen|||right.

How do I Modify a Stored Procedure?

I am using the sql server express manager and when I right click on
stored procedure I have a modify option. I use this, make my changes,
and hit save. But is always saves a separate .sql file and never
changes the original stored procedure. What am I doing wrong? How can
I modify the stored procedure in my database? Thank you for any help.You have to execute the changing script, NOT saving it, which always
saves the script to a file.
HTH; Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Jens wrote:
> You have to execute the changing script, NOT saving it, which always
> saves the script to a file.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
So you are saying that when I hit modify, change the code, and then hit
Execute (!) and that automatically saves it?|||needin4mation@.gmail.com wrote:
> Jens wrote:
>> You have to execute the changing script, NOT saving it, which always
>> saves the script to a file.
>> HTH; Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
> So you are saying that when I hit modify, change the code, and then hit
> Execute (!) and that automatically saves it?
>
It's not correct wording to say it's being saved. As Jens says, you'll
have to execute the new stored procedure to get it added to the
database, but that's not the same as saving it...:-). When you save it,
it's being saved as a regular sql file but that will not do anything to
the actual SP in the database.
Regards
Steen|||right.sql

Wednesday, March 28, 2012

How do I loop thru a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

SELECT id, label, url, sort
FROM mainNav
ORDER BY sort

Set @.tc = @.@.rowcount

while @.i <= @.tc

begin
Set @.i = (@.i + 1)

/* Select for submenu items*/
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end

RETURNI'm thinking that what you really want to do is perform a join and then handle the presentation stuff on the client side. I can't think of any valid reason why you would want to write your stored proc in the manner you have outlined above.

SELECT
m.id as MainID,
m.label as MainLabel,
m.url as MainUrl,
m.sort as MainSort,
s.id,
s.label,
s.url,
s.sort
FROM
mainNav m inner join SubNav s
ORDER BY
m.sort,
s.sort

Regards,

hmscott|||Yeah, you definitely seem hazy on the SQL concept. What is the output format you want for your dropdown list? Give us a sample of the data you want to display.|||The objective is to return a .Net dataset that contains a series of recordsets . The first recordset is the items contained in the top bar of the menu site. The subsequest recordsets contain the submenus for each item in the top menu.

The challenge is that it's not a simple binding issue once the data is returned from the stored proc. Some items in the top bar may not have a submenu and therefore require different html and javascript.

Inorder to render a lightweight, css-based (and W3C compliant) menu system, I need to determine at runtime which items have submenus and which don't

- items with no submenu need to have code that only closes other menus
- items with submenus need to have the closing code and also code to open there respective submenu.

A working (static) example would be something like http://peelcas.org/home/index.aspx

In order to pull this off, I have a c# routine that loops thru the dataset and renders the html.

Here's the c# code

///////////////////////////
// build global menu system
///////////////////////////

string sqlConnstring = ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString;
DataSet NavData = new DataSet();
NavData = SqlHelper.ExecuteDataset(sqlConnstring, CommandType.StoredProcedure, "OPA_GetMenuItems");
//Response.Write(NavData.Tables.Count.ToString());

int i = 1; // counter for looping thru tables collection
int tc = NavData.Tables.Count - 1;
System.Text.StringBuilder sbNavLinks = new StringBuilder();
System.Text.StringBuilder sbSubLinks = new StringBuilder();
string url;
//string webSectionName;
string label;
string anchorId;
string menuId;

// build main nav bar
sbNavLinks.Append("<div id=\"navBar\"><ul>\r");
while (i < tc)
{
label = NavData.Tables[0].Rows[i].ItemArray[1].ToString();
anchorId = NavData.Tables[0].Rows[i].ItemArray[3].ToString();
url = NavData.Tables[0].Rows[i].ItemArray[2].ToString();

// no submenu items for this web section therefore...
// ...create main nav bar link with global menu closing javascript only
if (NavData.Tables[i].Rows.Count == 0)
{
sbNavLinks.Append("<li><a href=\""
+ url + "\" onmouseover=\"P7_autoLayers(0);\">"
+ label + "</a></li>\r");
}
// has submenu items therefore...
// ...create main navbar link with submenu opening javascript
else
{
// main navbar link
sbNavLinks.Append("<li><a href=\""
+ url + "\""
+ " id=\"Anchor" + anchorId + "\""
+ " onmouseover=\"P7_autoLayers(0,'subMenu" + anchorId + "');"
+ "P7_Snap('Anchor" + anchorId + "','subMenu" + anchorId + "',0,24);"
+ "\">"
+ label + "</a></li>\r");
}
i++;
}
sbNavLinks.Append("</ul></div>\r");
navBar.Text = sbNavLinks.ToString();

// build submenus

int j = 0; // counter for looping thru rows in current table
int rc; // row count of current table
i = 1;
while (i < tc)
{
if (NavData.Tables[i].Rows.Count > 0)
{
// extract menu id for use in div id

menuId = NavData.Tables[i].Rows[0].ItemArray[4].ToString();
//Response.Write(webSectionSort);

sbSubLinks.Append("<div id=\"subMenu" + menuId + "\" style=\"position:absolute; z-index:" + menuId + "; visibility: hidden;\">\r");
sbSubLinks.Append(" <div class=\"subButton\">\r");
sbSubLinks.Append(" <ul>\r");

rc = NavData.Tables[i].Rows.Count -1;

while (j <= rc)
{
// extract data for this link
label = NavData.Tables[i].Rows[j].ItemArray[1].ToString();
url = NavData.Tables[i].Rows[j].ItemArray[0].ToString();

sbSubLinks.Append("<li><a href=\""
+ url + "\" class=\"subButton\">"
+ label + "</a></li>\r");
j++;
}

sbSubLinks.Append("</ul></div></div>\r\r");
}
i++;
j = 0;
}
subNavBar.Text = sbSubLinks.ToString();
NavData.Dispose();

Yes I too dislike all this looping business, but I don't have a way of binding and achieving the desired results...|||What about a left join, instead of an inner join. With a left join, you could easily determine the MainMenu items with no submenus (the submenu fields would be null).

You would still loop through it on the web server.

Alternatively, you could use the FOR XML clause to create an XML string (which you would have to wrap inside of xml tags to make compliant).

Regards,

hmscott|||I'm not a C coder. You need to give us the layout of the recordset that you want to get from the server. We can help you with that, and from there on its up to you.|||Thanks for the offer...

I think hmscott's suggestion of a Left Join might just work, so I will groove on that for a while. As much as you're not a C# guy, I'm not a Sql guy, so I often just need a nudge in the right directlon..

Thanks againsql

How do I loop through a record set in a stored procedure?

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDUREdbo.OPA_GetMenuItems
AS
Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = @.i)
ORDER BYmainNavId, sort
end

RETURN

Here's one way: You could get the resultset into a table variable. Add an additional column in the table variable and mark it off as processed after each record.

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

DECLARE @.t tabke (id int, label varchar(100), url varchar(100), sort varchar(100), Processed char(1) )
DECLARE @.minid int

INSERT INTO @.t
SELECT
id, label, url, sort, 'N'
FROM
mainNav
ORDER BY
sort

WHILE EXISTS (SELECT 1 FROM @.t WHERE Processed = 'N')
BEGIN
--Get the first record
SELECT
@.minid = id
FROM
@.t
WHERE
Processed = 'N'
ORDER BY
id

--your processing code
/*
Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end
*/

--make sure you mark the record as Processed
UPDATE
@.t
SET
Processed = 'Y'
WHERE
id = @.minid

END

|||

Depends on if you want the items to come back in one or multiple recordsets.

Here's a single recordset:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav
UNION
SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's two recordsets:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav

SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's multiple recordsets:

Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = (SELECT TOP 1 FROM (SELECT TOP @.i id,sort FROM mainNav ORDER BY sort,id) ORDER BY sort DESC,id DESC))
ORDER BYsort
end

You can also do it using a cursor if you want, which would probably be easier/faster if you have a LOT of menu items, but I'm guessing that since it's a menu, you are only talking about 5-20 items and not thousands.

|||

Basic cursor logic works like this, but the syntax is NOT correct, I haven't used a cursor in a LONG time:

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @.id
WHILE (@.@.FETCHSTATUS<>-2)
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav WHEREmainNavID=@.id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @.id
END
CLOSE MyCursor
DEALLOCATE MyCursor

That should be pretty close, all except for the READ NEXT FROM... I forget the command/syntax, that definately is not correct. And the cursor declaration is a bit off, but close.

|||

The multiple recordsets approach is the one I'm after - I need an .Net data to spit out the menus back in C#. I tryed your example and it did not compile - Sql came back with:

Incorrect Syntax near keyword 'From'.
Line 24: incorrect syntax:near'@.i'.

any ideas?

|||Ah, you must be using SQL Server 2000, yeah, that's not valid on 2000, only 2005. I'm not sure what your code looks like, but I would do the single resultset approach. Just loop through the resultset, and whatever you would do when you get a new resultset, just do it when the mainNav field changes value.|||

Thanks for your help. I actually took a different direction and managed to get around the problem with a left join...

Thanks again...

How do I lock a record in ms-Sql 2000 in a Stored Procedure

Let say 3 person update the same record at the same time the count field must be increment 3.

I want to search for the record on id.
Lock the record so other users can't READ the record
Read the count field of the record
ad +1 to the count
update the record with the new count
unlock the record so other user can increment the count on the same record.

Is there a beter way to do this?

All the user will run the same StoredprocedureOriginally posted by ddp2307
Let say 3 person update the same record at the same time the count field must be increment 3.

I want to search for the record on id.
Lock the record so other users can't READ the record
Read the count field of the record
ad +1 to the count
update the record with the new count
unlock the record so other user can increment the count on the same record.

Is there a beter way to do this?

All the user will run the same Storedprocedure

Also, you could use optimistic concurrency - for details see BOL.

how do i know who accessed/ran a procedure or query

Quote:

Originally Posted by devikacs

how do i know who accessed/ran a procedure or query

You can use a tool such as the SQL Profiler to filter queries as they happen, or you will have to modify your stored procedures to keep a history of who runs it. You could also build history functionality into your front end.

Other than that, I do not know of a way to keep track of people logging in directly to your server and executing queries.|||

Quote:

Originally Posted by Motoma

Other than that, I do not know of a way to keep track of people logging in directly to your server and executing queries.

thanks. i want to make a log of who accessed, so wanted to know if there's any way i can detect when some user accesses a database, or runs a query of his own

Wednesday, March 21, 2012

How do I go to line number referenced in SQL error message

I get a SQL error message like:
Server: Msg 7391, Level 16, State 1, Procedure sp_MyProc, Line 205
So I open up the stored proc with sp_helptext and select Edit | GoTo Line
205 in the results pane. Given the error, I would expect to see a linked
server reference on this line. However, I am on BEGIN of an IF statement
block.
How can I relate the actual stored proc line number with the line number
referenced in the error message?It is normally off by one or two lines, but begin counting at the create
proc command, NOT at the beginning of your script which might have (If
exists, drop, etc.)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Dave" <dave@.nospam.ru> wrote in message
news:ePOQEdTQFHA.2348@.tk2msftngp13.phx.gbl...
> I get a SQL error message like:
> Server: Msg 7391, Level 16, State 1, Procedure sp_MyProc, Line 205
> So I open up the stored proc with sp_helptext and select Edit | GoTo Line
> 205 in the results pane. Given the error, I would expect to see a linked
> server reference on this line. However, I am on BEGIN of an IF statement
> block.
> How can I relate the actual stored proc line number with the line number
> referenced in the error message?
>|||You can use the Debugger in Query Analyzer. Right-click on the SP in
the Object Browser and click Debug.
--
David Portas
SQL Server MVP
--

how do I get the uniqueidentifier of just inserted row?

Hello there!

it was a while since i studied SQL and that brings us to my problem...

I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().

later in the SP i need that uniqueidentifier value? how do I get it?

I tried this:


CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier = newid,
@.strHeader nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400),
@.strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @.uidArticleId,@.strHeader,@.strAbstract,@.strText,@.dt
Date,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAutho
rEmail,@.strKeywords)declare @.uidCategoryId uniqueidentifier
EXEC spGetCategoryId @.strCategoryName, @.uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@.uidArticleId, @.uidCategoryId)

But i get an error when I EXEC the SP like this:


EXEC spInsertNews
@.strHeader = 'Detta är den andra nyheten',
@.strAbstract = 'dn första insatt med sp:n',
@.strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@.dtDate = '2003-01-01',
@.dtDateStart = '2003-01-01',
@.dtDateStop = '2004-01-01',
@.strAuthor = 'David N',
@.strAuthorEmail = 'david@.davi.com',
@.strKeywords = 'nyhet, blajblaj, blaj'

the errormessage is: Syntax error converting from a character string to uniqueidentifier.

does anyone have a sulution to this problem?
Can I use something similar to the @.@.IDENTITY?
I will be greatful for any ideas...

thanks
/David, SwedenTry this

Declare @.seed int

set @.seed = @.@.Identity

return @.seed

Sam|||Hi,

Though you have default specified in your table as newid() , i would supress the default and generate a newid() in the procedure itself and force that in the Insert statement.

This way, you don't have to go back to the table to find out the last added newid() as you yourself are generating it in you proecure.

Regards,
Navneet|||I posted the same questioned and got back the following answer

or use ScopeIdentity. It returns the auto increment value in the current scope.

@.@.Identity can return a value from other tables.

Scope only returns what its in.|||thanks for the help... I solved it like this:

instead of having the SP recieve a parameter as uniqueidentifier
I created it inside the SP and gave it the value newid...

works fine, thanks|||::I posted the same questioned and got back the following answer
::
::or use ScopeIdentity. It returns the auto increment value in the current scope.
::
::@.@.Identity can return a value from other tables.

You may not have realized this - he is not using an identity field, so none of your solutions are relevant. I doubt it was teh same question, btw. YOu propably were using an identity field.|||The safest way is to use this T-SQL syntax after the INSERT query:

SET @.yourNewId = SCOPE_IDENTITY()|||::The safest way is to use this T-SQL syntax after the INSERT query:
::
::SET @.yourNewId = SCOPE_IDENTITY()

Really?

My documentation says that SCOPE_IDENTITY is for identity fields, not for GUID's.

Now, who is wrong? You or the documentation.|||oops, my mistake, read over the "GUID" part. I was thinking in int identity fields :-)

how do I get the uniqueidentifier of just inserted row?

Hello there!

it was a while since i studied SQL and that brings us to my problem...

I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().

later in the SP i need that uniqueidentifier value? how do I get it?

I tried this:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier = newid,
@.strHeader nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400),
@.strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @.uidArticleId,@.strHeader,@.strAbstract,@.strText,@.dt Date,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAutho rEmail,@.strKeywords)

declare @.uidCategoryId uniqueidentifier
EXEC spGetCategoryId @.strCategoryName, @.uidCategoryId OUTPUT

INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@.uidArticleId, @.uidCategoryId)

But i get an error when I EXEC the SP like this:

EXEC spInsertNews
@.strHeader = 'Detta r den andra nyheten',
@.strAbstract = 'dn frsta insatt med sp:n',
@.strText = 'hr kommer hela nyhetstexten att st. Hr fr det plats 2000 tecken, dvs fler n vad jag orkar skriva nu...',
@.dtDate = '2003-01-01',
@.dtDateStart = '2003-01-01',
@.dtDateStop = '2004-01-01',
@.strAuthor = 'David N',
@.strAuthorEmail = 'david@.davi.com',
@.strKeywords = 'nyhet, blajblaj, blaj'

the errormessage is: Syntax error converting from a character string to uniqueidentifier.

does anyone have a sulution to this problem?
Can I use something similar to the @.@.IDENTITY?
I will be greatful for any ideas...

thanks
/David, SwedenNever mind...

i solved it.

Here's the working code...

CREATE PROCEDURE spInsertNews
@.strHeader nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400),
@.strCategoryName nvarchar(200) = 'nyhet'
AS
DECLARE @.uidArticleId uniqueidentifier
SET @.uidArticleId = newid

INSERT INTO tblArticles
VALUES( @.uidArticleId,@.strHeader,@.strAbstract,@.strText,@.dt
Date,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAutho
rEmail,@.strKeywords)

declare @.uidCategoryId uniqueidentifier
EXEC spGetCategoryId @.strCategoryName, @.uidCategoryId OUTPUT

INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@.uidArticleId, @.uidCategoryId)sql

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );

int valueinserted = cmdselect.ExecuteNonQuery();

Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql

how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a

how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a dataset?

Set the Direction property of your Parameter.

Dim myParamAs New SqlParameter' ... set value, name, etc.' if you are sending a value (Default)myParam.Direction = ParameterDirection.Input' if you are requesting a value (assigned in SQL)myParam.Direction = ParameterDirection.Output' if you are sending a value AND requesting' it's changed valuemyParam.Direction = ParameterDirectioin.InputOutput' If you want the return code for the SQLmyParam.Direction = ParameterDirection.ReturnValue

|||

no that doesn't work. i know how to use parameters, but i have only ever seen the Output Parameter used withcommand.executenonquery()when inserting data, but this is no good to me because I am only usingdataAdapter.Fill(myDataset)

how do i get the OutPut Parameter with only filling a dataset?

|||

Theoretically, after your call todataAdapter.Fill(myDataset)

the out parameter should be accessible using

dataAdapter.SelectCommand.Parameters("paramname")

I haven't tried it out though.

|||

Yes, this should work. I believe everything is copied by reference, though, when you add a parameter to a command, then a command to an adapter. Thus, you should still be able to use the original parameter.

'after dataadapter.fill()Dim strValueAs String = myParam.Value

|||I posted some sample code here. Check if it helps:http://forums.asp.net/thread/1478830.aspx|||thanks that works

Monday, March 19, 2012

How do I get the most recent data from a table?

I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the

volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.

Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.

Geoffrey Callaghan

In 2005, the easiest way is to use the ROW_NUMBER() function:

create table container
(
containerId int primary key,
name varchar(10)
)
create table containerHistory
(
containerId int references container(containerId),
containerHistoryDate datetime,
value numeric(4,2),
primary key (containerId, containerHistoryDate)
)
insert into container
select 1,'Fred'
union all
select 2,'Barney'

insert into containerHistory
select 1,'20070101',1.1
union all
select 1,'20070102',1.12
union all
select 1,'20070103',1.1
union all
select 1,'20070104',1.8
union all
select 2,'20070101',1.1

select container.containerId, container.name, containerHistory.value
from container
join (select containerId,
row_number() over (partition by containerId order by containerHistoryDate desc) as rowNum,
value
from containerHistory) as containerHistory
on container.containerId = containerHistory.containerId
and containerHistory.rowNum = 1

Getting the first one (ordered decending) will get you the last one.

|||

Actually, I found an easier way to do it that seems to work.

select container.number, container.name,

(select top 1 qty_meas+qty_added from containerHistory where container.number = containerHistory .container_nbr

order by datetime desc) as balance,

(select top 1 datetime from containerHistory where tank.number = containerHistory .container_nbr

order by datetime desc) as LastReading

from container

This works well and runs fast. Do you see any problems with it?

|||

No, if that works for you, it may be faster/better. It really depends on how many of those subqueries you will need. The Row_number solution is really good for making sure that you get an entire row from a table. However, you want to get a single value from 2 different tables, well your way is probably best.

The row_number trick is going to be the best way to get the last (or first) full row in a set of rows.

How do I get the most recent data from a table?

volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.

Geoffrey Callaghan

In 2005, the easiest way is to use the ROW_NUMBER() function:

insert into containerHistory
select 1,'20070101',1.1
union all
select 1,'20070102',1.12
union all
select 1,'20070103',1.1
union all
select 1,'20070104',1.8
union all
select 2,'20070101',1.1

Getting the first one (ordered decending) will get you the last one.

|||

Actually, I found an easier way to do it that seems to work.

select container.number, container.name,

(select top 1 qty_meas+qty_added from containerHistory where container.number = containerHistory .container_nbr

order by datetime desc) as balance,

(select top 1 datetime from containerHistory where tank.number = containerHistory .container_nbr

order by datetime desc) as LastReading

from container

This works well and runs fast. Do you see any problems with it?

|||

The row_number trick is going to be the best way to get the last (or first) full row in a set of rows.

How Do I Get SQL Server 2000 Reboot Time

I'd like to report to the user when the server was most recently
rebooted.
Is there a stored procedure or SQL Server 2000 command that I can issue?you can use this script here from www.insidesql.de:
SELECT
DATEDIFF(hh,crdate,GETDATE()) AS UpTimeInStunden
FROM
master.dbo.SYSDATABASES
WHERE
Name = 'TempDB'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi,
no need to multipost, asnwered in .sqlserver.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Just use the SystemInfo.exe commandline tool.
<BillJohnson4@.gmail.com> wrote in message
news:1145643233.124362.207240@.i39g2000cwa.googlegroups.com...
> I'd like to report to the user when the server was most recently
> rebooted.
> Is there a stored procedure or SQL Server 2000 command that I can issue?
>|||> I'd like to report to the user when the server was most recently rebooted.
> Is there a stored procedure or SQL Server 2000 command that I can issue?
SELECT login_time FROM master.dbo.sysprocesses WHERE spid=1

How Do I Get SQL Server 2000 Reboot Time

I'd like to report to the user when the server was most recently
rebooted.
Is there a stored procedure or SQL Server 2000 command that I can issue?you can use this script here from www.insidesql.de:
SELECT
DATEDIFF(hh,crdate,GETDATE()) AS UpTimeInStunden
FROM
master.dbo.SYSDATABASES
WHERE
Name = 'TempDB'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi,
no need to multipost, asnwered in .sqlserver.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Just use the SystemInfo.exe commandline tool.
<BillJohnson4@.gmail.com> wrote in message
news:1145643233.124362.207240@.i39g2000cwa.googlegroups.com...
> I'd like to report to the user when the server was most recently
> rebooted.
> Is there a stored procedure or SQL Server 2000 command that I can issue?
>|||> I'd like to report to the user when the server was most recently rebooted.
> Is there a stored procedure or SQL Server 2000 command that I can issue?
SELECT login_time FROM master.dbo.sysprocesses WHERE spid=1

Monday, March 12, 2012

How do I get a procedure OUTPUT-parameter...

In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT...
however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...

this is the sp:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier OUTPUT,
@.strHeading nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400)
AS
SET @.uidArticleId = newid()
INSERT INTO tblArticles
VALUES(@.uidArticleId ,@.strHeading,@.strAbstract,@.strText,@.dtDate,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAuthorEmail,@.strKeywords)

my asp code is something like this:

...
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strHeading", SqlDbType.NVarChar, 300));
sqlcmdInsertNewsArticle.Parameters["@.strHeading"].Value = strHeading.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strAbstract", SqlDbType.NVarChar, 600));
sqlcmdInsertNewsArticle.Parameters["@.strAbstract"].Value = strAbstract.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strText", SqlDbType.NVarChar, 4000));
sqlcmdInsertNewsArticle.Parameters["@.strText"].Value = strText.Text;

...

sqlcmdInsertNewsArticle.Connection.Open();
sqlcmdInsertNewsArticle.ExecuteNonQuery();
sqlcmdInsertNewsArticle.Connection.Close();

How do I do if I want to catch the OUTPUT-parameter (@.uidArticleId)?

anyone?Start by defining another parameter in the Parameters collection:

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.uidArticleId", SqlDbType.UniqueIdentifier));
sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Direction = ParameterDirection.Output;

Then, after you call ExecuteNonQuery, grab the value of that parameter:

Dim id as GUID = sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Value;

I didn't test this code so you may need to tweak it, but that's the idea.

Don|||thanks... that would probably work if I just could get the %#&¤ connection to work... :)

(see: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=553656 )

How do I get a Fields List with Stored Procedures

If I type in a query I get a fields list. However I do not with a stored
procedure. I have tried pressing the Refresh button. This does not work. I
have tried entering the fields manually, but when I get to the value column,
the expression builder tells me that the dataset has no fields. I can run
the stored procedure in Reporting Services and get the correct results. I
just cannot figure out how to get a dataset to see the fields in a stored
procedure.
I've been trying everything I can think of for two days. Does anyone out
there know how to get the fields to show with a stored procedure?The value will be the same as the field name and the type is databasefield
so for example:
fieldname type value
CREW DatabaseField CREW
AND SO ON
You can't use the expression to pick fields because you have not entered the
fields manually. I find I have to enter my fields manually if I have temp
tables used in my stored proc
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||They should show automatically when you execute the dataset on the Data tab.
Are you sure you have a command type of 'storedprocedure'?
Worst case scenario try deleting your dataset and recreating it.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:6C363085-B23B-4B5B-AC32-1C0C1DA4AF3F@.microsoft.com...
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work.
> I
> have tried entering the fields manually, but when I get to the value
> column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||I have tried that. I have also tried right-clicking on the fields list pane
and adding fields. Either way when I try to pull the fields into the report,
they get put in there as SUM (Fields.fieldname.value). When I edit the sum
function away, I get an error saying that the field is not in the dataset.
When I hit Refresh, the fields disappear.
There are no temp tables or table variables in the stored procedure. It
returns one resultset.
How do I delete a dataset?
"John Grant" wrote:
> The value will be the same as the field name and the type is databasefield
> so for example:
> fieldname type value
> CREW DatabaseField CREW
> AND SO ON
> You can't use the expression to pick fields because you have not entered the
> fields manually. I find I have to enter my fields manually if I have temp
> tables used in my stored proc
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I fought with the same issue for 2 days when first working with sprocs in
rs. after you set the command type to stored procedure, run it it once from
the data window. Then refresh it. that did the trick for me. if you have
parameters, don't forget to include them as well.
--
John Cleveland
Network Manager
Urban systems Ltd
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||mmmmmmmmmmm, do you have set nocount on inside your stored proc?
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have the button to delete dataset while in the data tab
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have several good suggestions. Here is a list:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
5. If your stored procedure calls another stored procedure then try this:
add Set FMTONLY Off (the following is from Simon Sabin a SQL Server MVP).
"The issue with RS is that the rowset of the SP is defined by calling the SP
with SET FMTONLY ON because Temp tables don't get created if you select from
the temp table the metadata from the rowset can't be returned. This can be
worked around by turning FMTONLY OFF in the SP."
One of the 5 above should solve your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:59D6F2CC-1CE4-450F-9D55-74B724CB5AAF@.microsoft.com...
>I have tried that. I have also tried right-clicking on the fields list
>pane
> and adding fields. Either way when I try to pull the fields into the
> report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the
> sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
>> The value will be the same as the field name and the type is
>> databasefield
>> so for example:
>> fieldname type value
>> CREW DatabaseField CREW
>> AND SO ON
>> You can't use the expression to pick fields because you have not entered
>> the
>> fields manually. I find I have to enter my fields manually if I have
>> temp
>> tables used in my stored proc
>> "Liz" wrote:
>> > If I type in a query I get a fields list. However I do not with a
>> > stored
>> > procedure. I have tried pressing the Refresh button. This does not
>> > work. I
>> > have tried entering the fields manually, but when I get to the value
>> > column,
>> > the expression builder tells me that the dataset has no fields. I can
>> > run
>> > the stored procedure in Reporting Services and get the correct results.
>> > I
>> > just cannot figure out how to get a dataset to see the fields in a
>> > stored
>> > procedure.
>> >
>> > I've been trying everything I can think of for two days. Does anyone
>> > out
>> > there know how to get the fields to show with a stored procedure?|||I'm afraid that none of this is working. I deleted the dataset and recreated
it. I commented out the "Set Nocount On". I ran the stored procedure and
got the result set. I pressed Refresh. No good.
Any other ideas?
"John Cleveland" wrote:
> Liz,
> I fought with the same issue for 2 days when first working with sprocs in
> rs. after you set the command type to stored procedure, run it it once from
> the data window. Then refresh it. that did the trick for me. if you have
> parameters, don't forget to include them as well.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I know how frustrating it was for me to get it going as well. So if you
would like you can contact me dierctly. I'm not with MS at all, but do know
how to get it to work. Use my contact info below if you wish.
--
John Cleveland
Network Manager
Urban systems Ltd
office: (250) 762-2517
jcleveland@.urban-systems.com
"Liz" wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
> >
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||On Apr 12, 6:02 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
>
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> > "Liz" wrote:
In your SP make a select * into temptable from ...
and then in the last line write select * from temptable
When you do that if you refresh the fields appear, after that just
remove the into command and the "select * from temptable" but you
can't refresh otherwise the fields disappear.
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure... Hide quoted text -
> - Show quoted text -

Friday, March 9, 2012

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

Friday, March 30, 2012

How do I Modify a Stored Procedure?

How do I Modify a Stored Procedure?

How do I Modify a Stored Procedure?

Wednesday, March 28, 2012

How do I loop thru a record set in a stored procedure?

How do I loop through a record set in a stored procedure?

How do I lock a record in ms-Sql 2000 in a Stored Procedure

how do i know who accessed/ran a procedure or query

Wednesday, March 21, 2012

How do I go to line number referenced in SQL error message

how do I get the uniqueidentifier of just inserted row?

how do I get the uniqueidentifier of just inserted row?

how do i get the returned id from the store proceedure in asp c#?

how do i get the OUTPUT parameter value from a stored procedure that i am using to fill a

Monday, March 19, 2012

How do I get the most recent data from a table?

How do I get the most recent data from a table?

How Do I Get SQL Server 2000 Reboot Time

How Do I Get SQL Server 2000 Reboot Time

Monday, March 12, 2012

How do I get a procedure OUTPUT-parameter...

How do I get a Fields List with Stored Procedures

Friday, March 9, 2012

How do I find Total Disk Size from SQLServer

How do I find Total Disk Size from SQLServer

display SQL Server

Blog Archive

About Me