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

The Love between IBM iSeries Access and SQL 2012

Ok, so let’s start with my first blog.
Couple of days ago, I’ve been given a task to migrate some tables from AS400 to SQL.
So first of all, you would need a connector (provider) to IBM AS400 structure. So that connector comes with IBM iSeries Access software. After quick and “select All option” installation, I thought this is it.
I started SQL Import and Export Wizard to test things out. But the error appeared:
image

After couple of minutes, I found that the .NET config file had been corrupted. Searching through .NET config, I found the problem. In file “C:WindowsMicrosoft.NETFrameworkv4.0.30319Configmachine.config” there was a unclosed line
<system.data>
<DbProviderFactories>
<add name=”IBM DB2 for i5/OS .NET Provider” invariant=”IBM.Data.DB2.iSeries” description=”.NET Framework Data Provider for i5/OS” type=”IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26″ />
</DbProviderFactories>
   <DbProviderFactories/>  —-delete  this line
</system.data>
Of course you’ll probably have problems of saving that protected file. So, my advice, save that file on desktop, edit it, and then copy it back to original location.

After that, the Import Export worked as a charm. Now, next step is to create a DTX package, and schedule it to transfer data. SQL 2012 does not have Business Intelligence, but SQL Server Data Tools (further in text SSDT).

Start SSDT, create a project, create Control Flow, add Connection Managers to SQL server and to AS400.

But, I’ve stumbled on a problem here.
When working and testing DTX package from SSDT, everything works, but for some reason when you import that package into Integration Services, and start it, the password for IBM AS400 connector is missing.
Solution to this problem is:

Start SSDT with “Run as Administrator” option, design package, enter username and password into AS400 connection Manager , and select “Allow saving password”.
image

Save package. When importing to Integration 
Services you must select option “Rely on server storage and roles for access control”!

image

And that about covers the problem I’ve encountered working with these two products.

Enjoy migrating!
Smile