ADS: Sample application cannot access desktop database

The sample application provided with Access Database Synchronizer fires a "A request to send data to the computer running IIS has failed. For more information, see HRESULT." exception on my WinCE 4.2 device.

I debugged it with VS2005 to see what was going on and found that the problem resides in the line:

m_connDesktop.Pull("MSysObjects","SELECT Name FROM MSysObjects WHERE Type=1 and flags=0",accessSysConnectString,RDATrackOption.TrackingOff,"Err_MSysObjects");

It seems that connection m_connDesktop is ok but calling Pull method fails.

I've verified that SSEvAccessSync service is started when ActiveSync detects device connection and that firewall rules are correctly updated.

Also, i verified my System.mdw and i discovered that there is no "MSysObjects" table (only MSysGroupList, MSysGroupMembers, MSysUserList and MSysUserMemberships queries are there).

Do you think the problem is there?

How can i solve it without damaging System.mdw?

I cannot figure out what's the purpose of this line of code, can you help me?

Just a summary of what i've tried so far (unsuccessfully):

1. Once discovered MSysObjects hidden table, i changed "Tools->Security->Users and groups auth" to grant read privileges to the user used for connection

2. I've tried to change connection string to include "User Id=...". Anyway, connection object is created successfully: the error appears on Pull method invocation

3. I've configured SQL Server (SQLEXPRESS) to accept network connections, enabled SQL Server Browser and changed firewall settings accordingly

Feedback from someone that put it to work needed, please...

|||

Hi Fabio,

MSysObjects is not a hidden table inside System.mdw. It is a system table available with every access database.

The error message you are getting indicates desktop is not accepting packets.You can try the following scenario:

1) Before doing the Sync from Given Sample App. Just check if you have written the configuration information like Desktop URL, User Name etc. correctly. Default port Number through which we communicate is 1024. If you have changed the port number by editing registry , then you need to change your firewall setting and Desktop URL accordingly.

2) System.mdw file is located at "C:\Document and setting\<LOGIN ID>\System.mdw". If your Windows installation drive is not C:\ drive. you can change the driver letter by editing config.xml file located in device at "\Program File\AccessSync\Config.xml" under WIN_DIR node.

3) If above information is not helpful, you can open the internet explorer browser at device side and put the desktop URL in the address bar. If you are getting information "Page cannot be found. check the name and try again". It means desktop is not accepting packet at given port. you can try the same URL at desktop side internet explorer. If it is working at desktop side and not working at device side, it means connection from desktop is not proper through active sync. If desktop side also URL is not working, it means service is not started or you are having incorrect port number in URL.

Let me know if it doesn't help.

|||

Thank you pakumar,

it seems a connection through activesync problem (see 3.):

1. Here's my Config.xml:

<?xml version="1.0" encoding="UTF-8"?>

<SystemNode>

<AccessDB>C:\TestDB.mdb</AccessDB>

<DeviceDB>\My Documents\TestDB.sdf</DeviceDB>

<Login>Admin</Login>

<URL>http://PCNAME:1024/</URL>

<WinDir>C:</WinDir>

</SystemNode>

"Admin" is the Windows user logged in during synchronization.

I have opened port 1024 (not changed any registry key and checked it anyway) and granted access to "svcssevas31.exe" application. Anyway from now on i decided to shutdown windows firewall until problem solved.

2. My Windows installation drive is C:\. Debugging the app, i've verified that the connection string points to the correct location of System.mdw. I've found MSysObjects table and granted r/w data and schema access to Admin user.

3. From desktop it DOES work. From device it DOESN'T. I'm confused about this because:

From device's shell, i can successfully ping desktop IP address (seen as 127.0.0.1) and machine name|||

Further tests:

URL http://PCNAME:1024/ is accessible from desktop machines on the same network (also with firewall enabled).|||

Hi Fabio,

1) I can see you have installed both 2.0 and 3.0 device cabs. You can uninstall all 2.0 cabs sql.ppc.wce4.armv4.CAB and sql.dev.ENU.ppc.wce4.armv4.CAB . you should keep only 3.0 cab bits. That should solve the problem.

2) You have installed Wince 5.0 cab of sample application to WinCE 4.2 device. You can build the given sample application for WinCE 4.2 or PPC 2003 SE and copy the exe generated to \Program Files\AccessSync\ folder in device.

3) You can try the sample application on device emulator also if you have it, just to gain confidence in the product.

Let me know if you require more help.

ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005

Hello,

I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.

To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.

try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");

............................ code here ........................................

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));

VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.

pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);

printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}

Cheers,

Seth

You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.

Here is what you should do:

_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));

pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();

ADO - Cannot access the Return Parameter of a stored procedure on SQL Server 2005

Hello,

I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.

To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.

try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");

............................ code here ........................................

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));

VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.

pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);

printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}

Cheers,

Seth

You should not include returnVal into the call of CreateParameter. What happens is that compiler creates a temporary object which is useless to you.

Here is what you should do:

_ParameterPtr pReturnParam = pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t));

pCmd->Parameters->Append(pReturnParam);
pCmd->Execute(0,0,adCmdStoredProc);
variant_t value = pReturnParam->GetValue();