how to execute (using sp_executesql or other...) heterogeneous dyn

use SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in dynamic query
"herve maillarda" wrote:

> Hi,
> I need to run execute an heterogeneous dynamic query (I build the query
> using nvarchar variable).
> Code sample :
> CREATE PROCEDURE COPY_DATA (@.ServerName nvarchar(20), @.DbName
> nvarchar(20),@.TableName nvarchar(20), @.DateDeb DateTime, @.DateFin
> DateTime) AS
> -- Build SQL Query --
> Set @.Ch_SQL = +"INSERT INTO " + @.ServerName + "." + @.DbName + ".dbo." +
> @.TableName + " ( Date_Heure, Valeur )"
> Set @.Ch_SQL =@.Ch_SQL + " SELECT Date_Heure,Valeur FROM " + @.DbName +
> ".dbo." + @.TableName + " WHERE (Date_Heure >= CONVERT(DATETIME, '" +
> CAST(@.DateDeb AS nvarchar(20)) + "', 102) AND Date_Heure <=
> CONVERT(DATETIME, '" + CAST(@.DateFin AS nvarchar(20)) +"', 102))"
> -- Execute query
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> exec sp_executesql @.Ch_SQL
> GO
>
> I've alway an error coming from SQL server..."You must set ANSI_NULLS
> and ANSI_WARNINGS"...
> If I try the query from the Analyzer it works ...
> How can I do ?
> Thanks for your help...
> Herve.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>Won't work...
I still have the same error message coming from the Query Analyzer
"[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
connection."
I've try to add the options in my query (as you told me) :
Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO " +
@.ServerName ...
Do I miss something ?
thanks for your help.
Herve MAILLARD
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi
Do you have triggers that have not had the settings correctly defined?
http://groups-beta.google.com/group...br />
915f7f68
John
Herve MAILLARD wrote:
> Won't work...
> I still have the same error message coming from the Query Analyzer
> "[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
> require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
> connection."
> I've try to add the options in my query (as you told me) :
> Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO "
+
> @.ServerName ...
> Do I miss something ?
> thanks for your help.
>
> Herve MAILLARD
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!sql