Help Needed! Need To Force Leading Whitespace Characters On SQL Import! =
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
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
I am importing csv files using dts with a filedsn pointed to the folder that contains the csv files. Some of these csv's are too big to open in notepad for me to be able to set the datatypes as I see them so I'm letting sql create the tables on import and hope the datatypes get set correctly. I have found some data that is being changed on the import like "0001" has come in as "1". What datatype should I set so the leading zeros are not dropped? Also, what other conversions should I look out for that might get changed inappropriately? Thx
Hello - the very nature of this question seems to make no sense I know - but we received a huge volume of data (29 tables) in flat file format. I first imported them into MS Access because of its portability and it seemed to be more forgiving on imports. Now I have a complete MS Access DB with all tables, so I figured importing to SQL server should be a snap. However, on the import, I had 14 tables import successfully, and 15 failed!
Here is an example of one of the error messages I received: Insert Error, Column 3 - status 6; Data Overflow...this was on a date/time field in access, and here is the data contained in the referenced row/column: "8/19/4999"
the year "4999" is obviously the problem (at least i think), and I have no idea why this successfully imported to MS Access, but not to SQL Server....
what i'd like to be able to do (not the best practice, i know) for now is ignore these types of errors - and just force SQL server to take the data straight from MS Access and replicate it. We received this data from a 3rd party, and there's no telling how many data entry errors like this could be in each table - many of the tables have over 500,000 rows, and i don't want to have to go through fixing each of these errors by hand...anyone have any ideas?
I have a simply SSIS package with following data flow structure:
Flat File Source > Data Conversion > Aggregate > Dervied Column > Ole dB Destination
Basically, this package is executed on daily basis to import sales from a text file into sql server. Now there's a possibility that text file may contain previous sales (occasionally).
My sql table structure enforces data integrity through primary key and therefore my package errors out if there's a duplicate in text file which already exists in sql server.
I'm basically looking for a way to ignore these duplicates and continue to import rest of the file. I need a way to force execution (suppress errors if possible) and finish importing all text file.
I've tried making the maximumerrorcount more than # 50000 and failparent/failpackage on error = false.
Any help is greatly appreciated...thanks
Here's the errors I receive:
SSIS package "Package2.dtsx" starting. 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, Source - SALES_TXT [1]: The processing of file "Z:SALES.TXT" has started. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x402090DE at Data Flow Task, Source - SALES_TXT [1]: The total number of data rows processed for file "Z:SALES.TXT" is 20450. Information: 0x402090DF at Data Flow Task, Destination - SALES [37]: The final commit for the data insertion has started. Error: 0xC0202009 at Data Flow Task, Destination - SALES [37]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Sale'. Cannot insert duplicate key in object 'dbo.Sale'.". Information: 0x402090E0 at Data Flow Task, Destination - SALES [37]: The final commit for the data insertion has ended. Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Destination - SALES" (37) failed with error code 0xC0202009. 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: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0202009. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x402090DD at Data Flow Task, Source - SALES_TXT [1]: The processing of file "Z:SALES.TXT" has ended. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - SALES" (37)" wrote 18750 rows.
I'm having dificulties in loading data into a table coming from an excel file because one of the columns is a text based with an average of 1024 characters... How can i import that column? The excel source always shows me the column as a DT_WSTR of 255 characters...
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?
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?
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!
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?
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?
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.
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
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
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.
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message. How can I store over 16000 characters to sql table field (only one field) with language specific characters?
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.
Hi everybody, I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
I'm trying to suppress whitespace in a drilldown for textboxes that have suppress duplicates applied.
I have a matrix report that is showing whitespace in a drilldown because I am supressing duplicates. Based on what I read in other forums, if I set the ToggleItem to Len(FieldName)=0 that should supress the whitespace, right?
I can see that I have a field in the toggleitem called: Firstname. If I put the value Len(Firstname)=0 in the toggleitem property, then I get the error: The textbox 'textbox21' has Len(Firstname)=0' as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item. I think the code 'Len' is throwing it off.
If I put the value "Firstname" in the toggleitem property, then it doesn't return the error, so I know that firstname is a valid value for toggleitem, but setting the value to firstname doesn't suppress anything.
If someone can tell me how to supress a textbox based on a value, then this may get rid of the whitespace I'm trying to suppress. Any ideas? Thanks...
I am working on a report that has 3 bars per series. I would like to add some gapping or whitespace between each bar in each series. Also how do you deal with overlapping. I know it can be done in Excel, I can't find the property of how to do it in SSRS. Any assistance would be appreciated.
I am not a DBA whatsoever so please bear with me ...
There is an existing SQL table with 3 fields that are to gather numeric data:
Name - Data Type - Length 1. TPID1 - int - 4 2. TPID2 - int - 4 3. BPIN - int - 4
If any of the 3 above are submitted with a zero in front (input = 01234), the zero will not show. (output = 1234)
I thought if I changed Data Type to nvarchar, the data would be read just like text and appear as entered. (I reviewed other table designs and nvarchar is the data type for other similar data.) Saved the table changes and still the leading zero does not show.
I'm trying to write the contents of a csv file to a table, but I am having problems with fields with leading zeroes. Whenever I save as csv I lose the leading zeroes. Does anybody know how to prevent this?
I would like to add leading zeros in the date. Thsi is my existing procedure, it adds leading zeros, but it formats using "yyyy/mm/dd", instead of "yyyy-mm-dd" Select Id, Title, CONVERT(VARCHAR(10), ModifiedON, 111) --CAST(YEAR(ModifiedOn) AS VARCHAR(4))+'-'+CAST(MONTH(ModifiedOn) AS VARCHAR(2))+'-'+CAST(DAY(ModifiedOn) AS VARCHAR(2))as ModifiedOn From ActiveAds Where Row between @startRowIndex And @endRowIndex
I need to push it into a table that accepts whole numbers. (none of the results actually will have a decimal like .05 or 1.5, all .00 will be 0) What I need in the results is whole numbers, but if the results are 6.00 I need 06 to go into the table. I know a straight int convert will drop the decimals, and I could use a csae to set the .00 to 00, but what would be the best way to change the 6.00 to 06? THanks
