OLE DB: How to set the property DBPROP_SSCE_TRANSACTION_COMMIT_MODE correctly?

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Your code looks ok. I use the ATL OLE DB Consumer Templates for this and they also set the colid member to DB_NULLID. This is the only thing I see missing from your code.

Other than this, I would also look at the OLE DB error parameters to get more details.

|||

Every thing is okay except that you are setting the property on IDBInitialize. This is a session related property and so should be set using ISessionProperties.

If the question is answered, please mark it as answered.

Thanks

Raja

|||Thanks Raja,

that was exactly the crux of the matter!

Kind regards,
Andre

OLE DB: How to set the property DBPROP_SSCE_TRANSACTION_COMMIT_MODE correctly?

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Your code looks ok. I use the ATL OLE DB Consumer Templates for this and they also set the colid member to DB_NULLID. This is the only thing I see missing from your code.

Other than this, I would also look at the OLE DB error parameters to get more details.

|||

Every thing is okay except that you are setting the property on IDBInitialize. This is a session related property and so should be set using ISessionProperties.

If the question is answered, please mark it as answered.

Thanks

Raja

|||Thanks Raja,

that was exactly the crux of the matter!

Kind regards,
Andre

OLE DB Command with property expression

Hi,

I am trying to use an OLE DB Command to run a different SQL command for each row in the data flow. I have a script component that builds the SQL command and puts it in a data flow variable, and a property expression in the data flow mapping [OLE DB Command].[Sql Command] to that variable.

The problem is that the OLE DB Command has a validation error, saying that "the command text was not set for the command object", and it doesn't run.

Did I miss anything? should I tell the OLE DB Command that the SQL command would come from expression? or is it a bug?

Thanks.

This validation error can be ignored if you set DataFlow task's DelayValidation to true. However, if you don't set parameters correctly, you may get parameters not bound error later on.

The way I do this is to set up a "dummy" OLEDBCommand first, with the good parameter binding(s), then during execution, the SqlCommand will be replaced by the my real expression value at runtime time - This will work under the assumption that the column metadata does not change overtime, which means, although the SqlCommand will change at runtime, the parameter bindings will remain the same.

Pls try it out and let me know if you have further questions.

Thanks

Wenyang

|||

Thanks.

Unfortunately, my sql commands differ in metadata. I can set it so that the parameters are in the same order for all commands, but some of the commands will not use all the parameters.

anyway, I tried what you suggested with a specific command, but it still doesn't work. the SQL command is deleted whenever I save the package (before running it), and then I get the same validation error.

Isn't there a straight forward solution? I mean, the package knows I set a property expression, otherwise it wouldn't delete the SQL command upon save. If so, why does it through the error? is it a bug?

|||

. It is by design the column metadata has to be the same for each SqlCommand expression value. This is the case not only for OLE DB Command, but also for other dataflow components when using expressions in similar conditions.

. I tried once again, as long as the expression is set correctly, the SqlCommand's value will be set to the expression evaluation result after saving my pkg(before execution). To me there is no bug here. Which version you are on? Did you set "DelayValidation" to true? Can you try again and make sure you set your expression at DataFlow task's "expression" property properly?

Thanks

Wenyang

|||

Thanks, you helped me find (part of) the problem.

I had the sql command set (using a property expression) to a variable that gets its value only during the data flow execution from a script component. the default value for the variable was empty, and when I saved the package it put the empty value into SqlCommand, which is not a valid value.

Setting DelayValidation to true didn't help here, since at the beginning of the data flow execution the variable is still empty, and I get the same validation error at runtime.

What did help is putting a dummy default value to the variable. now it is running without validation errors.

but...

it doesn't change the property of the OLE DB command :-(

the variable gets a different value for each processed row (I check it with a script), but the OLE DB command still runs the default value assigned to it at the beginning.

any ideas?

|||

Your scenario should work as well. Please provide the SqlServer version you are on and the detailed steps of how you set up the expression for OleDbCommand's SqlCommand property and I'll see how I can help.

Thanks

Wenyang

OLE DB Command with property expression

Hi,

I am trying to use an OLE DB Command to run a different SQL command for each row in the data flow. I have a script component that builds the SQL command and puts it in a data flow variable, and a property expression in the data flow mapping [OLE DB Command].[Sql Command] to that variable.

The problem is that the OLE DB Command has a validation error, saying that "the command text was not set for the command object", and it doesn't run.

Did I miss anything? should I tell the OLE DB Command that the SQL command would come from expression? or is it a bug?

Thanks.

This validation error can be ignored if you set DataFlow task's DelayValidation to true. However, if you don't set parameters correctly, you may get parameters not bound error later on.

The way I do this is to set up a "dummy" OLEDBCommand first, with the good parameter binding(s), then during execution, the SqlCommand will be replaced by the my real expression value at runtime time - This will work under the assumption that the column metadata does not change overtime, which means, although the SqlCommand will change at runtime, the parameter bindings will remain the same.

Pls try it out and let me know if you have further questions.

Thanks

Wenyang

|||

Thanks.

Unfortunately, my sql commands differ in metadata. I can set it so that the parameters are in the same order for all commands, but some of the commands will not use all the parameters.

anyway, I tried what you suggested with a specific command, but it still doesn't work. the SQL command is deleted whenever I save the package (before running it), and then I get the same validation error.

Isn't there a straight forward solution? I mean, the package knows I set a property expression, otherwise it wouldn't delete the SQL command upon save. If so, why does it through the error? is it a bug?

|||

. It is by design the column metadata has to be the same for each SqlCommand expression value. This is the case not only for OLE DB Command, but also for other dataflow components when using expressions in similar conditions.

. I tried once again, as long as the expression is set correctly, the SqlCommand's value will be set to the expression evaluation result after saving my pkg(before execution). To me there is no bug here. Which version you are on? Did you set "DelayValidation" to true? Can you try again and make sure you set your expression at DataFlow task's "expression" property properly?

Thanks

Wenyang

|||

Thanks, you helped me find (part of) the problem.

I had the sql command set (using a property expression) to a variable that gets its value only during the data flow execution from a script component. the default value for the variable was empty, and when I saved the package it put the empty value into SqlCommand, which is not a valid value.

Setting DelayValidation to true didn't help here, since at the beginning of the data flow execution the variable is still empty, and I get the same validation error at runtime.

What did help is putting a dummy default value to the variable. now it is running without validation errors.

but...

it doesn't change the property of the OLE DB command :-(

the variable gets a different value for each processed row (I check it with a script), but the OLE DB command still runs the default value assigned to it at the beginning.

any ideas?

|||

Your scenario should work as well. Please provide the SqlServer version you are on and the detailed steps of how you set up the expression for OleDbCommand's SqlCommand property and I'll see how I can help.

Thanks

Wenyang