Problem with odbc in the same server

cpina
cpina Member Posts: 52
edited 2009-07-10 in NAV/Navision Classic Client
Hello. ](*,) ](*,) ](*,)

I need inserting Navision data in different database in the same server SQL Server 2000, Navision 4.0

I insert Navision data from two different servers and the process run OK, but when I try insert Navision data in the same server the process is broken, ##Problem with Execute, timeout##
I can read data but I can´t insert data.

SERVER A DB NAVISION - insert -> DB WEB Broken
SERVER B DB Navision (Backup of server A) - insert -> SERVER A DB WEB OK

Could you help my?

Thank´s a lot

CODE
CREATE(connec);
connec.Mode:=3;
connec.Open('Provider=SQLOLEDB;Data Source=A;Initial Catalog=WEB;Password=xxxx;User ID=yyyy');

string:='INSERT INTO dbo.CAB_Demanda_Servicios_Informatica (login,CODIGO_Tipo_Servicio_Informatica,Descripcion) VALUES '+
'('+ '''' + USERID + '''' +',6,'+ '''' + 'Dar de baja a: XXX'+ _recEmployee."Search Name" + ' con fecha: ' +
FORMAT (_recEmployee."Termination Date") + '''' +')';
connec.Execute (string);
connec.Close;

Comments

  • bbrown
    bbrown Member Posts: 3,268
    I'm thinking this may be a permissions issue. Similar to when using linked tables to interact with another SQL database on the same server. Since NAV uses SQL Application roles, any access to resources not defined in the Application Role are done using the Guest account.

    Try this: (It works for me with linked tables)

    Activate the Guest acount for the other database, then grant appropriate access rights to the guest account.

    Example: 
    Use [Your_Other_DB] 
Grant Connect to guest
GO

Grant select, update on dbo.soem_table to guest
GO
    There are no bugs - only undocumented features.
  • garak
    garak Member Posts: 3,263
    bbrown is right. Check if the user (here yyyy) has the permissions to insert / update / select / delete records.
    You can set the permissions over the SSMS in the contextmenu of the user (Folder Security on the database or on the server).
    You can also set the permissions with the grant command that bbrow posted.
    Do you make it right, it works too!

Categories