.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to conver Binary data into bigint

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server

I have table it is having data like thius



I want to conver it into bigint

View Complete Post

More Related Resource Links

Create excel file from Binary data on SqlServer



My requirement is that i upload an excel file then i need to validate that data.


1. Uploading file using using upload control saving binary in SqlServer

2. Then i am creating file from binary data  on Sql Server using below command and then using OPENROWSET to dump data to Sql table

3. Then reading Sql Table row wise and validating data.


Alter Procedure spUploadExcelFile
  @PKID int,
  @BatchID int


	Declare @VarBin varbinary(max)
	Declare @FileName varchar(100)
	DECLARE @sql nvarchar(MAX)
                DECLARE @errMsg nvarchar(MAX)
	DECLARE @ObjectReturn INT
	DECLARE @ErrorSource VARCHAR(255)
	DECLARE @ErrorDesc VARCHAR(255)
	DECLARE @ObjectToken INT

	Select @VarBin  = Content , @FileName = [FileName] from MultilangBinaryData Where PKID = @PKID
	SET @FileName = 'C:\'  + @FileName
	EXEC sp_OACreate 'ADODB.Stream' @ObjectToken OUTPUT
	EXEC sp_OASetProperty @ObjectToken, 'Type', 1
	EXEC sp_OAMethod @ObjectToken, 'Open'
	EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @VarBin
	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
	EXEC sp_OAMethod @ObjectToken, 'Close'
	EXEC sp_OADestroy @ObjectToken  
	SET @sql = 'INSERT INTO dbo.UserBulkData SELECT 0,NULL,'+ @BatchI

How to store binary data as nvarchar(max)?

I’m trying to store a blob of binary data as nvarchar(max) and it gets truncated after just a few bytes. I wonder why and how can I prevent it. I don’t use varbinary(max) because I’m trying to keep existing DB schema intact. Current DB contains a huge chunks of text in nvarchar(max) column and I’m trying to compress (zip) this text and store it back to the same field to reduce DB size.   DB is accessed using Sync Framework (and Entity Framework) on the client side, so actual DB engine is SQL Server Compact 3.5 SP2   Thank you! KonstantinThank you! Konstantin

SQLBindparameter for binary sql data type.

How to use SQLBindparameter for binary data type of SQL? Please help and how to use SQLGetStmtAttr and SQLSetDescField for this?  Cheers!!! Vatsa

Read Binary Data which is nothing but a Zip file and unzip through SSIS 2005 SP2

Hi ALL, I need some help in developing a task. I have a source database which is Oracle and it has a ZIP file stored inside the database in Binary format. When I move this data into the sql server 2005 database I get the data as binary data. Now the task begins with SSIS, I need to read the binary data which gives us a zip file and then unzip this zip file and read the XML data which is present inside the Zip file. I beleive some one might have already developed this task can you share the solution with us. Note: As this has to be moved into production I dont have permission to use third party tools like Cozy roc or install winrar.exe and simpy calling this exe from the execute process task in SSIS.  Raju

ex {"String or binary data would be truncated. The statement has been terminated."}

Hello all,Please I have a Contact form which a user can use to send his or her message to the admin of a website. When all is done, he or she must click on submit button which in turn runs the code below.However, when this done the is an error ex    {"String or binary data would be truncated. The statement has been terminated."}. I would be grateful if someone could help me out to prevent the error.Thank you. Protected Sub txtSendMessage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtSendMessage.Click Dim hdDataSource As New SqlDataSource() hdDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("hotelpauldbConnectionString1").ToString() hdDataSource.InsertCommandType = SqlDataSourceCommandType.Text hdDataSource.InsertCommand = "INSERT INTO contact (emailaddress, name, phone, reason, message) VALUES (@emailaddress, @name, @phone, @reason, @message)" hdDataSource.InsertParameters.Add("emailaddress", txtEmail.Text) hdDataSource.InsertParameters.Add("name", txtName.Text) hdDataSource.InsertParameters.Add("phone", txtPhone.Text) hdDataSource.InsertParameters.Add("reason", cmbReason.SelectedItem.Text) hdDataSource.InsertParameters.Add("message", txtMessage.Text) Dim

"String or binary data would be truncated" and field specifications

Hi all,   i have "String or binary data would be truncated" error when i try to execute an insert statment.   can i find witch field is affected by this error? (for return it to the user)   thank's all

Using WCF Reliable Messaging For Up To 10MB Binary Data

Hello, we have a requirement to send binary data up to 10 MB reliably over wireless and cellular connections while driving.  This means, if it done with 5 MB and the connection fails, would like to restart from where it left off.  I am looking into WCF Reliable Messaging - however, it is not clear to me if this would be appropriate, it talks about making sure a single message is sent, wondering about larger messages, if it would restart where it left off... or, if we should split it into smaller messages, OR, come up with a different way, perhaps a different protocol or write our own client/server with sockets... any feedback or suggestions would be appreciated   thanks   dan

DataTable to a binary field on data base



Is it possible to convert a datatable into binary data and save it into on a binary field on database?

How do I display excel, pdf and word binary data in a gridview?


Hello forum

I'm using the following code to upload images, pdf files, word files and excel to a sql table as binary data.  However, I can only figure out how to display the images but am clueless about the other type of files.  What adjustments do I need to make so that with a button click I can access the other files?  't By the way, I'm using an image control to display the binary data and I'm aware that that will only work to display images, I just don't how to access all the other types of files once uploaded. Thanks

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports System.IO

Partial Class Uploads_UReceipts
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Request.QueryString("ImageID") IsNot Nothing Then

            Dim strQuery As String = "select Name, ContentType, Data from tblFiles where id=@id"

            Dim cmd As SqlCommand = New SqlCommand(strQuery)

            cmd.Parameters.Add("@id", SqlDbType.Int).Va

Writing image binary data to the Response


Hi there,

I've created 2 web user controls. A = User control that displays an image, and B is the user control that hosts A. I'm trying to stream an array of bytes to A, but it seems like the Response.BinaryWrite method completely overwrites any heirachial controls (I can't see any of B's other controls besides the image).

How do I get A to just display the image from the byte stream? It doesn't come from a database, it's an image I created on the fly.

A's code:

        public void UpdateImage(byte[] imgbytes)
            Response.ContentType = "image/jpg";


How to send binary data ( ogg audio file) using a web service?


Hi folks, I would appreciate any help or guidance on this topic:

How can I receive binary data (specifically an ogg audio file) using a WCF4 service?

The general idea is to define and host a WCF service inside IIS that returns an audio file (ogg file)

I have read some info related with using ByteStreamMessageEncodingBindingElement class to achieve this, also I read some topic related on using MTOM to attach a binary file. It seems that it can be several ways or approaches to achieve this.

So any guidance on the themes and/or examples for the better way to solve this point would be very appreciated.

Thanks in advance.



Reading binary Data from Sql Server.

 am trying to read data from a sql 2000 database.

one of the fields is defined as datatype:image, Length:16.

when I am in SQL management studio  and do a select for this one column


Help with tutorial Displaying Binary Data in the Data Web Controls



I am going through the ASP.NET tutorials and my questions is how do you assign an image to a byte array. Because the example in the tutorial is getting rid of an OLE Header. My Database images, does not have an OLE Header. So how do you augment this code to work for my situation:

protected void Page_Load(object sender, EventArgs e)
 int categoryID = Convert.ToInt32(Request.QueryString["CategoryID"]);
 // Get information about the specified category 

CategoriesBLL categoryAPI = new CategoriesBLL();
Northwind.CategoriesDataTable categories = categoryAPI.GetCategoryWithBinaryDataByCategoryID(categoryID);
Northwind.CategoriesRow category = categories[0];

 // Output HTTP headers providing information about the binary data
 Response.ContentType = "image/bmp";

 // Output the binary data
 // But first we need to strip out the OLE header

 const int OleHeaderLength = 78;
 int strippedImageLength = category.Picture.Length - OleHeaderLength;
 byte[] strippedImageData = new byte[strippedImageLength]; 
Array.Copy(category.Picture, OleHeaderLength, strippedImageData, 0, strippedImageLength); Response.BinaryWrite(strippedImageData);


Binary Data Not Writing to Database


I have an ASP.NET app that writes binary data to a SQL database.  It worked fine using SQL Express, but does not ever write the data using SQL Server 2008 R2.  SQL Profiler shows the binary data in the UPDATE query, but nothing happens... no errors, no data saved.  Does anyone know what I'm missing?  Other data types work fine.

My data type is VarBinary(MAX)...


Binary Data Not Writing to Database

This may be more of a dev question, but I have an ASP.NET app that saves binary data to a SQL database.  It worked fine to a SQL Express 2008 database, but does not work on a SQL Server 2008 R2 Standard.  SQL Profiler shows the binary data in the UPDATE query, but nothing is ever actually written to the database.  I cannot find any errors either.  I have no problems writing other types of data.  Any ideas?

String or binary data would be truncated


Hi Folks,

After I inserted a row in my Database (row 27) I started getting this error when I try to insert, update or delete the record in the database.

I've searched about the error on google and it says that I should have a field that crossed the limit of characters.

I have only the autoincrement field, two varchar fields and a text field, neither one of the varchar fields crossed the limit, they arent even close. I found in google that one solution would be turn the field that is having problems in a text field, but the only field that actually can be causing the problem already is of the text type.

The exact error I get on VS 2005 when trying to change something in the row 27 is:

"No row was updated.

The data in row 27 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.

Corret the errors and retry or press ESC to cancel the change(s)."

I need some help guyz, see ya, hugs.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend