ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,636 through 4,650 (of 10,143 total)

RE: Isolation Level

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
this is the data i get after runing this script:
IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays
21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 7:43 am

#1613343
RE: Isolation Level

kapil_kk (5/8/2013)
this is the data i get after runing this script:
IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays
21B00005101101BVB848979722013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
22B00005101101BVB848979822013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
23B00005101101BVB848979922013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
24B00005101101BVB848980022013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
25B00005101101BVB848980122013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
26B00005101101BVB848980222013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
27B00005101101BVB848980322013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
28B00005101101BVB848980422013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
29B00005101101BVB848980522013-05-082013-05-08 18:51:50.9702013-11-08 18:51:50.9701TRN0000048011030
30B00005101101BVB848980622013-05-082013-05-08 18:51:50.9702013-11-08...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 7:30 am

#1613334
RE: Isolation Level

ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
Hi Chris,
I tried this tally table to my another scenario for creating booklet...
What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 7:24 am

#1613329
RE: Isolation Level

kapil_kk (5/8/2013)
Hi Chris,
I tried this tally table to my another scenario for creating booklet...
What I want in this is that if @quantity =2 and @leaf =10 then 20 voucherno will...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 7:22 am

#1613326
RE: Isolation Level

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
kapil_kk (5/8/2013)
One more doubt Chris,
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 6:54 am

#1613310
RE: Isolation Level

kapil_kk (5/8/2013)
One more doubt Chris,
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 6:37 am

#1613303
RE: Query seems to be constrained by single core CPU in multiple core server - any suggestions?

Can you post the query with obfuscated table names?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 6:36 am

#1613302
RE: Isolation Level

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
Here's what I'd recommend you do.
Firstly, read the error trapping article. If your batch fails in the middle of the transaction then you need a graceful way...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 6:12 am

#1613298
RE: Isolation Level

Here's what I'd recommend you do.
Firstly, read the error trapping article. If your batch fails in the middle of the transaction then you need a graceful way to back...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 5:11 am

#1613279
RE: Query seems to be constrained by single core CPU in multiple core server - any suggestions?

Can you post the actual execution plan as a .sqlplan attachment please? Makes this kind of exercise much easier.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 5:06 am

#1613278
RE: Isolation Level

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
Here's an excellent article to help you out:
http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
Ok thanks a lot.. 🙂
will go through this article after completing transaction work with this script...
plz help me on this
Here's a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 4:49 am

#1613274
RE: Isolation Level

Here's an excellent article to help you out:
http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 4:32 am

#1613268
RE: Isolation Level

kapil_kk (5/8/2013)
...
Yess it worked now 🙂
Thanks a lot..
can we move fwd now towards error handling and transaction
Sure. This bit first?
kapil_kk (5/8/2013)
Also, I am not clear what this will do please...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 4:00 am

#1613263
RE: Isolation Level

Yes that's correct.
Here's an updated script which is adjusted to account for NULL values of VoucherValue:
--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]
DECLARE
@vouchertypeID int = 1,
@denomination int = 100,
@quantity int = 10,
@amountvalue int =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 3:36 am

#1613256
RE: Isolation Level

kapil_kk (5/8/2013)
ChrisM@Work (5/8/2013)
The last SELECT in that script is
SELECT VoucherNumber = @VoucherNumber, retval = @retval
What values are returned?
VoucherNumberretval
0VB
Thanks.
Either the table gv_vouchervalue doesn't have a row where voucherabbreviation = 'VVB',...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 8, 2013 at 3:12 am

#1613245

Viewing 15 posts - 4,636 through 4,650 (of 10,143 total)