More problems with updateText

Hi,

Basically I am trying to add 2 ntext fields together sandwhiched by a literal ( '<BR /><BR />' ) in a SP for a report I will be running.

First step is to add <BR /><BR />, which I have done with some help from this forum, using a cursor and temp tables.

The last step is add the second ntext column (if it exists for the case only though). So like the 1st step I am using cursor and updatetext to amend the temporary table. The problem is that rather than update the temp table, the query section I have highlighted is run and nothing at all gets added to temptable. Since the column I am trying to add is a ntext I can't create a local variable. Does anyone know where I am going wrong?

Thanks in advance

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN

UPDATETEXT #tempreport.Status @.value2 null 0
(select IsNull(thevalue,'')
from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8)

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

For those interested, I managed to crack this by using another text pointer:

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN
declare @.value3 varbinary(16)
select @.value3 = textptr(thevalue) from tbl_memo m where caseid = @.currentcase and memotypeid = 8

UPDATETEXT #tempreport.Status @.value2 null 0 tbl_memo.thevalue @.value3

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2
END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

More complicated use of aggregates...

Greetings all!
I am stuck with something at the moment, and that added to the cold thats got me in its icy grip is really batting my head :(
Basically I have a table of records:
Record_ID (id) | Employee_ID (pin) | Record_type (msgtype) | record_date | record_time (logtime)
What I am looking for is, for each employee_ID on a specific day, the Record_ID of the earliest record of type '5'. I've only got one date in there at the moment so thats not such a problem. My attempt was:

SELECT combined.ID
FROM combined
GROUP BY combined.pin
HAVING min(logtime);
but that didnt work, complaining that combined.id is not in the group by.

Can anyone provide any suggestions?

Thanks!
~Shiv

EDIT: Forgot to mention, I'm using MS Access...Perhaps something like this (or its variations - I'd say that interesting part is the use of a subquery):select c.employee_id, c.record_id
from combined c
where c.logtime = (select min(c1.logtime)
from combined c1
where c1.employee_id = c.employee_id
)
and c.record_type = 'S'
group by c.employee_id, c.record_id;|||Cool, I'm giving that a go so I'll let you know how it works out. It's gotta go through about just under a million records, so its taking a while!!!
~T

More browser support for Safari and Firefox?

When I try to run a simple table report using Safari or Firefox the experience is less than stellar.

Using Safari the report never returns, basically the pag body is blank.

Using Firefox the table layout is very compressed.

Anything changes in the works to allow the reporting server to be used by non IE browser.

We run in a multi-platform environment and need the multiple browser support.

Thanks.

--sean

SQL Server 2005 Reporting Services

Hi sean

try adding this into ReportingServices.css file ( in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles)

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}

After adding this

Some changes has to be done in report as well

Add the table into Rectangle

Deploy the report.

Hope this solves the problem..

safari browser Not sure even i am facing lots of problem

if u find any solution please let me know

More browser support for Safari and Firefox?

When I try to run a simple table report using Safari or Firefox the experience is less than stellar.

Using Safari the report never returns, basically the pag body is blank.

Using Firefox the table layout is very compressed.

Anything changes in the works to allow the reporting server to be used by non IE browser.

We run in a multi-platform environment and need the multiple browser support.

Thanks.

--sean

SQL Server 2005 Reporting Services

Hi sean

try adding this into ReportingServices.css file ( in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\Styles)

/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}

After adding this

Some changes has to be done in report as well

Add the table into Rectangle

Deploy the report.

Hope this solves the problem..

safari browser Not sure even i am facing lots of problem

if u find any solution please let me know

Months and Years between Dates

Hi All,
I am stuck with a Date problem which I am trying to execute in a Stored Proc.

I basically get a Start Month and a Start Year And an End month & an End Year from a screen that I build.
Now what I want to do is to traverse all the Months_Year for this period.

ie For example if my start year is Feb 2001 and end Year is July 2004, then I need
Feb 2001
March 2001
April 2001
....
Jan 2004
..
July 2004 in a cursor.

Thanks in anticipation.
Raman.Hmmm what database are you using Oracle or Sybase ?
if its Sybase you can use the datediff to get your dates
if in Oracle you can use the to_date, to_char to manipulate the given dates and return what you want.

Originally posted by ramanjaiya
Hi All,
I am stuck with a Date problem which I am trying to execute in a Stored Proc.

I basically get a Start Month and a Start Year And an End month & an End Year from a screen that I build.
Now what I want to do is to traverse all the Months_Year for this period.

ie For example if my start year is Feb 2001 and end Year is July 2004, then I need
Feb 2001
March 2001
April 2001
....
Jan 2004
..
July 2004 in a cursor.

Thanks in anticipation.
Raman.|||Originally posted by llccoo
Hmmm what database are you using Oracle or Sybase ?
if its Sybase you can use the datediff to get your dates
if in Oracle you can use the to_date, to_char to manipulate the given dates and return what you want.

I am using SQL Server.
Basically What I amtrying now is to create 2 Temp tables. One with all the months, and one with all the years & then looping twice to create my combinations.
I feel it could be done in a better way although!|||please see the articles The integers table (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid569539_tax285649,00.html) and Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) (registration may be required, but it's free)

the examples show how to generate a series of dates using an integers table

applied to your example, you would use the integers within a DATEADD() function using the integer as the number of months to add from a starting date

monitoring servers

Is there a way (script, tool, whatever) that I can monitor sql servers
and services on multiple different servers. Basically I want on screen
that can display that status of many sql servers and services on
multiple servers.
Thanks !bringmewater@.gmail.com a crit:

> Is there a way (script, tool, whatever) that I can monitor sql servers
> and services on multiple different servers. Basically I want on screen
> that can display that status of many sql servers and services on
> multiple servers.
>
Hello,
I've used Nagios for this purpose. You can have a look at
http://www.babaluga.org/doku.php/sq...r/outils/nagios for some notes
I've taken about how to do it.
Rudi Bruchez, MCDBA
http://www.babaluga.com/|||excellent! thanks
Rudi Bruchez wrote:
> bringmewater@.gmail.com a =E9crit:
>
> Hello,
> I've used Nagios for this purpose. You can have a look at
> http://www.babaluga.org/doku.php/sq...r/outils/nagios for some notes
> I've taken about how to do it.
>=20
> --=20
> Rudi Bruchez, MCDBA
> http://www.babaluga.com/