Execute SQL Task: String or binary data would be truncated

Hi!

I try to execute SQL Task with simple statement

insert T (comment) values (@.Comment)

where comment is varchar(1000). I map package variable User::Comment (type string) to parameter @.Comment in SQL Task properties. But when length of User::Comment greater than 10 characters SSIS returns errors:

Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (@.Comment)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The similar messages from this forum suggest using the OLEDB connection. I try this method too, but get the same result.

Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (?)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What is it? Is this a bug, I made a mistake or I have SQL Server old version?

I have SQL 2005 (Developer Edition) 09.00.3054

TIA, Max.Aren't you missining the 'INTO' in the insert statement?

|||

Rafael Salas wrote:

Aren't you missining the 'INTO' in the insert statement?

That's an optional clause, technically, but it can't hurt to try it. Maybe the Execute SQL Task is a bit more rigid.

Execute SQL Task Editor - Variables

Hi,

Im trying to do an Insert:

INSERT INTO myTable(column1)

VALUES(..)

How can I use my parameter mapping value inside the SQL statement ablove?

Thank you.

Use ? where you want to use the parameters.

INSERT INTO myTable(column1)

VALUES (?)

|||

My SQL Task looks as follows:

Parameter Mapping:

User::ID Direction: Input Data Type: VarChar ParameterName: ID

SQL SourceType:

Direct Input

INSERT INTO Table1(id, name)
VALUES (1,'john')

How can I use my Parameter Mapping value inside the SQL Insert statement (instead of the 1 I put above)?

Thank you.

|||Try using a parameterName of 0 (zero)

Then:
INSERT INTO Table1 (id, name) VALUES (?,'john')|||

It worked!!

Thanks a lot!!

Execute procedure

Hello,

I have an SQL procedure as follows:

...

INSERT dbo.Levels (LevelName)
VALUES (@.LevelName)
...

LevelName is an input parameter of nvarchar type.

What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?

Thanks,

Miguel

Create a SqlConnection to the database.

Use the SqlConnection as a parameter to create a SqlCommand.

Set the command's CommandType to StoredProcedure.

Add a parameter called "@.LevelName" to the command with the value you want to insert.

Call ExecuteNonQuery on the command.

Clean up.

(The indicated SQL won't return any value.)

|||

I always use something like this (or a variation of, like ExecuteSPReturnDataTable, DataSet, etc depeding on the type of proc)

In a data access layer

1public long ExecuteStoredProcedure(string ProcedureName, Object[] Parameters)2 {3long result =new long();4 SqlCommand command;56try7 {8 Logon();910 command =new SqlCommand(ProcedureName, _conn);11 command.CommandType = CommandType.StoredProcedure;1213if (Parameters !=null){14foreach (SqlParameter paramin Parameters)15 {16 command.Parameters.Add(param);17 }18 }19 result = command.ExecuteNonQuery();2021 }22catch (Exception ex)23 {24 result = 0;25 }26finally27 {28try29 {30 Logoff();31 }32catch (Exception ex2)33 {34 }35 }3637return result;38 }

|||

Hi,

Could you please tell me what are the Logon and Logoff functions?

Could you provide me an example?

Anyway, I created a new code but it works not only with SQL database but also with other databases.

I didn't test it yet but here it is:

1' ExecuteStoredProcedure2Public Shared Function ExecuteStoredProcedure(ByVal procedureNameAs String,ByVal ParametersAs Object())As Long34' Create output5Dim outputAs New Long67' Define the connection string8Dim connectionStringAs ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnStr")910' Construct an ADO.NET provider factory11Dim dbProviderAs DbProviderFactory = DbProviderFactories.GetFactory(connectionString.ProviderName)1213' Create and define the connection14Dim connectionAs DbConnection = dbProvider.CreateConnection()15 connection.ConnectionString = connectionString.ConnectionString1617' Run command18Try1920' Open the connection21 connection.Open()2223' Create command24Dim commandAs DbCommand = dbProvider.CreateCommand()2526' Define command properties27With command28 .CommandText = procedureName29 .Connection = connection30 .CommandType = CommandType.StoredProcedure31End With3233' Add command parameters34If Not (ParametersIs Nothing)Then35 For Each parameterAs ParameterIn Parameters36 command.Parameters.Add(parameter)37Next38 End If3940' Execute command41 output = command.ExecuteNonQuery4243Catch exAs Exception4445' Define output as 046 output = 04748Finally4950' Close the connection51 connection.Close()5253End Try5455' Return output56Return output5758End Function' ExecuteStoredProcedure

What do you think?

Cheers,

Miguel

|||

My preference would be to use the Enterprise Library 2.0 Data Access Application block.

In it there are procedures for ExecuteStoredProcedure.

It is the neatest way since you not worried about the Data Access part and concentrate on the business intricacies of the project.

Let me know your thoughts.

|||

Hello,

This seems interesting.

Does it work also with Microsoft Access databases and MySQL or only with MS SQL and Oracle?

Thanks,

Miguel

|||

Hello,

Any idea how to run a stored procedure with Enterprise Library 2.0?

Any information on this anywhere?

Thanks,

Miguel

|||

Quoting from the help of the Enterprise Library

"

The application block supplements the code in ADO.NET 2.0 that allows you to use the same code with different database types. It includes classes for SQL Server and Oracle databases. These classes contain code that provides database-specific implementations for features such as parameter handling and cursors. In addition, theGenericDatabase class allows you to use the application block with any configured ADO.NET 2.0DbProviderFactoryobject. You can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database. The only requirement is that an ADO.NET 2.0DbProviderFactory class exists for the target database.

"

Therefore you have to write code for MS Access and MySQL in the block to extend it.I saw the code for the block. It had classes for SQL server and Oracle only.

Execute multiple sql commands with one trip to the database.

Ive got a List<sqlCommand> and I want to execute all the commands in it, but only using one trip to the database. I dont need any return values from any of these commands. Most of the commands are update/insert/delete. Sometimes after a insert command there may be a command "set @.NEWID =identity_insert" to facilitate the next insert to a related table, but thats the only other type of command to insert/update/delete.

I am using microsofts enterprise data application block. I want to execute all the commands in the list with just one trip to the database. Would the following code achieve this :

db = DatabaseFactory.CreateDatabase("wlmAdmin");
foreach (SqlCommand sc in saveCommands)
{
db.ExecuteNonQuery(sc);
}

or would this make multiple trips to the database? Im not 100% on whats going on behind the db object. If I create the db Item in the constructor of my class using "db = DatabaseFactory.CreateDatabase("wlmAdmin");" would this mean that every instance of that class would maintain an open database connection throughout its scope/lifetime?

Ive created the class to retrieve all data using a single stored procedure and then when saving, the class builds a List of SqlCommands which as Ive mentioned I want to execute in one trip, to make the number of trips to the database as small as possible. Because of this I don't need the db object maintain an open connection to the database all the time. However It doesnt seem to have any dispose methods so Im not clear on how it operates.

In the case that the code above opens a connection for each command, how would I got about batch executing the queries in one trip?

Thanks,

C

the code sample you provided would make seperate trips to the database for each Command in your saveCommands list. If you need to batch them all together with only a single trip to the database, you might want to consider using a Stored Procedure.

|||

Try with stored procedure.... It is a best solution for your problem...

|||

You can include multiple sql commands just by separating them with semi-colons -- but then it's very hard to track down problems (where did it fail?). You will be much better off using a stored proc as the other posters suggest. Be sure to include good error handling at all steps by checking @.@.error. You will probably want to wrap the whole thing inside a transaction. Be sure also to do these 2 sets:

set nocount on -- this stops needless traffic between sql server an you telling you how many rows were affected, there are other, better ways to get this info

set xact abort on -- the only way to guarantee that a sql error will cause proc termination and a rollback (assuming you've done a begin tran)

|||

Ok, so how do I go about setting up a stored procedure which takes multiple (variable) sqlcommands as arguments.

One of the main reasons Im using sqlcommands is so I can use sqlParameter objects to avoid sql injection, so If possible I want to use the commands, rather than a lot of sql strings seperated by semi-colons.

Im not too advanced on Stored Procedures, so all details are much appreciated :-)

Thanks,

C

|||

Here's a generic "template" -- This examples here will take care of 95% of what you need to do. Note that I may have some typos so if you get a syntax error that's probably why. You don't send the commands as arguments, you put them inside the proc.

Note especially:
- Error handling -- use of @.@.error and @.@.rowcount to determine whether things went bad
- Wrapping everything in a transaction and rolling back in the event of a bad result -- most important!
- Use of OUTPUT variables -- you can use as many of these as you want to return data to the caller -- just be sure to set the parameter type to Output in the caller
- Use of PRINT statement (during debugging)

If you google "sql server stored procedure" you will get many, many examples.

You can write procs in VS or in Enterprise Manager or for that matter in notepad. Unless sql server debugging is turned on (and it isn't in many shops because of permissioning issues) there is no real debugging available

create proc MyProc (IntVar as int, Char50Var as Char(50), VarCharVar as varcahr(255), FloatVar as float ReturnVar1 as int OUTPUT, ReturnVar2 as char(10) OUTPUT) as
set nocount on
set xactabort on

begin tran
declare @.ErrNum int,
@.NumRows int,
@.ErrMsg varchar(255)

--do something--
/* Check error code -- be sure to save it to a local variable first so it does not get overwritten */
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

/* Use print statements liberally when you are debugging to find out what's happening'
/* print 'blah, blah, blah' */

--do something--
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end
select @.NumRows = @.@.rowcount
if @.NumRows <> 1
begin
select @.ErrMsg = 'Error: Expected to update 1 row only but ' + convert(varchar(20),@.NumRows) + ' were updated instead!'
RAISERROR (@.ErrMsg ,16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

etc.
...
...
...
commit tran
go

grant exec on dbo.MyProc to ....user or windows group......
go

NOTE: To change a proc later you can drop and recreate it or use Alter Proc instead of Create Proc.
Alter Proc retains all the permissions

|||

Hi,

Thanks again for the reply. Im still not 100% here. Lets say I have 15 sqlCommands in my List<sqlCommand>. Now I want to send all these to the stored procedure at once how do I do this? There is no way in .net to called a stored prcedure with sqlCommands as parameters, so thats the first thing I dont understand? Each sqlCommand has different Parameters, a different number of Parameters and each operates on different tables.

You say I dont call the procedures as commands, I put them inside the procedure. Does this mean I write the procedures dynamically in my code, call create on them, and then execute them, all from my .net application? I think I must have this wrong.

If I say for example my class has created 2 commands for me to execute :

delete from Table1 where id = @.id (1 Parameter of type int)

update Table1 set uname = @.uname, lname=@.lname where id = @.id (2 parameters, 2 nvarchar, 1 int)

How do I create one stored procedure that will accept both these in one trip to the database and execute them. Again thanks for the feedback so far.

C

|||

staplebottom:

Lets say I have 15 sqlCommands in my List<sqlCommand>.

Either:

1. Make 15 separate calls, in which case you need to use ado to manage your transaction (you said they should all go together). If no transaction issue then just make 15 calls

2. Create 1 proc with all 15 commands and pass in all the parms you need

3. Send 1 call with 15 different 'exec ...' commands, separated by commas -- but then it's very hard to figure out whether anything went wrong and if so, which one.

|||

With that example, I would say you are trying to re-invent the dataadapter class, the dataset class, or a combination of both.

|||

So Im doing things wrong so. My aim was to just hit the database once to select all the data, one stored procedure, which then saves all the data into a dataset with a number of tables.

Then I manually check which tables are changed and create a command for each insert / update and delete. I understand that the dataset has this functionality, but felt that I might be able to reduce the number of trips to the database by implementing this myself for my own classes. So I created a list of commands for the changes to the dataset and thought I might be able to execute them all with one trip to the database. If this isnt possible I suppose I have one more question before I revert to using a few stored procedures (one for each table).

If I convert the sql commands to one long sql string and send it to the database as one command :

1. If I put a value into a sqlParameter say as Varchar and then read it back into a string, will it be safe from causing Sql Injections?

2. Will one long string of sql text e.g (update table set f =2; insert into table1 (test) values ('x') ; etc etc ..), seperated by by semi-colons execute faster than executing the sqlcommands using a trip each time.

3. If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

Thanks,

C

|||

I think you're worrying too much about making trips to the database. Keep things simple. Trying to do everything in one command will only get you into trouble.

staplebottom:

If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

No, I don't believe it will because you aren't creating any context for @.newid to exist in

|||

Yeah I think your right. I think Ill just try use a few stored procedures. Well it was at least a learning experience. Thanks for the feedback.

|||

1. No

2. Yes

3. Yes, btw it'd be SET @.newid=identity_insert(); -- Notice the parenthesis

BTW, the dataadapter will batch up multiple commands into a batch for sqlserver to execute (Reducing the roundtrips). Unfortunately, I see no way of extending/allowing this functionality outside of the dataadapter as Microsoft has marked everything you'd need as notinheritable, etc. Shame.

EXECUTE AT <Linked Server> Question

In a stored procedure, I have the following TSQL statement:

EXECUTE ('Insert Into Federation.dbo.FederationData (FederationData) Values
(?)', @.Buffer) AT [HostBox.abcd.mydomain.com\SQLServer2005,1563];

This works fine. However, I do not want to hard-code the name of the linked server like I have above. I would like to specify it at run-time (because it could be different based on certain situations). I cannot seem to get it to work using a variable to represent the linked server name, so it can be dynamic. Can it be done?

Thanks - Amos

What about something like the following:

DECLARE @.SQL1 varchar(500)

DECLARE @.STR_LINKEDSERVER varchar(80)

SET @.STR_LINKEDSERVER=N'HostBox.abcd.mydomain.com\SQLServer2005,1563'

SET @.SQL1 = N'EXECUTE (''Insert Into Federation.dbo.FederationData (FederationData) Values (?)'', @.Buffer) AT [' + @.STR_LINKEDSERVER + '];'

EXECUTE sp_executesql @.SQL1