maandag 20 juni 2011

Using sp_send_dbmail from a SQL-Agent job gives error EXECUTE permission denied.

I ran into the following problem.
Short environment description:
Server: SQL 2008 + SP2
User : domain\user1 is db_owner of database SSISMetaData and member of the msdb database role DatabaseMailUserRole

User: domain\fraaija is sysadmin on the server

SQL-Agent job contains SQL Transact Step:







On the advanced tab of the step the user domain\user1 is selected on the Run As user box.
When the job is executed the following error is generated:

Executed as user: domain\user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

Because I granted the DatabaseMailUserRole to the domain\user1 login I started to do some tests to see what was wrong.

 Started SSMS and connected to the server with the user domain\fraaija and started a new query where I executed the following same mail command. I do receive the mail as expected, so mail function is working. So lets execute the following and try again:

EXEC AS LOGIN = 'domain\user1'
SELECT  system_user -- displayed is the user domain\user1

Again I do receive the mail as expected, so why does this error when using the same command from the SQL-Agent job?

I found a solution to make it work for now but is this preffered? For now I leave it this way to keep working but when you have an alternative solution please let me know.

After altering the database setting with the command:
ALTER DATABASE [SampleDB] SET TRUSTWORTHY ON

[http://technet.microsoft.com/en-us/library/ms187861.aspx]

Now when the job is executed I receive the mail as expected.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter

donderdag 9 juni 2011

Extract house number & addition from address line


One of my customers needed to deliver a file with street name, house number and house number addition split over the 3 columns. The system that it was needed from did only have one address line that contained the entire street address including the house number and addition.

Addresses are not filled in following a standard like saying street-space-housenumber-space-addition but spaces, dots and dashes where used. I produced the small table example below:

image

I then did write the following query to extract the street, house number and addition.

image

When the query is executed this results in the following

image

The results is what I needed, I only find the query not that nice, when you have a simple solution please let me know.

I used several REPLACE statements to get the dash and dots out of the results. You could use the function created by Christian d'Heureuse to get the special characters out http://www.source-code.biz/snippets/mssql/1.htm

You can download the above code here: blog56

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.

Dynamic Intelligence | LinkedIn | Blog | Twitter