We have a multi-module application that uses lots of LIKE statements in the SQL scripts. The scripts install find in the Host SQL option, but will not install in the Package Assembly installation.
Testing PA on version 4.8.1. Here is one of the select scripts that fails, with the point of failure marked...
PROCEDURE [dbo].[WebCo_STSCustomers_R] (
@CustID
int
, @PortalID int
, @Name nvarchar(50)
, @City nvarchar(50)
, @State nchar(2)
)
AS
SET
SET
NOCOUNT ON XACT_ABORT OFF -- Allow procedure to continue after error
DECLARE
@ErrorNo int -- Local variable to capture the error code.
DECLARE
@RowCount int -- Number of rows returned
DECLARE
-- *******************
-- Set local variables
-- *******************
@ErrMsg nvarchar(1000) -- Error message to return
SET
@ErrorNo = 0
SET
@RowCount = 0
-- ************
-- Get the data
-- ************
SELECT
CustID
= [CustID], PortalID = [PortalID], Name = [Name], Address1 = [Address1], Address2 = [Address2], City = [City], State = [State], PostalCode = [PostalCode], PrimaryPhone = [PrimaryPhone], AltPhone = [AltPhone], CellPhone = [CellPhone], Contact = [Contact], EMailAddress = [EMailAddress]
FROM
STSCustomers
WHERE
AND
(([CustID] = @CustID) OR (@CustID IS NULL))
AND
(([PortalID] = @PortalID) OR (@PortalID IS NULL))
AND
(([Name] LIKE @Name + '%') OR (@Name IS NULL)) <==This line is where it appears to fail...
AND
(([City] LIKE @City + '%') OR (@City IS NULL))
(([State] = @State) OR (@State IS NULL))
SELECT
@ErrorNo
= @@Error
, @RowCount = @@RowCount
IF
(@ErrorNo != 0)
BEGIN
SELECT @ErrMsg = 'Error getting STSCustomers records in [dbo].[WebCo_STSCustomers_R]'
GOTO
GOTO ENDERROREND ENDOK
-- **************
-- Error Handling
-- **************
ENDERROR:
BEGIN
ENDOK:
RAISERROR 25000 @ErrMsgRETURN 1END
RETURN 0
GO
The scripts work find in SQL and in the Host SQL option. Syntax appears to be a DNN issue...
Anyone got any ideas?
CREATE