ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,721 through 6,735 (of 10,143 total)

RE: Can I create Email Trigger that compares column value in inserted row with value in another table?

andrew.sugden (8/21/2011)
...Is writing a trigger on an INSERT the best way to notify Users and is it possible to compare data entered with data in another table?
...
Client-side data entry validation....
^{“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

August 22, 2011 at 4:36 am

#1371329
RE: Column declaration

It's explained reasonably well in BOL.
n is the number of characters, which for CHAR(n) datatype is the same as the storage requirement in bytes. The storage requirement is 2xn...
^{“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

August 22, 2011 at 4:27 am

#1371323
RE: SQL Groups in INDIA

Try this.
^{“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

August 22, 2011 at 3:55 am

#1371309
RE: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing"

There's an interesting article on MSDN covering nested transactions:
http://msdn.microsoft.com/en-us/library/ms189336.aspx
^{“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

August 22, 2011 at 3:30 am

#1371301
RE: Inserting summarized data

One last point - the sequence table which holds the next sequence number for table gltrx_all. Below is a rewrite which will accelerate the update/read:
...
^{“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

August 22, 2011 at 3:01 am

#1371290
RE: Inserting summarized data

Thanks for the generous feedback, troe_atl. I'd agree with Sean's comments; lurk here from time to time, try resolving a few issues yourself - you don't have to post until...
^{“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

August 19, 2011 at 9:53 am

#1370856
RE: Inserting summarized data

Close. I've moved some workings to outside of the transaction to mimimise the transaction duration (and any resources which might be unnecessarily affected).
USE [ISPurchasing]
GO
/****** Object: StoredProcedure [dbo].[cust_CreateJournalEntrymod] ...
^{“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

August 19, 2011 at 7:55 am

#1370738
RE: Inserting summarized data

Not so fast! We're not done yet! It still requires a transaction for those table updates, and a try-catch block to make it all work nicely....
^{“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

August 19, 2011 at 7:17 am

#1370705
RE: Run query multiple times using different query values.

-- Let's see what the date arithmetic is for:
select DATEDIFF(mm,0,getdate()) -- months since day 0
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1,0) -- first day of last month
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,0)) -- first day of last...
^{“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

August 19, 2011 at 5:58 am

#1370649
RE: get date of first monday of the given year?

Ninja's_RGR'us (8/19/2011)
Quite easy with a calendar table.
Select top 1 * FROM dbo.Calendar WHERE Y = 2011 AND DW = <whatever fits your setting> ORDER BY dt.
Just as easy without. Make...
^{“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

August 19, 2011 at 3:51 am

#1370604
RE: Inserting summarized data

troe_atl (8/18/2011)
Hi guys. I really appreciate all of the help in optimizing this sproc. Chris, I've tested the code you submitted and everything works fine but I need the results...
^{“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

August 19, 2011 at 2:46 am

#1370587
RE: Inserting summarized data

ChrisM@Work (8/18/2011)
...This incorporates many of the changes suggested to date and replaces the cursor with a local temporary table...
Troe_atl, we still have to enclose the inserts within a transaction and...
^{“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

August 18, 2011 at 10:08 am

#1370238
RE: How to convert this 'dd-mm-yyyy' to 'yyyy-MM-ddTHH:mm:ss.fff' string format?

What action are you attempting to perform here?
^{“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

August 18, 2011 at 9:42 am

#1370215
RE: SQL Query question (count dates for each month)

Hello
Have a read of the link in my sig for general guidelines on posting. Included in this is a suggestion for the creation of sample data for folks to test...
^{“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

August 18, 2011 at 7:19 am

#1370100
RE: How to convert this 'dd-mm-yyyy' to 'yyyy-MM-ddTHH:mm:ss.fff' string format?

use enterpriseonly
go
declare @dt;
SET @dt = (SELECT d1 FROM [EnterpriseOnly].[dbo].[datee]);
or
SELECT @dt = d1 FROM [EnterpriseOnly].[dbo].[datee];
This assumes table [datee] has only one row.
^{“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

August 18, 2011 at 6:49 am

#1370084

Viewing 15 posts - 6,721 through 6,735 (of 10,143 total)