ANSI_NULLS options successfully changed.But Error occured.

Hi,

Accoding to coding the SP was successfully created.But Once its going to be

executed through vb ADO cording, its giving following error message.

MSDTC on server "NISHANTHA" unavailable.

NISHANTHA is a working server.Particuler SP is exists in that server which is

accessing another server.

Please send me why it is and How to solve.Please attach sample cording.

Thanks,

Pubudu

This error suggests that you will need to start the 'Distributed Transaction Coordinator' NT Service on your NISHANTHA server.

Chris

ANSI_NULLS OFF SQL2K5

Hi folks,
I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
automatically the settings (global)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
When I modify the SP cliccking on "Script store procedure as" and then
"ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
problem is when I modify the SP cliccking on "Modify"; if the SP was modified
first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
such setting in ON. Very weird!
Any insight about how to preserve such setting in OFF is really welcome.
Thanks. Roberto.BOBNET wrote:
> Hi folks,
> I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
> setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
> automatically the settings (global)
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFIER ON
> When I modify the SP cliccking on "Script store procedure as" and then
> "ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
> problem is when I modify the SP cliccking on "Modify"; if the SP was modified
> first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
> such setting in ON. Very weird!
> Any insight about how to preserve such setting in OFF is really welcome.
> Thanks. Roberto.
If at all possible you should avoid setting ANSI_NULLS OFF. Use the ON
setting always. The only good reason I can think of to use the OFF
setting is in order to support legacy code that you can't change. If
you do use the OFF setting then some features are not supported and
worst of all your code will be harder for others to comprehend.
Set ANSI_NULLS in the script just before the CREATE PROC statement:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_proc]
...
You can also set Management Studio's default behaviour under Options /
Query Execution\SQL Server\ANSI. Changing this to OFF is definitely not
recommended because it will affect ALL new procs unless you remember to
specify otherwise.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

ANSI_NULLS OFF SQL2K5

Hi folks,
I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
automatically the settings (global)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
When I modify the SP cliccking on "Script store procedure as" and then
"ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
problem is when I modify the SP cliccking on "Modify"; if the SP was modifie
d
first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
such setting in ON. Very weird!
Any insight about how to preserve such setting in OFF is really welcome.
Thanks. Roberto.BOBNET wrote:
> Hi folks,
> I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
> setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
> automatically the settings (global)
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFIER ON
> When I modify the SP cliccking on "Script store procedure as" and then
> "ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
> problem is when I modify the SP cliccking on "Modify"; if the SP was modif
ied
> first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
> such setting in ON. Very weird!
> Any insight about how to preserve such setting in OFF is really welcome.
> Thanks. Roberto.
If at all possible you should avoid setting ANSI_NULLS OFF. Use the ON
setting always. The only good reason I can think of to use the OFF
setting is in order to support legacy code that you can't change. If
you do use the OFF setting then some features are not supported and
worst of all your code will be harder for others to comprehend.
Set ANSI_NULLS in the script just before the CREATE PROC statement:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_proc]
...
You can also set Management Studio's default behaviour under Options /
Query Execution\SQL Server\ANSI. Changing this to OFF is definitely not
recommended because it will affect ALL new procs unless you remember to
specify otherwise.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

ANSI_NULLS and null comparison

Hi All,

I'm converting some stored procs from Sybase to SQL Server, and I'm
having serious problems with Null comparisons.

When I run the code below on Sybase, it returns both rows. When I run
it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
the code (and on the session through Query Analyzer), but it doesn't
seem to make any difference. Am I missing something?

-----
set ANSI_NULLS off

drop table #TestNull

create table #TestNull (Field1 varchar(10), Field2 varchar(10))

insert into #TestNull values (1, null)
insert into #TestNull values (1,1)

declare @.TestVar varchar(10)

select @.TestVar = Null

select * from #TestNull where Field1 = @.TestVar
-----

Thanks in advance,
SaulYour SQL Server select should look like this:

SELECT * FROM #TextNull WHERE Field1 IS NULL

CJ
"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul|||"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul

I guess you mean WHERE Field2 = @.TestVar ? That will return the (1, NULL)
row.

See SET ANSI_NULLS in Books Online - there are a number of examples. By the
way, you should generally say which version of MSSQL you're using, as some
behaviour may vary - see the comments in Books Online.

Simon|||Saul Margolis (saul_margolis@.hotmail.com) writes:
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
-----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----

I fail to see how the batch above could return any rows with any
setting. Looks like there is a typo in your repro.

Anyway, first of all, try to write your code so that you can use
ANSI_NULLS ON. There are features in MS SQL Server that requires
ANSI_NULLS to be ON:

o Indexed views.
o Index on computed columns.
o Access to linked servers.

With ANSI_NULLS off, these features are unavailable.

Also, be aware of when writing stored procedures, that the setting
of ANSI_NULLS when you create the procedure applies. That is, not
the run-time setting.

Also, you may need to have a go for ANSI_NULLS to have effect.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The Sybase family used to allow "foobar = NULL" to mean "foobar IS
NULL"; you just need to set everything to ANSI and change all your code.
Also, why are you putting integers into VARCHAR(10) columns in your
sample code?

Now you know why experienced progrmamers laugh at newbies who use
proprietary code because they think they will never have to port it :)

That is probably not funny right now; and it might get worse if the
Sybase is old -- look for *= as the outer join and other non-standard
behavior in some of the predicates.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ansi_nulls

We had a stored procedure that was created while
ansi_nulls was on. Is there a way to tell if SP has any of
the ansi settings changed from default? Just curious.
SkSELECT OBJECTPROPERTY(OBJECT_ID('<procedure name>'), 'ExecIsANSINullsOn')
1 is on, 0 is off.
Jacco Schalkwijk
SQL Server MVP
"Sandeep" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
> We had a stored procedure that was created while
> ansi_nulls was on. Is there a way to tell if SP has any of
> the ansi settings changed from default? Just curious.
> Sk|||Thanks a million Jacco.
SK
>--Original Message--
>SELECT OBJECTPROPERTY(OBJECT_ID('<procedure
name>'), 'ExecIsANSINullsOn')
>1 is on, 0 is off.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Sandeep" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
of[vbcol=seagreen]
>
>.
>

ANSI_NULLS

I've got a stored procedure that I'm issuing a distributed query in. When I
try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada,
yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS ON'
should do the trick. That's all well and good except that when I put this
statement in the SP, I get the same error. Where do I put the statement if
not in the SP. If I put it outside the SP, I still need to issue the
distributed query inside the SP so I still have the same problem?
ToddTry,
set ansi_nulls on
go
create procedure ...
go
AMB
"Todd Bright" wrote:

> I've got a stored procedure that I'm issuing a distributed query in. When
I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada
,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS O
N'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement i
f
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd|||Did you try to edit the procdure in QA rather than in EM (I think you did it
in EM), EM has the default settings to OFF, so if you edit this in EM it
will bring you the error, on th other hand if you do taht in QA via "Alter
procedire...yada..yada" with the setting turned ON it will work for you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Todd Bright" <ToddBright@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DE95BFD5-3039-48CD-B27D-4E4D80F0F4A9@.microsoft.com...
> I've got a stored procedure that I'm issuing a distributed query in. When
> I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON...
> yada,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS
> ON'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement
> if
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd|||Put it outside of the SP :) Like this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ...
GO
Assuming you want ANSI_NULLS and QUOTED_IDENTIFIER off again you can add:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
To the end.
"Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
news:DE95BFD5-3039-48CD-B27D-4E4D80F0F4A9@.microsoft.com...
> I've got a stored procedure that I'm issuing a distributed query in. When
> I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON...
> yada,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS
> ON'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement
> if
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd|||Thanks guys... I remember having to figure this out before, but that was a
few years ago. Now it's like... DUH!
"Todd Bright" wrote:

> I've got a stored procedure that I'm issuing a distributed query in. When
I
> try to save the SP, SQL Server tells me that ANSI_NULLS must be ON... yada
,
> yada, yada. Looking at the documentation I find that 'SET ANSI_DEFAULTS O
N'
> should do the trick. That's all well and good except that when I put this
> statement in the SP, I get the same error. Where do I put the statement i
f
> not in the SP. If I put it outside the SP, I still need to issue the
> distributed query inside the SP so I still have the same problem?
> Todd

Ansi_nulls

We had a stored procedure that was created while
ansi_nulls was on. Is there a way to tell if SP has any of
the ansi settings changed from default? Just curious.
Sk
SELECT OBJECTPROPERTY(OBJECT_ID('<procedure name>'), 'ExecIsANSINullsOn')
1 is on, 0 is off.
Jacco Schalkwijk
SQL Server MVP
"Sandeep" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
> We had a stored procedure that was created while
> ansi_nulls was on. Is there a way to tell if SP has any of
> the ansi settings changed from default? Just curious.
> Sk
|||Thanks a million Jacco.
SK
>--Original Message--
>SELECT OBJECTPROPERTY(OBJECT_ID('<procedure
name>'), 'ExecIsANSINullsOn')
>1 is on, 0 is off.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Sandeep" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e6401c4d7e2$13737150$a501280a@.phx.gbl...
of
>
>.
>

ANSI NULLS setting in 6.5

How can I find the current ANSI_NULLS setting in 6.5?
thanksThere no such setting in 6.5 at the server level. This is something you set
in the client app, using the SET command. (In fact, the setting at the
database level in 7.0 and 2000 is mostly useless as the client will override
anyhow, and some API's and tools will set this regardless whether the
developer want to or not.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"peter feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:92E1AC2B-3162-43AF-A04B-71CD6533CB37@.microsoft.com...
> How can I find the current ANSI_NULLS setting in 6.5?
> thanks