Don't worry, you'll get the hang of it - quicker, if you can attract the attention of someone here on ssc who's in your time zone 😀

Any questions...

That's a Common Table Expression, a CTE[/url]. How long have you been...

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...

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...

amit_pjoshi (7/10/2013)

---

amit_pjoshi (7/10/2013)

---

Hi Gail,

No we are not using BEGIN TRANSACTION / COMMIT TRANSACTION.

we are executing the same...

Amit, do you have a BEGIN TRANSACTION / COMMIT TRANSACTION in your code?

Confused...where exactly are you deleting files from? A windows...

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...

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...

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...

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...

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...

Sturner has a good point.

You might also want to check your firewall settings[/url].

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) + ',...

-- 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...