Conversion of "True" or "False" textfields to display "Yes" or &qu

Hi All,

I've created a report that has some textfields containing "TRUE" or "FALSE" as data output when I run the report. How do I convert these textfields to display "YES" or "NO" in the output when I run my report. (bits)

Please advise.

Thanks for your time and help.

=iif(Fields!ThisRow.Value = "True", "Yes", "No")|||

Thanks Brad !! I have another question...Is it possible to represent this column data as a Checkbox?

Please advise.

THanks.

|||

You could use wingdings font checkmark & a border around a textbox, with a conditional expression to display of the checkmark.

cheers,

Andrew

|||

Set properties of your textbox like this:

Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif(Fields!ThisRow.Value = "True", "P", "" )

Hope this helps.

Jarret

|||

Hi

Check this link:http://www.sqljunkies.com/HowTo/0D746276-352D-42D9-A2BB-225C0B2A3AB5.scuk

Hope this helps.

|||Thanks to you all for your help

Conversion of "True" or "False" textfields to display "Yes" or

Hi All,

I've created a report that has some textfields containing "TRUE" or "FALSE" as data output when I run the report. How do I convert these textfields to display "YES" or "NO" in the output when I run my report. (bits)

Please advise.

Thanks for your time and help.

=iif(Fields!ThisRow.Value = "True", "Yes", "No")|||

Thanks Brad !! I have another question...Is it possible to represent this column data as a Checkbox?

Please advise.

THanks.

|||

You could use wingdings font checkmark & a border around a textbox, with a conditional expression to display of the checkmark.

cheers,

Andrew

|||

Set properties of your textbox like this:

Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif(Fields!ThisRow.Value = "True", "P", "" )

Hope this helps.

Jarret

|||

Hi

Check this link:http://www.sqljunkies.com/HowTo/0D746276-352D-42D9-A2BB-225C0B2A3AB5.scuk

Hope this helps.

|||Thanks to you all for your help

Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

control over select output results

Hi All !

Is it possible to get rid of these dash symbols which are underlining
the column name when recordset is returned after query execution ?

For example, using isql.exe:

SELECT 'blah'
go

produces the following results:

--
blah

What I want to achieve is just
blah

I know that SET NOCOUNT ON switches the "X row affected" thing. But
how about column headers ?

Thanks for your time,

SeekerHi

I don't think there is a way to stop the dashed lines, although you could
send the output to a file and strip it out with findstr. To get rid of the
column headers (and lines) remove the check box for "print headers" in
tools/options/results or specify -h-1 when using isql or osql.

John
"." <seeker12@.subdimension.com> wrote in message
news:24693db3.0309012131.7cd01fe0@.posting.google.c om...
> Hi All !
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah
> I know that SET NOCOUNT ON switches the "X row affected" thing. But
> how about column headers ?
> Thanks for your time,
>
> Seeker|||seeker12@.subdimension.com (.) wrote in message news:<24693db3.0309012131.7cd01fe0@.posting.google.com>...
> Hi All !
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah
> I know that SET NOCOUNT ON switches the "X row affected" thing. But
> how about column headers ?
> Thanks for your time,
>
> Seeker

There is no way to remove this in an interactive session, as far as
I'm aware. If you're running from a script, you could parse the output
and remove the dashes, though.

Simon|||[posted and mailed, please reply in news]

.. (seeker12@.subdimension.com) writes:
> Is it possible to get rid of these dash symbols which are underlining
> the column name when recordset is returned after query execution ?
> For example, using isql.exe:
> SELECT 'blah'
> go
> produces the following results:
> --
> blah
> What I want to achieve is just
> blah

With ISQL and OSQL you can use -h-1 to turn off headers.

In Query Analyzer you can under Tools->Options->Results change results
to text, and select something else than column delimited.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

There could be a cludge!

Use -h-1 or remove the print headers and do a union. That forces the column
headers as the first row retrieved. You will need a method to sort the
results which probably makes it as bad as having the underlines themselves!

select 'pub_id' as pub_id, 'pub_name' as pub_name, 0 as ' '
union all
select CONVERT(varchar,pub_id), pub_name, 1 from publishers
order by 3

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f544438$0$249$ed9e5944@.reading.news.pipex.ne t...
> Hi
> I don't think there is a way to stop the dashed lines, although you could
> send the output to a file and strip it out with findstr. To get rid of the
> column headers (and lines) remove the check box for "print headers" in
> tools/options/results or specify -h-1 when using isql or osql.
> John
> "." <seeker12@.subdimension.com> wrote in message
> news:24693db3.0309012131.7cd01fe0@.posting.google.c om...
> > Hi All !
> > Is it possible to get rid of these dash symbols which are underlining
> > the column name when recordset is returned after query execution ?
> > For example, using isql.exe:
> > SELECT 'blah'
> > go
> > produces the following results:
> > --
> > blah
> > What I want to achieve is just
> > blah
> > I know that SET NOCOUNT ON switches the "X row affected" thing. But
> > how about column headers ?
> > Thanks for your time,
> > Seeker|||Thanks a heap to everyone who replied !

The -h-1 option is exactly what I was after.

What would we do without USENET !

Have fun!

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93EAE80C9CB44Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> . (seeker12@.subdimension.com) writes:
> > Is it possible to get rid of these dash symbols which are underlining
> > the column name when recordset is returned after query execution ?
> > For example, using isql.exe:
> > SELECT 'blah'
> > go
> > produces the following results:
> > --
> > blah
> > What I want to achieve is just
> > blah
> With ISQL and OSQL you can use -h-1 to turn off headers.
> In Query Analyzer you can under Tools->Options->Results change results
> to text, and select something else than column delimited.

Control characters output in attachments from xp_sendmail

I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;

EXEC master.dbo.xp_sendmail
@.recipients = '<email address>',
@.dbuse = 'TestDB',
@.query = 'select top 50 descr from AdTable',
@.message = 'nathan email test',
@.subject='SQL Mail test',
@.attach_results = 'true',
@.width = 100,
@.separator = ','

The results look like this! -

d
e
s
c
r
-
-
-
-
-

etc.

When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.

NathanHi

There is no DDL, but at a guess descr is a natural language datatype e.g
nchar or nvarchar. Try looking at convert/cast in books online to change it.

John
"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> have found that when I attach results as a file, every other character
> is a control character which causes each real character output on a
> separate line. I have no idea why this is happenening, I've never seen
> it before.
> The code looks like this;
> EXEC master.dbo.xp_sendmail
> @.recipients = '<email address>',
> @.dbuse = 'TestDB',
> @.query = 'select top 50 descr from AdTable',
> @.message = 'nathan email test',
> @.subject='SQL Mail test',
> @.attach_results = 'true',
> @.width = 100,
> @.separator = ','
> The results look like this! -
> d
> e
> s
> c
> r
> -
> -
> -
> -
> -
> etc.
> When I view the result text file with an advanced text editor I can
> see that every other character is a control - these characters are not
> in the data, I have already checked this, so it looks like its they
> are being created by SQL Server or the mail system? Any advice much
> appreciated.
> Nathan|||Hi John,

I don't think its to do with the data types as this strange formatting
occurs whatever the @.query parameter is set to e.g.

@.query = 'SELECT count(*) FROM Table1' is returned as;

-
-
-
-
-
-

5
7
8

The original data types were VARCHAR, there are no NVARCHAR columns in
the database. I'm stumped!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> Hi
> There is no DDL, but at a guess descr is a natural language datatype e.g
> nchar or nvarchar. Try looking at convert/cast in books online to change it.
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> > have found that when I attach results as a file, every other character
> > is a control character which causes each real character output on a
> > separate line. I have no idea why this is happenening, I've never seen
> > it before.
> > The code looks like this;
> > EXEC master.dbo.xp_sendmail
> > @.recipients = '<email address>',
> > @.dbuse = 'TestDB',
> > @.query = 'select top 50 descr from AdTable',
> > @.message = 'nathan email test',
> > @.subject='SQL Mail test',
> > @.attach_results = 'true',
> > @.width = 100,
> > @.separator = ','
> > The results look like this! -
> > d
> > e
> > s
> > c
> > r
> > -
> > -
> > -
> > -
> > -
> > etc.
> > When I view the result text file with an advanced text editor I can
> > see that every other character is a control - these characters are not
> > in the data, I have already checked this, so it looks like its they
> > are being created by SQL Server or the mail system? Any advice much
> > appreciated.
> > Nathan|||Hi

If you save the attachment to disk as a text file does this still happen?

John

"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> Hi John,
> I don't think its to do with the data types as this strange formatting
> occurs whatever the @.query parameter is set to e.g.
> @.query = 'SELECT count(*) FROM Table1' is returned as;
> -
> -
> -
> -
> -
> -
> 5
> 7
> 8
> The original data types were VARCHAR, there are no NVARCHAR columns in
> the database. I'm stumped!
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > Hi
> > There is no DDL, but at a guess descr is a natural language datatype e.g
> > nchar or nvarchar. Try looking at convert/cast in books online to change
it.
> > John
> > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> > > have found that when I attach results as a file, every other character
> > > is a control character which causes each real character output on a
> > > separate line. I have no idea why this is happenening, I've never seen
> > > it before.
> > > The code looks like this;
> > > > EXEC master.dbo.xp_sendmail
> > > @.recipients = '<email address>',
> > > @.dbuse = 'TestDB',
> > > @.query = 'select top 50 descr from AdTable',
> > > @.message = 'nathan email test',
> > > @.subject='SQL Mail test',
> > > @.attach_results = 'true',
> > > @.width = 100,
> > > @.separator = ','
> > > > The results look like this! -
> > > > d
> > > e
> > > s
> > > c
> > > r
> > > -
> > > -
> > > -
> > > -
> > > -
> > > > etc.
> > > > When I view the result text file with an advanced text editor I can
> > > see that every other character is a control - these characters are not
> > > in the data, I have already checked this, so it looks like its they
> > > are being created by SQL Server or the mail system? Any advice much
> > > appreciated.
> > > > Nathan|||Yes, the attachment looks exactly the same when saved to disk, and
viewed with different Editors e.g. WordPad, ConText

If I set @.attach_results = 'false' to have the results returned in the
body of the e-mail, the format is fine - the problem appears to lie
with whatever writes the text file from the results, which I'm
guessing is the sqlmap70.dll?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> Hi
> If you save the attachment to disk as a text file does this still happen?
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> > Hi John,
> > I don't think its to do with the data types as this strange formatting
> > occurs whatever the @.query parameter is set to e.g.
> > @.query = 'SELECT count(*) FROM Table1' is returned as;
> > -
> > -
> > -
> > -
> > -
> > -
> > 5
> > 7
> > 8
> > The original data types were VARCHAR, there are no NVARCHAR columns in
> > the database. I'm stumped!
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > > Hi
> > > > There is no DDL, but at a guess descr is a natural language datatype e.g
> > > nchar or nvarchar. Try looking at convert/cast in books online to change
> it.
> > > > John
> > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
> > > > have found that when I attach results as a file, every other character
> > > > is a control character which causes each real character output on a
> > > > separate line. I have no idea why this is happenening, I've never seen
> > > > it before.
> > > > The code looks like this;
> > > > > > EXEC master.dbo.xp_sendmail
> > > > @.recipients = '<email address>',
> > > > @.dbuse = 'TestDB',
> > > > @.query = 'select top 50 descr from AdTable',
> > > > @.message = 'nathan email test',
> > > > @.subject='SQL Mail test',
> > > > @.attach_results = 'true',
> > > > @.width = 100,
> > > > @.separator = ','
> > > > > > The results look like this! -
> > > > > > d
> > > > e
> > > > s
> > > > c
> > > > r
> > > > -
> > > > -
> > > > -
> > > > -
> > > > -
> > > > > > etc.
> > > > > > When I view the result text file with an advanced text editor I can
> > > > see that every other character is a control - these characters are not
> > > > in the data, I have already checked this, so it looks like its they
> > > > are being created by SQL Server or the mail system? Any advice much
> > > > appreciated.
> > > > > > Nathan|||Hi

I am confused by this! Please post DDL (CREATE Table, CREATE Procedure
statements), example data as insert statements that will enable someone to
replicate the problem. Also SQL Server version details may help as well as
Windows regional setting.

Thanks

John
"Nathan Griffiths" <nathan@.griffiths.net> wrote in message
news:1ee31d7.0406131443.2191dbed@.posting.google.co m...
> Yes, the attachment looks exactly the same when saved to disk, and
> viewed with different Editors e.g. WordPad, ConText
> If I set @.attach_results = 'false' to have the results returned in the
> body of the e-mail, the format is fine - the problem appears to lie
> with whatever writes the text file from the results, which I'm
> guessing is the sqlmap70.dll?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> > Hi
> > If you save the attachment to disk as a text file does this still
happen?
> > John
> > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> > > Hi John,
> > > > I don't think its to do with the data types as this strange formatting
> > > occurs whatever the @.query parameter is set to e.g.
> > > > @.query = 'SELECT count(*) FROM Table1' is returned as;
> > > > -
> > > -
> > > -
> > > -
> > > -
> > > -
> > > > 5
> > > 7
> > > 8
> > > > The original data types were VARCHAR, there are no NVARCHAR columns in
> > > the database. I'm stumped!
> > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > > > Hi
> > > > > > There is no DDL, but at a guess descr is a natural language datatype
e.g
> > > > nchar or nvarchar. Try looking at convert/cast in books online to
change
> > it.
> > > > > > John
> > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a)
and
> > > > > have found that when I attach results as a file, every other
character
> > > > > is a control character which causes each real character output on
a
> > > > > separate line. I have no idea why this is happenening, I've never
seen
> > > > > it before.
> > > > > The code looks like this;
> > > > > > > > EXEC master.dbo.xp_sendmail
> > > > > @.recipients = '<email address>',
> > > > > @.dbuse = 'TestDB',
> > > > > @.query = 'select top 50 descr from AdTable',
> > > > > @.message = 'nathan email test',
> > > > > @.subject='SQL Mail test',
> > > > > @.attach_results = 'true',
> > > > > @.width = 100,
> > > > > @.separator = ','
> > > > > > > > The results look like this! -
> > > > > > > > d
> > > > > e
> > > > > s
> > > > > c
> > > > > r
> > > > > -
> > > > > -
> > > > > -
> > > > > -
> > > > > -
> > > > > > > > etc.
> > > > > > > > When I view the result text file with an advanced text editor I
can
> > > > > see that every other character is a control - these characters are
not
> > > > > in the data, I have already checked this, so it looks like its
they
> > > > > are being created by SQL Server or the mail system? Any advice
much
> > > > > appreciated.
> > > > > > > > Nathan|||Hi John,

I have finally and quite by chance worked out what the problem is, and
wouldn't you know it, its caused by a bug in SQL Server 2000!

Basically, xp_sendmail returns Unicode data by default, a problem
fixed in SP1, but not updated in Books Online;

To return readable ANSI format results as a attachment I have to
include the additional xp_sendmail parameter:

@.ansi_attachment = 'true'

Its on the MS support website;

http://support.microsoft.com/defaul...kb;EN-US;280720

thanks anyway!

Nathan

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<fwdzc.4083$Rb4.33474736@.news-text.cableinet.net>...
> Hi
> I am confused by this! Please post DDL (CREATE Table, CREATE Procedure
> statements), example data as insert statements that will enable someone to
> replicate the problem. Also SQL Server version details may help as well as
> Windows regional setting.
> Thanks
> John
> "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> news:1ee31d7.0406131443.2191dbed@.posting.google.co m...
> > Yes, the attachment looks exactly the same when saved to disk, and
> > viewed with different Editors e.g. WordPad, ConText
> > If I set @.attach_results = 'false' to have the results returned in the
> > body of the e-mail, the format is fine - the problem appears to lie
> > with whatever writes the text file from the results, which I'm
> > guessing is the sqlmap70.dll?
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<mFfxc.874$Oj1.7196703@.news-text.cableinet.net>...
> > > Hi
> > > > If you save the attachment to disk as a text file does this still
> happen?
> > > > John
> > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > news:1ee31d7.0406072000.42435ac7@.posting.google.co m...
> > > > Hi John,
> > > > > > I don't think its to do with the data types as this strange formatting
> > > > occurs whatever the @.query parameter is set to e.g.
> > > > > > @.query = 'SELECT count(*) FROM Table1' is returned as;
> > > > > > -
> > > > -
> > > > -
> > > > -
> > > > -
> > > > -
> > > > > > 5
> > > > 7
> > > > 8
> > > > > > The original data types were VARCHAR, there are no NVARCHAR columns in
> > > > the database. I'm stumped!
> > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<bHXwc.2129$wx6.21830686@.news-text.cableinet.net>...
> > > > > Hi
> > > > > > > > There is no DDL, but at a guess descr is a natural language datatype
> e.g
> > > > > nchar or nvarchar. Try looking at convert/cast in books online to
> change
> it.
> > > > > > > > John
> > > > > "Nathan Griffiths" <nathan@.griffiths.net> wrote in message
> > > > > news:1ee31d7.0406011411.54e8da4f@.posting.google.co m...
> > > > > > I have been testing our SQL Mail setup in SQL Server 2000 (sp3a)
> and
> > > > > > have found that when I attach results as a file, every other
> character
> > > > > > is a control character which causes each real character output on
> a
> > > > > > separate line. I have no idea why this is happenening, I've never
> seen
> > > > > > it before.
> > > > > > The code looks like this;
> > > > > > > > > > EXEC master.dbo.xp_sendmail
> > > > > > @.recipients = '<email address>',
> > > > > > @.dbuse = 'TestDB',
> > > > > > @.query = 'select top 50 descr from AdTable',
> > > > > > @.message = 'nathan email test',
> > > > > > @.subject='SQL Mail test',
> > > > > > @.attach_results = 'true',
> > > > > > @.width = 100,
> > > > > > @.separator = ','
> > > > > > > > > > The results look like this! -
> > > > > > > > > > d
> > > > > > e
> > > > > > s
> > > > > > c
> > > > > > r
> > > > > > -
> > > > > > -
> > > > > > -
> > > > > > -
> > > > > > -
> > > > > > > > > > etc.
> > > > > > > > > > When I view the result text file with an advanced text editor I
> can
> > > > > > see that every other character is a control - these characters are
> not
> > > > > > in the data, I have already checked this, so it looks like its
> they
> > > > > > are being created by SQL Server or the mail system? Any advice
> much
> > > > > > appreciated.
> > > > > > > > > > Nathan

Consuming Stored Procedure Output Param

This is my SProc:

CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @.numberRows */
@.selectDate datetime
,@.selectCountry int
,@.numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_smc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDate and RequestDateTime < dateadd(dd,1, @.selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @.selectCountry
ORDER BY configname

/* @.@.ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SELECT @.numberRows = @.@.RowCount

GO

And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue("@.selectDate", dateEntered)

myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))

Dim myParamAsNew SqlParameter("@.numberRows", SqlDbType.Int)

myParam.Direction = ParameterDirection.Output

myCommand.Parameters.Add(myParam)

myCommand.Connection = con

con.Open()

Dim readerAs SqlDataReader = myCommand.ExecuteReader()Dim rowCountAsInteger = reader.RecordsAffected

numberParts.Text = rowCount.ToString

con.Close()

EndSub

What should I fix?

label1.Text = myCommand.Parameters("@.numberRows").Value

|||

If I remember, I had this same problem, and found that you can't use the DataReader if you want to get the output parameter. I think you have to use DataSet.

|||

Read the following for an explanation of why it is happening and how to get around it.

http://p2p.wrox.com/archive/aspx/2001-12/24.asp

|||

How do I do the DataSet approach?

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection("Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx")

Dim dbDataSet =New DataSet()Dim dbAdapterAsNew SqlDataAdapter

dbAdapter.Fill(dbDataSet)

|||

You can do the following

Dim dbDataSet =New DataSet()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataSet,"tablename")

dbDataSet.Tables("tablename").rows.count

In case you have only one table, you can use a datatable instead of a dataset

Dim dbDataTable =New DataTable()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataTable)

dbDataTable.rows.count

Consuming Error Output from a Derived column component

Hi,

I have created a program that imports a csv into the sql server. but during that import I need to track all the errors that occured for some malformed rows. I think I need to use the error output collection of the dataflow components to track the errors. I figured out that every dataflow component has a error output collection along with the data output collection. I want to write those error outputs into a separete database. So, I have created a SQL server data destination component and created a path between derived columns error output and it input collection. But it is not working as expected. can any body help on this?

or can anyone give me any example how to use/handle error output collection in SSIS?

I will appreciate all kind of suggestions.

thanks

Why is it not working? Are you receiving any errors? What are those errors?|||

Give more details...

What is the error?

Consuming a Recordset in a Script Transformation

I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.

Now I want to write that recordset to a flat file.

There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.

Anyone have any ideas on how to do this in a script transformation?

There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

|||

DarrenSQLIS wrote:

Try this -

SSIS Junkie : SSIS: Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

By the way I would consider raw files over this, especially if you have a lot of data.

I echo what Darren has said. And if you need proof, go here:

Comparing performance of a raw file against a recordset destination
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4159.aspx

-Jamie