We have text files that are comma delimited, use double quotes as text qualifiers and sometimes have embedded double quotes. The embedded double quotes are escaped with an additional double quote like: below.
"123","product q"
"124","product ""a"""
DTS 2000 had no problem with this- it correctly parsed the files. The 2005 SSIS file connection manager correctly parses this in preview mode. But when the task is executed the task fails with the message "The column delimiter for column X was not found".
What is the recommended approach for this - we have alot of files in this format.
The error message that comes while I preview the source data:
Error:
"The Preview sample contains embedded text qualifiers. The flat file parser doesnot support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at runtime"
Is there any alternative to remove these text qualifiers from the file. Do we have any utility that can convert these files into text qualifier free CSV file?
Hi I'm using SSIS to export views & tables in SQL 2005 to csv files. My problem is that when I select that I want to use " as a text qualifier, it also uses them to surround non-text fields such as time/date and integer fields.
In SQL 2000 I used DTS packages and they handled the data without any issues.
I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?
hello,i would like to know please if there's any mean to changedelimeters (, or ;) and text qualifiers in a file in a i/o file writtenin vb ex:open #1 for ...write #1 ...thx*** Sent via Developersdex http://www.developersdex.com ***
The files have pipe delimters and double quotes as text qualifiers. I can get the file to import with a bulk insert statement, but it brings in the double quotes in as well. What setting is it that can be set to indicate what the text qualifiers are?
We have an issue with importing a CSV file into SQL where using a double quote " text qualifier is failing. The data is correct but it fails on a particular line, complaining about the qualifier even though the qualifier is in place and previous lines have imported fine.
I explicitly set one column to have text qualifiers in a flat file connection mgr and specified to use double quotes as the qualifier, yet in the output file, the column is not qualified. What did I leave out ?
I have the misfortune of converting a DTS package to SSIS that loads a flat file that has a text fields that can contain embedded text delimiters ("), column delimiters (,) and even new lines (CR+LF i.e.,hex 0D 0A) in it. A sample line from the file is posted here, remember this is just one line though it shows as three lines, since the third field has embedded new line in it:
4,"Sam","EVP; MARKETING PRODUCT MANAGER ""Level I"", Internet Sales / HELP 8005551212",100
If you open in excel it handles it perfectly showing four fields, as below, and this is what I want ( I cannot get it aligned right in the posting, just save the above line in *.csv and open to see what it should be):
4 Sam "EVP; MARKETING PRODUCT MANAGER ""Level I"", 100 Internet Sales / HELP 8005551212"
Now, SSIS errors on the embedded text delimiters and breaks into two or three lines based on which option I chose. I have tried few options based on postings in the forum: a). Using undouble and undoubleout: Does not work when there are embedded column delimiters (,) in the text field b). Modified undouble script posted by lvovg at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1718225&SiteID=1. Handles the embedded column delimiters (,) perfectly, but the embedded new lines (CR+LF i.e.,hex 0D 0A) are breaking it. Since, I am using the ragged right format to read from the file then use transform script on the line by lvovg, the line is already broken by the ragged right format at the embedded new lines, hence does not work.
Right now I am stuck. Can someone please help (anyone from MS) ? I am already baffled at the amount of coding required to convert a very basic ( and working ) flat file load DTS package to SSIS. I am willing to persist bit longer to convert this to SSIS, before I give up and stick with DTS and wait for a fix / workaround.
I'm developing an SSRS report using VS2013 Premium with SSDT - BI v12.0.2430.0. SSRS is hosted on a SQL 2014 instance.The underlying database table has a varchar(max) field which stores text and embedded image data from a third-party application. I can extract this text, save it as an html file and open that in a browser. It renders complete with any embedded image.Meanwhile, the report textbox has its Placeholder Properties, Markup type set to HTML. Yet when I run the report, the text is displayed but any embedded image is not.Is there any approach I can take, perhaps transforming the varchar(max) data to another format in the query for my dataset, that can show the text and any embedded image in my report? Or is it that the TextBox control is limited, not unreasonably, to text only? If so, is there another control I could use, perhaps custom or third-party?
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
I have a DTS package that transfers some columns defined as varchar(8000).
Sometimes, the values in the columns defined this way have embedded CR/LF in them. When they do, DTS does not always transfer the values to the target.
I'm looking into a problem a friend is having, and I'll say right offthe bat that I work with with php and MySQL, and not MS SQL.What he is attempting to do (in MS SQL) is take two database fieldsfrom a table (string fields), multiply them together, and put them intoa third field. This third column in the table has not yet been createdthe time of running the query.If it needs to be multiple queries, that is fine. My first thought isto use a simple ALTER query to add the column to the table, then tocall a UPDATE function which uses a select statement inside of it. I'mnot sure if something like this can even be done.// ------------ Suggested queryUPDATE chrisslu SET 'discquantity' = '(SELECTchrisslu.quantity*chrisslu.nr_of_discFROM chrissluWHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE(str(period,6)>=?Andstr(period,6)<=?)// ------------ End Suggested queryIt starts with an UPDATE, but replaces the value to be set with aSELECT statement. I honestly don't even think this query issyntactically correct, I'm just trying to get the general concept down:).So, question the first: Is this type of query possible? The reasonI'm doing this is because I was told MS SQL has no way of storingtemporary variables... otherwise I would just call a SELECT statement,store the variable, and UPDATE the new field from the variable afterthe ALTER statement.Second question: If it is possible, am I on the right track, or doesit need to be entered in completely different than what I have?Third: Regarding the 'type'. Do I need to do any kind of typecastingor conversion of the fields? Both chrisslu.quantity andchrisslu.nr_of_disc are string fields (that is what I was told, theymay be varchar of some kind). In order to use them in a mathstatement, do they have to be floats, or doubles, or something similar?I appreciate any response, I know this was a long winded question.Chris
Is there any way to embed a video in a report? It doesn't seem like it but I thought I may be missing something. Maybe directly editing the xml code? Thanks.
I have embedded tabs in a text field that I want to import to a destination table.
I was thinking I need to replace the tabs with spaces. REPLACE(character_expression,searchstring,replacementstring) Anybody know how to specify ascii in the character expression.If there is a better way I am open to suggestions, however I do not way to remove this in the raw data but handle at transformation time. Thanks,Larry
I've an embedded image (logo) in my report, and in the preview it looks wonderful, but after deploy the logo doesnt appear in the report (report server).
The url of the dead-link-image is http://hamsql1/Reports$BI/Reserved.ReportViewerWebControl.axd?.
I thought that an embedded image is stored "in" the report, but it seems to be an other place. Is it nessecary to deploy an embedded image too? Is it a problem of permissions (i read it in another threat)? How to set these image-see-permission? Or is there an other problem?
I have a Select statement that was working just fine: string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; I am completely lost here.
I am trying to find something like a light version of SQL Server 2k to store data for an application that will be distributed through a cd.
Since we also have a web front end for this application when the user is online, we prefer that we can reuse the code for connection on both the cd and the web.
Any idea what will work?
I have come accross MSDE 2k but was not able to find any documentation on the pros and cons for this product regarding its capacity, the type of data it can handle, security, ...
I was wondering if anyone had tried to embed sql server express in a .net application. Specifically, I want to use it from within the app only, similar to embedded mysql (libmysqld) or firebird. I've seen some articles on sql server express saying this can be done, however there is no info on how do to this. Anyone know how it might be done?
Beyond my control: I am finding control characters (likely tab) ismaking its way into address fields of our operational system. This ismessing me up when I load the data into our warehouse w/ BCP (fieldsget shifted).Is the a nifty way to strip control characters from data?TIARob
What I'd like to do is use the result of one query as the input foranother. eg:INSERT INTO foo VALUES ((SELECT id FROM people WHERE name = "bar"),10,'foobar') WHEREid = 1;Problem is, MSSQL wants a scalar value. Is there a way around this?Can someone tell me the correct syntax for what I want to do, or is thissomething that will have to be done outside SQL?Err. Hope I've been clear. Thanks for any help you folks can give.
I have a report with background embedded image. I have placed few textboxes on top of them to display data. Every time I set the location of the textbox, it is behaving strangely by rendering at one location one time and changing location next time when it is rendered. I am viewing it in PDF format.
How can I fix the location of textboxes that renders at one location all the time ?
documentation states, that Windows XP Embedded (XPe) is not supported by any SQL Server 2005 Edition (as I intend to use the Epress Edition on that platform, I post to this forum). So, why is that? Is it just not tested or are there technical reasons for it? What are these reasons? What works and what doesn't on XPe? May XPe be supported in any later version?
Hi i have a video file path stored in my sql database and i have an embedded media player in my web page, how can i load up a different video file into the embedded media player based on movie id or movie name, this is the code i have written so far but im unsure if im in the right direction, thanks
I am trying to modify a piece of SQL where I want to get the date difference from todays date and the datestamp on the latest record on dbo.activitym1 table AC1 ..which matches the following criteria..WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number) PS1.number is the master record ID But this doesnt seem to work ..the best result i've been able to achieve is all records from the dbo.activitym1 table that match the where clause and i only want the latest by date heres the code any help would be most appriciatedSelect PS1.number, PS1.brief_description," + " DateDiff(day, PS1.open_time, Getdate()) as 'Days_Open', PS1.company," +" (select DateDiff(day, AC1.datestamp, Getdate())" + " From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.activitym1 as AC1" +" WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number) as 'Days_Since_Last_Assigned'," + " CONVERT(VARCHAR(10), PS1.open_time, 103) as 'Date_logged'" + " From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.probsummarym1 as PS1" +
"where (PS1.assignment = 'PROD - UK CENTRAL CPH COGNOS SUPPORT' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH CONS HEALTH' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH PUBLICATIONS' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH TERRITORY PLANNER' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH VIEWPLUS' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL CHEMICAL PIONEER' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY PROFILES' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY SEARCH' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL GENERIC PLNG & ANLYSER' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE NPF' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE PATENTS' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE RDF' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL MIDAS GENERICS' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING ANALOGUE' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING GENERICS MARKET PROGNOSIS' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING MARKET PROGNOSIS' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING OTC REVIEW' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING THERAPY FORECASTER' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CHEM INTPACK' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CLASSFN' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CORP' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CUSU' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB DSG' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB GPIN' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB INTPRD' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB KNOWLEDGELINK' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB LICENSING' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB LPIN' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB MARK SEG' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB OTHER' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB PRICES FACTORS' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB STANDARDS' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB WHO' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT ACCOUNTS' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT ADMINS' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT EXEC' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA CODING' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA CPMS' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA PROD CTRL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA QUALITY CONTROL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT INTL FIELDWORK' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT IT SOL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PANEL CTRL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA CODIN' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA PROCESSING' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA QRY & SUPPORT' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PRODUCT REFERENCE' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PSO' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT QUALITY ASSURANCE' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT RESEARCH HOSPITAL GROUP' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT WARD' OR " + " PS1.assignment = 'PROD - UK CENTRAL SITTINGBOURNE' )" + " and UPPER(PS1.Status) <> 'CLOSED'
I need to insert data in a varchar column to that when its displayed new lines will appear in the text, that is the field will hold text with multiple lines...
Is it true that replacing embedded sql statements (that insert/update and fetch data from sql databases) by stored procedures improves overall respones time and SQL performance. We have tons of embedded SQL statements in our Visual Basic modules and are debating whether replacing them by SQL Stored procedures will really be worht the effort.
Any insights on this?
Thanks in advance. If possible, please reply to my e-mail.Thanks.
Hello, Im doing research for my company for a project we are about to start, but the more I find, the more Im confusing myself. Maybe someone would be so nice to help me a little.
We need a database solution, either licensing (ISV) or i think maybe an embedded database. if its licensing, its not a problem, but after research, im thinking licensing would be a waste of time or too much and theire not enough information to go by just that.
Problem: Need to develop software for a client where there will be around 300 users using the system (not all at once neccisarrily). We are creating this software for them and they want to re-sell it after completion, but they do not want to make their customers purchase a database for the purchase of their software. (because of all types of licensing). We need the database RUNTIME to run on the customers machine to make the software work.
Is there a RUNTIME license for Independant-Software-Vendor(IVS) for redistribution? or would it make sense to embed a database ( for example, the free edition of sql server;embeeded feature :), or maybe firebird embedded database?) We are developing in Visual Studio 2005.
This might sound confusing, thats where im at. For those of you with more experience, hopefully you understand what im talking about.
Thank you. I have 2 days to find a solution. Ill continue looking and post in thread if i find anything new or clearify my problem.