July 29, 2014 at 7:43 am
I have a OLE DB Command Task that should update a column of a number of rows in a table.
It works fine with target connections with SQL 2012 (Development Edition and Express).
However, it does not work on a SQL 2008 S3 Edition.
The error returned : ... Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". ...
I used 'sa' for each connection. All other packages in the same project execute fine.
The problem is that the parameters cannot be parsed correctly. Even these simple lines in the 'SQLCommand' parameter cannot be validated:
declare @PrefixComment nvarchar(1024)
set @PrefixComment=?
Setting the property 'ValidateExternalMetadata' to False does not help. The same error is shown at runtime.
Any suggestion?
August 5, 2014 at 2:56 am
Some additional info on this post:
This works ( a single SQL statement )
Update DeliveryConditions
set isdefault=1
where id=?
This does not work ( a multi-line statement)
declare @id int ;
set @id=? ;
if @id != -1
Update DeliveryConditions
set isdefault=1
where id=@id
In an SQL task, I could use a variable as the source type but I need the same type of updates in a OLE DB command ...
August 5, 2014 at 3:17 am
What do you mean with the S3 edition of SQL Server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 3:48 am
I meant a SQL Server 2008 Service Pack 3. I read about service pack issues on the web, so I installed the service pack 3 (on a developer edition) to make sure I had the latest version.
(I have to migrate customer data (SQL 2000 and higher) to at least a SQL 2008 server)
August 5, 2014 at 4:38 am
Not sure why it doesn't work. What if you put the code in a stored procedure and you call that sp with the OLE DB command?
On a sidenote: the OLE DB command is really slow for larger data sets. Consider writing your updates to a staging table and then do a regulare UPDATE statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 5:21 am
Stored procedures are no problem (on the target database in this case).
I wanted to avoid creating objects in the source and target databases (or use linked servers on the staging database (source, target and staging db's are on separate machines)), as source and target db's are from customers...
Thanks anyway for you help.
August 5, 2014 at 8:57 am
lode.vervaeck 33792 (8/5/2014)
Some additional info on this post:This works ( a single SQL statement )
Update DeliveryConditions
set isdefault=1
where id=?
This does not work ( a multi-line statement)
declare @id int ;
set @id=? ;
if @id != -1
Update DeliveryConditions
set isdefault=1
where id=@id
In an SQL task, I could use a variable as the source type but I need the same type of updates in a OLE DB command ...
Just to get the understanding, you are using OLEDB Target Destination, and you want to update a column value with a variable value?
If so you can do the following:
1) Drop a Derived Column Data flow item
2) Create a new column and Place the variable in expression with appropriate data type.
3) use that column in the target.
hope it helps
August 5, 2014 at 9:23 am
Briefly explained:
I a have source table and a target table that are already filled with the same data, except for a comment field.
In the source table, the comment field is a plain text. In the target table the comment field is a RTF text.
Upon migration, I use a data flow task that
1. reads the source table (id and comment)
2. Add a Derived Column Transformation in which I add fields that contain the prefix and suffix texts that will be used to transform a plain text into rtf comment.
3. Finally I use a OLE DB command that updates the target table rows. In this transform I use the following SQL command:
declare @PrefixComment nvarchar(1024),@PrefixLine nvarchar(100)
declare @SuffixComment nvarchar(10),@SuffixLine nvarchar(100)
set @PrefixComment= ?
set @PrefixLine = ?
set @SuffixLine = ?
set @SuffixComment=?
Update ManufacturingBoms
set Comment=@PrefixComment+@PrefixLine+replace(replace(replace(replace(cast(? as nvarchar(max)),'\','\\'),'{','\{'),'}','\}'),char(13)+char(10),@SuffixLine+@PrefixLine)+@SuffixLine+@SuffixComment
where id= ?
This works fine for SQL 2012 target databases (Development Edition and Express Edition) but fails when targetting a SQL 2008 S3 database.
I chose this solution as several tables contain comment fields that have to be converted. All prefix and suffiex text are defined in variables and are reused through out the packages.
August 7, 2014 at 1:21 am
Honestly, if any data transformation is required, it should be in Derived Columns, trust me on this, people have spends a lot of hours to diagnose the issue, if these kind of logic is written inside hidden in the flows, just like you are doing right now. as per your logic, all the relative data is coming in the data flow.
so i will go to my previous comments i-e
Create a Derive Column data flow, place all the logic in the expression by doing so you will get the updated column you wanted, just use that column in the oledb command.
Simple, readable and scalable.
Make sense?
August 7, 2014 at 1:22 am
twin.devil (8/7/2014)
...Create a Derive Column data flow, place all the logic in the expression by doing so you will get the updated column you wanted, just use that column in the oledb command.
Simple, readable and scalable.
Did I just read scalable and OLE DB command in the same paragraph? 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2014 at 2:02 am
you did, you did ...
you did saw a scalable and OleDB command in a same paragraph 😀
i was actually referring to Derived column thou 🙂
August 7, 2014 at 2:40 am
In this case, it makes sense.
The transformation would be something like this:
@[User::CmtPrefix] + @[User::CmtPrefixLine] + replace(replace(replace(replace((DT_WSTR,2000)[Comments],"\\","\\\\"),"{","\\{"),"}","\\}"),"\r",@[User::CmtSuffixLine]+@[User::CmtPrefixLine])+@[User::CmtSuffixLine]+@[User::CmtSuffix]
I have two problems with this (correct me if I am wrong) :
1. The comments field is a NTEXT datatype, so I have to cast it before performing the REPLACE() function. The maximum value for the length that is accepted is 2000, which is too small.
2. This expression is not excepted. When I replace the field [Comments] by whatever literal string, the expression is accepted. I do not understand this.
--> replace((DT_WSTR,2000)[comments],"\\","\\\\") = OK
--> replace(replace((DT_WSTR,2000)[comments],"\\","\\\\"),"{","\\{") = NOT ACCEPTED
As a workaround, I already changed my packages in that way that the transformation is done in the source SQL. As I can't use parameters, I have to copy and paste the RTF strings in each select...
I have also other circumstances (not in a OLE DB transformation but in a simple SQL Task) in which I would like to test a parameter before performing an update, and this does not work either (again only for SQL 2008 target databases).
As I have to migrate data upon each development iteration, it would have been nice to be able to partially migrate data (updating existing tables in the target database).
August 7, 2014 at 4:25 am
I have two problems with this (correct me if I am wrong) :
1. The comments field is a NTEXT datatype, so I have to cast it before performing the REPLACE() function. The maximum value for the length that is accepted is 2000, which is too small.
Woww and now you have told me you are using NTEXT.
2. This expression is not excepted. When I replace the field [Comments] by whatever literal string, the expression is accepted. I do not understand this.
--> replace((DT_WSTR,2000)[comments],"\\","\\\\") = OK
--> replace(replace((DT_WSTR,2000)[comments],"\\","\\\\"),"{","\\{") = NOT ACCEPTED
This will not work as this will give you truncation error if the text exceed 2000.
As a workaround, I already changed my packages in that way that the transformation is done in the source SQL. As I can't use parameters, I have to copy and paste the RTF strings in each select...
good choice, is a good practice if you have source/target of sql and performance wise is the best. if you are not lucky enough to do so, you can use "Script Component" in that you can do a lot of things if you know how to write C#/VB.Net code.
I have also other circumstances (not in a OLE DB transformation but in a simple SQL Task) in which I would like to test a parameter before performing an update, and this does not work either (again only for SQL 2008 target databases).
As I have to migrate data upon each development iteration, it would have been nice to be able to partially migrate data (updating existing tables in the target database).
not sure what you want to do. care to share any example.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply