Re: Stored Procedure Error

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Replicating stored procedures that use double-quotes (") instead of single
quote for delimiting string literals is (') sadly not supported up to
SQL2000. The code to support this is quite substantial so I doubt it will
ever be back-ported. In the meantime, your only choice is to rewrite the
stored procedures to use ' for delimiting strings. I will let you know if I
ever get the chance to write an automated tool for this.

-Raymond

"A. Robinson" wrote:

> I'm setting this up through the GUI, not scripts. Unless there is a way to
> generate the scripts the EM.
>
> Here is one of the offending procedures:
>
> CREATE PROCEDURE snasp_ASSESSMENT_Get_RaAssmtAnnualAndYearlyStatusReason
> @CustID int= Null,
> @EntityID int=Null,
> @ResID int =Null,
> @visitNumb int = Null,
> @AssmtStatusCode char(4)=Null,
> @AssmtReasonCode char(4)=Null
> AS
>
> Declare @strSQL varchar(1000), @strFilter varchar(500)
> Declare @ViewName varchar(50), @AssmtSuperClassID int
> Create Table #SectionV ( ra_id int)
> Set @strSQL = ""
> Set @strFilter = ""
> Set NOCOUNT ON
>
> Set @ViewName = 'Fed_RaAssmtStatusReasonView'
>
> if @CustID is not Null
> Begin
> Set @strFilter = "a.cust_id = " + RTrim(convert(varchar(15),@CustID)) + "
> AND "
> End
> if @EntityID is not Null
> Set @strFilter = @strFilter + "a.entity_id = " +
> RTrim(convert(varchar(15),@EntityID)) + " AND "
> if @ResID is not Null
> Set @strFilter = @strFilter + "a.res_id = " +
> RTrim(Convert(varchar(15),@ResID)) + " AND "
> if @visitNumb is not Null
> Set @strFilter = @strFilter + "a.visit_numb = " +
> RTrim(Convert(varchar(15),@visitNumb)) + " AND "
> if @AssmtStatusCode is not Null
> Set @strFilter = @strFilter + "a.assmt_status_code = '" +
> RTrim(@AssmtStatusCode) + "' AND "
> if @AssmtReasonCode is not Null
> Set @strFilter = @strFilter + "a.assmt_reason_code = '" +
> RTrim(@AssmtReasonCode) + "' AND "
>
> Set @strFilter = @strFilter + " ( a.assmt_type_desc= 'MDS Type 1 A/AO' or
> a.assmt_type_desc='MDS Type 2 AM' or a.assmt_type_desc='MDS Type 3 Y/YO'
> or a.assmt_type_desc= 'MDS Type 4 YM' ) "
> Set @strFilter = @strFilter + " and ( (a.medicare_casemix is not null and
> a.medicare_casemix <> '' ) or (a.medicaid_casemix is not null and
> a.medicaid_casemix <> '' )) "
>
> --medicare_casemix
>
> Insert Into #SectionV (ra_id )
> Select ra_id FROM dbo.FedRaSectionView Where cust_id = @CustID and entity_id
> = @EntityID and res_id = @ResID and assmt_section = 'V'
>
> Set @strSQL = "Select a.*, SectionV_Flag = ( Case When b.ra_id is null then
> 0 else 1 end ) From " + @ViewName + " a Left join #SectionV b on a.ra_id =
> b.ra_id Where " + @strFilter + " Order By a.assmt_date desc "
>
> Exec(@strSQL)
>
>
>
> GO
>
>
>
> "Hilary Cotter" wrote:
>
> > please post your publication scripts as well as the schema of the
> > objects you are replicating here (or send them to me offline)
> >
> > --
> > Hilary Cotter
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
.

Relevant Pages

  • Re: Intercepting invalid parameters to a stored procedure
    ... the stored procedures for them. ... @OrderNumber int output ... @Qty int = 1 ...    INSERT INTO Order (CustomerName, ProductId, Qty) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: how to decrypt an encrypted stored proc in 2005
    ... Suppose that the stored procedures are needed exclusively from a particular ... DECLARE @intProcSpace bigint ... DECLARE @procNameLength int ... declare @BasePos int ...
    (microsoft.public.sqlserver.security)
  • Re: stored procedure return value question
    ... sqlserver stored procedures return result sets from a select, and a int ... direction of type ReturnValue. ... ExecuteScaler returns the value of the first column, of the first row, of ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Updating sql05 with Stored Procedure
    ... Have you provided stored procedures for select/insert/update/delete? ... @Original_CategoryID int, ... SET NOCOUNT OFF; ... SR.lutCategories WHERE (CategoryID = SCOPE_IDENTITY()) ...
    (microsoft.public.dotnet.framework.adonet)
  • SQL Server 2005 Replication - Permission Problems`
    ... I have a database in which I am merge replicating stored procedures. ... and reinitialise the subscription. ... We have granted execute permissions on all of these stored procedure to a ...
    (microsoft.public.sqlserver.replication)