Get the list of variables in a package inside a custom component

Hi

I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?

Thanks

Mani

Why do you need a list of variables?

Normally the two things you would do in a component is to validate a variable exists and read or write the value. Both of these can be achieved with the VariableDispenser class that is avilable from the base PipelineComponent object.

For example, in validate I would do something like this-

object obj1 = ComponentHelper.GetPropertyValue("OutputRowCountVariable", base.ComponentMetaData());
if ((obj1 != null) && (obj1.ToString().Length > 0))
{
if (!base.VariableDispenser().Contains(obj1.ToString()))
{
this.PostError(string.Format(Resources.ErrorPropertyInvalidVariableNotExist, "OutputRowCountVariable", obj1.ToString()));
return 1;
}
if (!this.ValidateVariableType(obj1.ToString(), out code1))
{
this.PostError(string.Format(Resources.ErrorInvalidVariableType, "OutputRowCountVariable", code1.ToString()));
return 1;
}
}

|||

Hi Darren

I am developing a oracle source component , I need to get the table or view name which are stored as variables. Iam not getting the ComponentHelper class .

thanks

Mani

|||So you do not need a list of variables, you just need to get the variable value. Use the VariableDispenser. Ignore the ComponenHelper, that is just a wrapper of mine, and in that instance I am just getting the value, nothing more than that.|||

hi thanks darren i got it i used the code

IDTSVariables90 var;

ArrayList tableOrViewName = new ArrayList();

this.VariableDispenser.LockForRead("TableName");

this.VariableDispenser.GetVariables(out var);

foreach (IDTSVariable90 variable in var)

{

tableOrViewName.Add(variable.Value);

}

Thanks

Mani

|||

A minor point but you could save the loop and array. You are only reading from one variable, so you could use LockOneForRead, e.g.

string tableName = "";

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

tableName = variable.Value.ToString();

variables.Unlock()

You should probably check that the Value of teh variable is not null as well, before calling ToString. Always call Unlock as soon as you can.

If you expect multiple tables to be selected, then this would need to be a delmited list in your variable value, you cannot have multiple variables of the same name. You could have a more complex type for the variable value, but I would use a delimited string so it is easier to manage for both design-time setting and also persistance. You can set a string through an expression for example, but not an object.

Get Sql Err Message 15422 when activating application role.

Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).

Developing a desktop application which calls a local instance of ".\sqlexpress".

This app needs to set data base options and add/del various table columns.

When activating the application role, I get the following message:

HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.

Anyone know what this message means?

I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.

Thanks

Gary

You cannot call sp_setapprole or sp_unsetapprole within another stored procedure. These procedures must be called directly (that's what the message means by ad hoc level).

Here's the sp_setapprole topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp

It mentions in the remarks section that: "The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction."

Thanks
Laurentiu

|||Many thanks Laurentiu!!!

Obviously, I will not continue going down a path which is not intended to work. You have save me time and agravitation.

Again, Thanks!!!

Gary|||Hi,

I have being trying to setup application roles in my application. I have been reading a couple of threads (the one with Ian), and am still a bit lost.

When you say that application roles must be called directly, how do you get the 3rd party application to to call this SP via ODBC and if you have to manualy type in the SP with the password, doesn't that allow the user (inputer) access to the database.

Please help.|||

You must make the calls directly from your application code, not by embedding them in a stored procedure and calling the stored procedure from the application code. Your application should issue the setapprole request directly, not call a procedure that calls sp_setapprole.

Thanks
Laurentiu

|||This is a snippet of C/C++ ODBC code I'm using. I have a valid statement handle allocated and is freed upon function return.

sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[0], 0, app, parmlen[0], &cb[0]);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[1], 0, pw, parmlen[1], &cb[1]);

strcpy(buff, "{call sp_setapprole (?,?)}");
sqlrc = SQLExecDirect(hstmt, buff, SQL_NTS);

I have examined the stored proc code and have noticed that it signals this error when the nest level is greater than 1. Is there some code in ODBC that creates a SP on the fly and then calls the SQLExecDirect ?

Many thanks.|||

I am not sure what you are asking for. For ODBC questions, you may want to post on the SQL Server Data Access forum.

If you want to find out how to set an application role withing your application, here is a simple C# example that does that. It assumes you have created the application role in a database named test with the following commands:

create database test

use test

create application role approle with password = 'Password)^'

In the following function, you should replace the YOURSERVERNAME string with the name of your SQL Server instance:

static void sql_test()
{
SqlConnection sqlConn = null;
String strConn = "Persist Security Info=false;";

strConn += "Server=\'" + "YOURSERVERNAME"
+ "\';Integrated Security=true;";

try
{
sqlConn = new SqlConnection(strConn);
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlConn.ChangeDatabase("test");

SqlCommand sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("sp_setapprole 'approle', 'Password)^'", sqlConn);
try
{
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}

This should print your original context and then the context of approle, showing that approle was set.

Thanks
Laurentiu

Get Sql Err Message 15422 when activating application role.

Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).

Developing a desktop application which calls a local instance of ".\sqlexpress".

This app needs to set data base options and add/del various table columns.

When activating the application role, I get the following message:

HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.

Anyone know what this message means?

I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.

Thanks

Gary

You cannot call sp_setapprole or sp_unsetapprole within another stored procedure. These procedures must be called directly (that's what the message means by ad hoc level).

Here's the sp_setapprole topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp

It mentions in the remarks section that: "The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction."

Thanks
Laurentiu

|||Many thanks Laurentiu!!!

Obviously, I will not continue going down a path which is not intended to work. You have save me time and agravitation.

Again, Thanks!!!

Gary|||Hi,

I have being trying to setup application roles in my application. I have been reading a couple of threads (the one with Ian), and am still a bit lost.

When you say that application roles must be called directly, how do you get the 3rd party application to to call this SP via ODBC and if you have to manualy type in the SP with the password, doesn't that allow the user (inputer) access to the database.

Please help.|||

You must make the calls directly from your application code, not by embedding them in a stored procedure and calling the stored procedure from the application code. Your application should issue the setapprole request directly, not call a procedure that calls sp_setapprole.

Thanks
Laurentiu

|||This is a snippet of C/C++ ODBC code I'm using. I have a valid statement handle allocated and is freed upon function return.

sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[0], 0, app, parmlen[0], &cb[0]);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[1], 0, pw, parmlen[1], &cb[1]);

strcpy(buff, "{call sp_setapprole (?,?)}");
sqlrc = SQLExecDirect(hstmt, buff, SQL_NTS);

I have examined the stored proc code and have noticed that it signals this error when the nest level is greater than 1. Is there some code in ODBC that creates a SP on the fly and then calls the SQLExecDirect ?

Many thanks.|||

I am not sure what you are asking for. For ODBC questions, you may want to post on the SQL Server Data Access forum.

If you want to find out how to set an application role withing your application, here is a simple C# example that does that. It assumes you have created the application role in a database named test with the following commands:

create database test

use test

create application role approle with password = 'Password)^'

In the following function, you should replace the YOURSERVERNAME string with the name of your SQL Server instance:

static void sql_test()
{
SqlConnection sqlConn = null;
String strConn = "Persist Security Info=false;";

strConn += "Server=\'" + "YOURSERVERNAME"
+ "\';Integrated Security=true;";

try
{
sqlConn = new SqlConnection(strConn);
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlConn.ChangeDatabase("test");

SqlCommand sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("sp_setapprole 'approle', 'Password)^'", sqlConn);
try
{
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}

This should print your original context and then the context of approle, showing that approle was set.

Thanks
Laurentiu