I know that anything in a CDATA section will be ignored by an XML parser. Does that hold true for the SSIS XML Source?
I am trying to import a large quantity of movie information and all of the reviews, synopsis, etc are contained in CDATA. example:
<synopsis size="100"><![CDATA[Four vignettes feature thugs in a pool hall, a tormented ex-con, a cop and a gangster.]]></synopsis>
Sounds like a good one, no?
The record gets inserted into the database however it contains a NULL in the field for the synopsis text. I would imagine that the reason for this would fall at the feet of CDATA's nature and that SSIS is ignoring it.
Hi, I am trying to create a XML out of sql 2005 database using FOR XML. I need to create XML for tables which may contain data having non-printable ascii characters (1-32 ascii character). I found FOR XML AUTO failes to genrate this XML, but i can genrate XML using CDATA section in FOR XML EXPLICIT. As following querie works fine for me.
SELECT 1 AS tag NULL AS parent, template_id AS [Emailqueue!1!user_id], misc1 AS [Emailqueue!1!!cdata] FROM Emailqueue WITH (NOLOCK) WHERE queue_id = -2147483169 FOR XML EXPLICIT
in above query misc1 column may contain some non printable ascii characters.
But i need to store this XML data in some sql XML variable as i need to pass it to store procedure which expects an xml input. While doing following i gets an error saying "illegal xml character"
DECLARE @XMLMessage XML
SET @XMLMessage = (SELECT 1 AS tag NULL AS parent, template_id AS [Emailqueue!1!user_id], misc1 AS [Emailqueue!1!!cdata] FROM Emailqueue WITH (NOLOCK) WHERE queue_id = -2147483169 FOR XML EXPLICIT)
I am doing all this exercise for SQL service broker. For which i even need to process same message using OPENXML on differen database server. Again which will need well formated XML.
I'm using some xslt documents to transform the xml output of my Reports but have come across two curiosities where the xslt filter seems to behave unusually.
Firstly, I need the final saved file to have an xml declaration, which I believe it should do by default. Even if I put omit-xml-declaration="no" in the xsl:output tag I don't get an xml declaration. At present we have a custom job that writes these declarations back into the xml after SRS has saved it.
Secondly and more importantly, I need to have some of my output tags wrapped in CDATA sections. I've tried using the cdata-section-elements attribute, again with no luck.
my XSLT looks something like this (simplified for space)
I'm trying to build a DTSX package that FTP's an XML file to the local file system and then imports it into an existing table.
My "Data Flow" for the package starts with an XML Source component and then goes to Data Conversion component and then to an OLE DB Destination component.
It all executes with out error and seems to work fine, but when I look at the data in the table after I've run the package it seems to have inserted the appropriate number of rows from the XML file but all of the column values are NULL.
All of the data in the XML file is surrounded by <![CDATA[ ]]> and I discovered that if I remove the CDATA wrapper by hand then it inserts the data properly. The only problem is that I'm not in a position to have the data provider remove the CDATA tags and some of the data in the XML file needs the CDATA wrapper or else it will not validate.
Anyone know of anything I can do to get the CDATA to import properly?
Can anyone tell me is it possible to put static data (a string) inside details section of a table? I've tried to find some more pieces of information in the Report Definition Language Specification, but to no avail.
When I put some strings as a detail's cells values they are displayed in the preview window in VS. But thay are invisible when I open my report using ReportViewer in my app. So does it means that the only accepted value of a cell within the details section of a table is a name of the field from DataSet (something like =Fields!FieldName.Value) ?
How can I take this example Flat file and parse out each section to a new flat file? Each section starts with HD (header row)
http://www.webfound.net/flat_file_example.txt
e.g. an example output file based on above (cutting out the first section) would be:
http://www.webfound.net/flatfile_output.txt
Also, I'll need to grab a certain value in each header row (certain position in the 100 byte header row) to use that as part of the filename that's outputed. I assume it would be better to insert these rows into a temp table then somehow do a search on a specific position in the row...but that's impossible? The other route is to insert each row into a temp table separated out by fields but that is going to be too combursome because we have several formats to determine separation of fields based on the row type so I'd have to create many temp tables and many components in SSIS when all we want to do is again:
1) output each group (broken by each header row) into it's own txt file
2) use a field in the header row as part of the name of the output txt file (e.g. look at the first row, whcih is a header row in flat_file_example. txt. I want to grab the text 'AR10' and use that as part of the filename that I create
Any suggestions on how to approach this whole process in SSIS...the simplest approach that will work ?
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
We have stored some special characters in our database and when we extract as XML Auto, Elements it through error illegal characters during import. I am looking and saw CDATA with Explicit option but don't know how to use it with multi tags like:
I am using SQL Server to return a XML result set. I then perform a XSLT transformation on the returned result set to fill in HTML form text and select elements. The data returned includes the & character. This character correctly transforms, however I believe that the & is negatively impacting my form post (one of the form elements disappears from the posted data). How can I get around this?
I have tried both in sql server 2000 and sql server 2005 the following code:
DECLARE @DOC VARCHAR(100) DECLARE @HDOC INT SET @Doc='<datos><texto><![CDATA[línea átona]]></texto></datos>' EXEC sp_xml_preparedocument @HDOC OUTPUT, @DOC SELECT * FROM OPENXML(@HDOC,'datos',2) WITH (texto nvarchar(50))
I always get an error 6603 in SQL2000 and 6602+8179 in SQL2005, wich means it can't parse the 'í' and 'á' characters, though they are in a CDATA. I have tried with <,>,@ characters and it works fine.
I have also tried using 'á' and 'í' without CDATA but I get the same message:
DECLARE @DOC VARCHAR(100) DECLARE @HDOC INT SET @Doc='<datos><texto><línea átona></texto></datos>' EXEC sp_xml_preparedocument @HDOC OUTPUT, @DOC SELECT * FROM OPENXML(@HDOC,'datos',2) WITH (texto nvarchar(50))
I used sql 2000 create a data base at home call 'Mysite' several months ago, there are about eight tables and 30 stored procedures. When I run my asp.net program at home yesterday, and found can't connect to database. The error as follow: "Cannot open database requested in login 'Mysite'. Login fails. Login failed for user 'MINGYANG-MSONXHASPNET'. "
When I open the database, very surpriced to found that my database was suspect and not any item inside it. The suspect database include CommunityStarterKit. But Northwind and pubs database still there?
What happen? Last week I just download new Norton antivirus software, does have any relationship with it? Does data really gone? How to solve this problem?
We lost all the data we had in a table. We restored the db and the transaction log up to a point in time, but could still not recover the table. We eventually only do db restore without the transaction log. We recovered the data in the table in question, but lost all the data entered that particular day. Does any body have a solution or suggestion that can help? I will be greatly appreciated.
this morning, one user asked me that she entered a data (only one record,)on the database yesterday, but later she checked that is gone, I check the database, i am sure the data is now there. how do i retrieve the data back, or how do i know whehter this is user problem? Thanks in advance
Hi Guru,This weekend, I migrated SQL Server7.0 to 2000 and below is thescenarios:1.) At exactly 4:00PM on Sturday 17th, do full backup(T-SQL) and put adatabase into single user mode.2.) Copy both Saturday 4PM and Friday night full dump to the SAN.3.) Delete WinNT4.0.4.) Reinstall Win2003 and service pack3a.5.) Copy Saturday 4PM backup file to the server and do restore and douser ID mapping.This database is in full mode and I do log backup every hour. However,after restoring the database, any changes after the 14th were missing.Now, When I restored Friday night backup, we still recovered data tothe 16th. My question is, why the 17th 4PM backup did not copy anychange from 15th to the 17th Saturday?I still had Sturday backup file, howe can I recover the 17th data? Logexplorer or ApexSQLLog?Is this Disk issue?Thanks very much,Silaphet,
I have encountered problem in the merge replication with conflict caused by foreign key constraint. As I did some reseach that it can be avoided by increase the upload and download batch generations.
My question is how can I recover the data which has been lost?
i make an application to insert data to sdf file and save it
when i colse the application and open it again i find my stored data on the sdf file well
and if i closed the application 1000 times i'll find my data ok
that's ok and that's not the problem
the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data
i realized that management studio delete's the data from my db
Is there any way to get the deleted records from LDF or MDF file. Today by accidentally I executed one query and that dropped my table Inbox(table name only) from database. And in that table there was more 4,000 records, all these lost. So anybody can help me how to recover this table records?. Is there any way to get these? I don't have any backup for this table.
hi, i want to lost data automatically after 3days. but i don't know how to use job scheduling in sql2000 and how to query to add date to datatime column automatically when the data is inserted. Plz help me!
Urgesnt..data lost when run the script.i generate a script a data base then i run it on query analizer in same data base .But problem is data base and all design view is safe but all data is lose.how can recover it.thanks.
I am wondering if there is a correlation between the size of a database and the amount of RAM required in the server. Here's what's going on:
NT4.0 SP 6a, SQL 7.0 2-800 MHz Pentium III Processors 512 MB RAM Swap Files: two, divided evenly between two partitions, min 512 per partition (dynamic) 3- 9.2 GB Drives, RAID 5
SQL Database size: 2.5 GB
The SQL administrators have a scheduled job set to dump the database every four hours, so they have 6 of these dump files per day. Seagate Backup Exec v 6.? backs up those files every evening. Of course, this is a mission-critical application.
As the NT/Hardware administrator, I was tasked to flash upgrade all the hardware that could be flashed. That happened about three weeks ago. Ever since I flashed it, getting the data dumps has been an inconsistent process. Sometimes it works, sometimes it errors out. The error message is " Lost Delayed - Write Data: The system was attempting to transfer file data from buffers to (path). The write operation failed, and only some to the data may have been written to the file."
On and off during the day, you can't even copy a small file on the server without receiving the same message.
Before I flashed the server, it was blue-screening and needing to be rebooted about once every two weeks. Since I flashed it, the server hasn't been rebooted, because it hasn't blue-screened.
The database dumps are about 2 GB. We were thinking that the way the dump process worked was that a snapshot of the data was put into memory before it was written to disk. We thought since the dump size was larger than Physical Memory + Virtual Memory, that that could be causing the error.
What do you guys think? 1. Is dumping the database first the preferred way to get a backup, or should a backup directly to tape be scheduled instead? 2. Do we have enough RAM for the database? 3. Are there special settings we should set for the RAID controller card to maximize throughput for SQL?
Thanks again. I am definitely not a SQL DBA and any information you can provide would be really helpful.
a sql server database always contains two file.*.ldf *.mdf When *.ldf was deleted the database may suspend.we can repair such database through about 20 steps.
if you want repair your database .please contact me with email:cn9526@163.net or msn/email :paulyang8848@hotmail.com
you can also see our homepage http://www.haou.net/sqlother/
HiWhen we issue a DBCC SHRINKFILE with empty option, the data fileis removed and the space is alloted back to the OS.Same way when we issue a an ALTER DATABASE to remove a database,all the data files and transaction log files are dropped.But can these files be recovered again by using any of the diskrecovery tools?Achilles
I have a website that is used to enter dates from a calendar. For example, user A might go to the website and select 20 different days in January. When user A clicks on the Save button the dates are written to the database with an INSERT command. This works fine. However, when user B then goes through the same procedure, say selecting 5 dates that are in the set selected by user A, those dates are properly saved in the database but the rows containing user A's same 5 dates disappear. I suspect this is something simple but I'm new and ignorant. Please help.
I am migrating from 2000 to 2005 and in the process of rewriting DTS to SSIS. So far so good.
I have an import of a flat file that I need to reproduce that works fine in DTS but throws SSIS.
The file contains details of products, and has a Long Description field that may contain lots of different chars - CR & LF amongst them. It's source and destination columns are Text type.
The file is tab delimited, text delimited with double quotes, with row delimiters of CRLF and column names on row 1.
The error SSIS throws is "The column delimiter for column "LongDescription" was not found"
I imagine (perhaps wrongly) that this is because of the extra CRLFs but it worked fine in DTS. It also previews fine, and lets me define the column properties OK in SSIS package designer.
Any help greatly appreciated. I am trying to avoid the obvious thing of replacing those chars in that field at this point as currently the export is shared between a live production server, and my test 2005 rig.
I have Outlook 2007 w/BCM running on XP Pro workstation connecting to a SBS 2003 R2 hosting my SQL 2005 database. I have two problems: First I can get the BCM to connect to the remote data but periodically the connection is lost and many times it requires a reboot to reconnect. Secondly related to teh first, I can never get MS Accounting 2007 to connect to the same server and database from my client.
I have already set up the server to use port 5356 via TCP amd added a hole through my client windows firewall for the same port. Both client and server are set to share files and printers.
Data is being trnasferred from an Oracle view to a SQL Server 2005 table.
Decimals can be previewed in the from the "SQL Command Text Window" but the columns in the target table which are defined as float shows the data being rounded to zero decimal places.
For the Data Source the always use default code page is selected.
I am using visual basic 2008 I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,
when I close the program and build again, the data previosly updated are deleted, and I get empty database?? why is that. do i need to set some copy to.........properties. i have used copy if new.
hi ; I am abut searching in a database that html data stored in it, I want to search something like this: search me And it stored like this: search <b> me </b> I don’t know how to remove html tags on stored procedure,
It will be great if you direct me to a search stored procedure that handle this it rather make the sorting and paging stuff too .
Stored procedure:1 ALTER PROCEDURE [dbo].[insert_DagVerslag] 2 -- Add the parameters for the stored procedure here 3 @serverID int, 4 @datum datetime, 5 @ID int OUTPUT 6 AS 7 BEGIN 8 -- SET NOCOUNT ON added to prevent extra result sets from 9 -- interfering with SELECT statements. 10 SET NOCOUNT ON; 11 -- Insert statements for procedure here 12 BEGIN TRANSACTION 13 INSERT Log ( serverID, datum) 14 VALUES( @serverID, @datum) 15 COMMIT TRANSACTION 16 SET @ID = @@identity 17 END 18
Method who is calling the stored procedure and causes the error1 public void AddDagVerslag(Historiek historiek) 2 { 3 SqlConnection oConn = new SqlConnection(_connectionString); 4 string strSql = "insert_DagVerslag"; 5 SqlCommand oCmd = new SqlCommand(strSql, oConn); 6 oCmd.CommandType = CommandType.StoredProcedure; 7 oCmd.Parameters.Add(new SqlParameter("@serverID", SqlDbType.Int)).Value = historiek.ServerID; 8 oCmd.Parameters.Add(new SqlParameter("@datum", SqlDbType.DateTime)).Value = historiek.Datum; 9 oCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)); 10 11 oCmd.Parameters["@ID"].Direction = ParameterDirection.Output; 12 13 try 14 { 15 oConn.Open(); 16 int rowsAffected = oCmd.ExecuteNonQuery(); 17 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek"); 18 oCmd.Parameters.Clear(); 19 historiek.HistoriekID = System.Convert.ToInt32(oCmd.Parameters["@ID"].Value); 20 21 foreach (HistoriekDetail hDetail in historiek.LstHistoriekDetails) 22 { 23 AddDagVerslagCategorie(historiek.HistoriekID, hDetail); 24 } 25 } 26 catch (Exception ex) 27 { 28 throw new ApplicationException("Fout toevoegen historiek : " + ex.Message); 29 } 30 finally 31 { 32 if (oConn.State == ConnectionState.Open) oConn.Close(); 33 } 34 }
Whats going wrong, i've added the ID parameter my ParameterCollection... so why cant it be found
