Hi,
I need to write a qry for sql 7/ASP to select a 72hr X 5 matrix of 1/2 hr periods - values need to show booked/unbooked for each of 5 cars for all 72 hours. The problem is the database only stores records for the booked periods.
The query (below) works ok for 1 car & one time slot, but calling this 720 times is not good! - Ideally I need some query that can return all the data in one go, and use the recordset to build a table showing availablity.
How do I write this kind of query ?
Thanks
---
dteDateTime="27-Jul-02 15:30"
iCarID=3
SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"can you give a brief example of what the result set is supposed to look like with the holes in it?
have a look at Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) which shows how to use an integer table to generate dates
rudy
http://rudy.ca/|||Hi,
The results would be as follows :
car_id | 24/07/02 00:00 | 24/07/02 00:30 | 24/07/02 01:00 ...
1 0 1 1
2 1 1 1
3 1 1 1
Each row would be a car, each column a date/time.
The date/times would cover 72 hrs ( 144 columns)
from any date/time specified.
The problem I have is that I only store the 1's in the db not the 0's!
The article looks like exactly what Im trying to do - Thanks.|||okay, what you want is a cross-tab report, and since you'll have to do that in asp anyway, there's not much point generating the missing values with sql, just do them with asp too...
rudy|||Thanks rudy,
At least I know what its called now! that was half the problem trying to research it.
I have solved this already in ASP, the performance was so bad- thats why I looked into doing it on the db server with SQL.|||the performance was bad in ASP?
then you must have been doing something wrong, like calling the database in a loop :cool:
call the database like this:SELECT bookedTime
FROM tblBookedTimes
INNER JOIN tblBookings
ON tblBookedTimes.fk_bookingID
= tblBookings.pk_bookingID
WHERE tblBookings.fk_carID=" & iCarID & "
ORDER BY bookedTimenotice that you will get back all the bookedTime values for the car, and they will be in bookedTime sequence
now "cross-tab" the bookedTime values using ASP logic, laying them out into an array with zeroes in the "empty" spots...
there's no way that's going to have bad performance
laying out the results into an array should be easy, because from your original question it looks like all your datetime values were previously entered with times on the half hour (which makes sense for a booking table)
so just retrieve the data in one query (you may want to add a WHERE condition for the right date, and maybe get more info on which car, etc.), and ask an ASP programmer for help with the array
i guess i should mention that i don't do ASP :rolleyes:
rudy
http://rudy.ca/
Showing posts with label asp. Show all posts
Showing posts with label asp. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Blocking Threshold Exceeded
I have a site that calls a stored procedure to populate data on an ASP page.
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>
Blocking Problem
Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked serve
r
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of m
y
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the sa
me
> SPID. The locks are on key-level. I've tested it and the problem seems to
be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fin
e
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...[vbcol=seagreen]
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f 234 spam-mails.
Betalende brugere fr ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after prope
r
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Serve
r
> 2000 (And still are. They had to move the database back). I can'tfigure ou
t
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thin
g
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f? 234 spam-mails.
> Betalende brugere f?r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked serve
r
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of m
y
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the sa
me
> SPID. The locks are on key-level. I've tested it and the problem seems to
be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fin
e
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...[vbcol=seagreen]
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f 234 spam-mails.
Betalende brugere fr ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after prope
r
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Serve
r
> 2000 (And still are. They had to move the database back). I can'tfigure ou
t
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thin
g
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f? 234 spam-mails.
> Betalende brugere f?r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
Blocking Problem
Hi there,
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked server
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of my
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the same
> SPID. The locks are on key-level. I've tested it and the problem seems to be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fine
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards :)
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>> Hi there,
>> maby someone know something about this kind of strange behaviour. One of
>> my
>> colleagues has made an ASP appl. using SQL 2000 and I helped him move
>> data
>> to another SQL Server 2000 . We just detached and attached and he changed
>> the connectionstring and it just worked fine... For a while. Suddenly the
>> users reported that the couldn't get a certain list of records and when I
>> checked the server there was a couple of X-locks on a table held by the
>> same
>> SPID. The locks are on key-level. I've tested it and the problem seems to
>> be
>> the Transaction handling in some way. I started the profiler and filtered
>> the trace to see
>> the SPID who made the locks and the T-SQL looked something like this :
>> Begin Transaction
>> Select...
>> Update...
>> Select..
>> Another one
>> Begin Transaction
>> Insert...
>> No commit or rollback even though my colleague tells me that he either
>> commits or rolls back his transaction in his code. But it looks like it
>> never hits the server ' Is this a known issue ?
>> Could it be a Service Pack issue. There's no SP's installed. It worked
>> fine
>> when we were running on the old server.. One difference between the old
>> server and the new server is that the new one is installed as a named
>> instance. A lot of the select statements uses a linked server but not the
>> actual update, insert, and delete statement. they're on the loval server.
>> All the locks has Owner type XAct. I really cannot figure out why the
>> Transactions hang. Is there any known issues about named instances and
>> linked servers ?
>> Anybody got a clue ?
>> Regards .)
>> Bobby Henningsen
>>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 234 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after proper
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Server
> 2000 (And still are. They had to move the database back). I can'tfigure out
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thing
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards :)
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> > Hi Bobby
> >
> > Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> > say which events you are profiling, but have you included transactions/SQL
> > Transactions? IT is not unknown for profiler to not include some logging,
> > expecially on a busy server and you are profiling on the same server.
> >
> > You don't say if you have performed any maintenance on this database, you
> > may want to defragment the indexes and update the statistics.
> >
> > Without seeing the actual code it is hard to comment on if it can be
> > improved. You may want to see if you need the select statement before the
> > update, or if you are unneccesarily wrapping select statements in
> > transactions. If your select statement before the update uses a linked
> > server
> > this may force the transaction to be a distributed transaction.
> >
> > John
> >
> > "Bobby Henningsen" wrote:
> >
> >> Hi there,
> >> maby someone know something about this kind of strange behaviour. One of
> >> my
> >> colleagues has made an ASP appl. using SQL 2000 and I helped him move
> >> data
> >> to another SQL Server 2000 . We just detached and attached and he changed
> >> the connectionstring and it just worked fine... For a while. Suddenly the
> >> users reported that the couldn't get a certain list of records and when I
> >> checked the server there was a couple of X-locks on a table held by the
> >> same
> >> SPID. The locks are on key-level. I've tested it and the problem seems to
> >> be
> >> the Transaction handling in some way. I started the profiler and filtered
> >> the trace to see
> >> the SPID who made the locks and the T-SQL looked something like this :
> >>
> >> Begin Transaction
> >> Select...
> >> Update...
> >> Select..
> >>
> >> Another one
> >>
> >> Begin Transaction
> >> Insert...
> >>
> >> No commit or rollback even though my colleague tells me that he either
> >> commits or rolls back his transaction in his code. But it looks like it
> >> never hits the server ' Is this a known issue ?
> >> Could it be a Service Pack issue. There's no SP's installed. It worked
> >> fine
> >> when we were running on the old server.. One difference between the old
> >> server and the new server is that the new one is installed as a named
> >> instance. A lot of the select statements uses a linked server but not the
> >> actual update, insert, and delete statement. they're on the loval server.
> >> All the locks has Owner type XAct. I really cannot figure out why the
> >> Transactions hang. Is there any known issues about named instances and
> >> linked servers ?
> >>
> >> Anybody got a clue ?
> >>
> >> Regards .)
> >> Bobby Henningsen
> >>
> >>
> >>
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 234 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
maby someone know something about this kind of strange behaviour. One of my
colleagues has made an ASP appl. using SQL 2000 and I helped him move data
to another SQL Server 2000 . We just detached and attached and he changed
the connectionstring and it just worked fine... For a while. Suddenly the
users reported that the couldn't get a certain list of records and when I
checked the server there was a couple of X-locks on a table held by the same
SPID. The locks are on key-level. I've tested it and the problem seems to be
the Transaction handling in some way. I started the profiler and filtered
the trace to see
the SPID who made the locks and the T-SQL looked something like this :
Begin Transaction
Select...
Update...
Select..
Another one
Begin Transaction
Insert...
No commit or rollback even though my colleague tells me that he either
commits or rolls back his transaction in his code. But it looks like it
never hits the server ' Is this a known issue ?
Could it be a Service Pack issue. There's no SP's installed. It worked fine
when we were running on the old server.. One difference between the old
server and the new server is that the new one is installed as a named
instance. A lot of the select statements uses a linked server but not the
actual update, insert, and delete statement. they're on the loval server.
All the locks has Owner type XAct. I really cannot figure out why the
Transactions hang. Is there any known issues about named instances and
linked servers ?
Anybody got a clue ?
Regards .)
Bobby HenningsenHi Bobby
Try checking DBCC OPENTRAN to see if any transactions are open. You don't
say which events you are profiling, but have you included transactions/SQL
Transactions? IT is not unknown for profiler to not include some logging,
expecially on a busy server and you are profiling on the same server.
You don't say if you have performed any maintenance on this database, you
may want to defragment the indexes and update the statistics.
Without seeing the actual code it is hard to comment on if it can be
improved. You may want to see if you need the select statement before the
update, or if you are unneccesarily wrapping select statements in
transactions. If your select statement before the update uses a linked server
this may force the transaction to be a distributed transaction.
John
"Bobby Henningsen" wrote:
> Hi there,
> maby someone know something about this kind of strange behaviour. One of my
> colleagues has made an ASP appl. using SQL 2000 and I helped him move data
> to another SQL Server 2000 . We just detached and attached and he changed
> the connectionstring and it just worked fine... For a while. Suddenly the
> users reported that the couldn't get a certain list of records and when I
> checked the server there was a couple of X-locks on a table held by the same
> SPID. The locks are on key-level. I've tested it and the problem seems to be
> the Transaction handling in some way. I started the profiler and filtered
> the trace to see
> the SPID who made the locks and the T-SQL looked something like this :
> Begin Transaction
> Select...
> Update...
> Select..
> Another one
> Begin Transaction
> Insert...
> No commit or rollback even though my colleague tells me that he either
> commits or rolls back his transaction in his code. But it looks like it
> never hits the server ' Is this a known issue ?
> Could it be a Service Pack issue. There's no SP's installed. It worked fine
> when we were running on the old server.. One difference between the old
> server and the new server is that the new one is installed as a named
> instance. A lot of the select statements uses a linked server but not the
> actual update, insert, and delete statement. they're on the loval server.
> All the locks has Owner type XAct. I really cannot figure out why the
> Transactions hang. Is there any known issues about named instances and
> linked servers ?
> Anybody got a clue ?
> Regards .)
> Bobby Henningsen
>
>|||Hi John,
i caqn see that theres is an open transaction under "Current Activity" so
I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
starting. The profiler is not running on the same server. I've updated all
the statistics.
I agree with you about the select statement and it eventually would be a
distributed transaction. But still. This was working on another SQL Server
2000 (And still are. They had to move the database back). I can'tfigure out
what's the difference other than this is running as a named instance. They
wil install SP3 this week and then we'll have to see. Another strange thing
is that the select staement mentioned is on a view which does a linked
server query and it then holds an Sch-S lock on the view. So when I look
under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
view!
Regards :)
Bobby Henningsen
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> Hi Bobby
> Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> say which events you are profiling, but have you included transactions/SQL
> Transactions? IT is not unknown for profiler to not include some logging,
> expecially on a busy server and you are profiling on the same server.
> You don't say if you have performed any maintenance on this database, you
> may want to defragment the indexes and update the statistics.
> Without seeing the actual code it is hard to comment on if it can be
> improved. You may want to see if you need the select statement before the
> update, or if you are unneccesarily wrapping select statements in
> transactions. If your select statement before the update uses a linked
> server
> this may force the transaction to be a distributed transaction.
> John
> "Bobby Henningsen" wrote:
>> Hi there,
>> maby someone know something about this kind of strange behaviour. One of
>> my
>> colleagues has made an ASP appl. using SQL 2000 and I helped him move
>> data
>> to another SQL Server 2000 . We just detached and attached and he changed
>> the connectionstring and it just worked fine... For a while. Suddenly the
>> users reported that the couldn't get a certain list of records and when I
>> checked the server there was a couple of X-locks on a table held by the
>> same
>> SPID. The locks are on key-level. I've tested it and the problem seems to
>> be
>> the Transaction handling in some way. I started the profiler and filtered
>> the trace to see
>> the SPID who made the locks and the T-SQL looked something like this :
>> Begin Transaction
>> Select...
>> Update...
>> Select..
>> Another one
>> Begin Transaction
>> Insert...
>> No commit or rollback even though my colleague tells me that he either
>> commits or rolls back his transaction in his code. But it looks like it
>> never hits the server ' Is this a known issue ?
>> Could it be a Service Pack issue. There's no SP's installed. It worked
>> fine
>> when we were running on the old server.. One difference between the old
>> server and the new server is that the new one is installed as a named
>> instance. A lot of the select statements uses a linked server but not the
>> actual update, insert, and delete statement. they're on the loval server.
>> All the locks has Owner type XAct. I really cannot figure out why the
>> Transactions hang. Is there any known issues about named instances and
>> linked servers ?
>> Anybody got a clue ?
>> Regards .)
>> Bobby Henningsen
>>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 234 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Hi Bobby
SP3a would be a minumum requirement and if you are looking at staying with
SQL 2000 for a while you should consider SP4 + patching to 2187 (after proper
evaluation and testing!)
John
"Bobby Henningsen" wrote:
> Hi John,
> i caqn see that theres is an open transaction under "Current Activity" so
> I'm quite sure about that. I prodfiling the T-SQL stmt starting and batch
> starting. The profiler is not running on the same server. I've updated all
> the statistics.
> I agree with you about the select statement and it eventually would be a
> distributed transaction. But still. This was working on another SQL Server
> 2000 (And still are. They had to move the database back). I can'tfigure out
> what's the difference other than this is running as a named instance. They
> wil install SP3 this week and then we'll have to see. Another strange thing
> is that the select staement mentioned is on a view which does a linked
> server query and it then holds an Sch-S lock on the view. So when I look
> under "Curent Activity" I see the X-locks but also 6-7 Sch-S locks on the
> view!
> Regards :)
> Bobby Henningsen
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6EF40E94-3429-484E-8B06-785BBF86D4F1@.microsoft.com...
> > Hi Bobby
> >
> > Try checking DBCC OPENTRAN to see if any transactions are open. You don't
> > say which events you are profiling, but have you included transactions/SQL
> > Transactions? IT is not unknown for profiler to not include some logging,
> > expecially on a busy server and you are profiling on the same server.
> >
> > You don't say if you have performed any maintenance on this database, you
> > may want to defragment the indexes and update the statistics.
> >
> > Without seeing the actual code it is hard to comment on if it can be
> > improved. You may want to see if you need the select statement before the
> > update, or if you are unneccesarily wrapping select statements in
> > transactions. If your select statement before the update uses a linked
> > server
> > this may force the transaction to be a distributed transaction.
> >
> > John
> >
> > "Bobby Henningsen" wrote:
> >
> >> Hi there,
> >> maby someone know something about this kind of strange behaviour. One of
> >> my
> >> colleagues has made an ASP appl. using SQL 2000 and I helped him move
> >> data
> >> to another SQL Server 2000 . We just detached and attached and he changed
> >> the connectionstring and it just worked fine... For a while. Suddenly the
> >> users reported that the couldn't get a certain list of records and when I
> >> checked the server there was a couple of X-locks on a table held by the
> >> same
> >> SPID. The locks are on key-level. I've tested it and the problem seems to
> >> be
> >> the Transaction handling in some way. I started the profiler and filtered
> >> the trace to see
> >> the SPID who made the locks and the T-SQL looked something like this :
> >>
> >> Begin Transaction
> >> Select...
> >> Update...
> >> Select..
> >>
> >> Another one
> >>
> >> Begin Transaction
> >> Insert...
> >>
> >> No commit or rollback even though my colleague tells me that he either
> >> commits or rolls back his transaction in his code. But it looks like it
> >> never hits the server ' Is this a known issue ?
> >> Could it be a Service Pack issue. There's no SP's installed. It worked
> >> fine
> >> when we were running on the old server.. One difference between the old
> >> server and the new server is that the new one is installed as a named
> >> instance. A lot of the select statements uses a linked server but not the
> >> actual update, insert, and delete statement. they're on the loval server.
> >> All the locks has Owner type XAct. I really cannot figure out why the
> >> Transactions hang. Is there any known issues about named instances and
> >> linked servers ?
> >>
> >> Anybody got a clue ?
> >>
> >> Regards .)
> >> Bobby Henningsen
> >>
> >>
> >>
>
> --
> ---
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 234 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>
Friday, February 24, 2012
blank textbox is NOT null ..
This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
@.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
> @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
@.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
> @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>
blank textbox is NOT null ..
This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
@.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />
Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
> @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
@.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />
Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode =
> @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>
blank textbox is NOT null ..
This must be a common asp.net / sql server problem.
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode = @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode => @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>
I only want to filter on the date when the filterdate textbox has a
value. Not sure if this is the best approach, but it does not appear to
be working.. I was hoping blank mean no filtering and all qualifying
rows returning
<asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode = @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
datetime))and enddate >= IsNull(cast(@.DateFilter as
datetime),cast('1/1/1999' as datetime)) order by StartDate" >
<asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
PropertyName="Text" Type="Datetime" />Hi
If you are going to dynamically create the SQL statement in you ASP then you
can omit the clause completely if no value is entered. If you are going to
use a stored procedure then check out
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dynamic_sql.html
John
"jobs" wrote:
> This must be a common asp.net / sql server problem.
> I only want to filter on the date when the filterdate textbox has a
> value. Not sure if this is the best approach, but it does not appear to
> be working.. I was hoping blank mean no filtering and all qualifying
> rows returning
> <asp:TextBox ID="DateFilter" runat="server"></asp:TextBox>
> SelectCommand="SELECT * FROM [GEN_RouteInfo_vw] WHERE plancode => @.plancode and RouteCode=@.RouteCode and CountryId=@.CountryId and
> startdate <= IsNull(cast(@.DateFilter as datetime),cast('1/1/2999' as
> datetime))and enddate >= IsNull(cast(@.DateFilter as
> datetime),cast('1/1/1999' as datetime)) order by StartDate" >
> <asp:ControlParameter ControlID="DateFilter" Name="DateFilter"
> PropertyName="Text" Type="Datetime" />
>
Sunday, February 19, 2012
Blank Pages in report
I am supporting a web based application (ASP.NET 1.1) that uses RS 2005 on the production server. I needed to change a report, I make my changes, it looks fine in the Developer Viewer and it exports fine to PDF on my PC. I have deployed it to the production server but when you pull the report within the application (PDF format) it has 3 additional blank pages on the end of the report. The report is the correct size. It does not excede the 8/11 page size including the margins, there are no hidden fields. The report does have 4 tables. I have even viewed the report from //localhost/reports on the server and it shows only one page and exports to pdf with one page. I have looked at all the postings that deal with Blank pages and nothing seems to correct the problem.
Does anyone have any ideas?
Thanks
If you take things out does it still put 3 extra pages in?|||I can try that tonight. Since this only happens in production, I must wait to off hours.|||Ok, I found the issue. This report is just a sub report to a master report. The master report was not formated to fit on 8.5/11 paper. Thus it pushed the additional blank pages. Once I formated the Master report, there where no more blank pages.
Thanks
|||This is the most common problem for blank pages in a report.
In development the user will accidentally extend the width of the report beyond normal page limits, so extra pages get printed.
Blank Pages in a report <-- text put there for people searching on this problem.
Make sure that your main report page setup is correct for the hieght and width of your report, and also double check any subreports.
Daryl
Blank Pages in report
I am supporting a web based application (ASP.NET 1.1) that uses RS 2005 on the production server. I needed to change a report, I make my changes, it looks fine in the Developer Viewer and it exports fine to PDF on my PC. I have deployed it to the production server but when you pull the report within the application (PDF format) it has 3 additional blank pages on the end of the report. The report is the correct size. It does not excede the 8/11 page size including the margins, there are no hidden fields. The report does have 4 tables. I have even viewed the report from //localhost/reports on the server and it shows only one page and exports to pdf with one page. I have looked at all the postings that deal with Blank pages and nothing seems to correct the problem.
Does anyone have any ideas?
Thanks
If you take things out does it still put 3 extra pages in?|||I can try that tonight. Since this only happens in production, I must wait to off hours.|||
Ok, I found the issue. This report is just a sub report to a master report. The master report was not formated to fit on 8.5/11 paper. Thus it pushed the additional blank pages. Once I formated the Master report, there where no more blank pages.
Thanks
|||This is the most common problem for blank pages in a report.
In development the user will accidentally extend the width of the report beyond normal page limits, so extra pages get printed.
Blank Pages in a report <-- text put there for people searching on this problem.
Make sure that your main report page setup is correct for the hieght and width of your report, and also double check any subreports.
Daryl
Subscribe to:
Posts (Atom)