Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Titre:
Can't get NTEXT into a remote database
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 !!!
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement