Distinguish changes by replication and changes by user

Hello,
Merge Replication, SQL Server 2005
I have got a table with an update trigger that is supposed to fire when a
user updates a field.
When replicating this database, the trigger fires when a user updates a
field *and* when the update is performed by the replication process.
How can I avoid the latter? Is there a TSQL-function to distinguish the two?
Thanks for your effort, Wolfgang
Another way is to hack into the sessionproperty
ie if ('replication_agent') <> 1 then its a user action.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wolfgang" <Wolfgang@.discussions.microsoft.com> wrote in message
news:168538E7-89A2-45F3-ABD6-566F2C4B3760@.microsoft.com...
> Hello,
> Merge Replication, SQL Server 2005
> I have got a table with an update trigger that is supposed to fire when a
> user updates a field.
> When replicating this database, the trigger fires when a user updates a
> field *and* when the update is performed by the replication process.
> How can I avoid the latter? Is there a TSQL-function to distinguish the
> two?
> Thanks for your effort, Wolfgang
sql

DISTINCT MonthName for a lot of dates....

Hi all,
I have a table with several rows, each has a datetime field.
I want to query this table, ideally with my stored procedure and return just
a set of month names/numbers if possible, but I keep going around in circles
either getting ALL of my dates back with the names in a new column, or only
the month names, but order incorrectly...
table structure:
PregnancyLog
LogID int
LogDateTime datetime
sample data
LogID, LogDateTime
1,29/01/05
2,30/01/05
3,01/02/05
4,03/02/05
5,04/02/05
6,11/03/05
7,12/03/05
8,23/04/05
9,12/08/05
Expected results
MonthName, MonthNumber
January, 1
February, 2
March, 3
April, 4
August, 8
Any help would be appreciated - my only current resolution would be to
create a view of my data which gets me the month names, and then do a
distinct on that with the stored procedure, but I'd rather just do it once
in the stored procedure if possible.
Regards
Rob"Rob Meade" wrote ...

> Any help would be appreciated
I hate it when this happens...looks like I might have sussed it myself...
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
Does that look acceptable to anyone? It gives me the results I wanted but I
just wanted to make sure..
Regards
Rob|||On Thu, 24 Nov 2005 23:20:43 GMT, Rob Meade wrote:

>"Rob Meade" wrote ...
>
>I hate it when this happens...looks like I might have sussed it myself...
>SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
>FROM PregnancyLog
>GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
>ORDER BY MONTH(LogDateTime)
>Does that look acceptable to anyone? It gives me the results I wanted but
I
>just wanted to make sure..
>Regards
>Rob
>
Hi Rob,
Looks good.
Here's an (untested) alternative:
SELECT DISTINCT DATENAME(month, LogDateTime) AS MonthName,
MONTH(LogDateTime)
FROM PregnancyLog
ORDER BY MONTH(LogDateTime)
Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
I'm not sure of that.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> Looks good.
Thank you :o)

> Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
> I'm not sure of that.
Cheers for that Hugo, it worked a treat, I left the MONTH(LogDateTime) in,
and added an alias of MonthNumber as I use this in the application.
But its still less code than I had - many thanks :o)
Regards
Rob|||Hi Hugo,
Any ideas how I would add a "count" to the end of the result set of the
number of log items for each month returned by the existin query...
Ie...
MonthName MonthNumber Counter
January 1 2
February 2 6
March 3 15
Any help would be really appreciated, I've tried adding COUNT(LogID) to my
query, but then I get message telling me that things need adding to the
aggregate function or the group by clause, which I did try adding again but
then I have to lose the order by or else I get EVERY row
again...nightmare..
Any help appreciated.
Regards
Rob|||On Fri, 25 Nov 2005 23:17:45 GMT, Rob Meade wrote:

>Hi Hugo,
>Any ideas how I would add a "count" to the end of the result set of the
>number of log items for each month returned by the existin query...
>Ie...
>MonthName MonthNumber Counter
>January 1 2
>February 2 6
>March 3 15
>Any help would be really appreciated, I've tried adding COUNT(LogID) to my
>query, but then I get message telling me that things need adding to the
>aggregate function or the group by clause, which I did try adding again but
>then I have to lose the order by or else I get EVERY row
>again...nightmare..
>Any help appreciated.
>Regards
>Rob
>
Hi Rob,
If you need to add a count (or any other aggregate function), then you
can't use my shorter version; you'll have to return to your original
version with GROUP BY.
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
COUNT(LogID) AS Counter
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
should work. If not, you'll need to provide more information, as
described in www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
> COUNT(LogID) AS Counter
> FROM PregnancyLog
> GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
> ORDER BY MONTH(LogDateTime)
> should work. If not, you'll need to provide more information, as
> described in www.aspfaq.com/5006.
Hi Hugo,
Worked a treat, many thanks - I thought I tried exactly that, but obviously
not, when I tried it, SQL moaned that I needed to add LogDateTime to the
GROUP BY...
Typical that I'd only just posted to see if I could get a few others to look
in this thread from yesterday as I wasn't sure if you'd return to this
message - and you've already solved it - lol - I'll get flamed now for
posting needlessly...hehe..sorry all :o)
Thanks muchly for the help - the website I'm creating is all about my new
born son, so its kinda important to me - thus appreciate the help even more
than usual :o)
Regards
Rob|||On Fri, 25 Nov 2005 23:31:09 GMT, Rob Meade wrote:
(snip)
> I'll get flamed now for
>posting needlessly...hehe..sorry all :o)
Hi Rob,
If you insist, I think I can arragne you being flamed. Do you want me to
call Celko over? ;->
Congratulations on your boy. Don't spend all your time building the
website - spend plenty time enjoying him. They grow up so fast.....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Distinct email addresses in 2 tables with different field names

Hi everyone,
I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).
Thank you
Maz.Hi Maz,
Please don't post questions independently in multiple newsgroups. You
question has already been answered in .programming.
--
Jacco Schalkwijk
SQL Server MVP
"Maziar Aflatoun" <maz88@.rogers.com> wrote in message
news:STSEb.32573$2We1.12257@.news04.bloor.is.net.cable.rogers.com...
> Hi everyone,
> I have 2 tables with table A containing an 'email' field and table B
> containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it
possible
> to issue a query that would return only the unqiue email addresses in
these
> 3 fields? So a long list with no duplicate emails(distinct).
> Thank you
> Maz.
>
>

Distinct Count on joined tables

Hi,

I have two tables Contact1 and Contsupp. Both have a field Accountno which will be in Table A once and Table B at least once (if at all). I'm trying to perform a query which returns all the matches of Accountno (subject to a where clause) BUT to return matches once. ie distinctly. This works fine when I return only the Accountno column, however when I return more columnns, duplication occurs.

SELECT DISTINCT
CONTACT1.ACCOUNTNO, CONTACT1.COMPANY, CONTSUPP.ACCOUNTNO AS Expr1, CONTSUPP.RECID, CONTSUPP.LINKACCT, CONTSUPP.COUNTRY, CONTSUPP.ZIP, CONTACT1.KEY3, CONTACT1.CONTACT, CONTACT1.CREATEON

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTACT1.ACCOUNTNO <> 'PTG')

ORDER BY CONTACT1.ACCOUNTNO

So this gives me dups.

Whereas this:

SELECT DISTINCT
CONTACT1.ACCOUNTNO

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTSUPP.CONTSUPREF <> 'PTG')

GROUP BY
CONTACT1.ACCOUNTNO

I believe works. Any ideas??

Thanks,
JamesProblem is values in additional columns

CONTACT1.COMPANY, CONTSUPP.ACCOUNTNO AS Expr1, CONTSUPP.RECID, CONTSUPP.LINKACCT, CONTSUPP.COUNTRY, CONTSUPP.ZIP, CONTACT1.KEY3, CONTACT1.CONTACT, CONTACT1.CREATEON

are different for the same ACCOUNTNO

example: you have

CONTACT1.ACCOUNTNO CONTACT1.KEY3
123 'Z'
123 'C'

so when you select distinct just ACCOUNTNO result IS
123
but select distinct CONTACT1.ACCOUNTNO CONTACT1.KEY3
result is
123 Z
123 C

Solution: you have to decide which KEY3 you select if you wanna have unique ACCOUNTNO. For this use group by:

select CONTACT1.ACCOUNTNO, max(CONTACT1.KEY3)
from ...
group by
CONTACT1.ACCOUNTNO

result

123 Z

so your code could be:

SELECT
CONTACT1.ACCOUNTNO, max(CONTACT1.COMPANY), max(CONTSUPP.ACCOUNTNO) AS Expr1, max(CONTSUPP.RECID), max(CONTSUPP.LINKACCT), max(CONTSUPP.COUNTRY), max(CONTSUPP.ZIP), max(CONTACT1.KEY3), max(CONTACT1.CONTACT), max(CONTACT1.CREATEON)

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTACT1.ACCOUNTNO <> 'PTG')

GROUP BY CONTACT1.ACCOUNTNO

ORDER BY CONTACT1.ACCOUNTNO|||Brilliant, thanks for that madafaka, works a treat.

Kind regards,
James|||madafaka, on closer examination, the query does supress the duplication of accountno however, it still returns the ACCOUNTNO from CONTSUPP even if the 'PTG' exists in CONTSUPREF for the same ACCOUNTNO.

I've trimmed my query down to:

SELECT
CONTACT1.ACCOUNTNO, MAX(CONTACT1.COMPANY) AS Expr1, MAX(CONTSUPP.ACCOUNTNO) AS Expr2, MAX(CONTACT1.CONTACT) AS Expr3, MAX(CONTSUPP.CONTSUPREF) AS Expr4

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTSUPP.CONTSUPREF <> 'PTG')

GROUP BY CONTACT1.ACCOUNTNO

ORDER BY CONTACT1.ACCOUNTNO

CONTACT1 will only ever contain ACCOUNTNO once, CONTSUPP on the other hand may have multiple instances of the same ACCOUNTNO. If CONTSUPREF contains 'PTG' in CONTSUPP for a particular ACCOUNTNO, I want the query to suppress the ACCOUNTNO altogether, but ONLY if 'PTG' is in CONTSUPP for that particular ACCOUNTNO.

In the attached example, I would want examples 2 and 3 to show but not example 1.

I hope that makes sense.

Thanks,
James|||Code Monkey 77,
to be honest I'm not 100% clear with your requirements.

If there's an ACCOUNTNO in CONTSUPP where CONTSUPREF = 'PTG'

1) you don't want to select ACCOUNTNO at all (even from CONTACT1)?

2) you want to select ACCOUNTNO from CONTACT1 but do not join it with record in CONTSUPP where CONTSUPREF = 'PTG'. basically skip records in CONTSUPP where CONTSUPREF = 'PTG'?

For the first case there are more options how to realise it:

SELECT
CONTACT1.ACCOUNTNO,
MAX(CONTACT1.COMPANY) AS Expr1,
MAX(CONTSUPP.ACCOUNTNO) AS Expr2,
MAX(CONTACT1.CONTACT) AS Expr3,
MAX(CONTSUPP.CONTSUPREF) AS Expr4
FROM
CONTACT1
LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE (CONTACT1.KEY3 LIKE 'APO%')
AND CONTACT1.ACCOUNTNO NOT IN (SELECT ACCOUNTNO
FROM CONTSUPP
WHERE CONTSUPREF = 'PTG'
)
GROUP BY CONTACT1.ACCOUNTNO
ORDER BY CONTACT1.ACCOUNTNO

for the second case try:

SELECT
CONTACT1.ACCOUNTNO,
MAX(CONTACT1.COMPANY) AS Expr1,
MAX(CONTSUPP.ACCOUNTNO) AS Expr2,
MAX(CONTACT1.CONTACT) AS Expr3,
MAX(CONTSUPP.CONTSUPREF) AS Expr4
FROM
CONTACT1
LEFT OUTER JOIN (SELECT ACCOUNTNO, CONTSUPREF FROM CONTSUPP WHERE CONTSUPREF <> 'PTG') CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE (CONTACT1.KEY3 LIKE 'APO%')
GROUP BY CONTACT1.ACCOUNTNO
ORDER BY CONTACT1.ACCOUNTNO

I don't know what DB do you use, so hopefully there won't be an issue with syntax.|||Thanks for your help Madafka, the first statement is the one that works. Apologies if my request was a little unclear.

Best regards,
James

Displying rtf database field properly formatted

I am a simple user- I have a SQL Server database that stores a particular field as rtf, including all of the formatting characters in addition to the actual user-entered text.

I use the report wizard to easily build reports, but this particular field outputs all of the gobbldygook but I just want the user-entered text.

Crystal Reports easily translates rtf- why can't Microsoft easily translate their own format? Any help would be appreciated.

Hi kc,

This is a feature we are looking at for later releases. We want something like this too.

sql

Displayong "Empty string" to a textbox on the report

Hi All!

I was checking the value of a field and if it is empty sending empty string to the textbox if not only the first few values and it is working but on the empty field something like "#Error" is being displayed.

here is the code:

=Iif(Fields!Lname.Value <>””, Fields!Lname.Value.ToString().Substring(0,10),"")

What I want to acheve is : If it is not zero to take the first 10 characters and if not to send an epmity string to the textbox.

Any help plz?

Thank you in advance!

In your expression, you are making the assumption that the string will be at least 10 characters. If it isn't 10 characters you will get an error. I am not sure what you are trying to accomplish but see the expression below. It will truncate the field if it is over 10 characters.

=Iif(Fields!Lname.Value.ToString().Length() > 10, Fields!Lname.Value.ToString().Substring(0,10), Fields!Lname.Value)

|||

here is the code:

=Iif(Fields!Lname.Value <>””, Fields!Lname.Value.ToString().Substring(0,10),"")

What I want to acheve is : If it is not zero to take the first 10 characters and if not to send an epmity string to the textbox

Thank you.The one that you send to me is not doing what i was looking for. Thank you very much.

|||

My expression does the exact same thing as yours except when there are less than 10 characters it will not attempt to truncate.

Input and output for my expression:

"" -> "" "foo" -> "foo" "bar" -> "bar" "SomeReallyLongString" -> "SomeReally"

Input and output for your expression

"" -> "" "foo" -> "#Error" "bar" -> "#Error" "SomeReallyLongString" -> "SomeReally"

|||

If I am getting it right, the problem I think is that when that field is empty or NULL, it returns an error:

So in your code actually, the first line for input and output would be:

"" --> #Error

I dunno how to resolve this in RS as I tried various things and they didn't work (like length = 0 etc.), only thing I can think of for now is to modify your query itself to return the substring instead of the field and then use this new field..

e,g,

select .....,..,.., substring(ISNULL(OldFieldName,''), 0, 10) as NewFieldName
from TableName

Ryan Ackley MSFT wrote:

My expression does the exact same thing as yours except when there are less than 10 characters it will not attempt to truncate.

Input and output for my expression:

"" -> "" "foo" -> "foo" "bar" -> "bar" "SomeReallyLongString" -> "SomeReally"

Input and output for your expression

"" -> "" "foo" -> "#Error" "bar" -> "#Error" "SomeReallyLongString" -> "SomeReally"

|||

Thank you very much. This is exactly what the problem that I am facing now let me try to see some other things and I will do as you suggest. Thank you.If you find anything new plz let me know.

Ephi

Displaying Updated Columns

I want to use a gridview in my asp.net app to just show changes to one field in an employee table. I have a history table trigger on all fields already for other reasons, however for this purpose i want to be able to show the user what the column said before and after the change.

I read up on the columns_updated function and it seems like it could work however It seems there would be an easier way than figuring out what the bitmask is on my 14 column table for a change on the 3rd column?

Any ideas.Okay I think I already solved this using a stored procedure joining my employee table and employee history table.sql

Displaying RTF fields in a report

Hi,

I would appreciate it anyone could help. I've saved RTF formatted data to a text field in the sql server db. I want to display this formatted text in a report with. Any ideas?

shot

I have the same problem, but I think I'm reallllly close to the solution.

I'm rendering the RTF to an image file and trying to load it into an image control via a referenced class. I have the class returning either an image or a byte array. Reporting services image controls apparently only work with a byte array, but as of yet I can't get it to work.

Using the same assembly reference I am able to view the resulting image with the pictureBox control, and the byte array appears to be populated as intended, it just won't load up in reporting services.

I'll attach my code thus far in hopes that you'll be able to get further than I have (if you do, be sure to post the fix).

I'm setting the image source to database, the mime type to image\bmp and value to "=Code.GetImage()". Oh, and don't forget to add the dll to the assembly cache when you add the reference to the assembly dll in reporting services, it doesn't seem to play well otherwise.

In the code section I've added the following function:

Function GetImage() As Byte()
objRTFImage.Rtf = "This is a test"
return objRTFImage.PrintToByteArray(200, 100) ' Syntax PrintToByteArray(int32 width, int32 height)
End Function

The class code in my assembly is posted below.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Runtime.InteropServices;

using System.Drawing.Imaging;

using System.IO;

namespace RichTextRenderer

{

public partial class RTFImage : RichTextBox

{

public RTFImage()

{

}

protected override void OnPaint(PaintEventArgs pe)

{

// Calling the base class OnPaint

base.OnPaint(pe);

}

//Convert the unit used by the .NET framework (1/100 inch)

//and the unit used by Win32 API calls (twips 1/1440 inch)

private const double anInch = 14.4;

[StructLayout(LayoutKind.Sequential)]

private struct RECT

{

public int Left;

public int Top;

public int Right;

public int Bottom;

}

[StructLayout(LayoutKind.Sequential)]

private struct CHARRANGE

{

public int cpMin; //First character of range (0 for start of doc)

public int cpMax; //Last character of range (-1 for end of doc)

}

[StructLayout(LayoutKind.Sequential)]

private struct FORMATRANGE

{

public IntPtr hdc; //Actual DC to draw on

public IntPtr hdcTarget; //Target DC for determining text formatting

public RECT rc; //Region of the DC to draw to (in twips)

public RECT rcPage; //Region of the whole DC (page size) (in twips)

public CHARRANGE chrg; //Range of text to draw (see earlier declaration)

}

private const int WM_USER = 0x0400;

private const int EM_FORMATRANGE = WM_USER + 57;

[DllImport("USER32.dll")]

private static extern IntPtr SendMessage(IntPtr hWnd, int msg, IntPtr wp, IntPtr lp);

// Render the contents of the RichTextBox for printing

// Return the last character printed + 1 (printing start from this point for next page)

public Image PrintToImage(String plainText, Int32 width, Int32 height)

{

this.Text = plainText;

return PrintToImage( width, height);

}

public Image PrintToImage(Int32 width, Int32 height)

{

Image image = new Bitmap(width, height);

Graphics g = Graphics.FromImage(image);

Int32 retVal = this.Print(0, this.Text.Length, g, new Rectangle(this.Location, new Size(width, height)));

return image;

}

public byte[] PrintToByteArray(String plainText, Int32 width, Int32 height)

{

return ConvertImageToByteArray(PrintToImage(plainText, width, height));

}

public byte[] PrintToByteArray(Int32 width, Int32 height)

{

return ConvertImageToByteArray(PrintToImage(width, height));

}

public int Print(int charFrom, int charTo, Graphics gr, Rectangle bounds)

{

//Calculate the area to render and print

RECT rectToPrint;

rectToPrint.Top = 0;// (int)(bounds.Top * anInch);

rectToPrint.Bottom = (int)(bounds.Height * anInch);// (int)(bounds.Bottom * anInch);

rectToPrint.Left = 0;// (int)(bounds.Left * anInch);

rectToPrint.Right = (int)(bounds.Width * anInch);// (int)(bounds.Right * anInch);

//Calculate the size of the page

RECT rectPage;

rectPage.Top = 0;//(int)(bounds.Top * anInch);

rectPage.Bottom = (int)(gr.ClipBounds.Height * anInch);//(int)(bounds.Bottom * anInch);

rectPage.Left = 0;//(int)(bounds.Left * anInch);

rectPage.Right = (int)(gr.ClipBounds.Right * anInch);//(int)(bounds.Right * anInch);

IntPtr hdc = gr.GetHdc();

FORMATRANGE fmtRange;

fmtRange.chrg.cpMax = charTo; //Indicate character from to character to

fmtRange.chrg.cpMin = charFrom;

fmtRange.hdc = hdc; //Use the same DC for measuring and rendering

fmtRange.hdcTarget = hdc; //Point at printer hDC

fmtRange.rc = rectToPrint; //Indicate the area on page to print

fmtRange.rcPage = rectPage; //Indicate size of page

IntPtr res = IntPtr.Zero;

IntPtr wparam = IntPtr.Zero;

wparam = new IntPtr(1);

//Get the pointer to the FORMATRANGE structure in memory

IntPtr lparam = IntPtr.Zero;

lparam = Marshal.AllocCoTaskMem(Marshal.SizeOf(fmtRange));

Marshal.StructureToPtr(fmtRange, lparam, false);

//Send the rendered data for printing

res = SendMessage(Handle, EM_FORMATRANGE, wparam, lparam);

//Free the block of memory allocated

Marshal.FreeCoTaskMem(lparam);

//Release the device context handle obtained by a previous call

gr.ReleaseHdc(hdc);

//Return last + 1 character printer

return res.ToInt32();

}

public static byte[] ConvertImageToByteArray(System.Drawing.Image imageToConvert)

{

byte[] Ret;

//try

//{

using (System.IO.MemoryStream ms = new MemoryStream())

{

imageToConvert.Save(ms,ImageFormat.Bmp);

ms.Position = 0;

Ret = ms.ToArray();

}

//}

//catch (Exception) { throw; }

return Ret;

}

}

}

|||

Hi

Thanks so much for your response, i luckily have the option of using Crystal....which supports RTF fields apparently - and which i'll def be using!!

Good luck with the RTF prob, i might pursue it in my personal time - please let me know if/when you have a solution and how you solved it.

thanks

displaying null dates

how to display NA in a date field if the date is NULL? Please Help.

select case when <Col Name> is null then 'NA'

else cast(<Col Name> as varchar(12))end <Col Name>

from <Table Name>

Mat

|||

You can use the following query..

Select Isnull(Convert(Varchar,DateColumnName,101),'NA') From YourTable

Here the date value will be converted as US format..

If you need both time & date use the following query..

Select Isnull(Convert(Varchar,DateColumnName,101) + ' ' + Convert(Varchar,DateColumnName,114),'NA') From YourTable

You can find all the date format convertion on Books Online under the title CAST and CONVERT

displaying non-duplicate keys of all duplicate entries

Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

SELECT
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
FROM
TABLE
GROUP BY
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
HAVING
COUNT(*) > 1

Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

Thanks,
DeanSure, use:SELECT
A."ID", A."FIRST", A."MIDDLE", A."LAST"
FROM TABLE AS A
WHERE 1 < (SELECT Count(*)
FROM TABLE AS B
WHERE B."FIRST" = A."FIRST"
AND B."LAST" = A."LAST"
AND B."MIDDLE" = A."MIDDLE")-PatP|||Thanks, that worked well, although it takes a good while for the server to process the query.|||Indicies would help this query a lot, particularly an index on last, first, middle.

-PatP

displaying more then one record from the same table in the same field

I was wondering if there was any way to make crystal reports grab multiple records from the same table and displahy them in one database field. Basicly I have a table that users would type info into each line of text that they type would equal one record on the table. When I run the report I need to display all the lines of text that they typed in. If I just refrence the table in the database field in crystal it will only pull the first record from the table and thats it.

Here is an example of the table
00000101 0001 This is a tes of the emergency broadcast system, if there was an actual emergency you would hear
00000101 0002 about it after the tones.This is a tes of the emergency broadcast system, if there was an actual
00000101 0003 emergency you would here about it after the tones.This is a tes of the emergency broadcast system

Now if I run the report the only thing it will dislay is the first line (record number "0001")

Thanks for any helpAdd a Textbox to your report. Then drag the first DB Field into the textbox, then drag the second DB Field into the same textbox.

I attached a picture of an Address field from one of my reports. It's a textbox and I dropped the 5 DB fields onto it (Name, Address, City, State, Zip)

displaying milliseconds in VB 6 program

Hi,
I am trying to get my VB program to recognize milliseconds when it is
returned as a datetime field from sql. I can format it to the seconds but
can't find out to include the milliseconds. Any help would be appreciated.
EllieReturn a CHAR/VARCHAR from SQL Server (using CONVERT()) and explicitly cast
the result as a string in VB (CStr() I guess). You could also write a
simple date formatting function in VB, but if you're getting short-circuited
by VB somehow and it trims them off, only if you return the milliseconds
separately. Some of these articles might be helpful:
http://www.aspfaq.com/2460
http://www.aspfaq.com/2313
http://www.aspfaq.com/2093
"Ellie" <nospam@.nospam.net> wrote in message
news:O88yFBXkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am trying to get my VB program to recognize milliseconds when it is
> returned as a datetime field from sql. I can format it to the seconds but
> can't find out to include the milliseconds. Any help would be appreciated.
> Ellie
>

displaying images horizontally - not vertically

I am trying to display image data stored in a database field using SRS 2005.
The image data is employee photos and I can get them to display in one column
straight down the report using a List control.
Is there a way to get them to display in 3 columns sorting across the page?
This would be like a High School yearbook layout...
Like this...
Pic 1 Pic 2 Pic 3
Pic 4 Pic 5 Pic 6
Instead of like this...
Pic 1
Pic 2
Pic 3
Pic 4Hi,
I'm not sure if this is done differently in SRS 2005 but in SRS 2000, you
can set the Columns property on the Report Page. Just click the page and
then in the Properties window, specify the number of columns needed (3 in
your case).
Hope that helps.
Assad
"jj#10" wrote:
> I am trying to display image data stored in a database field using SRS 2005.
> The image data is employee photos and I can get them to display in one column
> straight down the report using a List control.
> Is there a way to get them to display in 3 columns sorting across the page?
> This would be like a High School yearbook layout...
> Like this...
> Pic 1 Pic 2 Pic 3
> Pic 4 Pic 5 Pic 6
> Instead of like this...
> Pic 1
> Pic 2
> Pic 3
> Pic 4
>

Displaying image from db in crystal report

hello there

I am using VB.Net 2003 with oracle 9i. My table contains a BLOB field that contains images. I tried to create a report (crystal report that is packed with VB.Net 2003) by including the table and dragging the BLOB field onto the report. But when i try to run the report, it gives me "Failed to open a rowset" error. If i run the report without the BLOB field, the report works fine.

Any idea?!?!

Thanks in advance
AndyHow did you store the images in the table?
Sounds that some images are corrupted
Visist this site and see if you find solution
www.businessobjects.com

Displaying HTML as text

I am trying to display a field in a report that sometimes contains html
stored as text. I want to be able to strip out the html tags so I can display
the text only in the report.To strip off the html I believe there is a framework function that you could
use. Set the value of the textbox to an expression like this:
= Code.StripHTML(Fields!Fieldname.value)
You would write the function StripHTML that would return the value with the
html stripped.
This link shows how to do this using regular expressions.
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
There is definitely a dotnet function that you could use too. Google on
stripping html and you should be able to find it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
>I am trying to display a field in a report that sometimes contains html
> stored as text. I want to be able to strip out the html tags so I can
> display
> the text only in the report.
>|||Thanks for the info Bruce. I am not a programmer so forgive for my questions,
how do you embed custom code into a sql report?
"Bruce L-C [MVP]" wrote:
> To strip off the html I believe there is a framework function that you could
> use. Set the value of the textbox to an expression like this:
> = Code.StripHTML(Fields!Fieldname.value)
> You would write the function StripHTML that would return the value with the
> html stripped.
> This link shows how to do this using regular expressions.
> http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
> There is definitely a dotnet function that you could use too. Google on
> stripping html and you should be able to find it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael" <Michael@.discussions.microsoft.com> wrote in message
> news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
> >I am trying to display a field in a report that sometimes contains html
> > stored as text. I want to be able to strip out the html tags so I can
> > display
> > the text only in the report.
> >
>
>

Displaying Fields from Multiple Datasets (RS2000)

Hi guys, I have this problem of displaying fields from multiple datasets.

When I drag a field from the first dataset into a table, it works and displayed correctly. However when I dragged a field from a second dataset, it will be shown as

=First(Fields!FirstName.Value, "DataSet2") for Strings and

=Sum(Fields!StatusFlag.Value, "DataSet2") for Integers

I just want the integer from that single row but it sums up all the rows. When I removed the SUM keyword, the IDE will show build errors when I try to build the report. Is there any way to get around this? thanks!

Use the First keyword for integers as well.|||According to the data types, the IDE will always choose SUM as the aggregate for numeric values and FIRST as the aggregate for non-Integers. As Brad mentioned you can changed that, this is just a suggestion of the Designer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Displaying different text

I want text displayed as: some text here Somename another

text here. How to do that if Somename is a value of a field and it's

width changes? As it can't be done with a single textbox, can it be

done with a table? Can a table have dynamical width?The only dynamic sizing property in reporting services is the "CanGrow" property. However, this only applies to the height of objects. The width cannot be dynamically adjusted for larger values.|||So basically that type of formatting can't be done or is there another alternative i haven't thinked of?

Displaying De-Serialized Images in Reports

PREFACE:
I have a field in my SQL Server database that is called "Ink" and is Text as
the datatype.
I have put into this field a Base64 String which represents serialized
digital ink collected on a Tablet PC.
I wrote a small piece of Custom Code in the Report to de-serialize the ink
and transform it into System.Drawing.Bitmap, but it does not render in the
report.
QUESTION
Is it possible to display images in a report from a function that returns a
data type of System.Drawing.Bitmap?
If not, can I ask how you are planning to implement digital INK support in
databases?
--
Shawn Nanto
Leszynski Group, Inc.
Bellevue, WAImages can be directly displayed when they are returned as Base64 encoded
byte array. The image type has to be "Database" and you must set the
MimeType to the correct image format.
Note: System.Drawing.Bitmap is not supported.
The relevant section in the RDL file would look similar to this:
<Image>
<MIMEType>image/bmp</MIMEType>
<Source>Database</Source>
<Value>=Fields!InkImage.Value</Value>
...
</Image>
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shawn Nanto" <ShawnNanto@.discussions.microsoft.com> wrote in message
news:0A54FC08-2ECB-4996-B585-5F7E4BA3A8C9@.microsoft.com...
> PREFACE:
> I have a field in my SQL Server database that is called "Ink" and is Text
as
> the datatype.
> I have put into this field a Base64 String which represents serialized
> digital ink collected on a Tablet PC.
> I wrote a small piece of Custom Code in the Report to de-serialize the ink
> and transform it into System.Drawing.Bitmap, but it does not render in the
> report.
> QUESTION
> Is it possible to display images in a report from a function that returns
a
> data type of System.Drawing.Bitmap?
> If not, can I ask how you are planning to implement digital INK support in
> databases?
>
> --
> Shawn Nanto
> Leszynski Group, Inc.
> Bellevue, WA

Displaying data on a new page

Hi,
I am trying to display data on a new page when one field contains
specific data.
Once this data is read I wish for any following information to be
displayed on the next page. Any help would be much appreciated.
IvanHere is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Note in your case you would use Fields!Fieldname.value instead. Also, if you
want html just leave off the format and encoding part. RS defaults to HTML.
You need at least RS 2000 SP1 or greater for the above to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ivan" <ivan.drew@.gmail.com> wrote in message
news:1141128116.719467.278170@.v46g2000cwv.googlegroups.com...
> Hi,
> I am trying to display data on a new page when one field contains
> specific data.
> Once this data is read I wish for any following information to be
> displayed on the next page. Any help would be much appreciated.
> Ivan
>

Displaying data from multiple datasets in the same table

I have two datasets, the first one attached to a table, and I would like
to display a field from the second dataset based on a criteria. Think as an
analogy to joins, but for various reasons I cannot merge the queries to get
a single dataset (data is coming from different databases). So, I have a
common key and I want to lookup into the second dataset? How can I achieve
that?
Practical example: first dataset retrieves a list of people, with their
address and a column the contains the country code. In a different DB I have
a table that has associations for a country code a country name. How can I
display a list of people and the country name? No join at query level is
acceptable.
Regards,
MariusA data region can only be bound to one dataset. Why is a join in a query
unacceptable? Can you create a view in your database with a join in it and
create a dataset off of that view? Would that be an acceptable alternative?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
> I have two datasets, the first one attached to a table, and I would
like
> to display a field from the second dataset based on a criteria. Think as
an
> analogy to joins, but for various reasons I cannot merge the queries to
get
> a single dataset (data is coming from different databases). So, I have a
> common key and I want to lookup into the second dataset? How can I achieve
> that?
> Practical example: first dataset retrieves a list of people, with
their
> address and a column the contains the country code. In a different DB I
have
> a table that has associations for a country code a country name. How can I
> display a list of people and the country name? No join at query level is
> acceptable.
> Regards,
> Marius
>|||It's not acceptable because we are not using a database, but instead a
custom data extensions that gets data from our business layer objects. In
business layer we do complex calculations, so writing the reporting as pure
SQL queries wouldn't be feasible. So we have business layer object that
retrieves a list of persons, and a business layer that retrieves a table
with mapping from country codes to names. We would prefer not to implement
joins in our custom data extensions if it would be possible. Also, I guess
that subreports would do it, but is seems to be that it would be an
overkill, and would raise problems when going to numeric (orders with items,
and items with prices in a different dataset), because for example we could
have two different tables, and show the difference between two prices.
Regards,
Marius Cristian CONSTANTIN
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:Oc03VBHrEHA.1964@.TK2MSFTNGP12.phx.gbl...
>A data region can only be bound to one dataset. Why is a join in a query
> unacceptable? Can you create a view in your database with a join in it and
> create a dataset off of that view? Would that be an acceptable
> alternative?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Marius Cristian CONSTANTIN" <mconstantin.nos.pam@.bigfoot.com> wrote in
> message news:ewqG%23aGrEHA.348@.TK2MSFTNGP15.phx.gbl...
>> I have two datasets, the first one attached to a table, and I would
> like
>> to display a field from the second dataset based on a criteria. Think as
> an
>> analogy to joins, but for various reasons I cannot merge the queries to
> get
>> a single dataset (data is coming from different databases). So, I have a
>> common key and I want to lookup into the second dataset? How can I
>> achieve
>> that?
>> Practical example: first dataset retrieves a list of people, with
> their
>> address and a column the contains the country code. In a different DB I
> have
>> a table that has associations for a country code a country name. How can
>> I
>> display a list of people and the country name? No join at query level is
>> acceptable.
>> Regards,
>> Marius
>>
>