cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot Connect to New Database Server using ADO

SOLVED
Highlighted
Contributor

Cannot Connect to New Database Server using ADO

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Contributor

Re: Cannot Connect to New Database Server using ADO

The error occurs on the first instance of calling Qry[Execute], so the first loop where I call SELECT.

Contributor

Re: Cannot Connect to New Database Server using ADO

I was able to figure out the root cause of the issue with some help.

 

Turns out TestComplete likes to leave out "\" in strings, so putting \\ in my connection string got me connected properly.

18 REPLIES 18
Community Hero

Re: Cannot Connect to New Database Server using ADO

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)?

Regards,
Alex
[Community Expert Group]
____
[Community Expert Group] members are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Postings made by [Community Expert Group] members
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================
Contributor

Re: Cannot Connect to New Database Server using ADO

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.

Community Hero

Re: Cannot Connect to New Database Server using ADO

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.

Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Cannot Connect to New Database Server using ADO

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

 

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

Community Hero

Re: Cannot Connect to New Database Server using ADO

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

Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Cannot Connect to New Database Server using ADO

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.

Community Hero

Re: Cannot Connect to New Database Server using ADO

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?

Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Cannot Connect to New Database Server using ADO

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

Community Hero

Re: Cannot Connect to New Database Server using ADO

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?

Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
New Here?
Join us and watch the welcome video:
6 / 7 API Testing Mistakes Video
Top Kudoed Authors