.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 store binary data as nvarchar(max)?

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

Error converting data type nvarchar to numeric in Store Procedure execution



When I execute a Store Procedure via 'EXEC sp_XXX @p1, @p2' I'm getting this error 'Error converting data type nvarchar to numeric'.

After diggin into the TSQL code I have found that there's a field (type: nvarchar(200)) breaking the execution and resulting on the error.

If I query the table and the specified field with the same parameters as the Store Procedure the results are ok! If I execute the same code as the Store Procedure with the same parameters values the results are ok. The error only happens when executing the Store Procedure.

Any idea ?

Thanks in advance,

Gustavo Brian


If I cast the field to a smaller size value, as nvarchar(80), it works, but of course with data truncation.


How to store more than 4k bytes of data in SQL CE 4?


I need to store more than 4k of bytes in my SQLCE 4 database. How can I do that with the ASP.NET Web Pages 1.0?

1: The Microsoft.Data provider fails when I try to populate a parameter with more than 4k bytes.
2: Referencing SqlServerCe assembly and using SqlCeConnection/SqlCeCommand/SqlCeParameter throws exception:  SQL Server Compact is not intended for ASP.NET development.

How can I somehow map the generic DbType, which does not have NText as an option, to the ntext column in my database? (SqlCeParameter supports DbSqlType, which has ntext, but see problem #2)

Spider in .NET: Crawl Web Sites and Catalog Info to Any Data Store with ADO.NET and Visual Basic .NE


Visual Basic .NET comes loaded with features not available in previous versions, including a new threading model, custom class creation, and data streaming. Learn how to take advantage of these features with an application that is designed to extract information from Web pages for indexing purposes. This article also discusses basic database access, file I/O, extending classes for objects, and the use of opacity and transparency in forms.

Mark Gerlach

MSDN Magazine October 2002

SQL Server CE: New Version Lets You Store and Update Data on Handheld Devices


Handheld device users need to be able to synchronize with a main data store when it's convenient and, preferably, when the back-end database server isn't busy. SQL Server 2000 Windows CE Edition allows you to build a traveling data store that can be displayed and run on a variety of devices. SQL Server CE supports a subset of the full SQL Server package, and can be used as a standalone server or in tandem with SWL Server and IIS. The architecture of SQL Server CE, along with data manipulation, synchronization, and connectivity issues, are discussed in this article. Topics such as making your data public, choosing the right type of replication, and handling errors are also covered.

Paul Yao and David Durant

MSDN Magazine June 2001

Store Null Data In image datatype in sql server 2005



i wanted to store image in sqlserver database image field and i did. But now in some cases i want to store null value in that image field please let me know how to do this using file uploader.please give complete sample code.


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

Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

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

Unable to store combo box value to data grid view control

Hello,         I am using fw 3.5 SP1 with VS2008. I have bind my object collection with data grid view. I can see different values in combo box inside data grid view. But when I select any one value from combo box the selected value is changed to first value of combo box as soos as user leave the cell of the gird. Can anyone tell me where should I look for this problem so that my selected value remains same as user did select it.   Nilkanth Desai  

Dynamic SQL query cannot store more than 4000 characters even with NVARCHAR(MAX)

Hello, everyone. I am having a rather strange problem when building a dynamic query in SQL Server. I declare a variable of NVARCHAR(MAX) type, and build some queries into it, running them later with sp_executesql. One or two of these queries are so large that they surpass the 4000 char limit that, supposedly, NVARCHAR(MAX) overcomes. However, I still am having the classic problem of storing the whole query inside the variable! I use SQL Server 2008. All the documentation online I checked states that the solution is just using NVARCHAR(MAX) and the problem should not happen. How can this keep happening? Cheers, Ivo Pereira IT Consultant PortugalComputerDoc

Error converting data type nvarchar to numeric.

i create a form using form view tools in asp.net..when i click insert command it shows error..this is code which i use for InsertCommand:<asp:SqlDataSource ID="SqlDataSourceFormView" runat="server"         ConnectionString="<%$ ConnectionStrings:AED_PanasonicConnectionString2 %>"         SelectCommand="SELECT * FROM [Debtor]"        InsertCommand="INSERT INTO Debtor (CompanyName, Desc2, RegisterNo, Address1, Address2, Address3, Address4, PostCode)VALUES (@CompanyName, @Desc2, @RegisterNo, @Address1, @Address2, @Address3, @Address4, @PostCode)">        </asp:SqlDataSource>Error:-Error converting data type nvarchar to numeric.where i have to make changes?

How do I get a filtered collection of MembershipUsers from a data store using custom profile propert

A portion of my web.config file:   <profile enabled="true" defaultProvider="AspNetSqlProfileProvider">      <properties>        <add name="FirstName" type="System.String" />        <add name="LastName" type="System.String" />        <add name="Age" type="System.Byte" />        <add name="Weight" type="System.Byte" />        <add name="City" type="System.String" />        <add name="State" type="System.Byte" />        <add name="RelationshipStatus" type="System.Byte" />        <add name="PostalCode" type="System.String" allowAnonymous="true" />        <add name="FavoriteURLs" type="System.Collections.Specialized.StringCollection" allowAnonymous="true" />        <add name="ProfilePhotoFullPath" type="System.S

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

SharePoint Designer 2010: Business data connectivity metadata store is unavailable

Running SharePoint Server 2010 RTM. Fresh install in production not via the wizard but a vanilla install and bringing services up one by one. Just brought up the Business Data Connectivity Service. Other services currently up are Managed Metadata Service and User Profile Service. Two web applications with services deployed to both web applications. The scenario below holds true when connecting to a site on either web application. 1) Open a site, i.e., root portal site, in SharePoint Designer 2010 2) Click External Content Types. Error returned: "The Business Data Connectivity Metadata Store is currently unavailable" is returned. 3) Click Ok then click New | External Content Type. Error returned "THe server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults...". The Business Data Connectivity Service status in Central Administration is Started. Any ideas?
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