The CLOB is XML data that is > 8k (sometimes > 32k). Any suggestions?
A:
You may have to use the IMAGE type, as the BINARY type is limited to (IIRC) 8000 bytes in SQLServer 2K. The limits for varchar and varbinary increased in SQLServer 2005, so it depends on what your target is. For 2005, if your data is ASCII varchar will work, if it's unicode text use nvarchar, otherwise use varbinary.
If you're looking for sample code, you'll have to give us more information, like what language/platform you're using, and how you're accessing the two databases. Also, is this a one-time transfer, or something that you need to do programmatically in production?
Tim Sylvester
2009-07-13 22:01:22
This will be a regular data load from Oracle 10g to SQL Server 2005.I was hoping to do this in a stored procedure, but am open to a DTS package as well. Eventually, I will need to get the data into an XML data type so that I can use XQuery on the resulting XML. Thanks for your help!
NYARROW
2009-07-13 22:07:59
I've only done this the other direction, but it should be similar. You want the stored procedure to run in Oracle or SQLServer? (i.e., push or pull?)Have you set up a database link?http://www.databasejournal.com/features/oracle/article.php/3442661Once you have a link set up, it's tricky but possible to copy LOBs across it from a stored procedure. You will probably want to use a temporary table on the destination side, though.http://dbaforums.org/oracle/index.php?showtopic=4790
Tim Sylvester
2009-07-13 22:40:51
This has to run on the SQL-Server side. Unfortunatly we can't install the software to push -- I'd much rather run this on the Oracle side if I had the choice! The database link is set up, but I receive an "unsupported datatype" error when I submit an open query.
NYARROW
2009-07-13 23:17:38
What datatype(s) have you tried for the destination column?Have you tried setting up a DSN to copy the whole table rather than individual rows? (Once set up, a DSN can be easily scheduled.)Have you tried an "OPENROWSET BULK" on the remote table? http://msdn.microsoft.com/en-us/library/ms175915.aspx http://sequelserver.blogspot.com/2007/01/texcopy-sql-server-2005.htmlIf you're lucky this works as well on Oracle tables as SQLServer tables, but I don't see any information on that. Worst case is you have to dump to a file and bulk import from the file.
Tim Sylvester
2009-07-14 00:18:39
A:
Unfortunatly I was unable to run it within SQL Server, so I wrote a C# console application to import and parse the CLOB data, then to write out the results to SQL Server.
NYARROW
2010-04-13 23:32:12