DT_Str And Trailing Whitespace?
Apr 26, 2007
I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).
I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.
Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?
Any help, of course, will be appreciated.
View 8 Replies
ADVERTISEMENT
May 14, 2006
I have a databound textbox that is used to store a decimal value.
If my sql table stores this column as a decimal(2,2), then all of the numbers entered into the field will automatically put decimal places in that I don't want. For example, 45 becomes 45.00... 34.5 becomes 34.50.
If I set the sql table to nchar(10) and the dataset to system.string (max length of -1), then the number looks the way I would like it, however after a datatable update I end up with trailing whitespace after the number - filling up the rest of the unused 10 characters. For example, "45" becomes "45 " (8 spaces afterwards).
Does anybody know how I can fix this? I would prefer to store the numbers in SQL as a string (nchar(10))... but I don't know how to get rid of that darned whitespace. I would like to remove it at the database level and not at the client level if at all possible.
Thanks!
View 1 Replies
View Related
May 16, 2006
Anyone know the origin of sql 2005's datatype naming convention or even why SSIS has defined its own special data types? (Before SSIS I had never seem types like DT_UI1 except in programming languages.)
TIA,
barker
View 1 Replies
View Related
May 15, 2006
Okay, this is probably my own stupidity but here goes. I am trying to make a very rudimentary package. I've got a data flow that has a flat file source. This source has fields delimited by the | symbol. I give the fields in the flat file source names. The first field is always exactly 1 character. When I use the advanced editor to look at the fields' properties, I see the first field, called ProductLine, is considered to be of SSIS internal data type DT_STR with a length of 50. I change the 50 to 1 because that is as long as that field is.
No matter what I do, when I close the advanced editor and then open it again, the field length for ProductLine is again set to 50. The field is 1 long, not 50 long. How can I make the setting of 1 be retained? Nothing I do changes it. What is the point of allowing the length to be editable if the edit is not retained?
Thanks in advance,
SW
View 4 Replies
View Related
May 28, 2008
Can anyone tell me how to convert a string variable to boolean
Ihave a variable named VarLoc which is string datatype
In expression I want to give condition like
VarLoc == "1234"?False:True
its giving error because of converting string datatype to boolean.
Please help.
Thank You
View 3 Replies
View Related
Mar 30, 2007
Our shop's DTS packages usually use ActiveX scripts to dynamically name vendor text file inputs by date (YYYYMMDD), which we load Monday through Friday to our databases. For our 2005 server I am trying to dynamically name a 'flat file source' filename in SSIS as described in the article at the url http://www.mssqltips.com/tip.asp?tip=1084.
I am writing the expression (DT_STR, 4,1252) YEAR(DATEADD( "dd",0, getdate() ) to create a variable named FileName to capture today's 4 character year. If I could get a good preview of this value I would concatenate the month and day strings to it and use the resultant variable as part of the fully qualifeid name.
For the variable I've marked the 'evaluateasexpression' true and put the value type as str. But when I preview this expression I get the error message, 'expression has the result type of DT_STR which can not be converted to a supported type.'
What supported type? Does anyone see what the problem is?
View 9 Replies
View Related
Dec 18, 2007
Hi,
Having problem with OLE-DB connection to informix.
Created a flat file has two columns and 2 row:
1|a
2|b
tried to load it to a informix table:
create table t
( c1 int,
c2 char
);
2 rows inserted, but only column c1 got data.
Changed c2 from char to varchar then ok.
thanks for any help.
gg
View 17 Replies
View Related
Dec 27, 2007
Hi, I am trying to derive a column from:
mm/dd/yyyy hh:mms.fff to dt_dbtimestamp as:
(dt_dbtimestamp)(colum_name,1, 23) when I include the period and three digits for milliseconds the package fails if I leave it out it is successfull. I need to include milliseconds for my datawarehouse project. I tried many different ways and always with failure
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,23) fails
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,19) succeeds
Thanks
View 9 Replies
View Related
Oct 9, 2006
The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.
This appears to be a bug to me.
View 4 Replies
View Related
Sep 16, 2006
Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.
DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?
Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?
FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
View 4 Replies
View Related
Mar 28, 2008
I am currently working on a rather simple report, it cosists of a table with two columns
The first column contains Employee Names, and the second column contials details of what the have worked on
I have made the second column invisible, and can be made visible when the eployee name column is expanded.
The problem that i am having is that when the second column is collapesed, it still leaves whitespace, in the first column, of how ever many rows that are contained in the second column, is there a way, I can get rid of that whitespace?
Thanks
Chris
View 4 Replies
View Related
Jan 16, 2007
Hello All,
I am having a problem with whitespace showing up within a table. I have a grouping by date that toggles the fields correctly but when the fields are not expanded, the space where the data would be is showing as whitespace instead of collapsing up. Can anyone shed some light on how to make the whitespace go away?
Thanks
Clint
View 4 Replies
View Related
Oct 8, 2007
I am hoping there is an easy way to fix this. I have a report defined 8.5" x 11.0" with .5" Margin's all around. I am using the WinForms Report Viewer Control (doing Server side reports). It looks like the viewer is taking the left margin and adding it to the right margin. So you get large amounts of white space on the right of the viewer. This requires me to make the viewer way larger than it needs to be to get rid of the ugly Horizontal Scroll bar.
What I want is either don't show the margins until you print (removing the margins from the RDLC file does nothing), or have the left margin white space belong on the left side.
Why is this happening? And how can I fix it? Thanks!
View 1 Replies
View Related
Dec 19, 2006
Hi,
I have a report that, among other things, contains 3 tables laid out side-by-side horizontally. The first table is conditionally visible and when it is hidden the report contains whitespace instead of the table. The problem is that this causes an unappealing visual look because the other 2 tables look shifted too much to the right.
What is the recommended way for dealing with situations like this? There doesn't seem to be support for specifying the location (i.e. top, left) of elements based on expressions. Am I "stuck" with having to create multiple flavors of the report?
Thanks,
Vitaly
View 2 Replies
View Related
Feb 6, 2007
So...I'm trying to insert a tab (or just a few spaces) at the beginning of a line in a textbox. Is this possible? If so, what do i have to do?
View 1 Replies
View Related
Jul 23, 2005
I'm trying to figure out how to find the last whitespace character in avarchar string. To complicate things, it's not just spaces that I'mlooking for, but certain ascii characters (otherwise, obviously, justuse LEN). My initial thought was to REVERSE it, find the location(using CHARINDEX) looking for each of those characters (so, multiplequeries), then subtract that from the LEN of the string.The problem I'm running into is that there are about a dozen differentcharacters we're looking for. Any suggestions? My thought was to(this sounds silly, so there's gotta be a better way) dump the resultsfrom each CHARINDEX into a table, then find the MAX of the table anduse that. But, like I said, it sounds silly. I don't think I can do a[^0-9A-Z] either, since there are non-Alphanumeric characters we'relooking for.Many thanks.
View 10 Replies
View Related
Oct 2, 2006
I have a column that I do not want any whitespace in whatsoever. I'mwondering how do enforce this a DDL level instead of in 40 millionseat-of-the-pants after-the-fact computer programs accessing thedatabase.Regards,Terrence
View 5 Replies
View Related
Jul 20, 2005
Hi,I am trying to concoct a query that will join rows on the basis of awhitespace insensitive comparison. For instance if one row has the value'a<space>b' and another has the value 'a<space><space>b' I want them to beconsidered equal (but not the same as 'ab')I am happy to do some T-SQL if that helps. Also I have a full-text index onthe column in question, but note that I am comparing rows against eachother, not to a fixed string.This is for a one-off job, so if there is no obvious way to do it on thedatabase, I will just bcp out the data and knock up some perl or somethingto do it. All other things being equal I would rather do it on the databasethough.Many thanksAndy
View 11 Replies
View Related
Jan 11, 2008
I am having an problem with using full-text search and phrases in ASP.NET 2.0 when populating a treeview.
I have a stored procedure that searches a full text index and brings back the results in a datareader. It fails when I try to search for more than one word with a space in between.
I get the error SQLException was unhandled by usercode "Syntax error near 'up'' in the full-text search condition ''item up''."
The stored proc is as follows:
CREATE PROCEDURE [dbo].[SearchResults] @criteria as varchar(50)
AS
BEGIN
SELECT *
FROM PMGNT_PROJECTS PRJCT INNER JOINCONTAINSTABLE(PMGNT_PROJECTS,*,@criteria) K
ON PRJCT.PRJCT_REFNO = K.[KEY]
END
The stored procedure works fine though query analyser it's only when I try to use it in ASP.NET and use more than one word that it fails e.g. "item up". Search for single words also works fine in ASP.NET
Code is as follows:
1 if (SearchField.Text != "")
2
3 {
4
5 string searchString = "";
6
7 searchString = "'" + SearchField.Text + "'";
8
9
10
11 SqlConnection SqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
12
13 SqlCon.Open();
14
15 SqlCommand SqlCmd = new SqlCommand("SearchResults", SqlCon);
16
17
18 SqlCmd.CommandType = CommandType.StoredProcedure;
19
20 SqlCmd.Parameters.Add("@criteria", SqlDbType.VarChar);
21
22 SqlCmd.Parameters["@criteria"].Value = searchString;
23
24
25
26 SqlDataReader Sdr = SqlCmd.ExecuteReader();
27
28 SqlCmd.Dispose();
29
30 string[,] ParentNode = new string[200, 2];
31
32 int count = 0;
33
34 while (Sdr.Read())
35
36 {
37
38 ParentNode[count, 0] = Sdr.GetValue(Sdr.GetOrdinal("PRJCT_REFNO")).ToString();
39
40 ParentNode[count++, 1] = Sdr.GetValue(Sdr.GetOrdinal("PRJCT_CODE")).ToString();
41
42 }
43
44 Sdr.Close();
45
46 for (int loop = 0; loop < count; loop++)
47
48 {
49
50 TreeNode root = new TreeNode();
51
52 root.Text = ParentNode[loop, 1];
53
54 root.Value = ParentNode[loop, 0];
55
56 root.SelectAction = TreeNodeSelectAction.Select;
57
58 SqlCommand Module_SqlCmd = new SqlCommand("SELECT * FROM PMGNT_MILESTONES MILST where MILST_PRJCT_REFNO =" + ParentNode[loop, 0], SqlCon);
59
60 SqlDataReader Module_Sdr = Module_SqlCmd.ExecuteReader();
61
62 string[,] ChildNode = new string[200, 2];
63
64 int countChild = 0;
65
66
67
68 while (Module_Sdr.Read())
69
70 {
71
72 ChildNode[countChild, 0] = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("MILST_REFNO")).ToString();
73
74 ChildNode[countChild++, 1] = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("MILST_TITLE")).ToString();
75
76 }
77
78 Module_Sdr.Close();
79
80 for (int cloop = 0; cloop < countChild; cloop++)
81
82 {
83
84 TreeNode child = new TreeNode();
85
86 child.Text = ChildNode[cloop, 1];
87
88
89
90 root.ChildNodes.Add(child);
91
92 }
93
94
95
96 // Add root node to TreeView
97
98 SearchResultTree.Nodes.Add(root);
99
100 }
101
102 SearchResultTree.CollapseAll();
103
104 SqlCon.Close();
105
106
107 }
108
109
Can anyone Help ?
View 9 Replies
View Related
Mar 31, 2008
Hi,
I have a need to create a report that has a graph at the top and a table at the bottom. The graph at the top can optionally be made hidden because they cause problems when exported to Excel as images. However, when I set the Hidden property of the graph to true, positions of all items on the report remain absolute. Meaning of course that the table that is located half-way down the page remains half-way down the page and there is a lot of nothing on the first half where the graph used to be.
It would be desirable to have the ability to have the table move up when the graph is not visible, however it obviously doesn't do it automatically and also refuses me the ability to change the position with an expression.
Any advice is appreciated, thank you!
View 3 Replies
View Related
Feb 3, 2006
I know that this almost never happens... but Im dealing with an AIX flatfile-database conversion that brings 80 tables into SQL. Im not allowed to touch this stuff or to massage how its brought over to SQL, as it deals with medical records...
What I need to do though, is on creating my own table imports, for my "account" fields to match with the existing SQL conversion table "account" fields, I have to match requirements...
Existing account numbers are a total of 6 charaters. Account numbers with less than six characters contain leading whitespace character equivalents to total the six character spaces for "account number"
When I import records, I need to force the same requirement and have a min and max length of characters for "account number" = 6 characters and any account number less than 6 characters must also have the necessary whitespace character equivalent added to it.
How would I do this? It needs to be automated, as this is a process that will run nightly and cannot have a human sitting on it every day, 7 days a week... I cannot accurately join unless I can meet this requirement and my hands are tied because I can't change the way the formatting is done on the imported tables =(
Any help would be greatly appreciated... I'm quite stuck
View 5 Replies
View Related
May 2, 2007
Hi there.
I'd like to call dtexec with something like this:
dtexec /f myPackage.dtsx /Set package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;
I get an error along the lines of
Option "Source=localhost;Provider=blah;Integrated" is not valid".
How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.
Thanks,
Jon
View 5 Replies
View Related
Oct 14, 2015
I am facing whitespace issue in my SSRS report. I have simple tablix report with 10 columns. I am trying to toggle column 5 to 8 using on column 4. The problem I am facing is the whitespace. Now column 5 to 8 are only visible when we expand column 4. When the report is rendered on the screen, there is a huge gap between column 4 and 9.
View 4 Replies
View Related
Jul 20, 2005
I have a column defined as DECIMAL(10,6). I want to display it as astring but I do not want the trailing zeros. I cannot seem to get CASTor CONVERT or STR to exclude the zeros.Examples:45.340000 --> 45.3427.700000 --> 27.755.000000 --> 55Is there a function that will do this or do I need to write my own?
View 5 Replies
View Related
Jun 11, 2008
Would someone mind helping me with formatting a string please??
I have a column DECIMAL(4,2). In a stored proc I am selecting this field and converting it to varchar so I can append certain characters to it (this is really irrelevant to my question). However, before appending the characters, I need to remove zeros after the decimal point.
Examples:
3.00 should be 3
3.20 should be 3.2
3.05 should be 3.05
etc
View 6 Replies
View Related
Apr 28, 2003
If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!
Todd
View 2 Replies
View Related
Jun 27, 2006
Hi All,
Can anybody tell me how to remove the trailing zeros from the numeric field?
The data looks like this:
1.0000000
24.0000000
2356.0000000
61.0000000
It should look like this:
1
24
2356
61
Thanks.
View 3 Replies
View Related
Aug 20, 2007
Hi,
I need to delete trailing slashes ('') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat, Rajneesh, Ankush, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...
Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.
View 3 Replies
View Related
Dec 31, 2006
Hi All... I'm using a SQL Server 2005 database. I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out. I guess I can understand why... But it's a pain dealing with it. As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet. What's the easiest/best way to get rid of those spaces. Geez, it'd be real cool if I could put something in the SELECT statement. Any thoughts? Thanks much!! -- Curt
View 1 Replies
View Related
May 18, 2008
In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.
Thanks for the info.
View 1 Replies
View Related
May 19, 2008
How can i add two trailing decimals if there is no decimals:
for example if it is just 1, then make it 1.00
if it is 1.12 then leave it as it is.
can you please help.
select cast(ordernumber as varchar(10)) + '00' from ordertable
Thanks for the info.
View 1 Replies
View Related
May 28, 2007
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.
View 6 Replies
View Related
Oct 11, 2006
Simple scenario. I have a DB table with a money column. SSIS package with a data flow task which has an OLE DB source and flat file destination. I want the single money column from the database into the flat file.
If a (monetary) value in the table is 123.50, the I want 123.50 to appear in the file, not 123.5. Any ideas how I do this? I've tried all available data types, set the scale to 2 where available but nothing has an effect.
Any thoughts?
Greg.
View 1 Replies
View Related
