I have written a VB.Net application using a SQLConnection to connect to a SQL
2000 database. The application uses an update statement in a SQLDataAdapter
to make changes to a table on the server.
The problem is that one client is getting an error with the update failing
due to string truncation. The other clients do not get an error. The only
part of the string in question that matters is the first letter, so there is
no data integrity issue.
I realize that I could put the update in a stored proc and set ANSI_WARNINGS
OFF, and then call the proc. But, I was interested in why this only is a
problem on one client.
The ANSI_WARNINGS db_option for the database is FALSE. I have searched the
MS KB and used Google Groups to search microsoft.public.* to no avail.
From what I have read, there is no way to set ANSI_WARNINGS for the
SQLConnection object. The thing that confuses me is why all clients are fine
except that one. From what I can determine, all have the same version of SQL
Server ODBC driver, etc.
Any suggestions would be appreciated.
It could be that, that particular client's table structure is different.
Check and make sure that table's column widths are the same as the ones on
the other databases.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"A. Powers" <APowers@.discussions.microsoft.com> wrote in message
news:28CE89D3-8B4A-4218-8306-1168274499B7@.microsoft.com...
I have written a VB.Net application using a SQLConnection to connect to a
SQL
2000 database. The application uses an update statement in a SQLDataAdapter
to make changes to a table on the server.
The problem is that one client is getting an error with the update failing
due to string truncation. The other clients do not get an error. The only
part of the string in question that matters is the first letter, so there is
no data integrity issue.
I realize that I could put the update in a stored proc and set ANSI_WARNINGS
OFF, and then call the proc. But, I was interested in why this only is a
problem on one client.
The ANSI_WARNINGS db_option for the database is FALSE. I have searched the
MS KB and used Google Groups to search microsoft.public.* to no avail.
From what I have read, there is no way to set ANSI_WARNINGS for the
SQLConnection object. The thing that confuses me is why all clients are
fine
except that one. From what I can determine, all have the same version of
SQL
Server ODBC driver, etc.
Any suggestions would be appreciated.
2012年2月11日星期六
ANSI_WARNINGS error in Stored Procedure
Hi everyone,
I have a linked Server on my regular SQL server, and when I try to create a
stored procedure that reads from the linked server, I get the following erro
r:
ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS O
PTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES SEMANTI
CS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
When I click Check Syntax, it says that there are no Syntax Errors, but it w
ont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and "SET
ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same erro
r.
The query that is to be performed in the stored procedure is a simple SELECT
, no inner joins or anything. And the stored procedure receives only 1 (inte
ger) parameter.
Thanks in advance for your help.
Noe, from Mexico.Noe,
From the error I would understand that the linked server is not a Microsoft
SQL Server. Otherwise, how likely is it that the SQL Engine would be
concerned with "heterogeneous queries".
The error says to set these options for your _connection_ not for the query.
Since you are already connected before you try to compile the stored
procedure, it is too late for the SET commands to avoid the problem.
If you are using Query Analyzer to test this, then go to the menu item for
the connection and click these two settings on. (I believe that these are
normally set on by OLE DB / ODBC, but Query Analyzer may override these
settings.)
Russell Fields
"Noe" <noech77@.hotmail.com> wrote in message
news:D4F795B7-C67A-4F28-B95D-85B5FF7C7293@.microsoft.com...
a stored procedure that reads from the linked server, I get the following
error:
OPTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES
SEMANTICS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
wont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and
"SET ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same
error.
SELECT, no inner joins or anything. And the stored procedure receives only 1
(integer) parameter.
you are correct, the linked server is a JDEdwards.
I was trying to create the stored procedure from the New Stored
Procedure Window, instead of the Query Analyzer. I dont know why I didnt
try the Query Analyzer before.
So there's a tip for all of you people, use the query analyzer and
forget about problems. Thanks again Russell!
Noe.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I doesn't mater which Database you use. Even in SQL server when we
link two different server, we get this problem. Once way of solving
this is create the stored procedure in SQL server in the format below
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
CREATE PROCEDURE dbo.MBUTotal
AS
BODY of THE STORED PROCEDURE
RETURN
GO
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
This will solve this problem. Atleast for me it helped to solve the
problem.
Regards,
John Solomon. J
Noe Carranza wrote:
> *Hi Russell, thanks a lot for replying...
> you are correct, the linked server is a JDEdwards.
> I was trying to create the stored procedure from the New Stored
> Procedure Window, instead of the Query Analyzer. I dont know why I
> didnt
> try the Query Analyzer before.
> So there's a tip for all of you people, use the query analyzer and
> forget about problems. Thanks again Russell!
> Noe.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it! *
john1811
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message291544.html
I have a linked Server on my regular SQL server, and when I try to create a
stored procedure that reads from the linked server, I get the following erro
r:
ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS O
PTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES SEMANTI
CS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
When I click Check Syntax, it says that there are no Syntax Errors, but it w
ont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and "SET
ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same erro
r.
The query that is to be performed in the stored procedure is a simple SELECT
, no inner joins or anything. And the stored procedure receives only 1 (inte
ger) parameter.
Thanks in advance for your help.
Noe, from Mexico.Noe,
From the error I would understand that the linked server is not a Microsoft
SQL Server. Otherwise, how likely is it that the SQL Engine would be
concerned with "heterogeneous queries".
The error says to set these options for your _connection_ not for the query.
Since you are already connected before you try to compile the stored
procedure, it is too late for the SET commands to avoid the problem.
If you are using Query Analyzer to test this, then go to the menu item for
the connection and click these two settings on. (I believe that these are
normally set on by OLE DB / ODBC, but Query Analyzer may override these
settings.)
Russell Fields
"Noe" <noech77@.hotmail.com> wrote in message
news:D4F795B7-C67A-4F28-B95D-85B5FF7C7293@.microsoft.com...
quote:
> Hi everyone,
> I have a linked Server on my regular SQL server, and when I try to create
a stored procedure that reads from the linked server, I get the following
error:
quote:
> ERROR 7405: HETEROGENEOUS QUERIES REQUIRE THE ANSI_NULLS AND ANSI_WARNINGS
OPTIONS TO BE SET FOR THE CONNECTION. THIS ENSURES CONSISTENT QUERIES
SEMANTICS. ENABLE THESE OPTIONS AND THEN REISSUE YOUR QUERY.
quote:
> When I click Check Syntax, it says that there are no Syntax Errors, but it
wont let me save the stored procedure. I did put "SET ANSI_NULLS ON" and
"SET ANSI_WARNINGS ON" in the stored procedure, but I keep getting the same
error.
quote:
> The query that is to be performed in the stored procedure is a simple
SELECT, no inner joins or anything. And the stored procedure receives only 1
(integer) parameter.
quote:|||Hi Russell, thanks a lot for replying...
> Thanks in advance for your help.
> Noe, from Mexico.
you are correct, the linked server is a JDEdwards.
I was trying to create the stored procedure from the New Stored
Procedure Window, instead of the Query Analyzer. I dont know why I didnt
try the Query Analyzer before.
So there's a tip for all of you people, use the query analyzer and
forget about problems. Thanks again Russell!
Noe.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I doesn't mater which Database you use. Even in SQL server when we
link two different server, we get this problem. Once way of solving
this is create the stored procedure in SQL server in the format below
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
CREATE PROCEDURE dbo.MBUTotal
AS
BODY of THE STORED PROCEDURE
RETURN
GO
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
This will solve this problem. Atleast for me it helped to solve the
problem.
Regards,
John Solomon. J
Noe Carranza wrote:
> *Hi Russell, thanks a lot for replying...
> you are correct, the linked server is a JDEdwards.
> I was trying to create the stored procedure from the New Stored
> Procedure Window, instead of the Query Analyzer. I dont know why I
> didnt
> try the Query Analyzer before.
> So there's a tip for all of you people, use the query analyzer and
> forget about problems. Thanks again Russell!
> Noe.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it! *
john1811
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message291544.html
订阅：
博文 (Atom)