I am writing a codeunit in Nav 2.0 (us) that executes a stored procedure on sql server 7.0. I am having trouble getting the Command.ActiveConnection property instantiated with the correct connection string. The following is a sample of my code: ConnectionString := ‘Driver={SQL Server};Server=Server4;Uid=SqlUser;Pwd=;Database=TestWeb1’; Conn.ConnectionString := ConnectionString; Conn.CommandTimeout := 30; Conn.Open; ReceiptCmd.ActiveConnection := conn ReceiptCmd.CommandText := ‘sproc_dcstore_finrepl_receipt’; ReceiptCmd.CommandType := 4; ReceiptRS := ReceiptCmd.Execute; The codeunit will not compile with the active connection property assignment to “conn”. I get the errror Type conversion is not possible. The activeConnection property is looking for an integer, but ADO syntax specifies a basic assignment operation. I can execute sql code using the connection object and a text sql string, but I would like to use the server side processing available with a stored procedure, as well as keep the business logic within the SQL DB. Has anyone invoked a stored procedure using a command object within Navision 2.0? Thanks Programmer / Analyst DC Shoes, Inc. 1333 Keystone Way Vista, CA 92083
After hours of combining different combinations, I figured out what Navision needs. Here is the code: Create automation variables using MDAC 2.5 ConnectionString := ‘Driver={SQL Server};Server=Testserver;Uid=test;Pwd=;Database=TestWeb1’; //Execute the ReceiptCmd Object ReceiptCmd.CommandTimeout := 30; ReceiptCmd.ActiveConnection := ConnectionString; ReceiptCmd.CommandType := 4; //adCmdStoredProc ReceiptCmd.CommandText := ‘sproc_dcstore_finrepl_receipt’; ReceiptRS := ReceiptCmd.Execute; The connection object does not need to be instantiated at all, and the commandTimeout must be specified before the active connection connection string is assigned.
Moved to SQL