SQL Server Reporting Service (SSRS) – Use email friendly name

I had my SSRS running smoothly, but there was one thing bothering me. That was email sender friendly name. In RS Configuration Manager you can setup e-mail settings, and enter senders e-mail address.
But every time I got an email with report, the sent from stated sql@sqlreports.com. But I need an friendly name.
So, I just added “SQL REPORTS” sql@sqreports.com in RSCM and it worked! IT also works with MSSQL 2012 server.

image

SQL – Getting list of jobs and their steps

How to get list of jobs an their steps. For this I will use three tables dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules in msdb database.

You can create select from each of these tables to get relevant data.

select name,enabled,description from msdb.dbo.sysjobs
select job_id,step_id,step_name,command,database_name from msdb.dbo.sysjobsteps
select job_id,next_run_date,next_run_time from msdb.dbo.sysjobschedules

 

But we can combine these three tables, to get unified complex select.

SELECT
    job.name,
    job.enabled,
    job.description,
    steps.step_name,
    steps.command,
    steps.database_name,
    sch.next_run_date AS ‘Sched Date’,
    sch.next_run_time AS ‘Sched Time’
FROM msdb.dbo.sysjobs job JOIN
     msdb.dbo.sysjobsteps steps       
        ON job.job_id = steps.job_id INNER JOIN
    msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
–where name like ‘%BACKUP%’ –you can also filter with where clause
order by name

 

 

 

Good Luck

SQL- Creating linked server to AS400

Have you ever tried to connect to AS400. It’s been a hassle for me. But finally I got it to work, so let me share little of my wisdom with you.

First create linked server, then change options to it.

EXEC master.dbo.sp_addlinkedserver @server = N’AS400′, @srvproduct=N’i520′, @provider=N’IBMDA400′, @datasrc=N’AS400′, @provstr=N’User Id=USER;Password=PWD;Default Collection=DAT;’

Change option:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’NonTransactedUpdates’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’SqlServerLIKE’, 1
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc out’, @optvalue=N’true’
GO

So, you should now have functional linked server. Only, in my case its very slow, so it could be in your case to.

Good Luck

SQL – Table comparison

For table comparison, i like to use Red Gate SQL compare. Great tool, but if you don’t have money for it, you can always try to write your own comparation script.
You can then script it as function, and use it. It does not have, options like SQL Compare, but for now it will do the trick.
This simple comparation, uses only names of columns to compare. But you can code it to check also field type and field length.

First of all, lets create two simple tables:

CREATE TABLE [dbo].[Table_One](
    [Column1] [nchar](10) NULL,
    [Column2] [varchar](50) NULL,
    [Column3] [int] NULL,
    [Coulmn4] [datetime] NULL,
    [Column10] [char](20) NULL
) ON [PRIMARY]

CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
CREATE TABLE [test].[Table_Two](
    [Column1] [nchar](10) NULL,
    [Column7] [int] NULL,
    [Column8] [int] NULL,
    [Coulmn9] [datetime] NULL,
    [Column10] [varchar](20) NULL
) ON [PRIMARY]

As you see, I left

Then, declare variables for table names. It will be easier to handle table names in one place.
DECLARE @T1 VARCHAR(30) = ‘Table_One’, –names without schemas
        @T2 VARCHAR(30) = ‘Table_Two’

 

Now, we just have to create comparison select. Selecting distinct values from information_schema view first for one table, than the other.

— In TableOne but not in TableTwo
SELECT DISTINCT
       @T1 AS [First table],
       ‘>>’ AS Dir, –Direction
       @T2 AS [Second table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T2)
        AND ISC1.TABLE_NAME = @T1
UNION ALL
— In TableTwo but not in TableOne
SELECT DISTINCT
       @T1 AS [First_table],
       ‘<<‘ AS Dir, –Direction
       @T2 AS [Second_table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T1)
        AND ISC1.TABLE_NAME = @T2
ORDER BY Dir DESC, COLUMN_NAME ASC

 

And the result is:
image

 

Good Luck

Activate Windows 8 Enterprise

I recently installed Windows 8 Enterprise in my company. During the installation, setup does not require input of serial number/key. So when Windows installs, you must go through procedure of changing the key, and activating Windows.

So here it goes.

On Desktop, go with your mouse to bottom left corner. When Start icon pops up, press right mouse button and select Command Prompt (Admin).

image

Now you must run VB script to change serial key: (X-es represent your key)
slmgr.vbs -ipk
image
sucessfull1
Now all that is left to do is to activate your windows. Also through command prompt, and with the same
VBs script, but with diffrent switch.
slmgr.vbs -ato
image
After hitting the Enter, the pop up will tell you that Windows were activated successfully.
sucessfull2

image

Good Luck

SQL SMSS connect as another Windows NT user

I tried to find out how to connect through SMSS to SQL server as another domain user.
You must run SMSS as “RUN AS..”

so the code is as follows:
runas /netonly /user:domain\user “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

 

EDIT:!!
Due to version changes:

%windir%\System32\runas.exe /netonly /user:domain\user “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe -S SERVER”

EDIT!!

When you use the “/netonly” switch, you can log in using remote credentials on another domain which you are not member of, even if there’s no trust set up.
Runas uses credentials for accessing remote resources. The application interacts with the local computer as the current user, and interacts with remote computers as the user you’ve given.

After that, jus connect through your SMSS and run “SELECT SUSER_NAME,SYSTEM_USER” and you will see that you are under the login you specified.

Or in a new query you can write
execute as login=’DOMAIN\user’
“SELECT SUSER_NAME,SYSTEM_USER”

Good Luck

T-SQL: Get number from string

When you are working with custom software over your database, you can easily find some data you need to clean up. It can be trash from poorly written code, or users can write rubbish if no controls are established. So next couple of posts will be about searching and updating this kind of trash values.
First is getting a number from a string.
So, I had a task of exporting phone numbers in unified format, but numbers were in all variety:
+(385)11 1111-111
0038511/1111-111
etc.
I used to write WHILE loop which would read a character by character in string, and then get the number in numeric string.
Then I found out that there are couple of functions PATINDEX and STUFF which can help me out quickly.
This idea cam from this site SQLAuthoritiy from Pinal Dave.

PATINDEX(‘%pattern%’,expression) – returns first position of pattern
image
STUFF(expression,start,length,replace_string) – replaces length in expression from position start with replace string
image

Now, when we got this clear, create a function which will return only string. Function is created on database level. So if you want to use it everywhere you must have permission and use it like this: database.schema.function

use database
go

CREATE FUNCTION fnGetNumberFromString
(@strInput VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intNumber int
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput) – set first location of number
BEGIN
WHILE @intNumber > 0 –until position number of number in string is null or 0
BEGIN
SET @strInput = STUFF(@strInput, @intNumber, 1, ” ) –replace string with ‘blank’
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput )–next position of number
END
END
RETURN ISNULL(@strInput,0)
END
GO

Now just to test it

image 

That’s how you get numbers from string.

Good luck

Mail merge from SQL Database mail

Few months ago, I was given a task to send some notifications via circular letter. The best thing is to send it by MS WORD.
image
Unfortunately, after sending this, the attachment could not be read on the recipients side. Me and my colleague could not understand, is it because WORD or attachment or in the end mail server. Because we where on tight schedule, I came up with idea to send circular letter through SQL Database mail.
After importing the table of recipients, I have created an simple WHILE loop to call sp_send_dbmail.

It can be sent in plain text or HTML.
For this I have create a three users me, John Doe, Jane Doe.

This is T-SQL for loop plain text
/*****************************************PLAIN TEXT********************************************************/
use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
”+@Title+’
‘+@Name+’

We must inform you that you have signed a contract with TESTFirm.

‘+@option+’

Best Regards

Somebody’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
–@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************PLAIN TEXT********************************************************/

/*****************************************HTMLTEXT********************************************************/

use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
‘<font size="3" face="Arial" color="black">
<p><big>’+@Title+'</p>
<p>’+@Name+'</big></p>
<p> </p>
<p> </p>
<p>We must inform you that you have signed a contract with TESTFirm.</p>
<p> </p>
<p>’+@option+'</p>
<BR>&nbsp;<BR>
<p></p>
<p>Best Regards</p>
<p></p>
<p>Somebody</p> </font>’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************HTMLTEXT********************************************************/

I received three mails:
image

The result of plain text mail:
image 

The result of HTML mail:
image

So, that’s about it.

Good Luck

E-mails in MSSQL– How to setup Database Mail

MSSQL has a service called Database mail and it is used from MSSQL 2005 till today. This service is predecessor of SQL Mail.
I will write about Database mail, and how to set it up.
First of all, you have to open a port 25 (SMTP), so you can send mail from server to the “world”.
Second, we must enable Database Mail

sp_CONFIGURE ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO

After that, we must give permission of sending mail to SQL Agents service account. My Agent is running under domain service account. Add that user to DatabaseMailUserRole in msdb database
image

image

After adding the user to msdb database, next we must create mail profile.
image
image
image

if your mail server requires authentication, enter it in SMTP authentication, if not leave it blank.
image

image
 
Leave it blank, because you setup an account in msdb database.
image
Set Database mail system parameters.
image 

Last, but not least you have to enable sending alerts from SQL Agent.
image

Now, test email settings
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’SQL TEST’,
@recipients=’test@sql.com’,
@subject=TEST,
@body=’Body, Successfully sent an e-mail’

Database Mail, of course saves all outgoing e-mails.
You can check it in msdb database, in tables
sysmail_allitems
sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems
And of course you can check the log in table sysmail_log.
image

Good Luck