How to connect to Microsoft SQL Server Database

[Anusha_7199]

Hi Everyone,

In one of my project, I need to connect to database and then run a sql query to get the number of tables in the database, and I am trying to do this using installscript, but I am facing many issues, I tried it using ADO object, but there are some errors poping up, so does anybody know how to get this, please help me, i'm totally stuck in this

I have used this code:-

sConnString = "driver={SQL Server};";
sConnString = sConnString + "server=" +szServer+";";
sConnString = sConnString + "uid=" +szUsername+";";
sConnString = sConnString + "pwd=" +szPassword+";";
sConnString = sConnString + "database=" +szDb;

try
set AdoConnectionObj = CoCreateObject("ADODB.Connection");
AdoConnectionObj.ConnectionString = sConnString;
AdoConnectionObj.Open;

set AdoCommandObj = CreateObject("ADODB.Command");
AdoCommandObj.ActiveConnection = AdoConnectionObj;
AdoCommandObj.CommandText = szQuery;
AdoCommandObj.Execute();
catch

Is this right, please correct me if I'm wrong.

Also please let me know if there is anyother way to get this.

[ch_eng2]

@Anusha_7199 ,

Here are some corrections to your SQL connection string:

sConnString = "Provider=sqloledb;";
sConnString = sConnString + "Data Source=" +szServer+";";
sConnString = sConnString + "User Id=" +szUsername+";";
sConnString = sConnString + "Password=" +szPassword+";";
sConnString = sConnString + "Initial Catalog=" +szDb;

reference: https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/

HTH

[Anusha_7199]

Thank you for your reply,

I tried this but still I'm getting some error.

I have attached the screenshot of the error.

 

Please let me know If there is any other function in installscript to connect with database.

[ch_eng2]

1. Are you certain the  User Id and Password are correct?

2. Can you test the connection information using SQL Server Management Studio?