September 14, 2011 at 12:18 pm
SJanki (9/14/2011)
Can the currently stored data in the nText field in 2k5 be used in Acrobat? If not, the data will not suddenly 'work' if you move it to a VarBinary, it's already been corrupted as far as Acrobat is concerned. If it does work (which would surprise me), then you'll be able to explicitly copy it to the new column.
But nText is character data, so it cant open in Adobe Acrobat.... that is the reason I am trying to convert it to image/varbinary(max)
I am not sure if a file stored in sql server in nText type can be displayed. I'll have to check that.
I'm apparently not being clear. The binary data tha Acrobat needs is already GONE, when it was placed into the nText. This data will never work in Acrobat again, it's been modified and will not function again.
You need to create a VarBinary column and directly reload all the documents that were already included. This data is damaged. Corrupted if you will due to the datatype it was placed into.
I know of no way to restore this information back to the original state other then possibly taking the nText data, dropping it into a word file, resaving as .pdf, and then resaving THAT into a VarBinary/image column. Even then, you're going to lose a lot of the original formatting and the like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 14, 2011 at 12:20 pm
Kevin Dahl (9/14/2011)
Is it possible that in the ntext -> nvarchar conversion something got truncated? nvarchar has a max of 4000 characters, which is not an awful lot of space for most binary data.Try checking the length of your ntext column to ensure that you're not losing data. DATALENGTH() should work for that.
There are ways to convert directly from ntext to varbinary as well:
VARCHAR(MAX) and nVARCHAR(MAX) are the new preffered data types to exchange out nText for. They can hold BLOB data equivalent to nText, and have more support for string functions then the old nText did. IE: You can use LIKE against Varchar(Max) instead of being forced to use contains().
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 14, 2011 at 12:50 pm
<quote>I'm apparently not being clear. The binary data tha Acrobat needs is already GONE, when it was placed into the nText. This data will never work in Acrobat again, it's been modified and will not function again.You need to create a VarBinary column and directly reload all the documents that were already included. This data is damaged. Corrupted if you will due to the datatype it was placed into. I know of no way to restore this information back to the original state other then possibly taking the nText data, dropping it into a word file, resaving as .pdf, and then resaving THAT into a VarBinary/image column. Even then, you're going to lose a lot of the original formatting and the like.[/quote]
Hi, I got it now, what you are saying. Thanks!
Actually the file is uploaded to an nText column in another application that is a 3rd-party software and they cant change the datatype!! I suggested to store the files in a file-share I'll get the binary data from there...but looks like even that is not possible.
I'll try to convince the other team to re-load it in varbinary.
September 14, 2011 at 1:27 pm
Kevin Dahl (9/14/2011)
Is it possible that in the ntext -> nvarchar conversion something got truncated? nvarchar has a max of 4000 characters, which is not an awful lot of space for most binary data.Try checking the length of your ntext column to ensure that you're not losing data. DATALENGTH() should work for that.There are ways to convert directly from ntext to varbinary as well:http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
I checked the data-lengths , all are same.
In the website its mentioned-->
In SQL Server 2008, these conversions are even more easier since we added support directly in the CONVERT built-in function. The code samples below show how to perform the conversion(s):
declare @hexstring varchar(max);
set @hexstring = '0xabcedf012439';
select CONVERT(varbinary(max), @hexstring, 1);
set @hexstring = 'abcedf012439';select CONVERT(varbinary(max), @hexstring, 2);
But when I tried it I got the error Error converting datatype varchar to varbinary
September 14, 2011 at 1:47 pm
Maybe as Kraig suggested the data is corrupted in the 2005 db before you're converting it? Or perhaps the 3rd party app may be encoding it somehow?
Are you able to confirm that the ntext field in your SQL 2005 db contains the proper pdf document?
I did some tests on my end with a geometry field and was able to convert it from geometry -> hexadecimal text -> ntext -> nvarchar -> varbinary -> geometry, and have no issues with the resulting data.
September 14, 2011 at 2:01 pm
Evil Kraig F (9/14/2011)
Kevin Dahl (9/14/2011)
Is it possible that in the ntext -> nvarchar conversion something got truncated? nvarchar has a max of 4000 characters, which is not an awful lot of space for most binary data.Try checking the length of your ntext column to ensure that you're not losing data. DATALENGTH() should work for that.
There are ways to convert directly from ntext to varbinary as well:
VARCHAR(MAX) and nVARCHAR(MAX) are the new preffered data types to exchange out nText for. They can hold BLOB data equivalent to nText, and have more support for string functions then the old nText did. IE: You can use LIKE against Varchar(Max) instead of being forced to use contains().
Indeed sir, you are correct!
I'm stuck in a pre-MAX world for most of my work, sadly.
September 15, 2011 at 12:27 pm
The PDF file data in the nText column of the 3rd-party application looks like this-->
begin 660 O M)5!$1BTQ+C0-)>+CS],-"C<R(#`@;V)J#3Pa+TQI;F5A<FEZ960@,2],(#8R M,3,W-B]/(#<T+T4@-3(Y-30O3B`Y+U0@-C(P.3,Y+T@@6R`T.#@@,3@U73X^
I think it is encoded , because it is starting with begin 660..
Thoughts?
Also, I found that the 3rd-party application has the feature to display the file. Not sure how they are doing it.
September 15, 2011 at 12:32 pm
SJanki (9/15/2011)
The PDF file data in the nText column of the 3rd-party application looks like this-->begin 660 O M)5!$1BTQ+C0-)>+CS],-"C<R(#`@;V)J#3Pa+TQI;F5A<FEZ960@,2],(#8R M,3,W-B]/(#<T+T4@-3(Y-30O3B`Y+U0@-C(P.3,Y+T@@6R`T.#@@,3@U73X^
I think it is encoded , because it is starting with begin 660..
Thoughts?
Also, I found that the 3rd-party application has the feature to display the file. Not sure how they are doing it.
That looks like UUEncoding, I'm not sure how you'd decode that in SQL. There are command line utilities to uudecode data though, so if you're using SSIS maybe you could run the data through that as a part of your process?
September 16, 2011 at 1:52 pm
That looks like UUEncoding, I'm not sure how you'd decode that in SQL. There are command line utilities to uudecode data though, so if you're using SSIS maybe you could run the data through that as a part of your process?
Hi, I am using SSIS, I tried to convert data in Data-conversion transformation but not sure ...because it cant be converted to image and in SSIS i did not find anything like de-code.
When you say-->you could run the data through that as a part of your process,
what does that mean?
Because the users have the PDF file in a server, I am trying to propose a solution where I save the binary image of the pdf file(from the file-server) in the sql server->(varbinary(max)) and display the pdf-file from this column and it also works very fine with Full Text searching. I already did this in the test-environment.
(The job to get data from the 3rd party application will run every-night and then I 'll schedule the job to get the binary images of the files to the sql-server)
September 17, 2011 at 1:01 pm
SJanki (9/16/2011)
That looks like UUEncoding, I'm not sure how you'd decode that in SQL. There are command line utilities to uudecode data though, so if you're using SSIS maybe you could run the data through that as a part of your process?
Hi, I am using SSIS, I tried to convert data in Data-conversion transformation but not sure ...because it cant be converted to image and in SSIS i did not find anything like de-code.
When you say-->you could run the data through that as a part of your process,
what does that mean?
Because the users have the PDF file in a server, I am trying to propose a solution where I save the binary image of the pdf file(from the file-server) in the sql server->(varbinary(max)) and display the pdf-file from this column and it also works very fine with Full Text searching. I already did this in the test-environment.
(The job to get data from the 3rd party application will run every-night and then I 'll schedule the job to get the binary images of the files to the sql-server)
I was thinking you could use an SSIS execute task component to run the uudecode command line application (see here[/url] for a windows implementation of uudecode - I've included it as it may be useful to you for testing), but that probably wouldn't be very efficient as you'd have to run that task for each row of data.
A better option may be to use an SSIS script task and find a simple C# or VB implementation of uudecode (I found a C# one here, for instance) - that would probably work better. You could probably even implement it as a CLR function on your SQL server if you wanted, the code looks pretty straightforward.
Hope that helps!
September 18, 2011 at 7:39 pm
<quote>A better option may be to use an SSIS script task and find a simple C# or VB implementation of uudecode (I found a C# one here, for instance) - that would probably work better. You could probably even implement it as a CLR function on your SQL server if you wanted, the code looks pretty straightforward.</quote>
I did use this code last friday. The nText (uucoded) column I had from the source database, I retrieved it in my c# code and then I applied the c# implementation of uudecode , as given in the link, and then converted it to byte-array, then tried to open it as PDF file but I got the same error, Adobe can not open this file, it is damaged or is not a type that can be opened...
September 21, 2011 at 8:21 am
It could be that they're doing something in addition to the text encoding, or maybe they've modified the original to create their own encoding method. You're probably best off to contact the company and ask what your options are with regards to that field.
September 21, 2011 at 8:40 am
Kevin Dahl (9/21/2011)
It could be that they're doing something in addition to the text encoding, or maybe they've modified the original to create their own encoding method. You're probably best off to contact the company and ask what your options are with regards to that field.
Hi Kevin, I thought that I'll ask them what alogrithm they are using for decoding, but the client people told me its a long process to contact them!!!!
finally , I am getting the Images of the PDFs from a file-server and storing it in the SQL Server.
Due to firewall I cant open the file directly from the file-share, and also as I am using Full Text Search I needed the file-image in the SQL server.
So everything is all set now!!
I really appreciate your interest and your responses on this.
Thanks - Janki
September 21, 2011 at 10:06 am
prvmine (9/9/2011)
Yes you can transfer data from one server to another with an SSIS Package.Would suggest creating the package with SQL Server 2008 BIDS and deploying to the SQL 2008 instance-pulling data from the 2005 instance rather than pushing data to the 2008 instance.We have several 2008 SSIS pkgs pulling data from sql 2000 & 2005 to 2008 - no issues.
Hi,
I saved the SSIS package Integration Services-->in FileSystem and scheduled it as a job in SQL-Jobs.
When I run the package from Integration Services-->FileSytem it runs fine, no errors
but when the scheduled job runs it gives the error-->
(I am pulling data from 2005 to 2008, package is installed in sql server 2008 and the job is running here. It gives errro login failed for 'The source database ' In the ssis package I have the connectino manager for the source database but not sure why it says login failed..in the job)
Code : 0xCo202009 Source :connectionmanager 'connection manager to the source database'
Description : SSIS Error code DTS_E_OLEDBError ........
An OLE DB Record is available
Source :Microsoft SQL Server Native Client 10.0
Can not acquire connection from connection manager.
--
(Also, when I saved the package in the File System, I selected, 'Do not save sensitive information')
Is it because of this, it cant acquire the connection?
September 21, 2011 at 11:12 am
Do any of your data sources in the job (DataSources tab) use sql auth to connect to the instance? If yes, you will need to manually re-enter the passwords in the conn string:
...;Password=xxxxxx;...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply