GETDATE() and the local time zone

I have used the GETDATE() function within an expression to create a directory name based on the current date. I am in the Sydney time zone and the new day's folder name doesn't change until after 11 am - so GETDATE() is picking up the date and not adjusting for the time zone. How do I either set the time zone within the package or make the GETDATE() function look at time zone of the system on which it is run?You'll have to explain, because getdate() returns the system time where it is executed, time zone included. So.......|||Hmm.. that set me thinking about another date issue we have. We run in a citrix environment and I have noticed odd date displays before. This function behaves differently depending on whether I use RDP or ICA. I tested it by logging in under both types of session and creating an expression with GETDATE() in it and then evaluating it. The results were correct for RDP but not for ICA - so it looks like the problem is outside SSIS.

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

Get report server functional is not very clear could someone explain the security requirements?

We have reports server installed using the local system account when we try to access through web browrse we get and error that say that we need to go to the local system and view the event log. In the system event log there are lots of error that are complaining about dcom object not having permission and in the application log we are getting errors also I have tried to find some documentation that explains the set up requirements and have found lots of disconnect documents that do not give a good explanation of how to setup the accounts needed. I have been able to develop some very nice reports use BI studio but can seem to deploy them and we are unable to use report builder. Any help would be greatly apprecaited.. How do we get this enviroment correct...? When report server was installed it was installed using the local account and with the all other defaults. I'm sure that the issue is some sort of security problem but have no idea what I have to do get past this problem.. Also can you point me to a document that has the minium account requirments to be able to run this service? Or any other documentation that may be more complete and less fragmented that online books..

SQL server was installed using mixed security.

Is there a good book that explains this stuff?

I would try opening the Reporing Services Configuration Manager in the Windows Service Identity tab, and change the authentication to either a local windows domain account or an active directory domain account. Then try again and see what happens.|||I still get the following error

EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435be1, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.1399.0, P6 43e29533, P7 0, P8 00005253, P9 00000000, P10 NIL.

|||I don't really know where to go from there. If you have all components running on your local server (SQL 2005, Reporting Services, IIS), I would think using a local Administrator account should work, and then you could back off from there to lower security once you have it working. Maybe it isn't a security issue. Sorry I couldn't be more help.|||

Hello,

I've same error as your's, but before ReportServer works fine and now after changind IP adress I get an error

EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435be1, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.1399.0, P6 443ccc82, P7 ...

No solution, I search !!!!

Do you solve your problem ?

Get report server functional is not very clear could someone explain the security requiremen

We have reports server installed using the local system account when we try to access through web browrse we get and error that say that we need to go to the local system and view the event log. In the system event log there are lots of error that are complaining about dcom object not having permission and in the application log we are getting errors also I have tried to find some documentation that explains the set up requirements and have found lots of disconnect documents that do not give a good explanation of how to setup the accounts needed. I have been able to develop some very nice reports use BI studio but can seem to deploy them and we are unable to use report builder. Any help would be greatly apprecaited.. How do we get this enviroment correct...? When report server was installed it was installed using the local account and with the all other defaults. I'm sure that the issue is some sort of security problem but have no idea what I have to do get past this problem.. Also can you point me to a document that has the minium account requirments to be able to run this service? Or any other documentation that may be more complete and less fragmented that online books..

SQL server was installed using mixed security.

Is there a good book that explains this stuff?

I would try opening the Reporing Services Configuration Manager in the Windows Service Identity tab, and change the authentication to either a local windows domain account or an active directory domain account. Then try again and see what happens.|||I still get the following error

EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435be1, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.1399.0, P6 43e29533, P7 0, P8 00005253, P9 00000000, P10 NIL.

|||I don't really know where to go from there. If you have all components running on your local server (SQL 2005, Reporting Services, IIS), I would think using a local Administrator account should work, and then you could back off from there to lower security once you have it working. Maybe it isn't a security issue. Sorry I couldn't be more help.|||

Hello,

I've same error as your's, but before ReportServer works fine and now after changind IP adress I get an error

EventType sql90exception, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435be1, P4 reportingservicesnativeclient.ni.dll, P5 2005.90.1399.0, P6 443ccc82, P7 ...

No solution, I search !!!!

Do you solve your problem ?

Get OS Group membership

Hello,
Im using the 'xp_cmdshell' to know all users that belong to the local
administrators group and i'm using the 'net use localgroup' to get the output
into my report.
Here is the code and the Output:
set nocount on
exec xp_cmdshell 'net use localgroup'
go
set nocount off
OUTPUT:
output
--
Alias name administrators
Comment Administrators have..
NULL
Members
NULL
--
Administrator
DOMAINXXX\Domain Admins
DOMAINXXX\usera
DOMAINXXX\userb
The command completed successfully.
NULL
NULL
Is there any way to avoid lines where appear:
output
--
Alias name administrators
Comment Administrators have..
NULL
and all the information that is not part of the group membership?
Do you know other way to get this information?
Thanks,
Best regardsIf the builtin\administrators group still has a login in SQL
Server (it is by default but you could remove it),
you can get the members of the local admin group with:
EXEC xp_logininfo 'BUILTIN\Administrators', 'MEMBERS'
Another would be using openquery with ADSI:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Fri, 11 Feb 2005 09:53:03 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>Im using the 'xp_cmdshell' to know all users that belong to the local
>administrators group and i'm using the 'net use localgroup' to get the output
>into my report.
>Here is the code and the Output:
>set nocount on
>exec xp_cmdshell 'net use localgroup'
>go
>set nocount off
>OUTPUT:
>output
>
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>Members
>NULL
>--
> Administrator
> DOMAINXXX\Domain Admins
> DOMAINXXX\usera
> DOMAINXXX\userb
>The command completed successfully.
>NULL
>NULL
>Is there any way to avoid lines where appear:
>output
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>and all the information that is not part of the group membership?
>Do you know other way to get this information?
>Thanks,
>Best regards
>