Database Mail – test is OK, but jobs do not notify

  1. Run SQL Server Management Studio.

  2. In Object Explorer, expand the server.

  3. Right-click on the SQL Server Agent and then click its Properties.

  4. Click Alert System.

  5. Check Enable Mail Profile.

  6. In the Mail system list choose Database Mail.

  7. In the Mail profile list select a mail profile.

  8. Restart the SQL Server Agent

Good Luck!

Send Mail from SQL Server 2005

Hello Geeks,

I am planning to post very very useful thing.

Once at my work, one co-worker asked me to write such thing:

Retrieve query result from SQL Server 2005, save it as a .csv file and sent it to some recipients everyday at some time.

So let’s start it…

1. First of all you should create Profile and Account.

 

SQL Server Configure Database Mail

SQL Server  Database Mail Configuration Wizard

Next>

SQL Server  Database Mail Configuration Wizard_Select Configuration_Task

Next>

SQL Server  Database Mail Configuration Wizard_New_profile

Click Add…

SQL Server Database mail Account

Click OK…

SQL Server Database Mail Configuration Wizard New Profile

Next>

SQL Server Database Mail Configuration Wizard Manage Profile Security

Next>

SQL Server Database Mail Configuration Wizard Configure System Parameters

Next>

SQL Server Database Mail Configuration Wizard Complete the Wizard

Click Finish…

Everything should be successful.

2. We should enable Database Mail on the server. Using Database Mail XPs.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

3. Let’s send mail… Using msdb.dbo.sp_send_dbmail procedure… Indicating the following parameters.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sqlReport', @recipients = 'mariam.kupa@gmail.com', @subject = 'SQL Report', @body_format = 'HTML',
@body = '.............', @query = 'SELECT * from [testDB].[dbo].[v_Report_Mari] order by [Computer Name]', @attach_query_result_as_file = 1, @query_attachment_filename = 'qry.csv', @query_result_no_padding = 1, @append_query_error = 1, @exclude_query_output = 1, @query_result_header = 1, @query_result_separator=' '

Let’s discuss each parameter:

[ @profile_name = ]  This is the name of the profile from where message will be sent(We set up it before, 1st step)

[ @recipients = ]       This is a semicolon-delimited list of e-mail addresses to send the message to.

[ @subject = ]           This is the subject of the e-mail message.

[ @body_format = ]   This is the format of the message body. Available values are: TEXT (default) and HTML.

[ @body = ]               This is the body of the e-mail message.

[ @query = ]              This is a query to execute. It’s result can be attached as a file, or included as a text message included in the body . Consider that the query is executed in a separate session and local  variables in the script using sp_send_dbmail can not be used.

[ @attach_query_result_as_file = ] Available values are 1 (YES) and 0 (NO).

[ @query_attachment_filename = ] Name of the attached file.

[ @query_result_no_padding = ]     Available values are 0 (padding) and 1 (no padding). If you indicate 1 it possibly reduce the file size,because there will not be any padding. Default value is 0.

[ @append_query_error = ]             Available values are 0 and 1. If any error occurs in the query indicated in [ @query = ] and that parameter value is 1, then mail will be sent appended with the error message. If the value is 0 then mail will not be sent. Default value is 0.

[ @exclude_query_output = ]        When the value is 1, the execution of the sp_send_dbmail does not print any of the query execution messages on the console. For 0 it is vice versa. The default value is 0.

[ @query_result_header = ]           Specifies whether the query results include column headers. Available values are 1 and 0.

[ @query_result_separator = ]       Indicates by which columns should be separated. Default value is ‘ ’(space). In our example, i ‘ve indicated TAB, just type 2 quotation marks place cursor between of them and press desired key, in our case TAB.

4. Increase the mail size limit from Database Mail. You can do it during setting up the account but I prefer to discuss it separately.

SQL Server Configure Database Mail

SQL Server  Database Mail Configuration Wizard

Next>

SQL Server  Database Mail Configuration Wizard View Change system parameters

Next>

SQL Server  Database Mail Configuration Wizard View Change system parameters

Next>

5. Creating job…

SQL Server Create New Job

SQL Server New Job General Tab

SQL Server New Job Steps Tab

Insert our code in the Command field:

SQL Server New Job Steps General Tab

SQL Server New Job Schedules

SQL Server New Job Schedules Job Schedule

It is easy to understand, I will not explain… This window is for to schedule job for the specified time. Click OK..OK…

Usefull view for checking email status:

SELECT *
FROM msdb.dbo.sysmail_event_log

 

That is all..

Sql Sever Backup Problem

If you have the following error during backup/restore of SQL Server database:

image

Just click OK, and let’s start its workaround…

In Select Backup Destination window do not browse anything, or it will arise the previous error, just type the path and name of the backup file, like that:

image

Click OK, there will appear the following window:

image

Click Yes… Your backup should work.

I have also checked my backup by restoring it and everything works fine…

Note: Support of the Microsoft recommends you the following workaround:

“To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.”

I did it without this security issueSmile

 

P.S I found another solution of it… especially by granting db_backupoperator role to that user….

NOTE: If you do not grant this role, user will be able to make a backup as I did in the previous section, but will not be able to restore database…

Install SQL Server 2005 Express Edition & Enabling Remote Client Connection

Run the installation. You can download it from here.

Microsoft SQL Server 2005 Setup End User License Agreement

 

Microsoft SQL Server 2005 Setup Installing Prerequisites

Microsoft SQL Server 2005 Setup Installing Prerequisites 2

Microsoft SQL Server 2005 Setup Welcome Server Installation Wizard

Microsoft SQL Server 2005 Setup System Configuration Check

Microsoft SQL Server 2005 Setup Microsoft SQL Server Installation

Microsoft SQL Server 2005 Setup Registration Information

Microsoft SQL Server 2005 Setup Feature Selection

Microsoft SQL Server 2005 Setup Feature Selection 2

Microsoft SQL Server 2005 Setup Instance Name

Microsoft SQL Server 2005 Setup Existing Components

Click Next>

Microsoft SQL Server 2005 Setup Service Account

Microsoft SQL Server 2005 Setup Authentication Mode

Microsoft SQL Server 2005 Setup Collation Settings

Microsoft SQL Server 2005 Setup Configuration Options

It is optional, if you want to send error reports to Microsoft then check them. I will not disturb Microsoft and leave these boxes unchecked SmileSmile

Microsoft SQL Server 2005 Setup Error and Usage Report Settings

Microsoft SQL Server 2005 Setup Ready to Install

Microsoft SQL Server 2005 Setup Progress

Microsoft SQL Server 2005 Setup Completing MS SQL Server Setup

 

Enabling Remote Client Connection

Click Start->All Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration…Click Surface Area Configuration for Services and Connections

Microsoft SQL Server 2005 Surface Area Configuration

 

Microsoft SQL Server 2005 Connection Settings Change Alert

Click OK, and reload Database Engine service:

Microsoft SQL Server 2005 Surface Area Configuration_Service_Stop

Microsoft SQL Server 2005 Surface Area Configuration_Service_Start

Click OK.

Understanding sysobjects table in SQL Server

System tables and views are very useful objects that may be queried in many times by database background processes itself or by DBAs. These objects sometimes contain columns that are not so easy to understand what they are for. Here, in this post I will discuss  mostly used content of SYSOBJECT table.

Take into the consideration, that updating or deleting rows in these tables are not recommended. Background processes do it for you.

The most useful columns in SYSOBJECT table are name, id, xtype, uid, parent_obj, crdate.

As you can guess name column saves the name of the database object. Id column is the unique number for this object. xtype shows the type of this object, where:

  • AF = Aggregate function (CLR)
  • C = CHECK constraint
  • D = Default or DEFAULT constraint
  • F = FOREIGN KEY constraint
  • L = Log
  • FN = Scalar function
  • FS = Assembly (CLR) scalar-function
  • FT = Assembly (CLR) table-valued function
  • IF = In-lined table-function
  • IT = Internal table
  • P = Stored procedure
  • PC = Assembly (CLR) stored-procedure
  • PK = PRIMARY KEY constraint (type is K)
  • RF = Replication filter stored procedure
  • S = System table
  • SN = Synonym
  • SQ = Service queue
  • TA = Assembly (CLR) DML trigger
  • TF = Table function
  • TR = SQL DML Trigger
  • TT = Table type
  • U = User table
  • UQ = UNIQUE constraint (type is K)
  • V = View
  • X = Extended stored procedure

I’ve got this list from the internet,most of them are useful to know.

uid                – saves the user id, which is the owner of this object.
parent_obj – Id of the parent object. For example, for constraint parent_obj will be the ID
                        of the table, on which this constraint was defined.
crdate         -Object creation date.

There are several other tables, which shows the user objects:

–Shows the table columns owned by the current user.

select *
from information_schema.columns

–Shows the tables owned by the current user.

select *
from information_schema.tables

–Shows the list of all functions and procedures owned by the current user.

select *
from information_schema.routines

Real World Scenario

One day, the person came to me and asked if it was possible to find the table name when you just know the column name. I told him yes Smile and wrote the following simple query:

select a.name as columnName
           ,b.name as tablename
from SYSCOLUMNS as a,SYSOBJECTS as b
where a.ID=b.ID
and a.name=’column_name’

I hope this post was useful for you…

Oracle Heterogeneous Services(hsODBC)

Heterogeneous Services provide the ability to communicate with non-Oracle databases and services.

I will discuss, how to communicate with SQL Server 2005 from Oracle.

1. First of all, you should have a trusted username in SQL Server 2005 to connect to the SQL database.

If you don’t have it, do the following:

1.1 Connect to the SQL Server 2005 database.
1.2 In the Object Explorer, expand the Security folder, then Logins and create new login.

New Login...(Sql Server 2005)

1.3 In General, type Login name, choose SQL Server authentication, type password for user, uncheck User must change password at next login, choose English in Default language.

Login_New(SQL Server 2005)

1.4 In the Server Roles, public and sysadmin roles should be checked.

Login Properties_Server Roles(SQL Server 2005)

1.5 In the User Mapping, check the database for which this user will be owner(Note that this database should be the database from where you want to query data).

Login_Properties_User_Mapping(SQL Server 2005)

1.6 In Status.

Login_Properties_Status

1.7 Right click on the server in the Object Explorer window, choose properties, in Security, choose SQL Server and Windows Authentication mode.

Server Properties_Sequrity(SQL Server 2005)

1.8 Reload the server.

2. Now, when you already have a trusted user in SQL Server 2005. Let’s install ODBC driver for sql server on the computer where Oracle is installed.

2.1 Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), go to System DSN tab, click Add button.

ODBC Data Source Administrator_System DSN

2.2 Choose SQL Server, click Finish.

Create New Data Source

2.3 In the Name field, type some name, we will use it later as a SID_NAME. In Server field, type the IP of the server, where SQL Server 2005 database is installed.

Create a New Data Source to SQL Server(Data Source)

2.4 Choose second radio button, and type Login ID and Password for the user, which is a trusted user in SQL Server 2005(We have created it before, section 1).

Create a New Data Source to SQL Server

2.5 Check Change the default database to, and choose your database(Note this should be the database from where you want to query data).

Microsoft_SQL_Server_DNS_Configuration

2.6 Leave default settings.

Microsoft_SQL_Server_DNS_Configuration_2

2.7  This window shows you the summary, click Test Data Source button.

ODBC Microsoft SQL Server Setup

2.8 If you have indicated the correct settings, it should be successful.

SQL Server ODBC Data Source Test

3. Now, it’s time to configure in Oracle the following files: listener.ora, tnsnames.ora and %ORACLE_HOME%\hs\admin\inithsodbc.ora.

3.1 In listener.ora file add the following entries:

listenerradiustosql =
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
         (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_listenerradiustosql=
(SID_LIST=
   (SID_DESC=
     (SID_NAME=radiustosql)
     (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)--Your oracle home
     (PROGRAM=hsodbc)
    )
)

Note: HOST must be localhost(127.0.0.1) not the computer IP, on where oracle is installed.
PORT is non-default port 1522, or choose the port which is free.
You should have two different listeners, one for Oracle itself and other one for radiustosql.

3.2 In tnsnames.ora file add the following entries:

radiustosql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    (CONNECT_DATA=(SID=radiustosql))
    (HS=OK)
  )

3.3  Rename %ORACLE_HOME%\hs\admin\inithsodbc.ora to %ORACLE_HOME%\hs\admin\initradiustosql.ora and change its content to the following:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = radiustosql
HS_FDS_TRACE_LEVEL = OFF

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

3.4 Reload both Listeners: Oracle listener and newly created listenerradiustosql.

–To start default listener

>lsnrctl stop
>lsnrctl start

–To start non-default listener

>lsnrctl stop listenerradiustosql
>lsnrctl start listenerradiustosql

4. Connect to Oracle database and create database link.

4.1
–To create database link

create database link radiustosql
connect to m identified by itsPassword using 'radiustosql'

–To check if it works, run the following:

SELECT *
FROM dual@radiustosql

–Output

DUMMY
--------
X

Retrieving data from Active Directory to SQL Server 2005

We will use Visual Basic script to retrieve data from Active Directory. This script will pass data to the SQL Server procedure, which will insert it to the database table.

Just for Note: I used WinNT in visual basic instead of LDAP, and it was smaller script than this one, which I have written below:). But when I was trying to run this script error code: 8000500D was arising, error was indicating to the department property… Unfortunately, I couldn’t find the solution how to retrieve department property using WinNT, that is why I decided to write code by LDAP(code is written below).

So let’s start.

1. Connect to the database.

–First of all let’s create a table, which will hold this information.

CREATE TABLE [ADUsersTable] (
[UserId]     [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Username]   [nvarchar] (1000),
[FullName]   [nvarchar] (1000),
[Department] [nvarchar] (1000)
)

Note: If IDENTITY(1,1)option is new for you, click here to clarify.

–Create procedure.

CREATE PROCEDURE ad_addProc
@Username   nvarchar(1000),
@Fullname   nvarchar(1000),
@Department nvarchar(1000)
AS
BEGIN
   INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
   VALUES(@Username ,@Fullname,@Department)
END

2. On the computer,  where SQL server is installed, create a text file, insert the following Visual Basic script and rename this file to AdUsers.vbs

Option Explicit
Dim oDomain
Dim MyConnection
Dim MyCommand
Dim par1
Dim par2
Dim par3
‘Domain is bg.ge
Set oDomain = GetObject("LDAP://dc=bg,dc=ge")
Set MyConnection = CreateObject("ADODB.Connection")
'Create SQL connection string,  to connect to the database.
MyConnection.Open "Driver={SQL Server};server(local);
database=DBname;uid=BOG0\mkupatadze;pwd=*****;Trusted_Connection=yes;"

Set MyCommand = CreateObject("ADODB.Command")
Set MyCommand.ActiveConnection = MyConnection
Set par1 = MyCommand.CreateParameter("@Username", 202, 1, 1000)
Set par2 = MyCommand.CreateParameter("@Fullname", 202, 1, 1000)
Set par3 = MyCommand.CreateParameter("@Department", 202, 1, 1000)
'Note that ad_addProc is the procedure created in SQL Server database.
MyCommand.CommandText = "ad_addProc"
MyCommand.CommandType = 4
MyCommand.Parameters.Append par1
MyCommand.Parameters.Append par2
MyCommand.Parameters.Append par3
RetrDataFromAD(oDomain)
‘Write the recursive function.
Sub RetrDataFromAD(oArray)
   Dim oADObject
   For Each oADObject in oArray
     ‘If error occurs, script will not be terminated.
     On Error Resume Next
     ‘Clearing  values of parameters
     par1.Value =" "
     par2.Value =" "
     par3.Value =" "
     Select Case oADObject.Class
       Case "user"
          ‘sAMAccountName is the username
          par1.Value = oADObject.Get("sAMAccountName")
          par2.Value = oADObject.Get("name")
          par3.Value = oADObject.Get("department")
          MyCommand.Execute
       Case "organizationalUnit" , "container"
          RetrDataFromAD(oADObject)
     End Select
    Next
End Sub
MyConnection.Close()

Double Click on that file and to see the result run the following query:

Select *
from [ADUsersTable]

Identity Columns

The syntax of the identity property is the following:

IDENTITY[(seed ,increment)]

Note: If you do not indicate seed and increment options, the default value for each of them will be 1. So IDENTITY(1,1) and IDENTITY is the same.

When the column is created by this property , numeric sequence will be created for you and this column will hold the sequential values, started by seed and incremented by increment parameter values.

For example:

–Create table by this property

CREATE TABLE [ADUsersTable] (
[UserId]     [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Username]   [nvarchar] (1000),
[FullName]   [nvarchar] (1000),
[Department] [nvarchar] (1000)
)

–Insert rows

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('MKUPATADZE' ,'MARIAM KUPATADZE','IT')

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('GBERIDZE' ,'GIORGI BERIDZE','IT')

–Query data

SELECT *
FROM [ADUsersTable]

–Result

USERID|USERNAME     |FULLNAME          |DEPARTMENT
 1    | MKUPTADZE   | MARIAM KUPATADZE | IT
 2    | GBERIDZE    | GIORGI BERIDZE   | IT

–Insert another row and rollback it, let’s see what happens

BEGIN TRAN

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('AGANDILIANI' ,'ANNA GANDILIANI','IT')

ROLLBACK TRAN

–Insert one more row

INSERT INTO [ADUsersTable] ([Username], [FullName], [Department])
VALUES('SCHELISHVILI' ,'SOPHO CHELISHVILI','IT')

–Query data

SELECT *
FROM [ADUsersTable]

–Result

USERID|USERNAME     |FULLNAME          |DEPARTMENT
 1    | MKUPTADZE   | MARIAM KUPATADZE | IT
 2    | GBERIDZE    | GIORGI BERIDZE   | IT
 4    | SCHELISHVILI| SOPHO CHELISHVILI| IT

As you can see rolling back the transaction does not reset the current value of the sequence.

–To see the current value of the sequence, run the following:

DBCC checkident(ADUsersTable)

My result

Checking identity information:current identity value '4',current column value '4'.

Inserting desired identity values

By default, you can’t indicate value during insert for the column which was created by IDENTITY property. If you try the following command:

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(3,'VDALAKISHVILI','VASIL DALAKISHVILI','IT')

–It will arise the following error

Cannot insert explicit value for identity column in table 'ADUsersTable'
when IDENTITY_INSERT is set to OFF.

–To solve this, set IDENTITY_INSERT to ON, insert rows and then set IDENTITY_INSERT to OFF

SET IDENTITY_INSERT ADUsersTable ON

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(3,'VDALAKISHVILI','VASIL DALAKISHVILI','IT')

SET IDENTITY_INSERT ADUsersTable OFF

Note: If you do not turn off IDENTITY_INSERT, then you will not be able to use the generated sequence. Every time you run the INSERT statement you will be forced to indicate value for USERID column.

–Query data

SELECT * FROM ADUsersTable

–Result

USERID|USERNAME      |FULLNAME           |DEPARTMENT
 1    | MKUPTADZE    | MARIAM KUPATADZE  | IT
 2    | GBERIDZE     | GIORGI BERIDZE    | IT
 3    | VDALAKISHVILI| VASIL DALAKISHVILI| IT
 4    | SCHELISHVILI | SOPHO CHELISHVILI | IT

–To see the current value of the sequence , run the following:

DBCC checkident(ADUsersTable)

–The result

Checking identity information:current identity value '4',current column value '4'.

Note: If the desired value is less then the current value of the sequence, the current value will not be affected. As in our case, we inserted value 3, the current value was 4, that’s why current value was not affected.

But if the desired value is more then the current value of the sequence, current value will be affected.

For example:

–Repeat the previous steps, but now let’s insert higher value than the current one

SET IDENTITY_INSERT ADUsersTable ON

INSERT ADUsersTable (UserId,Username,FullName,Department)
VALUES(7,'TMAISURADZE','TEMUR MAISURADZE','IT')

SET IDENTITY_INSERT ADUsersTable OFF

–Query data

SELECT * FROM ADUsersTable

–Result

USERID|USERNAME      |FULLNAME           |DEPARTMENT
 1    | MKUPTADZE    | MARIAM KUPATADZE  | IT
 2    | GBERIDZE     | GIORGI BERIDZE    | IT
 3    | VDALAKISHVILI| VASIL DALAKISHVILI| IT
 4    | SCHELISHVILI | SOPHO CHELISHVILI | IT
 7    | TMAISURADZE  | TEMUR MAISURADZE  | IT

–To see the current value of the sequence , run the following:

DBCC checkident(ADUsersTable)

–The result

Checking identity information:current identity value '7',current column value '7'.

That’s all. 🙂

How to restore database in SQL Server 2005

I will discuss how to restore database in SQL Server 2005, by the very simple way.
So let’s start:

1. Run  SQL Server Management Studio.

2. In Object Explorer, choose Databases , right click and choose Restore Database…

Restore Database(SQL Server 2005)

3. In the new window, select the database name which you want to restore (my database name is MariamDB).

Destination for restore(SQL Server 2005)

4. On the same window, in Source for restore section, choose From device option, and click browse button.
Source for restore(SQL Server 2005)

5. In Specify Backup window, click Add button.
Specify Backup(SQL Server 2005)

6. Choose the destination where your backup(.bak) file exists, and click OK.
Locate Backup File(SQL Server 2005)

7. Picture should look like this:
Source for restore_Select the backup sets to restore(SQL Server 2005)

8. Check the row, which is showing your backup file information.
Source for restore_Select the backup sets to restore(checked)(SQL Server 2005)

9. Go to the options, and check Overwrite the existing database, and click OK.
Restore Database_Options(SQL Server 2005)

That is all! 🙂

How to Create Database Link in sql server 2005

Database link can be done in oracle(click here to see how).But if we want to do it in SQL server 2005 we should use linked servers.

Permissions to connect linked sever:

Port: 1433 must be open.

Create linked server:

1. Click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
Run SQL Server Management Studio

2. In the Connect to Server dialog box, specify the name of SQL Server, and click Connect.

3.In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

Create New Linked Server in SQL server

4. Click General, choose SQL Server option ,type the name of sql server in Linked server field

General->choose SQL Server option

5. Click Security , Choose Be made using the security context, fill Remote login and With password fields and click OK

Security->Be made using the security context option(SQL server)

6. The syntax to query data from linked server is the following:

select *
from [server name].[database name].[owner name].[table name]

–In our case

select *
from [LINKED_SQLSERVER_NAME].[database name].[owner name].[table name]