"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

If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

Chris

|||

this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

it's a feature for my users, indicating whitch field is too long

|||

As far as I am aware, there's no way to determine which column's length has been exceeded.

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

Chris

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

IF LEN(@.MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

END

GO

"Serializing" to the SqlServer Database

Hi.

I was wondering, if there is an easy way to insert and retrieve objects from SqlServer database? Basically this is what i want to do:

- I define a class, which has several dozen members.
- I want to insert many of these objects to the database.
- All classes members should have its own column in the database table.

Do I have to manually constuct a database tables and sql inserts, so I could save the objects to the database? Or is there a way to tell the .NET to construct me a the sufficient database tables and sql statements? Can DataSets used somehow to achieve this result?

I'm not sure what version of the .NET framework you are using. If you are using 1.1, you might want to take a look at the Microsoft Enterprise Library:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

Specifically look at the Configuration Application Block which allows you to serialize objects that can be stored in XML, the registry, SqlServer, or in a custom location if you wish to implement your own custom storage provider.

In the case of Sql Server, defined stored procedures are called to store and retrieve the data from Sql server. The object is serialized when it is stored and deserialized when it is retrieved. The sample code includes scripts that can be used to create the underlying tables and stored procedures.

There is a January 2006 release of the 2.0 Enterprise Library that I'm not as familiar with as I am with the 1.1 version.

|||I am using the .NET Framework 2.0.

But since the Enterprise Library contains this kind of functionality, it'll be good assumption that the CompactFramework itself doesen't contain one?

I thought that .NET has the functionality, because in a Microsoft event, where a MS-Evangelist demostrated some new features of .NET 2.0, this kind of DB-based settings handling cought my eye at some point (sorry Aali, I was a little sleepy;)), I think it had something to do with the browser based ASP.NET website management console. I don't remember the details though...

But it could be the case, that the services of the very same Enterprice Platform have been used with the management console.

Thanks for You reply! (You folks can of course give me more info if you please;))
|||

You can do all the things with .NET (1.1 or 2.0) that the Enterprise Library is doing. The advantage of using the Enterprise Library is that Microsoft has already done the work for you (as well as the testing) and wrapped it up in a nice framework.

Here is an outline of the steps you'd need to take

To save an object:
1. Make sure your class has the [Serializable] attribute applied. Your class must support (either implicitly or explicitly) a no parameter constructor.
2. Popluate an object made from the class with data
3. Call the Serialize() method of an XmlSerializer object (for example). You pass in to this method your object and the stream that the serialized output will be sent to.
4. Construct the proper SQL statements to insert the stream data into a custom database table. The data type of the SQL column the stream data is saved to must be able to accept the character range of the stream as well as it's length. NText would be a good choice.

To retrieve an object:
1. Construct the proper SQL statements to populate a Stream with the data that was stored in #4 above.
2. Call the Deserialize() method on the same type of formatter used in #3 above passing in the Stream object. The method returns an object that must be cast to the class defined in #1 above.

Hope this helps.

<<identity field>>

hi friends,
i have an identity column in my table.(e.x : id )
and it's identity increment is 1.
when i insert a row the id field is 1, and in next record the field is 2....
now , i delete second record(id=2)
and now when i insert a record again , the id column is 3.
i want to record be 2 instead 3.

plz help me.
thanks

This cannot be accomplished "as standard"

When you delete a record, add the id to a "secondhand id" table, then, when you want to insert a new record, see if there is a secondhand id.

The rationale for this approach is that there are no fast methods to iterate the table with id's that may be free when there are large numbers of records

|||

well you can do it but it would not be the best way to do programming......... there can be 2 ways.

Method 1 :

Forget about identity columns.
insert the record by yourself using insert command.
before using insert command retrieve the id (as in your case) of the last record of the table. you can do it like this.
str=select id from <tablename> orderby id
get the results by using this command into a datareader and store the last value in a variable
dim last_id as integer
while dr.read
last_id=dr(0)
end while.
now insert the last_id as the id in your insert statement...

Method 2:

you can reseed(reset) the identity value when you delete a record. i mean to say when you delete a record just reset the identity column to that id(or might be one previous id) for reseting the coulmn see the link below.
http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

hope this could be helpful

'concatenating' several columns together for Insert into an XML column

Hi,

I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.

An example might help:

The Table to insert into has the following columns:

Table name: myTable

ID int

Data1 string

Data2 string

Data3 string

DataXml xml

I am inserting into this table using the following:

Code Snippet

insertinto myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2, Data3,

(select Data4, Data5, Data6)from myOtherTable as inside

where inside.ID = outside.ID forxmlraw)

from myOtherTable as outside

This works but it strikes me as inefficient. There is an additional lookup for each row which seems unnecessary as we are already at that row of the same table.

If it was a simple concatenation, I would do something like 'Data4 + Data5 + Data6', and it seems this is not much different except that instead of concatenation everything is being wrapped in Xml.

Does anyone have any ideas of a better way to do this?

Any help much appreciated.

Holf:

I am definitely not an expert at XML. I too am learning. This is also pretty much the way that I do it. I am also interested in seeing if Martin has a better way of doing this. To me, it looks like you are doing it right.

Kent

|||

Kent,

Thanks for the response. Well, I've checked the query plan generated and I can confirm that there is defintely some nested looping going on. I was wondering if the optimizer would realize what is happening and do some shortcuts.

Given this, I'm thinking it is going to be more efficient to concatenate the XML bits in, e.g. '<row Data4="' + Data4 + '" />' etc.

I don't want to do this because to manipulate XML I'd like to use XML tools, but I'll happily concatenate if it works out to be faster.

Thanks again for the reply. I'm learning XML too, and it does seem as though there's a lot to learn just now!

Holf

|||Be aware that FOR XML will do necessary escaping (e.g. a value like "foo & bar" will be escaped as "foo &amp; bar") while SQL string concatenation will not do any escaping that XML requires.|||Thank you, Martin, I had forgotten about that particular side effect; I have experienced pain from this particular problem a couple of times.|||

Ah yes, that is a very good point.

Well, I may write a function to which I can pass my XML elements and which will respond with some nicely crafted XML. I am considering compiling and importing a .NET assembly specifically for this task, as in .NET there are some nice tools for building up valid XML from raw elements, and these tools take care of escaping invalid characters.

I know this sounds like overkill but I still think it will be more efficient than the subquery approach above. Of course, I will be testing to find out.

Better check that my hosting provider allows upload of .NET assemblies to SQL Server...

Thanks Martin and Kent for your thoughts on this.

|||

Well, I've checked with my hosting provider and they do not allow upload of .NET assemblies. This is not surprising really, given it is a shared database and with the rights to upload assemblies, users could compromise the entire database.

So, I've written a very simple function to escape the necessary characters (of which there are not many from what I read):

Code Snippet

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERFUNCTION [dbo].[fn_EscapeForXml]

(

@.stringToEscape asnvarchar(256)

)

RETURNSnvarchar(256)

AS

BEGIN

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'&','&amp;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'<','&lt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'>','&gt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'''','&apos;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'"','&quot;')

RETURN(@.stringToEscape)

END

Note that the '&' has to be escaped first, or otherwise the '&'s resulting from the other replace operations are themselves replaced.

When I am concatenating my strings to make an XML document, I pass anything which I know may have invalid characters through this function first.

I have had some XML that I have treated in this way returned to a .NET environment. When the XML is deserialized these characters are resolved as they should be into their original form, so it all seems to be working.

##Temp tables

I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.

CREATE PROCEDURE usp_CreateTakeoff
@.iEstimate int,
AS

CREATE TABLE ##Temp_Takeoff
(
Field1 .....
Field2 .....
)

-- Add Structural data
usp_AddStructural @.iEstimateID, 1, 'Structural'
usp_AddForming @.iEstimateID, 2, 'Forming'
...
...
...
GO

Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

Also, I cannot see the table using crystal reports, connecting etc..... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

Any ideas?

Mike BGlobal and local temp tables are created in tempdb.|||Global and local temp tables are created in tempdb.
Now, if I remember correctly, 1 (#) indicates global and 2 (#) indicates local.

So if multiple users executed the stored proc with ##Temp, then each connection would create a table unique to the connection? So multiple users could execute this proc without interfering with each other?

Mike B|||Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.|||Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.
Not the first time, won't be the last I am afraid! :) Thanks for the correction.

Mike