Oracle, Linux, AWS, Azure, GCP
December 26, 2011 1 Comment
Run SQL Server Management Studio.
In Object Explorer, expand the server.
Right-click on the SQL Server Agent and then click its Properties.
Click Alert System.
Check Enable Mail Profile.
In the Mail system list choose Database Mail.
In the Mail profile list select a mail profile.
Restart the SQL Server Agent
Good Luck!
November 7, 2011 Leave a comment
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.
Next>
Next>
Click Add…
Click OK…
Next>
Next>
Next>
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.
Next>
Next>
Next>
5. Creating job…
Insert our code in the Command field:
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..
September 12, 2011 Leave a comment
If you have the following error during backup/restore of SQL Server database:
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:
Click OK, there will appear the following window:
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 issue
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…
April 6, 2011 Leave a comment
Run the installation. You can download it from here.
Click Next>
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 
Click Start->All Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration…Click Surface Area Configuration for Services and Connections
Click OK, and reload Database Engine service:
Click OK.
February 28, 2011 1 Comment
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:
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 
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…
January 28, 2011 1 Comment
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.
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.
1.4 In the Server Roles, public and sysadmin roles should be checked.
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).
1.6 In Status.
1.7 Right click on the server in the Object Explorer window, choose properties, in Security, choose SQL Server and Windows Authentication mode.
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.
2.2 Choose SQL Server, click Finish.
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.
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).
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).
2.6 Leave default settings.
2.7 This window shows you the summary, click Test Data Source button.
2.8 If you have indicated the correct settings, it should be successful.
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
January 27, 2011 Leave a comment
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]
January 26, 2011 Leave a comment
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. 🙂
January 20, 2011 Leave a comment
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…
3. In the new window, select the database name which you want to restore (my database name is MariamDB).
4. On the same window, in Source for restore section, choose From device option, and click browse button.
5. In Specify Backup window, click Add button.
6. Choose the destination where your backup(.bak) file exists, and click OK.
7. Picture should look like this:
8. Check the row, which is showing your backup file information.
9. Go to the options, and check Overwrite the existing database, and click OK.
January 17, 2011 4 Comments
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.
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.
4. Click General, choose SQL Server option ,type the name of sql server in Linked server field
5. Click Security , Choose Be made using the security context, fill Remote login and With password fields and click OK
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]
DBA Knowledge Base 