SharePoint - How can i get varbinary data from sql table

Hi,

im trying to get varbinary data from sql table with a external list, but it shows that external list does not support this type of data. Is there any other way to get it? any idea? pls it is very urgent to me. Thnx in advance

thnx kalpana but  as i wrote before, i know the table where the column is but this type of data is not supported by external list. Is there any other way to get varbinary data from sql server to SHAREPOINT?

thnx

one more thing in my mind to try Variable-length binary data n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1  bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

if you are specifying max then your concern should be with varbinary

   database.AddOutParameter(command, "vbCertificate", SqlDbType.VarBinary, int.MaxValue);

Hope will helps!!!!!!!!!!!!!!!

regards,

Hi,

Try this,

private static string getXmlFromTpFields(byte[] tpFields)
        {
            using (var memoryStream = new MemoryStream(tpFields))
            {
                // ignore the first 14 bytes; I'm not sure why but it works!
                for (var index = 0; index <= 13; index++)
                    memoryStream.ReadByte();

                var deflateStream = new DeflateStream(memoryStream, CompressionMode.Decompress);

                using (var destination = new MemoryStream())
                {
                    deflateStream.CopyTo(destination);

                    var streamReader = new StreamReader(destination);
                    destination.Position = 0;
                    return streamReader.ReadToEnd();
                }
            }
        }

Hope helps you.

can you pls explain better? i need to be connected with sql server database from a sharepoint solution? Can I? because the external lists does not support varbinary data type. thnx in advance

In SharePoint 2003 and 2007, there was a table called AllLists which had a column called tp_Fields which contained an xml containing all fields for a specific list.

an example of the xml stored in the tp_Fields column would be this for a SharePoint List with 3 fields:

<FieldRef Name="ContentTypeId" />
<FieldRef Name="_ModerationComments" ColName="ntext1" />
<FieldRef Name="WebPartTypeName" ColName="nvarchar9" />

We have an application that is reading from this column using C# code e.g.

var tpFields = (String) drView["tp_Fields"];

In SharePoint 2010, the datatype of this column has changed to varbinary and contains just some binary data instead!

(I know the ideal/recommended solution was to use the SharePoint web services or SharePoint object model and not relying on the underlying tables but unfortunately we have an existing app and we'd need to make it work with 2010 as well. I hope we don't have to redesign everything!)