about "trailing Space" in the record

Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.

How can I make SQL to return different result?

Thanks.

That is the result of the ANSI standard.

One way that you could use to determine if the values, including trailing blanks, are different, is to use the datalength() functions. Something like this:

Code Snippet

DECLARE
@.MyVar1 varchar(20),
@.MyVar2 varchar(20)

SELECT
@.MyVar1 = 'data',
@.MyVar2 = 'data '

IF datalength( @.MyVar1 ) = datalength( @.MyVar2 )
PRINT 'Values are the same'
ELSE
PRINT 'Values are NOT the same'

|||Thanks Arnie.

My case is a bit different. I need to run a sql statement (DELETE FROM MYTABLE WHERE key='KEYVALUE') from an application. The data in the table is imported from raining data. Some data has trailing space. Because of the ANSI standard, 'KEYVALUE ' will be deleted if I run that statement which is not I want.

I just wonder if there is a switch to we can turn it on/off to make it different.|||

Perhaps this will work for you:

WHERE ( KeyValue = 'KeyValue'

AND datalength( KeyValue ) = datalength( 'KeyValue' )
)

|||Thanks. That will do.

A syntax error

Hi,

I write a test DMX as follows to expriment

cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, PredictStdev(BloodPressure) AS Stdev " +
"FROM PredictTimeSeries(BloodPressure, " + numTimePoints + ")" +
") " +
"FROM " + modelName;

However, after execute this command, a error show:

The syntax for 'Stdev' is incorrect. I have no idea about the reason to raise to error.

How can I fix it and why this error occur?

Any help would be welcome.

Ricky.

Stdev is a keyword in the language supported by our server. To use it as a column name, you need to use brackets:

cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, PredictStdev(BloodPressure) AS [Stdev] " +
"FROM PredictTimeSeries(BloodPressure, " + numTimePoints + ")" +
") " +
"FROM " + modelName;|||

Thx Bogdan.

Regards,

Ricky

A Gripe about Error Messages

[OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component.

I've condensed the useful information in the statement down to the following:

"An error occured."

I'd like to also provide a plain english paraphrase.

An error occured somewhere to something. This means that something somewhere didn't work right. The cause of the thing not working right is an error of some sort. We'd like to provide you with the following piece of diagnostic information: we know that an error occured somewhere to something because the error row disposition tells us this. We hope that helps. Thank you, and have a nice day.

Now, could anyone translate this into Klingon? I think it would be easier to understand and just as useful.

I agree with JO. Most of the SSIS error messages seem to be Vague and Unhelpful. It takes lot of time for a SSIS Dev to decipher the error messsages. The SSIS team should throw more meaningful and error messages that we all can understand and take action appropriately. Errors of the kind mentioned do not help in resolving the issues.

Thanks

AK

|||

Rename your components and look to the first error thrown from a given task for diagnostic/debugging purposes.

Leaving pipeline components at their default names makes debugging far harder. Renaming inputs and outputs is not usually done, because most components, provided they aren't sources, have a single input.

Jamie Thompson has a very useful naming convention , which if followed, will make that error message, and really, the ones which preceeded it, more meaningful, because the component name will not be the default. That doesn't make 0xC0... hex code any more meaningful, but I can tell you it does help.

Those creating somewhat permanent to permanent table names rarely leave the table at dbo.Table_1 because the name doesn't convey intent/purpose. Same concept goes for pipeline components, give them a meaningful name.

Was that the first error received? The first error is almost invariably the most helpful one, and its doubtful that was the first error thrown by the dataflow. Some of the first errors are still not as meaningful as I would like, but often this has to do with the provider, such as an OLEDB provider, which produces the messages which SSIS relays. Before imagining these are the words of an apologist, perhaps note my prior gripes about error messages as well.

Subsequent errors, particuarly in dataflows, are not as useful in debugging/diagnostics, because they are further and futher removed from the specificity and purpose of the component, and relate to how the pipeline works. Look (generally speaking) to the first error for any given task invocation.