Truncation Error Arrising From Quote Escaped Quotes In Quote Encapsulated Strings
Feb 15, 2006
Hi All,
I'm having trouble importing a CSV file into SQL using SSIS. The trouble seems to stem from truncation and from quote encapsulated fields.
Firstly it's worth noting that some of the data within the quotes contains the separator. E.g.
12,"some text, and a comma",34
Thankfully SSIS seems to cope with that by specifying " as the Text Qualifier. My next problem was that the SSIS Import and Export Wizard gave an error: "failed because truncation occurred". But I fixed this by specifying the OutputColumnWidth for the NCHAR and NVARCHAR type columns to be the width from the table definition.
But now I have another problem with the length of fields. Consider the following where the center column is NCHAR (22)
101,"some text, and a comma",303
102,"some ""quoted text"" bye",303
The first row has the correct width (once the encapsulating quotes are stripped out), but the second row does not, because it seems that when I exported the table the export wizard escaped the quote characters within encapsulated strings using quote characters. So I am back to getting the "truncation occurred" error. How can I get around this within SSIS?
Cheers,
Tim.
View 1 Replies
ADVERTISEMENT
Jan 18, 2001
I have 2 SQL 6.5 databases on separate servers. Server A replicates a text field into a table on server B.
On server A the field contains text similar to THIS IS FRED'S HOUSE. After replication to Server B it looks like THIS IS FRED''S HOUSE. The distribution database also has it as THIS IS FRED''S HOUSE. Using ODBC trace I cannot see the value being passed in the text field as it is displayed as a question mark e.g. ?.
How do I fix the problem ?
Thanks
View 1 Replies
View Related
Jan 16, 2007
I know this issue has been raised here before, but I have a different angle/question on it.
Flat File source, SQL Destination. No column from the source file has a consistent real width, just consistent to be less that the SQL column.
Issue:
CHAR(12), SMALLINT, CHAR(78), BIGINT
"100012",0,"This is the ""Memo"" of the record, it needs to be imported.",12
This row errors out due to the double quotes around the word MEMO. Additionally, the entire package stops processing due to there being a comma in the memo after the quote encapsulation is escaped.
Has this issue been addressed in any of the SP's? If not, how about this thinking:
1.) Character index the pattern ," and take the value of the first instance of the pattern.
2.) Character index the pattern ", and take the LAST instance of the pattern (ignoring the pattern match after the first column).
3.) Replace all double qoutes between the values of the indexes with single quotes.
4.) Proceed with the Comma separated, quote encapsulated import.
Any thoughts? Additionally, if this seems like it may work, does anyone have a clue how to program this in SSIS, or would I be better to run my files through a VBScript parser to accomplish this first?
Thanks in advance.
View 6 Replies
View Related
Jul 9, 2007
I have a Foreach loop that dynamically builds a query that gets submitted to a Progress 9.1d database. The "SELECT" portion of the query is static - the WHERE clause is built dynamically each pass through the for each loop. The vast majority of the columns have a dash ("-") in their names, so the query passed to progress has to have double quotes surrounding the column names. Not a big deal you would think. If I am retrieving 3 columns, my string would look like "SELECT ""manager-name"", ''"employee-name"", ""date-of-hire"" FROM PUB.EmployeeTable" - with the columns requiring quoted identifiers escaped with double quotes. My script task adds some additional info to the WHERE clause based on other conditions. Once my script task has built the query string, it assigns that string value to my Dts.Variables("SqlString") variable.
I can put a MsgBox() call in the Script task to display my query once it has been built (displaying Dts.Variables("SqlString").Value) - and it looks fine. Using the above example, it would simply be: SELECT "manager-name", "employee-name", "date-of-hire" FROM PUB.EmployeeTable.
So - My foreach loop is set up so that the query command is pulled from my Dts.Variables("SqlString") variable. It fails anytime my query has a column that requires quotes around it. I was confused, since I could take the exact query string displayed by my test MsgBox() and validate it's syntax against our Progress database (and even run it) and it worked fine.
So - I then inserted a breakpoint to break on PreExecute for the DataReader task (next task after Scrip task) to view the value of the Dts.Variables("SqlString") variable just before it gets assigned to the DataReader's Sql Command property. That's when I noticed that the value of the Dts.Variables("SqlString") in the Watch window actually showed that the "double" double quotes I had entered in the SELECT clause (to escape to one double quote) were being replaced with " (slash quote). SO - in the Watch window, my Dts.Variables("SqlString").Value reads as follows:
SELECT "manager-name", "employee-name","date-of-hire" FROM PUB.EmployeeTable.
Obviously, that query is not going to fly via SQL-92/ODBC connection to our Progress database. I've tried everything I can think of - even trying to replace "" with "" in the Foreach expression editor where the Sql Command of the data reader gets assigned. I'm at a total loss. Why would SSIS plug in what looks like C# character escaping syntax when all the expressions are supposed to be VB.NET? I've already successfully run several packages that query our Progress databases that do NOT use the double quotes in the SELECT clause and they work fine. Does anyone have any ideas??
View 3 Replies
View Related
Oct 3, 2007
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
View 1 Replies
View Related
Jun 28, 2007
I have a ssis package created in VS2005 that transforms a SQL query result into an excel spreadsheet. The DataFlow uses a DataReaderSource to query the data and an ExcelDestination to transform the data. The problem is that in the resulting spreadsheet any columns containing string data have a single quote inserted in front of the data. So a string like [My data] becomes ['My data]. When I preview it in the Excel Destination Editor it looks right (without the single quote). What is happening here, is it a bug or does something need to be configured. I checked all the properties that I could find. This is working nice, if I could only overcome this problem.
View 1 Replies
View Related
Sep 2, 2014
I'm trying to create a Character string so that I can execute dynamic SQL.
The date is going to change.
DECLARE @Select VARCHAR (50)
DECLARE @SQLQuery VARCHAR (500)
DECLARE @PreSelect CHAR (1)
DECLARE @CurrentDate Date
SET @SQLQuery = 'SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH,'
SET @PreSelect = '''
SELECT @Preselect AS PreSelect
If I try this statement which what I really want. I would like to include the Quote with the Select.:
SET @Select = ''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE >''
I get the following error:
Invalid object name 'RDB_DWH_ASSOCIATE_ENTITY'.
View 9 Replies
View Related
Aug 29, 2007
my asp.net application communicate with SQL database, but when I have single quote ( ' ) in my string then it throws error. and it does not insert that string in database. How can I solve this problem . or give me some suggestions on this issue.
thank you
maxmax
View 3 Replies
View Related
Apr 9, 2001
Getting following error when I run this query. How do i handle apostrophe?
update holidays
set name = 'May 13 - Mother's Day',
month = 5,
month_day = 13,
holi_type = 1
where id = 52
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark before the character string ',
month = 5,
month_day = 13,
holi_type = 1
where id = 52
'.
View 5 Replies
View Related
Apr 23, 2014
I ran a large query and exported it to xls. In Excel, I noticed a value of '2.00E+01'. I formatted the Excel cell and then got a value of '20'. I searched for the record in SQL Server 2008, ya I know, ancient. The value is '2E1' which excel is seeing as scientific notation.
Can I use a case statement that when an 'E' is present to add a single prefix quote like '2E1? Or perhaps do it for the whole column? Or put put double quotes around the whole field?
View 2 Replies
View Related
Mar 25, 2008
I have a stored proc that creates a view so I can pass parameters. I need to replace the 120 with the variable @MEA. I can't get my quotes right to make this work.
ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)
AS
DECLARE @Msg varchar(255)
declare @SQL varchar(4000)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1
SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()-120))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)
View 3 Replies
View Related
Nov 9, 2006
How do I insert a single quote ' into a table? For example:Insert mytable values (1,''')I get an error message.Any ideas? The server does not recognize double quote (why? is thistsql, ansi?), otherwise I could have written it as:Insert mytable values (1,"'")Thanks.
View 4 Replies
View Related
Jun 29, 2006
I am using SQL Server 2005, I have a NVarChar parameter called @Text that I use to build a dynamic Where clause for my SELECT Statement.
SET @l_Where = @l_Where + N' AND (StatusDesc LIKE ''' + Replace(@Text, "'", "''") + N'%'')'
This gives me an invalid column error.
How do I replace the single quotes in @Text with 2 Single Quotes?
TIA,Jason
View 1 Replies
View Related
Mar 27, 2001
How do I update a record with a string containing a single quote ' ???
SET Row=' charlies' dilemma ' doesn't work since the string gets cut in half
View 1 Replies
View Related
Nov 24, 2004
Hi everyone -
Here is the setup -
new install, same old software
only difference is the machine name has
a single quote (apostrophy) in the name...
xyz's
Now - a portion of the application is failing -
the starting of the application runs ok,
and the system is indeed seeing the database
(it allowed the user to log in)
so the connection string seems to be ok - just in that
one part...
does anyone know if the machine name with an embedded
apostrophy will cause the ADO connection string to puke???
vb6
MSDE
thanks
tony
View 1 Replies
View Related
Mar 7, 2005
Hi,
I have a table with a "description" row.
In this row I may have some simple quote (') character.
How can I select them ?
something like :
select * from myTable where description like %'%
thanks
View 3 Replies
View Related
Mar 3, 2006
Hi,
I am trying to concatenate single quotation mark (ie- ' ) in the select query but not succeed.
If I concatenate word India's, how should I have to write the select query..?
Its just query I have not concern with any specific database.
View 2 Replies
View Related
Mar 9, 2006
Hi,
i am trying to add a single quote to a string. This is a must because i am making a full select statement in which i need the single quote to compare values. Obviously this breaks my string invalidating my query.
ej:
SELECT avg(tabla.ip_trend_value) as valor, FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) as tiempo
FROM TESTLAB5.dbo.CE02_L21_916AI31_43 tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2
WHERE t2.ip_trend_value = 'Alimentacion Digestores' and t2.ip_trend_time = tabla.ip_trend_time
group by FLOOR(Cast(tabla.ip_trend_time AS FLOAT))
and this will become something like this.
SELECT @TableName = 'TESTLAB5.dbo.'+@TableName
SELECT @SQL = 'SELECT avg(tabla.ip_trend_value), FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2'
SELECT @SQL = @SQL + ' WHERE t2.ip_trend_value = '@NombreVar'and t2.ip_trend_time = tabla.ip_trend_time'
SELECT @SQL = @SQL + ' group by FLOOR(Cast(ip_trend_time AS FLOAT))'
the @NombreVar is the equivalence of 'Alimentacion Digestores'.
is there something i can add or change to make it work ?
View 2 Replies
View Related
Oct 29, 2006
Hi Guys,
I have a problem with one of my apps. The apps was written long long time ago in C++ and uses SQL server 7
The problem is that when the users key in some data with single quote in it the query failed, obviously. Is there any way that SQL Server automaticaly manages this kind of Insert/Update statements ?
Thanks & Best Regards,
Faisal.
View 4 Replies
View Related
Oct 5, 2006
I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.
What would be the best way to work around this, other than read the line and replace it with a space.
View 7 Replies
View Related
May 21, 2007
Hi
I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that 'email@email.com' is replaced as email@email.com.
Thank you
zipfeli
View 2 Replies
View Related
Mar 10, 2008
HI,I anm geting error when i want to store some text which contens single quote like this Hi I am 'santosh'.as i am using text editor which genetates XML data (not pure) so i have used varchar(max) to store the data but it gives error.
Is thier any way to store text with single quote........
urgent plz.
View 1 Replies
View Related
Dec 25, 2003
Hi,
I am beginer to ASP.NET. I want to insert ' ( single quote ) which is entered by the user in a textbox of the ASP.NET web page. As you all know in the insert command the column is ending at that single quote.
View 3 Replies
View Related
Dec 14, 2005
In my asp.net page when I run a query against the database the datagrid get populated with only the records that starts with the first letter for example A good day. But none of the records that starts like this "A fine day" or "A nice day" displays in the datagrid. I tried to replace the Double Quote but it still returns only the records without " quotes. My query looks like this:
Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like @ArticleHeading) ORDER BY [Articles].[ArticleHeading]"
View 6 Replies
View Related
Mar 17, 2001
I WANT TO INSERT A SINGLE QUOTE IN TEXT AS "ABC'S"
INSERT INTO TABLE1 (CODE,NAME) VALUES ('A001','ABC'S')
View 1 Replies
View Related
Dec 14, 2005
Some of my records starts with a double quote, when I do a search for all records starting with A I can get all except the ones that start "A fine day ...
View 4 Replies
View Related
Sep 22, 2006
Hi all
How to enter single quote character in a sting column for eg: Channel's
Thanks
View 6 Replies
View Related
Sep 20, 2005
How do I get a single quote (') in a NVARCHAR string in MS SQL Server?e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''Obviously this is invalid as the single quote before "blah" would end thevarchar string.How do I get round this?
View 8 Replies
View Related
Nov 15, 2006
Hi
Declare @Customer varchar(255)
Set @Customer = Single quotes + customer name + single quotes
Select Customerid from Customer Where name = @Customer
I have a query written above, but i was not able to add single quotes to the set statement above. Can i know as how to go about it?
Early reply is much appreciated.
Thanks!
View 15 Replies
View Related
Apr 2, 2007
i got this error when i was trying to deploy the report... in the preview mode report is working good....
D:
eport1.rdl The expression for the query €˜dataset€™ contains an error: [BC30648] String constants must end with a double quote.
can any one gimme the solution
View 11 Replies
View Related
Oct 24, 2008
I'm cleaning up a column in my table and getting rid of special characters.The only think I can't get rid of with the REPLACE function is single quotes.I'm doing aUPDATE TableSET Column = REPLACE(Column,'''','') --that's four single quotes then two single quotesBut the single quotes in my column wouldn't go away.I know that
DECLARE @string varchar(50) = 'test''s strings'SET @string = REPLACE(@string,'''','')SELECT @string
View 19 Replies
View Related
May 5, 2008
Hi There,
I can't figure out what is causing this error: can you please have a look at my code and let em know what is wrong or rework it for me:
="SELECT ARIBC.cntbtch, ARIBC.btchdesc, ARIBC.AUDTUSER, ARIBC.AUDTDATE, ARIBC.AUDTTI, GLPSOFTMAP.PSPRODUCT, GLPSOFTMAP.PSDEPT, GLPSOFTMAP.PSACCOUNT, GLPSOFTMAP.ACCTID, ARIBH.CODECURN, ARIBH.INVCDESC, CONVERT(varchar,ARIBH.FISCYR + ARIBH.FISCPER) as PERIOD, CONVERT(varchar,ARIBH.IDCUST) AS ACCOUNT, ARCUS.NAMECUST as ARNAME, CASE TEXTTRX WHEN 1 THEN 'INV' WHEN 2 THEN 'DM' WHEN 3 THEN 'CM' END AS CLASS, CONVERT(varchar,ARIBH.IDINVC) AS TRX_NUMBER, (CASE TEXTTRX WHEN 3 THEN ROUND((ARIBD.AMTEXTN*-1),2) ELSE ROUND((ARIBD.AMTEXTN),2) END) AS ""AMOUNT FROM ARIBC"" INNER JOIN ARIBH ON ARIBC.CNTBTCH = ARIBH.CNTBTCH INNER JOIN ARCUS ON ARIBH.IDCUST = ARCUS.IDCUST INNER JOIN ARIBD ON ARIBH.CNTBTCH = ARIBD.CNTBTCH AND ARIBH.CNTITEM = ARIBD.CNTITEM INNER JOIN GLAMF ON ARIBD.IDACCTREV = GLAMF.ACCTFMTTD INNER JOIN GLPSOFTMAP ON ARIBD.IDACCTREV = GLPSOFTMAP.ACCTID WHERE ARIBC.BTCHSTTS=3 AND ERRENTRY <= 0 AND BTCHDESC NOT LIKE '%54-8003%' AND ARIBH.IDINVC in ('" &Replace(Parameters!invoicenum.Value,",","','")"
Thanks,
RC
View 1 Replies
View Related
Oct 31, 2007
hi,
how to avoid single quote character in string which comes from textbox in insert query
View 3 Replies
View Related
