Hi,
I have a StudentMark table (StudentID int , SubjectID int, Mark int,
Semester tinyint)
The pass criteria for each student is given as follow
1. He should pass in all the 6 subjects (pass mark = 40)
2. Condition 1 +
1. Avg of any 4 subjects is 40% + Avg any 3 70%
or
2. Avg of any 5 subjects is 50% + avg of any 2 is 80%
or
3. Avg of all the subjects is 60%
can i do it in a single Query ? I had done it using table vars?
Thanks in Advance
LaraBased on your narrative, it sounds like it can be handled in a single query.
But without details on table structures, sample data & expected results,
others can only guess ( for details see: www.aspfaq.com/5006 )
Anith
Showing posts with label int. Show all posts
Showing posts with label int. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Help_with_Store_Procedure
create Proc PopulateArchive
@.CustomerID int,
@.EmployeeID int,
@.Firstname varchar(50),
@.Lastname varchar (50),
@.birth smalldatetime,
@.Inndate smalldatetime,
@.Garanti smalldatetime,
@.Comment varchar(100),
@.ArchiveDate smalldatetime
as Insert into Archive
values (@.CustomerID@.employeeId,@.Firstname,@.Lastname,@.Birt h,@.Inndate,@.Garanti,@.Comment,@.ArchiveDate)
select CustomerID,Firstname,Lastname,birth,Inndate,Garant i from Customer where Customer=@. CustomerID
Delete from Customer where CustomerID = @.CustomerID
exec PopulateArchive 1,2,'Geir','Sam','1956/03/10','2004/03/17','2001/03/25','b,k,0','Your account have been inactive ','2004/03/17'
When I run this query I get this error Server: Msg 208, Level 16, State 1, Procedure PopulateArchive, Line 12
Invalid object name 'Archieve'.
What I want to achieved with this query is that when I delete a customer the deleted data will be moved to the Archieve Table. Please HelpThe error message shows one spelling (Archieve), while the stored procedure source code shows another (Archive). This makes me suspicious that either: a) something went wrong in the cut/paste, or b) some other code (a trigger?) is causing the error message.
There is also no RETURN statement in your stored procedure, and no obvious batch marker (GO). This could cause a weird side-effect if you executed this script verbatim.
-PatP|||That's true PatP. I just drop the Store Procedure and then recreate it.I could run the query successfully . The customer ais deleted, But the query does not insert the deleted columns into the Archive Table.
I suspect my query might be wrong|||Hi pat,
Originally posted by Pat Phelan
There is also no RETURN statement in your stored procedure, and no obvious batch marker (GO). This could cause a weird side-effect if you executed this script verbatim.
-PatP
intresting.
"This could cause a weird side-effect if you executed this script verbatim."
I am unaware of these things.
could u please expalin here what actually those side effects are or do i need to start a new thread for that.|||Based upon the information supplied with the Script for my stored Procedure if you know where the error is, you can help to correct the script. All I need is a query to that delete a customer based on the customerID supplied and the deleted data inserted into the Archive Table.|||If you aren't married to your original code, I would suggest:CREATE PROCEDURE dbo.PopulateArchive2
@.CustomerID int,
@.Comment varchar(100),
AS
BEGIN TRANSACTION
INSERT INTO into Archive
SELECT customerId, employeeId, firstName
, lastname, birth, InnDate
, Garanti, @.comment, GetDate()
FROM dbo.Customer
WHERE CustomerID = @.CustomerID
IF 0 <> @.@.error GOTO bail
DELETE FROM Customer
WHERE CustomerID = @.CustomerID
IF 0 <> @.@.error GOTO bail
COMMIT TRANSACTION
RETURN
bail:
ROLLBACK TRANSACTION
RAISERROR ('PopulateArchive failed!', 13, 1) -- Season to taste
RETURN
GO
-- Demo the stored procedure
EXECUTE dbo.PopulateArchive2 1, 'Your account have been inactive 'Note that you need to test this in your environment. I made some assumptions, but I'd be surprised if those assumption cause any problems.
This procedure will get the customer information from the customer table, avoid unnecessary recompilations (because it uses two part names), insure that either the whole process succeeds or fails as a unit (because of the transaction management), and log errors (due to the RAISERROR statement).
-PatP|||The 'Invalid object name" error sometimes occurs because the table is not owned by dbo, and the owner name is not specified in the code.
Check the ownership on "Archive" (I assume you just made a cut and paste error with the message). Also check it's permissions and make sure the login executing the procedure has rights to it.|||Originally posted by newbies
That's true PatP. I just drop the Store Procedure and then recreate it.I could run the query successfully . The customer ais deleted, But the query does not insert the deleted columns into the Archive Table.
I suspect my query might be wrong
I used the Script that u supplied, the stored procedure run successfully. but the deleted Customer data is not inserted into the Archive Table:
I get this two result when I ran the query using query analyzer. I suspect that something is missing in the Insert statement
(1 row(s) affected)
(1 row(s) affected)|||Hmmm... Just for debugging, right before the COMMIT let's try addingSELECT * FROM Archive WHERE CustomerID = @.CustomerIDI'd like to see what the stored procedure sees there. I'm wondering if you might have more than one archive table (one for DBO, one for your current user) or more than one transaction (one we created in the stored procedure, one that contains it that gets rolled back), or something else that is causing this behavior.
Based on the query output that you posted, it sure looks like everything is running correctly. I have to suspect that we aren't looking at the right piece of this puzzle.
-PatP|||I have modified the Stored Procedure by placing the Select * b/w the if and commit
IF 0 <> @.@.error GOTO bail
SELECT * FROM Archive WHERE CustomerID= @.CustomerID
COMMIT TRANSACTION
I exec the SP and it return this error
Server: Msg 295, Level 16, State 3, Procedure PopulateArchive, Line 10
Syntax error converting character string to smalldatetime data type.
I am trying to figure out what this error means, seems to be the columns that have date, I have smalldatetime data-type for all columns that have something to do with date|||Originally posted by newbies
I have modified the Stored Procedure by placing the Select * b/w the if and commit
IF 0 <> @.@.error GOTO bail
SELECT * FROM Archive WHERE CustomerID= @.CustomerID
COMMIT TRANSACTION
I exec the SP and it return this error
Server: Msg 295, Level 16, State 3, Procedure PopulateArchive, Line 10
Syntax error converting character string to smalldatetime data type.
I am trying to figure out what this error means, seems to be the columns that have date, I have smalldatetime data-type for all columns that have something to do with date
I have checked the datatype they all match I am still getting a syntax eror. the procedure can not be exec|||I have fixed the error regarding the datatype error, the problem was due to columns placement within the select staement. The problem is not yet fixed namely the deleted data are not inserted into the Archive table.
Need help thanks for the input so far|||The stored Procedure is ok now. Because FK constraint between these tables prevent the stored procedure to run successfully, when I removed the FK, the deleted data are moved to the archive table. That's what it should do. mission Accomplished. Use this opportunity to thank Pat Phelan
for the help rendered|||My first guess would be that your Customer.InnDate column is a character, and the Archive.InnDate is a SMALLDATETIME or a DATETIME.
-PatP|||Yes I just check it out that was true. The query is wonderful, I am so impressed. I now move on to the next stage, developing the front end using VB.Net
Thank u for the help|||Those darned computers... They keep doing what I tell them to instead of what I meant for them to do!
Anyway, I'm glad that you are on your way!
-PatP
@.CustomerID int,
@.EmployeeID int,
@.Firstname varchar(50),
@.Lastname varchar (50),
@.birth smalldatetime,
@.Inndate smalldatetime,
@.Garanti smalldatetime,
@.Comment varchar(100),
@.ArchiveDate smalldatetime
as Insert into Archive
values (@.CustomerID@.employeeId,@.Firstname,@.Lastname,@.Birt h,@.Inndate,@.Garanti,@.Comment,@.ArchiveDate)
select CustomerID,Firstname,Lastname,birth,Inndate,Garant i from Customer where Customer=@. CustomerID
Delete from Customer where CustomerID = @.CustomerID
exec PopulateArchive 1,2,'Geir','Sam','1956/03/10','2004/03/17','2001/03/25','b,k,0','Your account have been inactive ','2004/03/17'
When I run this query I get this error Server: Msg 208, Level 16, State 1, Procedure PopulateArchive, Line 12
Invalid object name 'Archieve'.
What I want to achieved with this query is that when I delete a customer the deleted data will be moved to the Archieve Table. Please HelpThe error message shows one spelling (Archieve), while the stored procedure source code shows another (Archive). This makes me suspicious that either: a) something went wrong in the cut/paste, or b) some other code (a trigger?) is causing the error message.
There is also no RETURN statement in your stored procedure, and no obvious batch marker (GO). This could cause a weird side-effect if you executed this script verbatim.
-PatP|||That's true PatP. I just drop the Store Procedure and then recreate it.I could run the query successfully . The customer ais deleted, But the query does not insert the deleted columns into the Archive Table.
I suspect my query might be wrong|||Hi pat,
Originally posted by Pat Phelan
There is also no RETURN statement in your stored procedure, and no obvious batch marker (GO). This could cause a weird side-effect if you executed this script verbatim.
-PatP
intresting.
"This could cause a weird side-effect if you executed this script verbatim."
I am unaware of these things.
could u please expalin here what actually those side effects are or do i need to start a new thread for that.|||Based upon the information supplied with the Script for my stored Procedure if you know where the error is, you can help to correct the script. All I need is a query to that delete a customer based on the customerID supplied and the deleted data inserted into the Archive Table.|||If you aren't married to your original code, I would suggest:CREATE PROCEDURE dbo.PopulateArchive2
@.CustomerID int,
@.Comment varchar(100),
AS
BEGIN TRANSACTION
INSERT INTO into Archive
SELECT customerId, employeeId, firstName
, lastname, birth, InnDate
, Garanti, @.comment, GetDate()
FROM dbo.Customer
WHERE CustomerID = @.CustomerID
IF 0 <> @.@.error GOTO bail
DELETE FROM Customer
WHERE CustomerID = @.CustomerID
IF 0 <> @.@.error GOTO bail
COMMIT TRANSACTION
RETURN
bail:
ROLLBACK TRANSACTION
RAISERROR ('PopulateArchive failed!', 13, 1) -- Season to taste
RETURN
GO
-- Demo the stored procedure
EXECUTE dbo.PopulateArchive2 1, 'Your account have been inactive 'Note that you need to test this in your environment. I made some assumptions, but I'd be surprised if those assumption cause any problems.
This procedure will get the customer information from the customer table, avoid unnecessary recompilations (because it uses two part names), insure that either the whole process succeeds or fails as a unit (because of the transaction management), and log errors (due to the RAISERROR statement).
-PatP|||The 'Invalid object name" error sometimes occurs because the table is not owned by dbo, and the owner name is not specified in the code.
Check the ownership on "Archive" (I assume you just made a cut and paste error with the message). Also check it's permissions and make sure the login executing the procedure has rights to it.|||Originally posted by newbies
That's true PatP. I just drop the Store Procedure and then recreate it.I could run the query successfully . The customer ais deleted, But the query does not insert the deleted columns into the Archive Table.
I suspect my query might be wrong
I used the Script that u supplied, the stored procedure run successfully. but the deleted Customer data is not inserted into the Archive Table:
I get this two result when I ran the query using query analyzer. I suspect that something is missing in the Insert statement
(1 row(s) affected)
(1 row(s) affected)|||Hmmm... Just for debugging, right before the COMMIT let's try addingSELECT * FROM Archive WHERE CustomerID = @.CustomerIDI'd like to see what the stored procedure sees there. I'm wondering if you might have more than one archive table (one for DBO, one for your current user) or more than one transaction (one we created in the stored procedure, one that contains it that gets rolled back), or something else that is causing this behavior.
Based on the query output that you posted, it sure looks like everything is running correctly. I have to suspect that we aren't looking at the right piece of this puzzle.
-PatP|||I have modified the Stored Procedure by placing the Select * b/w the if and commit
IF 0 <> @.@.error GOTO bail
SELECT * FROM Archive WHERE CustomerID= @.CustomerID
COMMIT TRANSACTION
I exec the SP and it return this error
Server: Msg 295, Level 16, State 3, Procedure PopulateArchive, Line 10
Syntax error converting character string to smalldatetime data type.
I am trying to figure out what this error means, seems to be the columns that have date, I have smalldatetime data-type for all columns that have something to do with date|||Originally posted by newbies
I have modified the Stored Procedure by placing the Select * b/w the if and commit
IF 0 <> @.@.error GOTO bail
SELECT * FROM Archive WHERE CustomerID= @.CustomerID
COMMIT TRANSACTION
I exec the SP and it return this error
Server: Msg 295, Level 16, State 3, Procedure PopulateArchive, Line 10
Syntax error converting character string to smalldatetime data type.
I am trying to figure out what this error means, seems to be the columns that have date, I have smalldatetime data-type for all columns that have something to do with date
I have checked the datatype they all match I am still getting a syntax eror. the procedure can not be exec|||I have fixed the error regarding the datatype error, the problem was due to columns placement within the select staement. The problem is not yet fixed namely the deleted data are not inserted into the Archive table.
Need help thanks for the input so far|||The stored Procedure is ok now. Because FK constraint between these tables prevent the stored procedure to run successfully, when I removed the FK, the deleted data are moved to the archive table. That's what it should do. mission Accomplished. Use this opportunity to thank Pat Phelan
for the help rendered|||My first guess would be that your Customer.InnDate column is a character, and the Archive.InnDate is a SMALLDATETIME or a DATETIME.
-PatP|||Yes I just check it out that was true. The query is wonderful, I am so impressed. I now move on to the next stage, developing the front end using VB.Net
Thank u for the help|||Those darned computers... They keep doing what I tell them to instead of what I meant for them to do!
Anyway, I'm glad that you are on your way!
-PatP
Monday, February 27, 2012
Help: Query
Hi,
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>
Sunday, February 19, 2012
HELP: Delayed Inserts in SQL Server!
Hi all,
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/
Subscribe to:
Posts (Atom)