Cursor not declared when trying to connect

MGC22
MGC22 used Ask the Experts™
on
We have an application that accesses Axapta SQL  databases. Our sql code works correctly and accesses one customer's database, but using the same code to access
another customer's database yields the following error message:  

Error in fOpenDB = 37000: [Microsoft][ODBC Sql Server Driver][Sql Server] The cursor was not declared - err.Number = 40002

We are coding with VB6 and are using Sql Server 2000 on a Windows 2000 machine.
Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Where did you declare your cursor? on the server or the client? Note that you cannot use both cursor (i.e. in VB and in SQL Server).

Author

Commented:
I was not declaring a cursor in VB. I have since added the following code(see below) and the declare cursor error is corrected but now I receive the following error on both databases.

Error Message:   Error in fOpenExpOrd = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'CustTable' err.Number = -2147217865

Code:
oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

     Set rsADOAXOrdHdr = New ADODB.Recordset
     rsADOAXOrdHdr.CursorType = adOpenKeyset
     rsADOAXOrdHdr.LockType = adLockOptimistic
     rsADOAXOrdHdr.Open "CustTable", oConn, , , adCmdTable

Error occurs on the open, Table name is correct.

Thanks for any help

Author

Commented:
I have just noticed something else, for the database that was working the table owner is 'dbo'. For the database that is not working the owner is 'bmssa'. The not working database has a table that the owner is 'dbo' and I can gain access to it!
So I guess my new question is how do I gain access to the tables that  have the owner 'bmssa'? Will it be posible to acces both database's tables with 1 set of code?

Thanks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi!
  Try adding or creating a new database roles to assing
who-can-access-what on the database or simply try modifying the role 'public'.

--==============================
--To create a new server role named Managers
--==============================
sp_addrole 'Managers'

--==================================
--To add a security account to an existent server role
--==================================
      sp_addrolemember
            @rolename = 'Managers' ,
            @membername = 'existing_security_account'

modifiying the public role would be suitable to gant 'Options'
like 'SELECT','UPDATE','DELETE','EXECUTE'... to dessired tables
and StoredProc, you want every user have access.

Regards, Felipe
With the previously comment stated In the previous example you could try this to add bmssa to the owner server role:

      sp_adduser
             @loginame =  'bmssa',
             @name_in_db =  'bmssa',
             @grpname =  'db_owner'
Commented:
Yep, or you go to the enterprise manager -> sql server -> security -> logins. And to the database access tab inside the users properties.

Author

Commented:
Thanks for all your help.
Since the Server and database are the customers system and we just access it for verifying information and some updating I have created a variable that I save in the registry as to who the owner of the table is and then use that information when querying the data.
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
I'm Glad that you finally find a solution.

But I still wonder why... of your accepted Answer!

Best Regards, Felipe
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial