Hi!

Try:

SET @tsql1 = 'Update ' + @fullpath + 'Customer SET [cust_notes] = ''' + REPLACE(@Notes,'''','''''') + ''''

Basically, generated SQL string should have single quote around @Notes. Then, for case @Notes contains single quote, you should make it duplicated. Duplicated quote means quote is part of the string, not end of the string.


>My app is such that every customer has their own database. Every select/delete/update sproc pulls the customer database name from the configuration table into a string and I build a big fat update string and EXEC (@tql1 + @tsql2) to fire it off. This is working great for the entire app until I try to update NTEXT fields in a table, I have spent hours messing around with this and can't seem to get this going. I figure a guru out there has already been there and done this before. Any help would be greatly appreciated...
>
>Here is a sample of what I'm trying to do:
>
>CREATE PROCEDURE UpdateCustomerNotes
>(
>@CustId INTEGER,
>@Notes NTEXT
>)
>
>DECLARE @fullpath NVARCHAR(100),
> @tsql1 NVARCHAR(4000),
> @tsql2 NVARCHAR(4000)
>
>-- This next variable is actually pulling the database name from another sproc from the configuration table of every customers associated database.
>SET @fullpath = 'Customer_abc001_Data.dbo.'
>
>SET @tsql1 = 'Update ' + @fullpath + 'Customer SET [cust_notes] = ' + @Notes
>SET @tsql2 = ' WHERE [cust_id] = ' + CAST(@CustId AS NVARCHAR)
>
>EXEC (@tsq1 + @tsql2)
>
>This whole thing works if the field is a nvarchar (usually I have to put single or double quotes around the @Notes var, but as soon as I changed the field to NTEXT in the table nothing works. I've tried everything, but having to have a dynamic database name seems to screw everything up since there always has to be some sort of a variable to handle what database/file to update.
>
>TIA !!!