ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,396 through 4,410 (of 10,143 total)

RE: Data in date column to decrement with one

asifejaz (7/10/2013)
thanks. Sorry i didnt understand what you mean by this
WITH Updater AS (
pls let me know.
That's a Common Table Expression, a CTE[/url]. How long have you been...
^{“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

July 11, 2013 at 7:48 am

#1631586
RE: Count value once in 30 days

puja63 (7/10/2013)
Hi Sean,
Thanks, but it's not just distinct customer, I need to count based on whether his response to survey was in the last 30 days. If he responded to...
^{“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

July 11, 2013 at 2:46 am

#1631496
RE: Deleted items still appears in output

hidayah (7/10/2013)
Hi All,
Attached is the result that I required (the line I highlighted in blue that contains the correct info that I need).
As you can see, I have tons of...
^{“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

July 11, 2013 at 1:06 am

#1631478
RE: Increase length of NVARCHAR(MAX) more than 8000 Character

amit_pjoshi (7/10/2013)
amit_pjoshi (7/10/2013)
ChrisM@Work (7/10/2013)
Amit, do you have a BEGIN TRANSACTION / COMMIT TRANSACTION in your code?
Hi Gail,
No we are not using BEGIN TRANSACTION / COMMIT TRANSACTION.
we are executing the same...
^{“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

July 10, 2013 at 7:08 am

#1631203
RE: Increase length of NVARCHAR(MAX) more than 8000 Character

Amit, do you have a BEGIN TRANSACTION / COMMIT TRANSACTION in your code?
^{“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

July 10, 2013 at 6:55 am

#1631188
RE: Deleted items still appears in output

hidayah (7/10/2013)
Hi,
Yes, I do get that much of rows. So how do I permanently deleted those files that I do not want?
Confused...where exactly are you deleting files from? A windows...
^{“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

July 10, 2013 at 5:03 am

#1631149
RE: Increase length of NVARCHAR(MAX) more than 8000 Character

amit_pjoshi (7/10/2013)
Lowell (7/10/2013)
instead of
EXEC sp_executesql @sql
use
EXEC(@sql)
which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be...
^{“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

July 10, 2013 at 3:15 am

#1631123
RE: Increase length of NVARCHAR(MAX) more than 8000 Character

Lowell (7/10/2013)
instead of
EXEC sp_executesql @sql
use
EXEC(@sql)
which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good...
^{“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

July 10, 2013 at 2:59 am

#1631119
RE: Increase length of NVARCHAR(MAX) more than 8000 Character

nvarchar(max) holds one or two gb. It's not the problem. There are a number of possible issues here, the most likely is that you are using other variables in the...
^{“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

July 9, 2013 at 7:24 am

#1630785
RE: Data in date column to decrement with one

asifejaz (7/8/2013)
Thanks for your help. Really appreciate that. I dragged in the project that is at its end before go live..
I ran the script and the out put that appears...
^{“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

July 9, 2013 at 6:57 am

#1630776
RE: Using a Recursive CTE to Generate a List

Beautifully illustrated, well written and easy to follow. Even though the rCTE method has been shown more than once to be somewhat slower than FOR XML PATH, there are some...
^{“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

July 9, 2013 at 5:06 am

#1630733
RE: Error with Linked Server and Dynamic SQL

Sturner has a good point.
You might also want to check your firewall settings[/url].
^{“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

July 8, 2013 at 9:15 am

#1630488
RE: Problem using date comparison in Dynamic SQL Please help

DECLARE
@SQLsource VARCHAR(1000),
@ArchiveBatchID INT,
@ArchiveControlField VARCHAR(20),
@TableName VARCHAR(20),
@ArchiveSourceDateField VARCHAR(20),
@ArchiveCutOffDate DATETIME
SELECT
@ArchiveBatchID = 97,
@ArchiveControlField = 'ControlColumn',
@TableName = 'MyTable',
@ArchiveSourceDateField = 'SourceDate',
@ArchiveCutOffDate = GETDATE()
SET @SQLsource = 'SELECT '
+ CAST(@ArchiveBatchID AS varchar) + ',...
^{“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

July 8, 2013 at 8:29 am

#1630463
RE: Split data

-- Extend the sample data set:
DROP table #tbl1
Create table #tbl1
(
ID INT identity primary key,
voucherno varchar(10),
modifieddate datetime
)
insert into #tbl1
values
('V00001','2013-07-07 11:32:44.810'),
('V00002','2013-07-07 11:32:44.810'),
('V00003','2013-07-08 12:32:44.810'),
('V00004','2013-07-08 12:32:44.810'),
('V00005','2013-07-07 11:32:44.810'),
('V00006','2013-07-07 11:32:44.810'),
('V00007','2013-07-09 11:32:44.810'),
('V00008','2013-07-09 11:32:44.810'),
('V00009','2013-07-08 11:32:44.810')
-- examine the result...
^{“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

July 8, 2013 at 6:05 am

#1630391
RE: Data in date column to decrement with one

Here's a little test harness. It's a simplification of your problem - all you have to play with is the accrual date and the delta you want to apply to...
^{“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

July 8, 2013 at 5:34 am

#1630383

Viewing 15 posts - 4,396 through 4,410 (of 10,143 total)