asked on

Cursor not declared when trying to connect

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.

CJ_S

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

MGC22

ASKER

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

MGC22

ASKER

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

felipe_51

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

felipe_51

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'

ASKER CERTIFIED SOLUTION

CJ_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MGC22

ASKER

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.

felipe_51

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
I'm Glad that you finally find a solution.

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

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