Data Conversion failed due to Potential Loss of data

Hi,

I am getting this error when my ssis package is running

Data Conversion failed due to Potential Loss of data

the input column is in string format and output is in sql server bigint

the error is occuring when there is an empty string in the input. what should i do to overcome this

It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.

Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

|||

desibull wrote:

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

NULL and "empty string" are two very different things.

To expand on what I suggested earlier and desibull's code above:

ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]

OR

ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]

Data Conversion (String to DateTime)

I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates.

Below is the code that I am using:

<asp:SqlDataSource

id="SqlDataSource1"

runat="server"

connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>"

selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq"

insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@.RepName, @.BranchName, @.Date, @.DueDate, @.ByEmail, @.ByFax, @.Fax, @.ProjExp, @.PrintQty, @.Disc, @.Email, @.RepEmail)">

<InsertParameters>

<asp:FormParameterName="RepName"FormField="LoanRepNameBox"/>

<asp:FormParameterName="BranchName"FormField="BranchList"/>

<asp:FormParameterName="Date"FormField="CurrentDateBox"Type="DateTime"/>

<asp:FormParameterName="DueDate"FormField="ReqDueDateBox"Type="DateTime"/>

<asp:FormParameterName="ByEmail"FormField="ProofByEmailCheckbox"Type="boolean"/>

<asp:FormParameterName="ByFax"FormField="ProofByFaxCheckbox"Type="boolean"/>

<asp:FormParameterName="Fax"FormField="FaxNumberBox"/>

<asp:FormParameterName="ProjExp"FormField="ProjectExplanationBox"/>

<asp:FormParameterName="PrintQty"FormField="PrintQuantityBox"/>

<asp:FormParameterName="Disc"FormField="PDFByDiscCheckbox"Type="boolean"/>

<asp:FormParameterName="Email"FormField="PDFByFaxCheckbox"Type="boolean"/>

<asp:FormParameterName="RepEmail"FormField="LoanRepEmailBox"/>

</InsertParameters>

</asp:SqlDataSource>

protectedvoid Button1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

}

I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance.

Thank you,

Paul

What about remove (cross out in the following) the Type="boolean" for these formfields:

<asp:FormParameterName="ByEmail"FormField="ProofByEmailCheckbox"Type="boolean" />

<asp:FormParameterName="ByFax"FormField="ProofByFaxCheckbox" Type="boolean" />

<asp:FormParameterName="Disc"FormField="PDFByDiscCheckbox" Type="boolean" />

<asp:FormParameterName="Email"FormField="PDFByFaxCheckbox" Type="boolean" />

|||

Thank you, that ended the exception errors.

Paul

data conversion - numeric to string loosing precision

Hi All,

i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.

the problem is when this numeric field is > 0 it looses the precision on its decimal value.

example, if numeric value is 0.32

after converting this to a string, the new value will be : .32

it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.

when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.

can any one help on this?

Is your ultimate database target for the numeric data type a character based column, hence the need to retain the leading zeros?

If that is the case, retaining leading zeros can be accomplished with a derived column transform, rather than a data conversion transform. There you have access to the SSIS expression language, with a relatively standard, if meager, set of string functions, as well as type casts.

Another alternative for pretty much an data type conversion is a Script Transform, which will afford you the full power of the .NET framework, including in this case custom numeric formats for types destinated for strings.|||

Hi jaegd,

i tried that, but no luck. i use a derived table and this is my expression

ISNULL(investmentPercentage) ? 0.00 : investmentPercentage

here investmentPercentage is a numeric (15,2). i add the above expression as a new column (string) (8 chars). but still it looses the 0

.00 when it's null

.32 when it;s 0.32

any comments

AJ

|||

Use the Script Task, and follow the instructions in this link for numeric format specifiers for strings. You need to do something similar to this...

Dim number as float

number.ToString("D")

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

|||

In the derived column, you can still acheive it with conditional logic The script as mentioned above is cleaner, but if you want to avoid adding that, you can try an expression like:

(investmentPercentage < 1 ? "0" : "") + (DT_WSTR, 20)investmentPercentage

This expression will add a "0" to the beginning of the string if the number is less than one (which is the only time the zero is dropped). When the number is greater than or equal to 1, an empty string is added.

NOTE: this does not take into account negative values. If investmentPercentage can be negative, you will need to tweak the expression a little (or post here, and I can help with that).

Thanks
Mark

|||

Hi Mark,

thanks for that, but it sort of half answer my question.

yes investmentPercentage can be negative or can even be null. i was wondering if there's an eaisier way. i've got a lot of cloumns like this, writing an expression for each of them this long can be tedious. is this how you would "tweak" it?

ISNULL(investmentPercentage) ? "0.00" : (investmentPercentage < 1 && investmentPercentage >-1 ? "0" : "") + (DT_WSTR, 8)investmentPercentage

|||

You might find it easier to use string formatting in the script component... I can't think of any other way that would be better.

As for tweaking the expression, you will have to do some extra things to take care of the negative sign (so the prepended zero doesn't end up before the negative sign). The following expression adds a case for values between -1 and 0 to handle that:

ISNULL(investmentPercentage) ? (DT_WSTR,8)"0.00" : investmentPercentage < 0 && investmentPercentage > -1 ? "-0" + SUBSTRING((DT_WSTR,8)investmentPercentage, 2, 8) : ((investmentPercentage >= 0 && investmentPercentage < 1 ? "0" : "") + (DT_WSTR,8)investmentPercentage

Let me know if this does the trick for you.

Thanks
Mark

Data Conversion

I have single letters in a Flat file and it is in the string format,but now i want to convert it in to int format.I have tried doing this by SSIS but it is not working.
I used data conversion and copy column transformation.

I got a error message.

it automatically correct the meta data mismatch. But when i run the package, it gives the following error message.

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

This is just one error message.

Please can you tell me the steps to do this correctly.

Also I need to know how to run a SSIS package through command line?

Thanks

Nishan

Did you say you were trying to convert letters to int? Can't do that. Is there any more info?|||

I have a flat file which has string types of data. I want to convert them into int type before I load them into destination table. I used data convertion and derived column but neither worked for me.

Ex;

Source file (String type);

column 1

B

B

C

C

Destination file format should look like this. B - > 1 , C - > 2

Destination File ( int Type);

Column 1

1

1

2

2

Can you explain the setps to do this conversion using SSIS

Thanks

|||The Derived Column task should work for you: Use the conditional operator|||Shamen,
Please keep your posts together in one thread.

This can be done in a lookup transformation. Use the following SQL for the lookup:

select "B",1
union all
select "C",2
union all
select "D",3"
....

Then hook up your source to the lookup component, selecting the second column as the return value. Coming out of the lookup component, you'll have the number associated with the letter and can then go into a flat file destination.|||

Thanks Pill and SQL Pro...I will try..at the same time I want to convert the data type too.

Yes I will keep all my posting together in one thread...

Also I have another question...Once I create the package can I run it on command line? How can I do that?

Thanks

|||Thanks I will try|||

shamen wrote:

Also I have another question...Once I create the package can I run it on command line? How can I do that?

DTEXEC runs packages from the command line.

|||

Thanks....Still I'm having a problem with data conversion...I tried using both derived column and data conversion transformation....but still no luck...

This is the error message I got when I tried with data conversion transformation.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [75]: Data conversion failed while converting column "ReceiptType" (67) to column "ReceiptType" (112). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (75) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has ended.

Information: 0x402090DF at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

The program '[4360] Package.dtsx: DTS' has exited with code 0 (0x0).

|||

You can use the CODEPOINT function to return a numeric value for a character. That means you can use an expression in a Derived Column transform to do the work:

Expression: CODEPOINT(UPPER(YourColumn))-64

(the 64 is there because CODEPOINT("A") = 65)

This will work nicely where you have a one-character value to transform.

You can craft a more elaborate expression for multiple characters if required.

Dylan.

|||

Thanks Dylan...But at the same time I want to convert data type from String to Int.....

I'm supposed to get specific value for specific letters. As a example for letter B I'm supposed to get 1.

B - > 1

C- > 2

But first of all I have to figure it out how to convert data from string to Int.

Thanks

|||

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

|||

OK, but as pointed out above, you are on a fruitless quest.

A letter is not a number, so it cannot be converted to an int.

What you are saying is like asking how mainy legs a daisy has, or how many teeth in an elephant's trunk. It just doesn't make sense to talk about converting a letter into an int. By definition an int is a number, which is not a letter.

That said, what you CAN talk about is removing the column with the letter and replacing it with a corresponding column containing an int.

Then it is just a matter of defining the rule by which you convert your letter into a number.

One such rule is to say there is a specific mapping. So B=2, F=61, R=12.

In this case the select statement above is a good way of performing that mapping. However you can't deal with unknown values. This is like saying "African elephants have 6 teeth in their trunk. Asian elephants have 9 teeth." Using this rule, you can answer the question "How many teeth in an African elephant's trunk?" but not "How many teeth in an Australian elephant's trunk?" because you have no information about Australian elephants.

The other approach is to say there is a formula, so B=2, C=3, D=4, E=5. In this case you can say that even if you have never used an "F", if one were to be used for some reason then it would be equal to 6. That is where functions like CODEPOINT can help.

This is like saying "All daisys have twice as many legs as petals." Now you can look at any daisy, and answer the question "How many legs on my daisy?"

Notice that what we have done is create two different methods for answering questions that do not make sense in the real world. And likewise, we have revealed two different methods for turning a letter into an int, even though doing so does not make sense.

You should stop thinking about turning your letter into an int, and focus instead on dropping the column and replacing it with a derived int column. You are not converting anything, you are transforming and replacing. If it looks like "converting" to your pointy-haired managers, then that's fine, but you will know better on the inside.

Good luck,

Dylan.

|||

jwelch wrote:

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

That would depend on context and maintainability requirements.

If the letter value can be derived by formula rather than by lookup, that would be more appropriate in my opinion. If you are hardcoding values in a lookup, then you are creating a potential maintenance problem.

In addition, creating a complex union query where there are only ever two values to look up would be overcomplicated when an IF expression would be simpler.

Without knowing more about the requirements and implementation specifics, it is risky to talk about which solution is simplest. Normally I wouldn't care, but I've taken issue in this case because I've had to maintain a fair amount of work recently that was done using this "simplest" approach. Two years later, it has turned out to be a major pain.

|||

OK...Well I will try...

Thanks all

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:
>

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.
Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:

> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.
|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...[vbcol=seagreen]
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:

Data Conversion

This should be ez but I can't get it working;
Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
to 'dd/mm/yyyy' and 'dd/mm/yy' format?
Db on SQL 2005.Try
set dateformat mdy
select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
Linchi
"TBoon" wrote:
> This should be ez but I can't get it working;
> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
> Db on SQL 2005.|||testing
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E4C66EE1-B961-41BC-B555-818C2EA41599@.microsoft.com...
> Try
> set dateformat mdy
> select convert(varchar(10), cast('9/25/2006 4:17:12 PM' as datetime), 103)
> select convert(varchar(8), cast('9/25/2006 4:17:12 PM' as datetime), 3)
> Linchi
> "TBoon" wrote:
>> This should be ez but I can't get it working;
>> Anyone knows how to convert a string value of '9/25/2006 4:17:12 PM'
>> to 'dd/mm/yyyy' and 'dd/mm/yy' format?
>> Db on SQL 2005.

Data checking?

Hey all,
prolly a simple solution, but why isn't the following string working in
my execute sql step within DTS? It produces results, just not the ones
I want... What am I doing wrong?

select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'

It's displaying all the records? It should only be displaying those
records that do *not* contain letters or numbers.
Thanks in advance!
-RoyOn 6 Jan 2005 06:13:08 -0800, roy.anderson@.gmail.com wrote:

> Hey all,
> prolly a simple solution, but why isn't the following string working in
> my execute sql step within DTS? It produces results, just not the ones
> I want... What am I doing wrong?
>
> select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'
> It's displaying all the records? It should only be displaying those
> records that do *not* contain letters or numbers.
> Thanks in advance!
> -Roy

Your clause is selecting rows where the x column contains at least one
character that is not a digit and also contain at least one character that
is not a letter. If you had a row where x was all letters, all digits, or
maybe all letters plus punctuation but no digits, etc., then it would not
be included.

The clause you want is probably

WHERE NOT (x LIKE '%[0-9a-z]%')

(parenthesis optional)|||Thanks much Ross, after some toying around, the end product that works
is:

WHERE (x LIKE '%[^0-9a-z]%')

I'm unsure why having the "NOT" specified beforehand produces no
results, but it doesn't. I'm assuming it's because sqlserver perceives
the NOT as referring to the wildcards too, ergo, it's only looking for
blank fields.

Thanks much for the help!!!|||On 6 Jan 2005 09:02:41 -0800, Roy wrote:

> Thanks much Ross, after some toying around, the end product that works
> is:
> WHERE (x LIKE '%[^0-9a-z]%')
> I'm unsure why having the "NOT" specified beforehand produces no
> results, but it doesn't. I'm assuming it's because sqlserver perceives
> the NOT as referring to the wildcards too, ergo, it's only looking for
> blank fields.
> Thanks much for the help!!!

It looks to me like your query is requesting those rows that contain at
least one non-letter, non-digit character. I thought you wanted rows that
contained no letters and contained no digits... maybe I'm still confused
... but if you've got what you want, great.

Data alignment in Textbox

Hi All,
I am working on a report, in this report a textbox is present, which is expected to show string. This string length is larger than what textbox can accommodate in single line. I want this textbox to show only right part of the string in case it can accommodate full string.
After using the right alignment I assumed it would do show, however it does not work like that. It always shows the left part of string in case, string is bigger than width of textbox.
Restriction from design:
1. Cannot increase the width of textbox.
2. Cannot use the Can Grow = true to show string in multiple lines.

Below is the example of my issue:
Say there is a string: "Microsoft SQL Server Reporting Services".
because the textbox width is not sufficient to see full string, I want to "Reporting Service". Which is of same size which column can accommodate. So I used Right alignment in textbox. however text box shows "Microsoft SQL Server". So, is there a solution to see required text in the textbox.

Hi,

Data alignment in Textbox

Hi All,
I am working on a report, in this report a textbox is present, which is expected to show string. This string length is larger than what textbox can accommodate in single line. I want this textbox to show only right part of the string in case it can accommodate full string.
After using the right alignment I assumed it would do show, however it does not work like that. It always shows the left part of string in case, string is bigger than width of textbox.
Restriction from design:
1. Cannot increase the width of textbox.
2. Cannot use the Can Grow = true to show string in multiple lines.

Below is the example of my issue:
Say there is a string: "Microsoft SQL Server Reporting Services".
because the textbox width is not sufficient to see full string, I want to "Reporting Service". Which is of same size which column can accommodate. So I used Right alignment in textbox. however text box shows "Microsoft SQL Server". So, is there a solution to see required text in the textbox.

Hi,

Dash in search string

Is it posible that dashes "-" are considering as punctuation when using
CONTAINSTABLE function? It's the behavior I see but I cannot find any text in
BOL that specify this.
This gives lots of rows:
SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-0')
This gives no of rows:
SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-00')
But I know that from the first resultset, full-text indexed column include
data that start with 'ISSI-2007-00'...
I would like that someone can confirm this behavior and let me know where I
can find ducomentation on that "-" behavior!
David, MCDBA
For the most part it is throw away. The rules vary from language to language
though. Basically it is broken as ISSI and 2007 and 0 (for ISSI-2007-0), and
ISSI 2007 and 00 for the second one.
I get different results from you.
create table david(pk int identity not null constraint davidpk primary key ,
charcol varchar(20))
GO
create fulltext index on david(charcol) key index davidpk
GO
insert into david(charcol) values('ISSI-2007-0')
insert into david(charcol) values('ISSI-2007-00')
GO
select * from david where contains(*,'ISSI-2007-0')--both found
GO
select * from david where contains(*,'ISSI-2007-00')--only the second is
found
GO
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:F80F130B-78F1-4942-89AA-589E6419DCB7@.microsoft.com...
> Is it posible that dashes "-" are considering as punctuation when using
> CONTAINSTABLE function? It's the behavior I see but I cannot find any text
> in
> BOL that specify this.
> This gives lots of rows:
> SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-0')
> This gives no of rows:
> SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-00')
> But I know that from the first resultset, full-text indexed column include
> data that start with 'ISSI-2007-00'...
> I would like that someone can confirm this behavior and let me know where
> I
> can find ducomentation on that "-" behavior!
> David, MCDBA

Cutting to a certain word

Hi there, i need to know how to cut a string to the nearest word. For example, i've got an article and i need to extract just a part of the beginning, i could use LEFT([content], 250) but there is little chance this will cut on a word. Therefore i need to know if there is a function that will cut to the nearest word in T-SQL or i will simply put a summary field in the database. (I prefer to generate the summary on the fly if possible)

You could use CHARINDEX.

SELECT LEFT(column, CHARINDEX(' ', column) ) FROM yourTable

|||

I just looked up what you said and this doesn't make sense...

I didn't even try but i know what this will result in... Returns the first word of a column...

But i guess if i provide a startAT value(param 3) to charindex i can start the search at 250 characters and it will cut accordingly...

|||

Yes, this will always give you a full word:

Declare @.tvarchar(100), @.startatintSET @.t ='this is a good test and a long sentence to test end of word'SET @.startat = 33SELECT @.t,left(@.t,@.startat + CHARINDEX(' ',right(@.t,len(@.t) - @.startat)))

Customizing the connection string to the server where sp will be created?

When you create a SQL server project, it asks you for the connection string to the database. Can I change this connection string at run time?. By the way it is included in the project properties at desing time.I don't really understand the question - the connection string you mention is the string which tells VS where the assembly and procs etc will be deployed, when you do Deployment - at wich stage would you like to change the string? When your code is invoked eventually, the connection string is not used.

Niels

Customizing NoRows Rpt Svs 2003

How can I customize the NoRows String message in a subreport? The text
it displays is black and in Times New Roman. I need to change the font
and color.
Thanks in advance,
RobertNevermind, I'm an idiot. Thanks. Didn't realize the font property on
the actual subreport controlled that.