Problem with odbc in the same server

cpina

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;

bbrown

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

garak

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.