Hello All,

I'm working on a .NET application that uses the OleDB Provider to access / update the SalesLogix database. 

When I execute the SProc in SQL it executes fine, but when I execute in .net, the OleDB exception says 'Failed to parse SQL.'

Sage support said to parse the SQL syntax of the procedure using the Execute SQL tool in Administrator. But, it failed on the first line of the procedure where it declares a variable 'DECLARE @sVariable varchar(10)'.  Does this mean you can't execute any stored procedures that use variables?

I've read the posts about executing stored procedures thru SLX, but I don't understand why you can't use stored procedures?  As an SQL developer, I know it's faster to execute SQL stored procedures on the SQL server than to bring the recordsets into .net and loop through them.

Can anyone be of assistance?
Thanks in advance!

I've posted how to execute a stored procedure from with SLX using vbScript and a SQL Command object.

T-SQL and all of that rot is a SQL programming language, it isn't 'strictly' SQL and certainly will NOT execute in SLX Administrator EXECUTE SQL.

Lookup up examples of how to execute a stored procedure from .Net and code away.

and you won't be going through the SLX OLE DB Provider. The SLX OLE DB Provider processes SQL Statements, not Stored Procedures, temporary tables, UDF's, T-SQL, etc.

You're mixing idioms here......

Here's Microsoft's instructions on how to execute a SQL Stored procedure from ASP .NET for example:

http://support.microsoft.com/kb/306574

Good luck!

Does your SLX database have any remote users?

If so, any db amendments made by the stored procs you execute on the host db will not sync out to them.

I also contacted the poster [KWengiel] on ITToolbox as wll (and as Phil did).

We use a ton of T-SQL, Stored Procedures, Triggers, User Defined Functions, wild\complex SQL.....for returning result sets and storing non sync data (like OLAP BI data for corporate). If you want this data to synch it's easy if you architect it properly....

Use the above stuff plus native SQL Server connections for FAST, complex query, returns of data sets in READ ONLY mode....now that you have your work list and data.....loop through it and insert\update through ADO\datasets and the SLX OLE DB Provider one insert or update method (or command object) at a time......those statements synch to remote offices, remote users, and the LAN database with no problems.....

We found out the hard way that retrieving 10,000 records joined to X tables of data through the SLX OLE DB Provider as a user is simply PAINFUL. The key is as a user.....because each record returned is treated as a seperate SQL Transaction......Begin Trans.....End Trans.....and SLX performs its User SECODE security and FLS for each record......(ADMIN it doesn't, which is why developers need to always test this stuff as a user! always).

OK RJ, the process I am trying to create inserts new account records into the AccountSummary, Address and Account tables. But, I don't want to create a new Account without an Address, so I wanted to use a transaction, hense the reason for the original SQL stored proc.

I took the data out of the SQL stored procedure and put it into an ADO recordset. Then, I loop thru the recordset and pass the insert statements to the OleDB provider.

If I pass the SQL statements to the OleDB provider individually, they execute, successfully.  But, if I pass them to the provider in a oledb transaction, the statement fails to parse the SQL on the first command and the transaction is rolled back.

Any help would be greately appreciated! 

You can't mix command objects and ADO Begin End Trans execution.....the Command object, when executed, is executed.....keep this all in ADO only.

So open up the write ADO record set Begin Trans, Update, End Trans.....

I don't know how well the OLE DB PRovider does for transaction processing for 7 records in one transaction, but you could always try it out.....

BTW, I don't worry about TRANSACTION Processing for something like 99.9999999% of the stuff that I've done over the last decade+4.

Take a look at how SLX stores Account, AccountSummary, Contact, Account Address(es), Contact Address(es) and any other tables needed when it stores a new contact account.

Works like a charm.

Now if you want to sbe uper picky on this (like this is a business critical Payroll App instead of CRM), you could always fetch your stored records back to make sure they were inserted.....if not DELETE them from the database......