More ntext trouble

I'm trying to build a REPLACE() function for NTEXT values.
I'm facing a lot of trouble doing it, and I think it could be a very common problem.
I think the biggest problem is when one READTEXT a chunk of a NTEXT value, I couldn't find any way to put that chunk into an NVARCHAR in order to postprocess it.
Let me show you some source code:

BEGIN TRAN
DECLARE @.ptrval varbinary(16)
DECLARE @.datalen integer
DECLARE @.chunk nvarchar(4000)
DECLARE @.i integer
DECLARE @.q integer
DECLARE @.maxlenvarchar integer

-- LET THE POINTER @.ptrval POINTS TO THE NTEXT VALUE, GATHER THE REAL LENGTH INTO @.datalen (300000 bytes approx.)
SELECT @.ptrval = TEXTPTR(ntext_value), @.datalen = DATALENGTH(ntext_value)/2
FROM my_table
WHERE id = 8293

SELECT @.i = 0, @.maxlenvarchar = 4000

-- I WANT TO REPLACE ALL OCCURRENCIES OF CHARACTER 'A' WITH CHARACTER 'B'
-- INTO THE NTEXT FIELD. SO, I THOUGHT THAT IT COULD BE POSSIBLE DOING IT THIS WAY:
-- 1) READ THE NTEXT VALUE IN CHUNKS OF 4000 BYTES
-- 2) PUT EACH CHUNK INTO AN NVARCHAR VARIABLE (@.chunk)
-- 3) APPLY THE REPLACE FUNCTION TO EACH @.chunk
-- 4) REBUILD THE NTEXT VALUE AND STORE IT AGAIN INTO THE TABLE

-- HERE, IN THE WHILE LOOP IS PART OF THE JOB, I COULDN'T GO BEYOND THIS
WHILE @.datalen > 0
BEGIN
IF @.datalen > @.maxlenvarchar
SELECT @.q = @.maxlenvarchar
ELSE
SELECT @.q = @.datalen

-- THIS COMMAND READS 4000 BYTES OF THE NTEXT VALUE,..
-- BUT HOW CAN I STORE IT INTO A NVARCHAR IN ORDER TO USE REPLACE() ??
READTEXT my_table.ntext_value @.ptrval @.i @.q

SELECT @.i = @.i + @.maxlenvarchar, @.datalen = @.datalen - @.q

-- I DON'T KNOW HOW TO "PRINT" THE CHUNK, THIS COMMAND PRINTS THE POINTER VALUE (HEX)
PRINT @.ptrval

-- I CAN SEE THE TEXT POINTER IS ALWAYS VALID
PRINT TEXTVALID ('my_table.ntext_value', @.ptrval)
END

COMMIT

-- WHAT I HAVEN'T SEE YET IS THE PART OF REBUILDING THE NTEXT VAL AND WRITE IT AGAIN INTO THE DATABASE.Have you tried using the PATINDEX and UPDATETEXT functions ?|||I've read about them ... but I cannot imagine in which way I should use them in order to do what I want.|||Here you go:

code example (http://www.1aspstreet.com/xq/ASP/txtCodeId.395/lngWid.5/qx/vb/scripts/ShowCode.htm)|||/*
SP on WWW page linked in rnealejr's message would process only a table with one record.
It also "rereplace".
*/

/* DESCRIPTION
Replacing is thought to be very simple, but this is not right in blobs.

I use overlaying SUBSTRING frame of 4000 chars
sliding by (4000+1-len(@.OldStr))
-> 4000 chars sure
Cursor *MUST* be used, because of statement UPDATETEXT,
SQL pseudoprocedure, which does not have FROM part
and needs poiter to blob, unique for each blob column and table row.

Temp tables:
#BlobTable - table with Blobs (to be updated)
#BlobPos - current pos to be sure not to "rereplace" 'A'->'AA' ('XXAXX'->'XXAAAAAAAAAAAA...XX')
#BlobRes - results of single find
#BlobUpd - list for blob updates

Beware of replacing data with table option 'text in row' set !!!
4000 limit is for nvarchar(international,UNICODE), use 8000 varchar for single language.

*/

--INITIALIZATION (creating temp tables, generating some 16kB blob data)
set nocount on
set textsize 2147483647
if object_id('tempdb..#BlobTable') is not null drop table #BlobTable
if object_id('tempdb..#BlobPos') is not null drop table #BlobPos
if object_id('tempdb..#BlobRes') is not null drop table #BlobRes
if object_id('tempdb..#BlobUpd') is not null drop table #BlobUpd
GO
create table #BlobTable (
id int identity(1,1) primary key
,Blob ntext not null
,BlobCopy ntext not null
)
create table #BlobPos (
id int primary key
,ptr varbinary(16) not null
,BlobLen int not null
,BlobPos int not null
,BlobDelta int not null
)
create table #BlobRes (
id int primary key
,Pos int not null
)
create table #BlobUpd (
id int not null
,Pos int not null
)
GO
--generating test data (10 different blobs, about 80000 chars each)
declare @.ptr varbinary(16)
declare @.ptrCopy varbinary(16)
declare @.index int
declare @.addtext nvarchar(4000)
declare @.Counter int
set @.addtext=replicate('X',4000)
while 10>(select count(*) from #BlobTable) begin
insert #BlobTable(Blob,BlobCopy)
select
replicate('X',count(*))+'abcdefghijklmabcdefghijkl m'
,replicate('X',count(*))+'abcdefghijklmabcdefghijk lm'
from #BlobTable
select @.ptr=textptr(Blob),@.index=datalength(Blob)/2,@.ptrCopy=textptr(BlobCopy)
from #BlobTable
where id=SCOPE_IDENTITY()
set @.Counter=0
while @.Counter<20 begin
set @.index=@.index
updatetext #BlobTable.Blob @.ptr @.index 0 @.addtext
set @.Counter=@.Counter+1
end
end
update #BlobTable set
BlobCopy=Blob
GO
--INITIALIZATION-END

--REPLACE ('efgh' WITH 'efgh?')
declare @.OldStr nvarchar(4000) --up to 4000 UNICODE chars
declare @.OldStrLike nvarchar(4000)
declare @.length int
declare @.lengthDelta int
declare @.ptr varbinary(16)
declare @.NewStr nvarchar(4000) --up to 4000 UNICODE chars
declare @.index int
declare @.c cursor
--init of replace
set @.OldStr='efgh'
set @.NewStr='efgh?'
set @.lengthDelta=len(@.NewStr)-len(@.OldStr)
set @.length=len(@.OldStr)
insert #BlobPos(id,ptr,BlobLen,BlobPos,BlobDelta)
select id,ptr=textptr(Blob),BlobLen=datalength(Blob)/2, BlobPos=0,BlobDelta=0
from #BlobTable
--loop of finding
insert #BlobRes(id,Pos)
select bt.id,charindex(@.OldStr,substring(Blob,bp.BlobPos+ 1,4000))
from #BlobTable bt
join #BlobPos bp
on bt.id=bp.id and (bp.BlobPos+@.length)<=bp.BlobLen
while @.@.rowcount>0 begin
insert #BlobUpd(id,Pos)
select br.id,bp.BlobPos+br.Pos-1+bp.BlobDelta
from #BlobRes br
join #BlobPos bp
on br.id=bp.id and br.Pos>0
update bp set
bp.BlobPos=bp.BlobPos+case when br.Pos>0 then br.Pos else 4000+1-len(@.OldStr) end
,bp.BlobDelta=bp.BlobDelta+case when br.Pos>0 then @.lengthDelta else 0 end
from #BlobPos bp
join #BlobRes br
on bp.id=br.id
delete #BlobRes
insert #BlobRes(id,Pos)
select bt.id,charindex(@.OldStr,substring(Blob,bp.BlobPos+ 1,4000))
from #BlobTable bt
join #BlobPos bp
on bt.id=bp.id and (bp.BlobPos+@.length)<=bp.BlobLen
end
--update of blob by list
set @.c=cursor local forward_only static for
select bu.Pos,bp.ptr
from #BlobUpd bu
join #BlobPos bp
on bu.id=bp.id
open @.c
fetch next from @.c into @.index,@.ptr
while @.@.fetch_status=0 begin
updatetext #BlobTable.Blob @.ptr @.index @.length @.NewStr
fetch next from @.c into @.index,@.ptr
end
close @.c
deallocate @.c
GO
--END OF REPLACE

--FINALIZE
set nocount off
select * from #BlobTable
drop table #BlobTable
GO

/*
This algorithm is really slow, especially on large blobs.
I found many fast optimalizations for this almost simple algorithm,
but I realized that I am not so much interested in blobs.
I should spend a week of freetime or more to merge ideas and optimize, too much for fun.
*/

More conversation_endpoints

So I took the time to build a reproduction of the conversation_endpoint problem that was discussed in another thread. I build two databases, with a send and receive queue. This is essentially the way the code works here at my site. I have a script near the bottom that sends messages every 5 minutes for 2 hours. If there is any logic that removes conversation_endpoints 30 min then the Message record table will show them.

Please let me know what I am doing wrong, so I can change my production code to help eliminate the large buildup in the sys.conversation_endpoints.

Thanks!

use master

go

if exists ( select * from sys.databases where name = 'SBSource' )

drop database SBSource

go

if exists ( select * from sys.databases where name = 'SBTarget' )

drop database SBTarget

go

-- Setup environment for test

create database SBSource

GO

ALTER DATABASE SBSource SET ENABLE_BROKER

ALTER DATABASE SBSource SET TRUSTWORTHY ON

GO

create database SBTarget

GO

ALTER DATABASE SBTarget SET ENABLE_BROKER

ALTER DATABASE SBTarget SET TRUSTWORTHY ON

GO

use SBSource

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[SourceQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBSourceTest] authorization [dbo]

ON QUEUE [dbo].[SourceQueue]

( [Test] );

CREATE ROUTE [ToTarget] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBTargetTest' , ADDRESS = N'LOCAL';

GO

create procedure dbo.ProcessEndDialogMessages

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier,

@.message_type sysname,

@.message_body xml;

begin transaction;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

while @.conversation_handle IS NOT NULL

begin

end conversation @.conversation_handle;

commit;

begin transaction;

set @.conversation_handle = null;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

end

commit;

end

go

Alter QUEUE dbo.[SourceQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessEndDialogMessages, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use SBTarget

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[TargetQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBTargetTest] authorization [dbo]

ON QUEUE [dbo].[TargetQueue]

( [Test] );

CREATE ROUTE [ToSource] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBSourceTest' , ADDRESS = N'LOCAL';

GO

create table dbo.MessageRecord ( Conversation_handle uniqueidentifier, Inserted datetime )

go

create procedure dbo.ProcessTargetQueue

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier,

@.message_type sysname,

@.message_body xml;

begin transaction;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

while @.conversation_handle IS NOT NULL

begin

insert into dbo.MessageRecord ( Conversation_handle, Inserted ) values ( @.conversation_handle, getdate() );

end conversation @.conversation_handle;

commit;

begin transaction;

set @.conversation_handle = null;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

end

commit;

end

GO

Alter QUEUE dbo.[TargetQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use sbsource

go

-- start sending messages, check count in conv_endpoints along the way

set xact_abort on

set nocount on

declare @.EndAt datetime,

@.msg xml,

@.ch uniqueidentifier;

set @.EndAt = DATEADD( hh, 2, getdate() )

set @.msg = '<message>dfsafa</message>';

while getdate() < @.EndAt

begin

set @.ch = null;

begin transaction;

begin dialog conversation @.ch

from service [SBSourceTest]

to service 'SBTargetTest'

on contract [Test]

with

encryption=off;

send on conversation @.ch message type [msgTest] (@.msg);

note the abscence of an end conversation, so no fire and forget!

commit;

waitfor delay '00:05:00'

end

GO

-- check on data after complete.

select state, count(*)

from SBSource.sys.conversation_endpoints

group by state;

select state, count(*)

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

group by state;

select m.*, c.*

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

select *

from SBTarget.dbo.targetqueue

Please use the SQLServer feedback site to report this issue: https://connect.microsoft.com/SQLServer/Feedback

Thanks,
~ Remus

Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, alon
g
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as lon
g
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fai
l
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers the
n
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, i
t
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, al
ong
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as l
ong
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate)
.
> Adding rows for "empty" months was also fairly easy, I did a outer join on
a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might b
e
> doing it entirely the wrong way. Normally I would make two subqueries, A a
nd
> B, which are the month-by-month rollups. I then have a WHERE that joins th
em
> B.trandate <= A.trandate. But since the data has been grouped by month, th
e
> actual date is no longer available (it's been "grouped out" of the results
).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to f
ail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you e
nd
> up with 200412 being smaller than 20043, and if you cast them to numbers t
hen
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw dat
a
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in,
it
> needs to refer to data in the A subquery, and if there is a way to do this
I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE
is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A an
d
>B, which are the month-by-month rollups. I then have a WHERE that joins the
m
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results)
.
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fa
il
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you en
d
>up with 200412 being smaller than 20043, and if you cast them to numbers th
en
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:

> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maurysql

Monthly rollups, tricky SQL question?

I'm trying to build a report that shows any user's purchases per month, along
with the resulting balance. That is proving tricky, and to add to the
confusion I have to add rows even where there was no purchases/sales, as long
as the remaining balance was not zero.
Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
Adding rows for "empty" months was also fairly easy, I did a outer join on a
table of dates (is there an easier way to do this?).
What's got me stumped is the "everything up to now", and I think I might be
doing it entirely the wrong way. Normally I would make two subqueries, A and
B, which are the month-by-month rollups. I then have a WHERE that joins them
B.trandate <= A.trandate. But since the data has been grouped by month, the
actual date is no longer available (it's been "grouped out" of the results).
So I'm trying to come up with a way to compare these.
1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
work, because if you have something like 6/2005 it will consider that to fail
against the date 7/2004, because 7 > 6. Is there a way to fix this?
2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
comparing those, but SQL Server uses months with no leading zero, so you end
up with 200412 being smaller than 20043, and if you cast them to numbers then
200412 is bigger than 20053. Is there some way to make this work?!
3) maybe I'm doing it all wrong! Is there some way I could get the raw data
without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
that would help here) and then do the grouping? It seems this should work,
but when I try it it seems to be difficult to fold the outer join back in, it
needs to refer to data in the A subquery, and if there is a way to do this I
can't figure out the syntax (at least not in an outer join, maybe a WHERE is
the way to go?)
MauryMaury Markowitz wrote:
> I'm trying to build a report that shows any user's purchases per month, along
> with the resulting balance. That is proving tricky, and to add to the
> confusion I have to add rows even where there was no purchases/sales, as long
> as the remaining balance was not zero.
> Doing the monthly rollup is easy enough with a group by on MONTH(trandate).
> Adding rows for "empty" months was also fairly easy, I did a outer join on a
> table of dates (is there an easier way to do this?).
> What's got me stumped is the "everything up to now", and I think I might be
> doing it entirely the wrong way. Normally I would make two subqueries, A and
> B, which are the month-by-month rollups. I then have a WHERE that joins them
> B.trandate <= A.trandate. But since the data has been grouped by month, the
> actual date is no longer available (it's been "grouped out" of the results).
> So I'm trying to come up with a way to compare these.
> 1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
> work, because if you have something like 6/2005 it will consider that to fail
> against the date 7/2004, because 7 > 6. Is there a way to fix this?
> 2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
> comparing those, but SQL Server uses months with no leading zero, so you end
> up with 200412 being smaller than 20043, and if you cast them to numbers then
> 200412 is bigger than 20053. Is there some way to make this work?!
> 3) maybe I'm doing it all wrong! Is there some way I could get the raw data
> without grouping, join on the original dates (I have a dbo.FirstDayOfMonth
> that would help here) and then do the grouping? It seems this should work,
> but when I try it it seems to be difficult to fold the outer join back in, it
> needs to refer to data in the A subquery, and if there is a way to do this I
> can't figure out the syntax (at least not in an outer join, maybe a WHERE is
> the way to go?)
> Maury
Take a look at CUBE / ROLLUP in Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 5 Sep 2006 12:31:02 -0700, Maury Markowitz wrote:
(snip)
>What's got me stumped is the "everything up to now", and I think I might be
>doing it entirely the wrong way. Normally I would make two subqueries, A and
>B, which are the month-by-month rollups. I then have a WHERE that joins them
>B.trandate <= A.trandate. But since the data has been grouped by month, the
>actual date is no longer available (it's been "grouped out" of the results).
>So I'm trying to come up with a way to compare these.
>1) MONTH(B.date) < MONTH(A.data) AND YEAR(B.date) < YEAR(A.data) does not
>work, because if you have something like 6/2005 it will consider that to fail
>against the date 7/2004, because 7 > 6. Is there a way to fix this?
>2) I tried making up a "fake date" with YEAR(date) + MONTH(date) and
>comparing those, but SQL Server uses months with no leading zero, so you end
>up with 200412 being smaller than 20043, and if you cast them to numbers then
>200412 is bigger than 20053. Is there some way to make this work?!
Hi Maury,
I suppose your current queries do the grouping by month with something
like this:
GROUP BY YEAR(TheDate), MONTH(TheDate)
Right?
If you change it to
GROUP BY DATEDIFF(month, '19000101', TheDate)
you can easily compare dates
WHERE DATEDIFF(month, '19000101', Date1) >
DATEDIFF(month, '19000101', Date2)
or get back to datetime format (at first day of the month):
SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> If you change it to
> GROUP BY DATEDIFF(month, '19000101', TheDate)
> you can easily compare dates
> WHERE DATEDIFF(month, '19000101', Date1) >
> DATEDIFF(month, '19000101', Date2)
> or get back to datetime format (at first day of the month):
> SELECT DATEADD(month, DATEDIFF(month, '19000101', Date1), '19000101')
Thanks, I'll try that!
Maury

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
Mark
SQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>
|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script thi
s?
> Thanks in advance.
> Mark
>
You may want to look at
[url]http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx[/u
rl]
John