Showing posts with label certain. Show all posts
Showing posts with label certain. Show all posts

Thursday, March 22, 2012

Clarifications on Queue Service and Queue Readers

Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:

A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.

My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.

B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.

Thank you very much for the time,

LubomirOnly reading one message per invocation will together slow down you application enough so that the other things you're worried about will probably be minor by comparison. As long as there are enough seperate dialogs so that there's at least one dialog per queue read you should see parallelism because a receive only locks one conversation group. If I understand what you're doing you will have one one dialog per conversation group so you will lock on one dialog per transaction.|||

Hello,

A. Service Broker functionality is entirely driven by the normal transactional operations rules of SQL Server. All effects of any Service Broker verbs (BEGIN DIALOG, SEND, RECEIVE, END CONVERSATION etc) will be undone if the transaction is rolled back. So if a queue reder rollsback a RECEIVE, the messages will be back in the queue, available again for activation. Be careful dough, if you activated stored procedure rollsback frequently, you are in danger of triggering poison message detection mechanism. If the activated procedure rollsback the RECEIVE verb result 5 times in a row, the whole queue will be disabled. For details, see http://msdn2.microsoft.com/en-us/library/ms166137.aspx

And yes, queue readers will continue to be activated as long as there are messages in the queue available for receive. Also, queue readers will be activated each time a database comes online, like at server start up.

B. Queue readers can progress in paralel as long as there are multiple conversations available for processing. RECEIVE verb will lock the conversations on which it returned messages, so that no other queue reader processes messages from the same conversation(s). for more details, see the 'Conversation groups' topic in BOL, at http://msdn2.microsoft.com/en-us/library/ms166131(en-US,SQL.90).aspx

HTH,
~ Remus

|||Thank you very much for the prompt replies guys.
This makes perfect sense.

Lubomir

Clarifications on Queue Service and Queue Readers

Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:

A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.

My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.

B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.

Thank you very much for the time,

LubomirOnly reading one message per invocation will together slow down you application enough so that the other things you're worried about will probably be minor by comparison. As long as there are enough seperate dialogs so that there's at least one dialog per queue read you should see parallelism because a receive only locks one conversation group. If I understand what you're doing you will have one one dialog per conversation group so you will lock on one dialog per transaction.|||

Hello,

A. Service Broker functionality is entirely driven by the normal transactional operations rules of SQL Server. All effects of any Service Broker verbs (BEGIN DIALOG, SEND, RECEIVE, END CONVERSATION etc) will be undone if the transaction is rolled back. So if a queue reder rollsback a RECEIVE, the messages will be back in the queue, available again for activation. Be careful dough, if you activated stored procedure rollsback frequently, you are in danger of triggering poison message detection mechanism. If the activated procedure rollsback the RECEIVE verb result 5 times in a row, the whole queue will be disabled. For details, see http://msdn2.microsoft.com/en-us/library/ms166137.aspx

And yes, queue readers will continue to be activated as long as there are messages in the queue available for receive. Also, queue readers will be activated each time a database comes online, like at server start up.

B. Queue readers can progress in paralel as long as there are multiple conversations available for processing. RECEIVE verb will lock the conversations on which it returned messages, so that no other queue reader processes messages from the same conversation(s). for more details, see the 'Conversation groups' topic in BOL, at http://msdn2.microsoft.com/en-us/library/ms166131(en-US,SQL.90).aspx

HTH,
~ Remus

|||Thank you very much for the prompt replies guys.
This makes perfect sense.

Lubomirsqlsql

Sunday, March 11, 2012

choose main or preferred supplier ( was "A thing that cannot be solved...?") -

I have a table which contains suppliers that can supply certain articles.
There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table.
As a alternative you can use priority. Each supplier has given priority where
0 means "most preferred" and any other means "less preferred"
What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.

Abbreviated description of the table, called AL.

SuppNo Article Arthuvudavt Prio
10101 A-10 NULL 0
10202 A-10 1 0
10303 A-10 NULL 1
10101 B-10 NULL 0
10202 B-10 NULL 0
10303 B-10 NULL 1
10101 C-10 NULL 1
10202 C-10 NULL 0
10303 C-10 NULL 1

In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and
only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.

Is there any way this could be done?

The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.

Thanks in advance
Jonas

Sorry for the poor formatting, I didn't have the time to make it right.Just sort the data appropriately, and take the first row from the sorted set.

-PatP|||The thing is yes, you could do what you say, sort it and set rowcount = 1 or something similar, but what I really wanna do is to make a single T-SQL statement that picks one resultset, and only one under every circumstance described, and I don't know how to do just that.

(Yes, the earlier explanation was pretty crappy, gotta blame me being swedish ...) :confused:|||With your reference to rowcount=1, I'll assume that you are using Microsoft SQL (although it could also be Sybase). If that is the case, you can use the TOP 1 clause within your SELECT statement.

Bra tur!

-PatP|||With your reference to rowcount=1, I'll assume that you are using Microsoft SQL (although it could also be Sybase). If that is the case, you can use the TOP 1 clause within your SELECT statement.

Bra tur!

-PatP

Yeah, came to think of it just when I read your reply PatP. What can I say, you are The Man. Thanks a bunch! "Tack s mycket!"

Oh, and it's actually "Lycka till!" if I should be picky, but I got the message ... :)|||In a insert-trigger, I'd like to fetch and update a purchase order once it's placed, using info. from the earlier described AL table

SuppNo Article Arthuvudavt Prio First_field Other_Field
10101 A-10 NULL 0 12.00 12.00
10202 A-10 1 0 12.00 13.00
10303 A-10 NULL 1 25.00 35.00
10101 B-10 NULL 0 1.00 2.00
10202 B-10 NULL 0 5.00 5.00
10303 B-10 NULL 1 5.00 6.00
10101 C-10 NULL 1 11.00 11.00
10202 C-10 NULL 0 22.00 35.00
10303 C-10 NULL 1 35.98 52.34

I would preferreable do this in one single update statement, and not using variables. I would rather not use cursors to loop the Inserted-tables either, since this is supposed to be a major performance issue.
The purchase order table is called BP and contains the "First_Field" and "Other_Field" which also exists in the AL-table.
The problem is that I need to do the update and match the supplier to the article ordered in the way the earlier posts by PatP describes, but as I said earlier, I'd like to do this by using only one update-statement. It's also possible for the inserted-table to contain many rows, and therefore I cannot use the "top 1" method discussed earlier.

If I use the suggested solution by PatP that he described earlier, that works for the problem described in the beginning, but not for this one.
Anyone have any ideas? (Oh, yes, I am using T-SQL for MS SQL Server.)

Thanks in advance Jonas|||How about using:CREATE TABLE Nephilim_Supplier (
SuppNo INT NULL
, Article NVARCHAR(20) NULL
, Arthuvudavt INT NULL
, Prio INT NULL
, First_field MONEY NULL
, Other_Field MONEY NULL
)

CREATE TABLE Nephilim_Invoice_Detail (
InvoiceID INT NULL
, Article NVARCHAR(20) NULL
, SuppNo INT NULL
, OtherStuff NVARCHAR(50) NULL
)

INSERT INTO Nephilim_Supplier (
SuppNo, Article, Arthuvudavt
, Prio, First_field, Other_Field
) SELECT 10101, 'A-10', NULL, 0, 12.00, 12.00
UNION ALL SELECT 10202, 'A-10', 1, 0, 12.00, 13.00
UNION ALL SELECT 10303, 'A-10', NULL, 1, 25.00, 35.00
UNION ALL SELECT 10101, 'B-10', NULL, 0, 1.00, 2.00
UNION ALL SELECT 10202, 'B-10', NULL, 0, 5.00, 5.00
UNION ALL SELECT 10303, 'B-10', NULL, 1, 5.00, 6.00
UNION ALL SELECT 10101, 'C-10', NULL, 1, 11.00, 11.00
UNION ALL SELECT 10202, 'C-10', NULL, 0, 22.00, 35.00
UNION ALL SELECT 10303, 'C-10', NULL, 1, 35.98, 52.34

INSERT INTO Nephilim_Invoice_Detail (
InvoiceID, Article
) SELECT 1001, 'A-10'
UNION ALL SELECT 1001, 'B-10'
UNION ALL SELECT 1001, 'C-10'
UNION ALL SELECT 1002, 'B-10'
UNION ALL SELECT 1002, 'C-10'

UPDATE Nephilim_Invoice_Detail
SET SuppNo = (SELECT TOP 1 SuppNo
FROM Nephilim_Supplier AS s
WHERE s.Article = Nephilim_Invoice_Detail.Article
ORDER BY Arthuvudavt DESC, Prio, SuppNo)

SELECT *
FROM Nephilim_Invoice_Detail-PatP|||My intention was to update the first_field and other field with info from the supplier in a insert-trigger on the Nephilim_Invoice_Detail table.

The Nephilim_Invoice_Detail table should be altered like this :
CREATE TABLE Nephilim_Invoice_Detail (
InvoiceID INT NULL
, Article NVARCHAR(20) NULL
, SuppNo INT NULL
, OtherStuff NVARCHAR(50) NULL
, First_field money
, Other_Field money
)

I assume you want me to make the trigger in the following way, given your earlier update-statement.

create trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
as
UPDATE Nephilim_Invoice_Detail
SET
first_field = (SELECT TOP 1 first_field
FROM Nephilim_Supplier AS s
WHERE s.Article = Nephilim_Invoice_Detail.Article),
other_field = (SELECT TOP 1 other_field
FROM Nephilim_Supplier AS s
WHERE s.Article = Nephilim_Invoice_Detail.Article
ORDER BY Arthuvudavt DESC, Prio, SuppNo)

If that is the case, that won't work. I apologize for my poor explanation of the problem. :o

EDIT :
It's not working in neither of the two stated cases below:

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'
UNION ALL
select
1, 'C-10', NULL, 'Some C-10 stuff'|||This is kind of hard to get your head around the first time you see it, but I'd use something like:UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
FROM Nephilim_Invoice_Detail AS a
JOIN (SELECT TOP 1 z.first_field, z.first_field, z.other_field
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)You might also need to include the inserted pseudo-table too, to kind of keep a leash on things.

-PatP|||Aye, the inserted table should probably be added to the inserted-trigger, but I still cannot get it to work. The insert-trigger now looks like this :

alter trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
as
UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM Nephilim_Invoice_Detail AS a
JOIN (SELECT TOP 1 z.first_field, z.other_field, z.Article
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)
JOIN inserted on
a.Article = inserted.article

Is there something in that code that I've missed? All I did was to add the join to the inserted table in a similar way you added a join to the subtable made by a select.
Neither of the two inserts in my previous post works ...

Starting to feel like total "n00b" here, but then again, I probably am ... :o|||You appear to be missing the SuppNo in the derived table (the Z alias). What are you seeing (error messages, inappropriate rows updated, appropriate rows not updated, blue flames coming out of your server, etc)?

I can't get much mental traction on "Neither of the two inserts in my previous post works ..." It doesn't help me to understand what the problem is.

-PatP|||What I meant with "neither of the two inserts work" was that yes, they are syntactically correct, but in neither of the two inserts the trigger updates the fields I intended to update. The update-trigger "shoots blanks", or whatever you wanna call it ...

I suppose you want me to alter the trigger into this:

alter trigger Nephilim_Invoice_Detail_insert on Nephilim_Invoice_Detail for insert
as
UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM Nephilim_Invoice_Detail AS a
JOIN (SELECT TOP 1 z.first_field, z.other_field, z.Article
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)
JOIN inserted on
a.Article = inserted.article
join z on
a.suppno=z.suppno

if so, I get a
Server: Msg 208, Level 16, State 1, Procedure Nephilim_Invoice_Detail_insert, Line 3
Invalid object name 'z'.

when I try to do the insert. The z-table is alias:ed into table name b, so I thought I shouldn't have to use z in a join since I joined the a and b tables ...

(Thoughtfully wonders to himself, "wonder how much patience this PatP dude really has with someone like me. Starting to feel annoyed by myself for not getting this ...)|||This is mostly "window dressing" to clean things up so its easier for my feeble mind to grasp, along with an outer join just in case there aren't any rows with the inserted.Article value in the Nephilim_Supplier table:ALTER TRIGGER Nephilim_Invoice_Detail_insert
ON Nephilim_Invoice_Detail
FOR INSERT
AS

UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM inserted
JOIN Nephilim_Invoice_Detail AS a
ON (a.Article = inserted.Article)
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)

SELECT inserted.* -- Debugging, let's see what won't match
FROM inserted
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = inserted.Article)
WHERE b.Article IS NULL

RETURNThe extra SELECT is just for debugging. It will return any rows that are in the inserted pseudo-table that don't have a match in the derived table.

-PatP|||With your altered procedure, that now looks like this :

ALTER TRIGGER Nephilim_Invoice_Detail_insert
ON Nephilim_Invoice_Detail
FOR INSERT
AS

UPDATE a
SET
a.first_field = b.first_field
, a.other_field = b.other_field
, a.suppNo = b.suppNo
FROM inserted
JOIN Nephilim_Invoice_Detail AS a
ON (a.Article = inserted.Article)
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = a.Article)

SELECT inserted.* -- Debugging, let's see what won't match
FROM inserted
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = inserted.Article)
WHERE b.Article IS NULL

RETURN

And the following data in the Nephilim_Supplier - table

SuppNo Article Arthuvudavt Prio First_field Other_Field
---- ------- ---- ---- ------- -------
10101 A-10 NULL 0 12.0000 12.0000
10202 A-10 1 0 12.0000 13.0000
10303 A-10 NULL 1 25.0000 35.0000
10101 B-10 NULL 0 1.0000 2.0000
10202 B-10 NULL 0 5.0000 5.0000
10303 B-10 NULL 1 5.0000 6.0000
10101 C-10 NULL 1 11.0000 11.0000
10202 C-10 NULL 0 22.0000 35.0000
10303 C-10 NULL 1 35.9800 52.3400

The following insert still doesn't fill in the fields I wanted in the trigger, first_field, other_field and suppNo.

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'

The example previously posted with two posts in the inserted table doesn't work either:

insert into Nephilim_Invoice_Detail
(InvoiceID, Article, SuppNo, OtherStuff)
select
1, 'B-10', NULL, 'Some other stuff'
UNION ALL
select
1, 'C-10', NULL, 'Some C-10 stuff'

With the given info, the result should be that the first_field, other_field and suppNo should be filled with info from the Nephilim_Supplier.

In the "single-insert"-case, the result should be

First_Field and Other_Field from supplier 10101 or 10202, it really doesn't matter since this data is breaking the usage of the table. My trigger should however work despite this.)

In the "dual-insert"-case I wish the result to be

First_Field and Other_Field for article C-10 should be the data associated with supplier 10202 for that article, since that supplier has the lowest prio.
First_Field and Other_Field would be updated as stated in the single insert case.

What I don't understand here is why the debug part concludes that there is no match. Obviously there is, I am using articles with associated suppliers. :confused:|||If the trigger is spitting out rows, at least from it's perspective there isn't a match. There might be datatype issues (char versus nvarchar perhaps), collation differences, or other issues, but the code doesn't see the relationship and that is all that really matters at this point.

If you can't work this one out:
1. Create a totally empty database.
2. Create the tables.
3. Create the trigger.
4. Test to be sure it misbehaves.
5. Detach the database.
6. Zip the database (mdf and ldf) up.
7. Post it here.
8. I'll see what I can do, but it won't be quick because I'm swamped.

-PatP|||Attached to this post is the database. Use the previously posted insert's to provoke the situation.
To me it looks like something is wrong in the trigger, but I just can't figure out how it should be changed.|||SELECT inserted.* -- Debugging, let's see what won't match
FROM inserted
LEFT OUTER JOIN (SELECT TOP 1
z.first_field, z.other_field, z.Article, z.SuppNo
FROM Nephilim_Supplier AS z
ORDER BY Arthuvudavt DESC, Prio, SuppNo) AS b
ON (b.Article = inserted.Article)
WHERE b.Article IS NULL

The 'problem' is that b.Article is NOT NULL because you have rows in Nephilim_Supplier that have the same 'Article'. You need uniqueness - but, on your INSERT, suppNo is Null, so I don't know where you going to get it from.

Disallow NULLs on every column you can, and then SQL Server will start pointing out your problems. Also, you are committing ACIDic sin w/o a primary key on Nephilim_Invoice_Detail - fix that.|||Ok, I buy the thing about ACID:ic sin, however these tables are only created to simplify a real world problem, so I'll let it slide for now ...

The problem at hand is that when a insert is made into Nephilim_Invoice_Detail the trigger should figure out which supplier it should retrieve info from, and put that supplierno along with the First_Field and Other_Field data on the post being created. The only way it can do that is via the field Article. However, this field isn't unique, therefore ArtHuvudAvt is just in parallell with prio. Since none of these two fields or NOT NULL by design for reasons I won't discuss here, there can be many matches. My trigger should use the rules described earlier in this thread to find a match, and if it ends up with more than one match, use one of the matching posts.

In the realworld table, the "Nephilim_Supplier"-table has a PK of Article, arthuvudAvt, Prio in that way creating uniqueness. That uniqueness however isn't useable from my point of view, since I'm only given the articleNo to begin with in my trigger. Hopefully I straigthened the problem out.

- Nephy|||*bump!* :o

Sunday, February 19, 2012

Checking if an entry exists in a database

I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)

I had written a simple example here.

Below code has simple example on it:

<%

@.PageLanguage="VB" %>

<%

@.ImportNamespace="System.Data" %>

<%

@.ImportNamespace="System.Data.SqlClient" %>
<scriptrunat="server">
ProtectedSub btnCheckData_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)TryDim HasdataAsStringDim myConnectionAsNew SqlConnection("server=server\sqlserver;database=db;User Id=uid;Password=swd;")Dim myCommandAsNew SqlCommand( _"Select 'X' from preview Where facility='" & TextBox1.Text &"'", myConnection)

myCommand.Connection.Open()

Hasdata = myCommand.ExecuteScalar()

myCommand.Connection.Close()

myCommand.Connection.Dispose()

If Hasdata ="X"Then
' Do if data exist process here
Response.Write("Data exit")
Else
' Do if data not exist process here
Response.Write("Data not exist")
EndIfCatch exAs Exception

Response.Write(

"Error:" & ex.Message)ExitSubEndTryEndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div> <asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/><br/><asp:ButtonID="btnCheckData"runat="server"OnClick="btnCheckData_Click"Text="CheckData"/></div></form>

</

body>

</

html>

I have used executescalar and checked data is there or not in above example. If you see select, in that I am taking input from textbox for where clause, I am checking if that value is in DB or not, if not we won't get any data, but if the value is there, then select returns 'X'. So by basing on that I am checking the If condition as below.

If Hasdata ="X"Then
' Do if data exist process here
Response.Write("Data exit")
Else
' Do if data not exist process here
Response.Write("Data not exist")
EndIf

So as you asked if data is there you can keep it in True condition or if not there then you can keep in false condition.

Hope it helps!

|||Thanks for the help. When I run the code, I get an error telling me I need to cast to string (I'm using c#). And when I fix that line to this: rescheck = (string)cmd.ExecuteScalar(); It says: Invalid column name 'Owner'

Any ideas on how to fix this?

Thanks|||

Here is C# code:

<%@. Page Language="C#" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

protected void btnCheckData_Click(object sender, EventArgs e)
{
try
{
String Hasdata;
SqlConnection myConnection = new SqlConnection(@."server=Sreebrain\SreeSQL;database=SriTest;User Id=sa;Password=srikog1!;");
SqlCommand myCommand = new SqlCommand( "Select 'X' from preview Where facility='" + TextBox1.Text + "'", myConnection);

myCommand.Connection.Open();
Hasdata = (string)myCommand.ExecuteScalar();
myCommand.Connection.Close();
myCommand.Connection.Dispose();

if (Hasdata == "X")
{
// Do if data exist process here
Response.Write("Data exit");
}
else
{
// Do if data not exist process here
Response.Write("Data not exist");
}
}
catch (Exception ex)
{
Response.Write("Error:" + ex.Message);

}
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="btnCheckData" runat="server" OnClick="btnCheckData_Click" Text="CheckData" /></div>
</form>
</body>
</html>

|||

Ok, You got invalid column means, In your select statement you might given a column name as owner, that field could not be in your Table.

Ex: Select 'X' from preview

Instead of column name give 'X' there so that you can check for 'X' when you got the result back.
Also make sure in your where clause you given the disered field as you wanted.

If you still get error after you do these changes, please post the code, so that I can invistigate the problem.

Hope this time it will make you clear!

|||Thanks for all the help. You were right on the money. Everything's fixed :)|||

I have 2 labels with visibility set to FALSE, I want to display 1 label when the entry exists and the other 1 when the entry does not exist in my DB

your code looks like it should work, yet I am getting some exceptional error

here the error

Object reference not set to an instance of an object.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 61: If Hasdata = "X" Then
Line 62: Label12.Visible = True
Line 63: Else
Line 64: Label9.Visible = True
Line 65: End If


Source File:D:\Visual Studio 2005\Projects\WebApplication11\WebApplication11\admin\Createu.aspx.vb Line:63

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
WebApplication11.Createu.TextBox1_TextChanged(Object sender, EventArgs e) in D:\Visual Studio 2005\Projects\WebApplication11\WebApplication11\admin\Createu.aspx.vb:63
System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +105
System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +134
System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
System.Web.UI.Page.RaiseChangedEvents() +137
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4778



Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

and heres the code


Protected Sub TextBox1_TextChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles TextBox1.TextChangedTry Dim HasdataAs String Dim myConnectionAs New SqlConnection("Server=localhost;UID=SA;PWD=****;database=knowledge")Dim myCommandAs New SqlCommand("Select'X' from usr Where Uid='" & TextBox1.Text & "'", myConnection) myCommand.Connection.Open() Hasdata = myCommand.ExecuteScalar() myCommand.Connection.Close() myCommand.Connection.Dispose() If Hasdata = "X" Then Label12.Text = True Else Label9.Visible = True End If Catch ex As Exception Response.Write("Error:" & ex.Message)Exit Sub End Try End Sub

Thursday, February 16, 2012

Checking for free disk space and getting mail when it falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

Thanks

Hi Vaddi -

Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.

HTH...

checking for free disk space and getting mail , when falls below a certain limit

Hello

I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.

I would like to know how I can change it , as a percentage calculation.

For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.

The script I have is :

declare @.MB_Free int

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace

ThanksHere's what I use:
set nocount on

declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold

set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'

if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message

end

drop table #FreeSpace
go

Tuesday, February 14, 2012

CHECKDB table exclusion ?

Hi There

I have checked CHECKDB syntax in BOL, i just want to be sure.

In there no way to run CHECKDB on a user database and exclude certain tables, in SS2000.

I have 2 really huge tables , over a billion rows. Nightly i want to run CHECKDB on the database but i do NOT want CHECKTABLE run on these 2 tables as it takes hours.

Or is my only option to instead of running CHECKDB, writing a script that loops through user tables excluding these and running CHECKTABLE. If this is my only option can it be done with a cursor looping through user tables and running CHECKTABLE on each one?

Thanx

Your only option is to run CHECKTABLE with the specific tables. CHECKDB doesn't have any option to filter tables.