Monday, March 26, 2012
How to apply the snapshot manually ?
To apply the snapshot manually, you can:
Save the snapshot files to removable media such as a compact disc, tape device, or removable disk and then send the media to the Subscriber location.
Yes, but how to apply the snapshot then ?
(My snapshot folder is much smaller than backup file of the whole database.)
Thanks for any ideas.
CarusoHA: only backup and restore
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q320499#3
Monday, March 19, 2012
how to add record with same Primarykey that user deleted
Many thanks.An autonumber? Look at IDENTITY_INSERT in BOL.
Friday, February 24, 2012
How to add a do loop in sql? Thanks a lot!
Dec 2004. Now I am doing it by manually changing the date for each
month, but I want to do all the months at one time. Is there a way to
add something like a do loop to achieve that goal? Please see my query
below. Thanks so much!
declare @.month_date_b smalldatetime
--B month beginning date
declare @.month_date_e smalldatetime
--E month ending date
select @.month_date_b='9/1/2004'
select @.month_date_e='9/30/2004'
select a.person_id, a.fn_accno, a.fn_bal, b.mm_open
from fn_mm_fnbal as a
join fn_mm_list as b
on a.person_id=b.person_id
and b.mm_open < @.month_date_e
where a.bal_date between @.month_date_b and @.month_date_e
group by a.person_id, a.fn_accno, a.fn_bal, b.mm_open
order by a.fn_accno, a.fn_balRather than a loop, you can use a join to a set of dates. Or you could
just query the dates out of a Calendar table if you have one. Calendar
tables are very useful for this sort of thing:
SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
FROM fn_mm_fnbal AS A
JOIN fn_mm_list AS B
ON A.person_id = B.person_id
JOIN
(SELECT CAST('20040101' AS DATETIME) UNION ALL
SELECT '20040201' UNION ALL
SELECT '20040301' UNION ALL
SELECT '20040401' UNION ALL
SELECT '20040501' UNION ALL
SELECT '20040601' UNION ALL
SELECT '20040701' UNION ALL
SELECT '20040801' UNION ALL
SELECT '20040901' UNION ALL
SELECT '20041001' UNION ALL
SELECT '20041101' UNION ALL
SELECT '20041201') AS C(cal_date)
ON A.bal_date >= C.cal_date
AND A.bal_date < DATEADD(M,1,C.cal_date)
AND B.mm_open < DATEADD(M,1,C.cal_date)
GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
ORDER BY A.fn_accno, A.fn_bal
--
David Portas
SQL Server MVP
--|||Thanks David! It is a great idea, I forgot to use my calendar table.
David Portas wrote:
> Rather than a loop, you can use a join to a set of dates. Or you
could
> just query the dates out of a Calendar table if you have one.
Calendar
> tables are very useful for this sort of thing:
> SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
> FROM fn_mm_fnbal AS A
> JOIN fn_mm_list AS B
> ON A.person_id = B.person_id
> JOIN
> (SELECT CAST('20040101' AS DATETIME) UNION ALL
> SELECT '20040201' UNION ALL
> SELECT '20040301' UNION ALL
> SELECT '20040401' UNION ALL
> SELECT '20040501' UNION ALL
> SELECT '20040601' UNION ALL
> SELECT '20040701' UNION ALL
> SELECT '20040801' UNION ALL
> SELECT '20040901' UNION ALL
> SELECT '20041001' UNION ALL
> SELECT '20041101' UNION ALL
> SELECT '20041201') AS C(cal_date)
> ON A.bal_date >= C.cal_date
> AND A.bal_date < DATEADD(M,1,C.cal_date)
> AND B.mm_open < DATEADD(M,1,C.cal_date)
> GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
> ORDER BY A.fn_accno, A.fn_bal
> --
> David Portas
> SQL Server MVP
> --