How to extract BLOB data from db ??

[sqlcasey]

Hi,

I need to retrieve the entire contents of a TEXT field from one of our tables. I am seeing that this is not a matter of just running a simple SELECT stmt because it truncates the field. I tried saving to a file, casting as various datatypes, but it keeps truncating the result.

Any ideas how to do this?

Thanks

 

[sqlcasey]

Will BCP do this? I tried using BCP, but got this error:

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a con
nection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure may
be caused by the fact that under the default settings SQL Server does not allow
remote connections.

 

[ptheriault]

Your text field being truncated might be caused by size limit a column can have in query anaylzer. you can check and change it in SSMS.
Select Query from the menu
Then click on Query Options

In the Execution\General page there is a parameter to set the maximum size of text and ntext data returned from a select.

For your second post you got that error because bcp uses remote connections and you have not enabled remote connections in SQL Server surface area configuration.




- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

Thanks, I figured it out using bcp.

but fyi

My "textsize" in query options is set to the max of 2147483647 bytes, but it still truncates, whether I output to the screen or a file.

Also, the connection error I got was due to the fact that I was on the inactive node of the cluster, I think. I bypassed the whole command line thing by using xp_cmdshell and running bcp from the query window. Worked like a charm. The output file was *huge*.

My understanding at this point is that working with blobs is different than regular datatypes. You need special tools to work with them, according to this article anyways:

http://www.sqlmag.com/Article/ArticleID/39058/sql_server_39058.html

However, the author does not mention bcp.

 

[ptheriault]

That is true. You should also check out the new varchar(max) and nvarchar(max) datatypes for 2005. They hold up to 2gb of data and they don't have the restrictions on the operations that you can perform. I've been trying to replace all my text fields where I can.


- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

that is good to know, thanks