How to connect SQL server using QTP script

1) We should know the below details before establish database connection with SQL server.


Provider = SQLOLEDB.1  (Default for all SQL server connection)
Server   = Server_Name
uid = User_ID
Password = Password for login into SQL server.
Database = Name of the database


2) QTP dont have the built-in function for access SQl server. So we can use access the data in SQL server by using ADODB connection. SO we should create object for ADODB connection and Recordset,

Set objcon=CreateObject("ADODB.Connection")
Set objrs=CreateObject("ADODB.RecordSet")

3) Establish database connection by using connection string

Syntex : 

objcon.open "Provider= Provider name (SQLOLEDB.1);Server=server_Name;uid=User_ID;Password=User_Password;Database=Database_Name;"

Example:

objcon.open "Provider=SQLOLEDB.1;Server=SQLServerName;uid=arunrajvdm;Password=arunrajvdm;Database=arunrajvdmdatabase;"

Here,

Provider = SQLOLEDB.1  (Default for all SQL server connection)
Server   = SQLServerName
uid = arunrajvdm
Password = arunrajvdm
Database = arunrajvdmdatabase


4) Execute Query

Syntex:

objrs.open "SQL_Statements",object of ADODB connection(objcon)

Example:

objrs.open "Select * from Emp",objcon


5) Access the data from database by using column_Name

we can use Fields property of Recordset object.

Syntex :

objrs.Fields("Column_Name")

Example

objrs.Fields("First_Name")



Sample Script:

Extracting the data from SQL Server database and update into the Excel


Set xl=CreateObject("Excel.Application")
Set xlwb=xl.Workbooks.Open("E:\SQLConnection.xls")
Set xlws=xlwb.Worksheets("TestData")

Set objcon=CreateObject("ADODB.Connection")
Set objrs=CreateObject("ADODB.RecordSet")

objcon.open "Provider=SQLOLEDB.1;Server=SQLServerName;uid=arunrajvdm;Password=arunrajvdm;Database=arunrajvdmdatabase;"
objrs.open "Select * from Emp",objcon

i=1
While not objrs.EOF
 xlws.Cells(i,1)= objrs.Fields("Emp_Name")
 objrs.MoveNext
 i=i+1
Wend


objrs.Close
Set objrs=nothing
objcon.Close
Set objcon =nothing

xlwb.Save
xl.Quit

Set xlws=nothing
Set xlwb=nothing
Set xl=nothing