Cannot Connect to New Database Server using ADO

[frank_vanderstr]

I have created automation scripts that used an ADO command object to connect to our internal server's databases and make several select, update and insert statements, and that would run great.

We recently migrated to a new database server and now even with the proper edits to my connection string, I can't appear to connect to the database despite the connection string test being a success.

Here is an example of my code.

var Qry = ADO.CreateADOCommand();

var dbname = DatabaseName;
Qry["ConnectionString"] = "Provider=SQLOLEDB.1;Password=pw;Persist Security Info=True;User ID=SQLServerUsr;Initial Catalog=dbname;Data Source=Servername";
for(var i = 1; i <= rowcount; i++) {
                   var testcell = VarToStr(xlsheet.Cells(i+1, 61).Value);
                     Log.Message(testcell);
                   tags[i] = testcell;
                  Qry["CommandText"] = "SELECT catalogid FROM oproducts WHERE oproducts.cname = '" + prodnamesarray[i] + "'"
                 Qry.CommandType = cmdText;
               var catalognum = Qry["Execute"]();
              prodids[i] = catalognum.Fields.Item("catalogid").Value;

}

for(var j = 1; j < rowcount; j++) {
            if(tags[j] != "") {
                   Qry["CommandText"] = "INSERT INTO MenuTagProductMap (MenuTagID, MenuProductID, EnteredDate) VALUES('" + tags[j] + "', '" + prodids[j] + "', GETDATE())";
                  Qry.CommandType = cmdText;
}
}

I also made my connection string by using the stores object in testcomplete to connect to my database initially.

I was made aware of permission changes on the new server, but I have had my test user given the proper permissions, but it doesn't appear to be connecting still, anyone have any ideas as to why this may be?

[AlexKaras]

Hi,

-- Is it *only* the name of the database server that changed with *everything* else left as it was?

-- What is the error (error code, error text, anything else) that is returned on connect failure?

-- Can you connect to this server from some other tool (command-line, management console, etc.) using the same machine and user (both Windows and database ones)?

[frank_vanderstr]

From what I was told the permissions of non Database Administrator users has changed, but I did have a special user made that should have the permissions needed.

The specific error is Microsoft OLE DB Provider for SQL Server. :[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I was also able to connect to the server from SQL Manager using the created user. The test connection is also a success when using the TestComplete Database Objects.

[tristaanogre]

Double check some things concerning your username, password etc... make sure there are no "special" charcaters that may need special handling to be passed through properly.

[frank_vanderstr]

Yup, no special characters in either unless "_" counts.

Password is a combination of upper and lowercase letters and numbers.

[tristaanogre]

Just out of curiosity. ... have your tried creating an ADO query object instead of a command object? Or just simply create and ADO Connection object just to make sure that works. It seems strange that the connection string works fine in TC in one specific situation but not when creating ADO objects, almost like the new permissions are preventing certain kinds of operations

[frank_vanderstr]

I have not tried creating and ADO query object, as the ADO command was the most easy for me to understand and execute in code.

Permissions might be the culprit, but the user on SQL has the needed permissions for executing Selects and Updates.

Connecting seems to work but when I go to execute a sql statement I get the error.

[tristaanogre]

Well, you aren't doing a Select or Update... you're doing an Insert... not sure how granular permissions can go in MS SQL but perhaps that's the cuplrit?

[frank_vanderstr]

I am doing a select first, if you look in the first for loop.

[tristaanogre]

Is that where they error is generating? I perhaps missed it but is it possible that the error is happening on the Insert and not the Select?