DBA: SSRS City Stories: @ExecutionTime date format for SSRS Subscriptions

Had an weird issue today. Sudarshan logs a ticket complaining about the date format in the subject title of the  emails sent from his SQL Server Reporting Services subscriptions. It's in the US format (MM/DD/YYYY) instead of the AU format (DD/MM/YYYY).

Googled around, and found that the date of the subscriptions was set by the variable @ExecutionTime. Further goolging mentioned that the date format cannot be set. But this cannot be, as the dates of the subscriptions prior to the 30th of March were fine!

Searched other sites, and found that the date format of @ExecutionTime is based on the value of the locale field in the subscriptions table inside the ReportServer database. Manually changing this value from "en-US" to "en-AU" solves the issue and gives the correct date format. However, any new subscriptions that Sudarshan creates is still in the "en-US" format! So where does SSRS pull this value from???

I guessed that it pulls the value from the Language parameter of the report. Sudarshan tests this out, but it was not the case, so back to Google I go.. and that was where I found my answer once again. SSRS pulls this value from the user's browser language setting! WTF?!

Case closed. :D

DBA: DC City Stories: Make SQLPlus Results Pretty!

Here are some common commands for the users of SQLPlus to make our results readable:
set PAGESIZE 100
This sets number of rows that appears before another header is printed. You can exclude the header all together by setting the value to 0.
set WRAP off
This ensures the results don't wrap to the next line if it's too long.
col column_name for a25 
This specifies a specific column size. Substitute the name of the column in, and the desired size in the last parameter.

DBA: DC City Stories: Oracle 11g Password Expiry

Don't be caught out with 11g's new DEFAULT profile password policy. The 11G DEFAULT profile has the following:

• 6 month password expiry
• 10 failed login attempts before lockout

Both will cause havoc when they're service accounts.


To unlock an account:


ALTER USER SCOTT ACCOUNT UNLOCK;

To find the profile settings:

col profile for a20
col resource_name for a25
col limit for a15
select profile,resource_name,limit
from dba_profiles
where resource_type='PASSWORD'
order by profile;

PROFILE    RESOURCE_NAME             LIMIT

---------- ------------------------- ---------------

DEFAULT    PASSWORD_REUSE_TIME       UNLIMITED

DEFAULT    PASSWORD_LOCK_TIME        1

DEFAULT    FAILED_LOGIN_ATTEMPTS     UNLIMITED

DEFAULT    PASSWORD_VERIFY_FUNCTION  NULL

DEFAULT    PASSWORD_LIFE_TIME        UNLIMITED

DEFAULT    PASSWORD_REUSE_MAX        UNLIMITED

DEFAULT    PASSWORD_GRACE_TIME       7

To find out the status of the accounts:

set pagesize 100
set wrap off
col USERNAME for a25
col PROFILE for a15
col ACCOUNT_STATUS for a20
col EXPIRY_DATE for a15

select USERNAME, PROFILE, ACCOUNT_STATUS, EXPIRY_DATE
from DBA_USERS;

USERNAME                  PROFILE    ACCOUNT_STATUS       EXPIRY_DATE

------------------------- ---------- -------------------- ---------------

CORPDIR_USER              DEFAULT    OPEN

MMS_USER                  DEFAULT    OPEN

NIZAR_RO                  DEFAULT    OPEN

SS_USER                   DEFAULT    OPEN

SCOTT                     DEFAULT    EXPIRED & LOCKED     17-MAR-11

ORACLE_OCM                DEFAULT    EXPIRED & LOCKED     15-AUG-09

XS$NULL                   DEFAULT    EXPIRED & LOCKED     15-AUG-09

MDDATA                    DEFAULT    EXPIRED & LOCKED     15-AUG-09

DIP                       DEFAULT    EXPIRED & LOCKED     15-AUG-09

APEX_PUBLIC_USER          DEFAULT    EXPIRED & LOCKED     15-AUG-09

SPATIAL_CSW_ADMIN_USR     DEFAULT    EXPIRED & LOCKED     15-AUG-09

SPATIAL_WFS_ADMIN_USR     DEFAULT    EXPIRED & LOCKED     15-AUG-09

DBSNMP                    MONITORING EXPIRED              20-SEP-11

To change the DEFAULT profile password policy:

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;

DBA: DC City Stories: SQL Server 2000 Disable User Account

SQL Server 2000 doesn't have the ALTER LOGIN statement. So to be able to disable the login you'll have to call the sp_denylogin procedure instead:

EXEC sp_denylogin 'exampleuser'

or

EXEC sp_revokelogin 'exampleuser'

To give them back access again you should use:

EXEC sp_grantlogin 'exampleuser'


Note: sp_denylogin, sp_revokelogin and sp_grantlogin only works on Windows accounts and groups.

To be able to deny pure SQL Server logins, it seems like the only option is to remove that login completely with

EXEC sp_droplogin 'exampleuser'


but to enable it again, it needs to be re-created with:

EXEC sp_addlogin 'exampleuser', 'examplepassword'


or just remove that logins access to the current database with


EXEC sp_revokedbaccess 'exampleuser'

DBA: DC City Stories: Datapump

Oracle always has a way with error message. Why can't they make it clear and precise?

I was doing a database export using datapump for a client, and I passed in PARFILE. The following error was spat out:

ksunsodb01:oracle\(KTECH\)@ expdp parfile=expdp.par

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 07 July, 2011 11:09:21

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 488

ORA-29283: invalid file operation

Not very clear huh? Well, what Oracle was complaining about was that it was missing the Datapump directory on a OS level. Therefore, check which directory your PARFILE is using: 

ksunsodb01:oracle\(KTECH\)@  cat parfile_expdp_WMDATA.par

USERID="/ as sysdba"

dumpfile=DATA_PUMP_DIR:expdp_KTECH_WMDATA_20110707.dmp

logfile=DATA_PUMP_DIR:logexpdp_KTECH_WMDATA_20110707.log

schemas=WMDATA

flashback_time="to_timestamp(to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss'), 'dd/mon/yyyy hh24:mi:ss')"

In our case, the directory is "DATA_PUMP_DIR". Find out where this directory is mapped to:

ksunsodb01:oracle\(KTECH\)@  sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 7 11:14:07 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

Connected.

SQL> select directory_path, directory_name from dba_directories;

DIRECTORY_NAME                                                        DIRECTORY_PATH                     

------------------------------------------------------------
/u06/flash2/exports/OTECH/         DATA_PUMP_DIR

And check that the directory actually exists. In our case it doesn't, so modify it to a directory that exists:

SQL> create or replace directory DATA_PUMP_DIR as '/u06/flash1/export/KTECH/';

Directory created.

SQL> exit

Now, we're ready to run again. :)

How to Indent ..

A personally note to self.. If you want to indent like this:

Hello

You have to use this code:

<pre class="source-code"><code>Enter text here</code></pre>

Hello World Again...

Been sampling a lot of different blog sites to try and find the best one...(ever since Raj destroyed my first blog on DV8.com.au, and then Breakfree finally killed it off the 2nd time) ..  Tumblr, WordPress .. etc... I might have just found my ideal one in blogger since Google themselves made the Android app. We shall see.. but until I've decided, it'll be twitter for now.