Hide label if no data

Hi all,
is it possible to hide a textbox (a normal label) if in the adiacent cell
there is no data in tha dataset? (for example a null value).
Thanks a lot.
--
Luigi
http://blogs.dotnethell.it/ciupaz/Yes it is. You can add this expression in your text box;
iif(Fields!TextBoxData.Value <0, Fields!TextBox.Value, nothing)
"Luigi" wrote:
> Hi all,
> is it possible to hide a textbox (a normal label) if in the adiacent cell
> there is no data in tha dataset? (for example a null value).
> Thanks a lot.
> --
> Luigi
> http://blogs.dotnethell.it/ciupaz/

Hide fields with blank value

I am creataing a report with a Local Mode report and ReportViewer. Part of the report has an address:

[FName] [LName]
[Address1]
[Address2]
[City] [State] [ZIP]
[Phone]

So the data might come out on the report:

John Doe
123 Fake Street
Suite 32
Somewhere USA 00101
(800) 555-1212

Which is great, except if address2 is blank (as in most cases it is) it comes out:

John Doe
123 Fake Street

Somewhere USA 00101
(800) 555-1212

How can I make it Hide the [Address2] Field if it is blank, so that extrat line space is taken out?

Thanks

UPDATE** - As soon as i posted this site i realized when you use the expression for this, it should be =Field(Value) = "". I had =Field(Value) <> "". I was backwards. Sorry for the uncecessary post.

In the visibility property of textbox give the expression as

IIF(Len(Fields.Address2.value)>0,True,False)

Thanks

Subbu

Hide Calculated Column in Matrix Report And show only in Total

Hi All,

I need to show the Cumulative calculated value only in Total by year/Group. I could not use Visibility expression using

InScope, as it creates *Blank column. Please go thru details below.

Year

Month01 02 03 Total

Salary Salary Salary Salary Cumulative (Calc)

Employee01 20 5 25 25

Employee02 10 10 20 45

.....

Total

How can i achieve this?. Any suggestion on this would be appreciated.

Thanks,

You have a couple of options:

1 - Can you add Total Salary as part of your dataset and return that for each Employee/Year ?
If so, then you just include that field in your cumulative statement, so no need for the column

2 - You may be able to take both Tot Salary and Cumulative salary and place them in a single cell using the cells in cells technique. Select your cell and drop a Rectangle control into it. You’ll notice the background change from solid white to the transparent grid pattern. Next, select a textbox control and drop this into the rectangle. You have to get it perfect and sometimes it’s a bit annoying when you don’t land exactly on the control. Next, drop in another textbox so it rests directly beside your first one.
In one cell add your Sal Tot, and in the right textbox the Cumulative Salary. Set the Sal Tot textbox to hidden=True and as small width as possible. To the end user it will appear as though this is a single column.

Hide a parameter

Using Report Manager (default viewer for Reporting Services) is possible to
choose one parameter to be hide. This hide parameter has a default value and
the user inform the others.
thanksHi Julio,
On the parameter property dialog you can uncheck the "Prompt user"
checkbox.
--
Scott
http://www.OdeToCode.com
On Tue, 3 Aug 2004 08:29:56 -0300, "Julio"
<julio.caldas@.brfree.com.br> wrote:
>Using Report Manager (default viewer for Reporting Services) is possible to
>choose one parameter to be hide. This hide parameter has a default value and
>the user inform the others.
>thanks
>

Hide a column (value) in a subtotal of a matrix?

Hello,
I am trying to hide (or possible show a calculated value in a subtotal)
a value in a matrix. My dataset returns something in this format.
RowHeader1, RowHeader2, ColumnName, ColumnType, Amount
1, 1, Total, Amount, 100
1, 1, Total2, Amount, 0
1, 1, Variance, Percent, 1.00
1, 2, Total, Amount, 50
1, 2, Total2, Amount, 55
1, 2, Variance, Percent, .10
I have row groups on RowHeader1 and RowHeader2. Also, I have a Column
Group on ColumnName and I have SUM(Fields!Amount.Value) in the Data
cell. The Matrix looks something like this:
Total Total2 Variance
1 1 100 0 100%
2 50 55 10%
TOTAL 150 55 110%
What I would like to do is either have the correct value in the
SubTotal field for the Variance (which I don't think is possible) or
just hide it. I tried to use InScope() as a start but I have been
getting nowhere.
Any help would be greatly appreciated.
Thanks,
AbeIs there a way to know if you are in the Subtotal Row or not?
Thanks|||Apparently you looked already at the InScope function. If you have multiple
row/column groupings you need to make sure that your conditional expression
considers all cases. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abe" <abe@.flonet.com> wrote in message
news:1106156667.460735.143790@.c13g2000cwb.googlegroups.com...
> Is there a way to know if you are in the Subtotal Row or not?
> Thanks
>|||Thanks a lot for your help, I really appreciate it.

Hide 0 value

Hello,

I made a calculed member

with this formule distinctcount([sales order].members)

I would like that the "0" value doesn't appear.

Is it possible ?

Sure. Use the IIF statement in your calculated member to evaluate the value. If yoe return a value of 0, substitute a another value in it's place. You could substitute NULL or even a string such as "n/a".

Good luck,

Bryan

|||

I tried this formule

iif(distinctcount([no appels].members)=0,NULL,distinctcount([no appels].members))

and I have an error :

distinctcount must be used as a function of higher level (most external)

|||

I find the answer,

I created a new calculed member and I could use the iif to exclude the 0 values.

sql

hidden rows

Hi
Hope someone can help.
I'm trying to hide a row based on 2 criteria.
If i use one this =iif(Fields!RespTimeEq.Value > Fields!RespTime.Value,
True, False) it works but as soon as i add another criteria e.g.
=iif(Fields!RespTimeEq.Value > Fields!RespTime.Value and
Fields!VisitType.Value="GSC", True, False) it doesn't work.
Is this possible hide a row based on two criteria and if so what is the
correct syntax for it.
Thanks PaulI would thing that there is something wrong with
Fields!VisitType.Value="GSC", either this is never "GSC" (uppercase,
lowercase) or VisitType is not a string
So perhaps Fields!VisitType.Value.ToUpper()="GSC", would work (?)
"pcalv" wrote:
> Hi
> Hope someone can help.
> I'm trying to hide a row based on 2 criteria.
> If i use one this =iif(Fields!RespTimeEq.Value > Fields!RespTime.Value,
> True, False) it works but as soon as i add another criteria e.g.
> =iif(Fields!RespTimeEq.Value > Fields!RespTime.Value and
> Fields!VisitType.Value="GSC", True, False) it doesn't work.
> Is this possible hide a row based on two criteria and if so what is the
> correct syntax for it.
> Thanks Paul

Hidden Property - "Deserialization failed: is not a Valid Value" E

If I add an expression to the visibility property on a text box, table row,
etc.. and then remove that same expression, I get the following error message
in preview:
"deserialization failed: is not a valid value..."
I get an option to edit the XML code. When I look at the code, I find the
<Hidden /> tag. But get this, there is no </Hidden> tag. Apparently, VS is
removing only one side of the Hidden tag when I delete the expression.
I used to be able to edit the code, but now I can't even edit the code to
take the remaining tag out. With this error my rdl file became, for all
intents and purposes, corrupt. Luckily I had posted it to a dev Report
Manager site earlier and was able to recover it.
Has anyone seen this?
Also, not being able to close VS2005 because of the the "Modal Dialog
Open..." box is killing me. Anyone have a solution to that yet besides
killing the devenv process?
Thanks in Advance,
bsod55I just had a similar error - I had to open the file and remove the Hidden
tags. In the IDE I right clicked on the file and selected Open from the menu
- this gave me the XML version and allowed me to remove the tags.
--
mal
"bsod55" wrote:
> If I add an expression to the visibility property on a text box, table row,
> etc.. and then remove that same expression, I get the following error message
> in preview:
> "deserialization failed: is not a valid value..."
> I get an option to edit the XML code. When I look at the code, I find the
> <Hidden /> tag. But get this, there is no </Hidden> tag. Apparently, VS is
> removing only one side of the Hidden tag when I delete the expression.
> I used to be able to edit the code, but now I can't even edit the code to
> take the remaining tag out. With this error my rdl file became, for all
> intents and purposes, corrupt. Luckily I had posted it to a dev Report
> Manager site earlier and was able to recover it.
> Has anyone seen this?
> Also, not being able to close VS2005 because of the the "Modal Dialog
> Open..." box is killing me. Anyone have a solution to that yet besides
> killing the devenv process?
> Thanks in Advance,
> bsod55

Hidden parameter using userid

I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.I have since discovered the problem is not as stated but is due to the fact that I have hidden parameters dependent on the input of an earlier parameter. If the input parameter is a drop down then all is OK but if you have to interactively enter a value then you have to click the View Report button twice. Removing the userid parameter meant that the dependent parameter was last in the list and again all was then OK; however, I still have some reports where I have more than one hidden parameter dependent on an earlier value so the problem still occurs.
"JohnH" wrote:
> I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.

Hidden parameter

I am trying to set one of report parameters to hidden. When I run the report
I am getting an error that the hdden parameter is missing a value. Can
someone help?Are you assigning the parameter a default value?
Rodney Landrum
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:51EC9662-B240-4F57-9C7F-3C376BF7E237@.microsoft.com...
>I am trying to set one of report parameters to hidden. When I run the
>report
> I am getting an error that the hdden parameter is missing a value. Can
> someone help?sql

Hidden Expression for Group Header

I am trying to hide a group header (table row) based on the value of that group, but have not been able to get it working.

Let's say I have 2 groups in my table:

Group 1: State

Group 2: City

I want to hide the group header (tablerow2) where State = "Unknown" but I want the Group 2 cities to still display - just w/o a header.

I tried putting the following expressions on the hidden property of tablerow2 with no luck:

First I tried referencing the field that it's grouped on:

=Iif((Fields!state.Value = "Unknown", true, false)

I also tried refereing the name of the group:

=Iif(("State_GRP" = "Unknown"), true, false)

In both cases, nothing gets hidden - all rows, including the group header that says "Unknown", are visible.

Any help would be greatly appreciated!

Kathleen

You are doing this correctly, and I was not able to reporduce the behavior--it works as you are thinking. I would make sure that the case of "Unknown" is consistent with the field value. I would do this in the expression (or change the query to do the trimming), so that case and extranious white space is not a factor.

=IIF(Fields!state.Value.Trim().ToLower()="unknown", true, false)

Ian

|||Thanks Ian - the trim/tolower seemed to resolve whatever issue I was having.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.|||Can anyone shed some light on this? Is this a bug?
I am having same problem as the parameter does not refresh if some other parameter is changed.

For example if I change the event in the list box, the appropriate begin and end date is not modified?

hi this is dileep

my query is
when i enter a value in the textbox like sports | cricket | footbal
please give me
insert query :: the values will be stored into the database in 3 different rows
no matter the no of rows

in .net

Quote:

Originally Posted by dileepk

my query is
when i enter a value in the textbox like sports | cricket | footbal
please give me
insert query :: the values will be stored into the database in 3 different rows
no matter the no of rows

in .net

Hi there,

Answer is not that easy my fren. Pls show us some of your work beforehand, will try to validate your SQL statement. Good luck & Take care.

Help: Table Lock Confirmation

I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!

This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.

BEGIN TRANSACTION

UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @.forum_id

SELECT @.new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @.forum_id

COMMIT TRANSACTIONI would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..|||Originally posted by Kaiowas
I would go for a different solution; a transaction is used to be able to rollback data in case of a failure, and may help to solve a concurrent-user issue. But not like this. I would think there could be another update between the update and the select..

Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.|||The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Thank you very much Pat!|||Originally posted by stevenpath
Thanks for your response, but I am using the transaction to lock the table initiated by the UPDATE forum WITH (TABLOCKX). I understand this table should stay locked until the COMMIT TRANS.

You're right! Sorry I missed that, but why not stick with it?|||Originally posted by Pat Phelan
The code that I used to use was:BEGIN TRANSACTION

SELECT @.forum_last_used_msg_id = 1 + a.forum_last_used_msg_id
FROM forum (HOLDLOCK) AS a
WHERE a.forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

UPDATE forum
SET forum_last_used_msg_id = @.forum_last_used_msg_id
WHERE forum_id = @.forum_id

IF 0 <> @.@.error GOTO bail

COMMIT TRANSACTION
BEGIN TRANSACTION

bail:
ROLLBACK TRANSACTIONThis holds the lock at the row level, and does a rollback if anything goes wrong.

-PatP

Pat why the extra BEGIN TRAN?

Is that a type o?

Geez what a way to hit 2000|||Originally posted by Brett Kaiser
Pat why the extra BEGIN TRAN? It is a nifty little trick that I dreamed up one night in a haze...

When using nested stored procedures, things got really, really complicated if the transaction level got puckered up, and things just went to heck in a handcart. I had to find some way that I could rollback without blowing the whole tamale out of the water. Necessity being a mother (as you so recently pointed out), I came up with a deviant solution.

The code is two transactions when life is good, with an empty one being rolled back, which has no impact on the database. When life is hard, it is only one transaction, which is also rolled back so it has no impact on the transaction count either...

The net result is that it is an odd bit of code, but it works nicely in all of the peculiar ways that we need code to function. Someday I'll have to post a little diatribe about the bad old days, when Sybase wanted considerably more dollars for each replicated database (per year) than they wanted for the license for the database! That drove us to some peculiar work arounds, this being one of them.

-PatP|||OK, I see it now...but why do it that way?

Why not handle it like the code in this thread?

http://www.dbforums.com/showthread.php?threadid=988019&perpage=15&pagenumber=1

What's the difference...you're using Goto's anyway...|||As I said in the previous post, this was a side effect of the cost of using early (like 1994) versions of SQL replication. Our work around required each procedure have one entry point, and one exit point from a code execution perspective so we could effectively "bottle" the procedure with calls to other procedures that our work around required.

It wasn't necessarily pretty, but it saved us more than a million dollars per year in licensing fees.

-PatP

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/