Determine ANSI_PADDING for a table?

1) How do I determine the ANSI_PADDING setting for an existing table? (SQL
2000 or SQL 2005.)
Objectproperty doesn't show ANSI_PADDING, although it shows ANSI_NULLS.
2) How do I change the ANSI_NULLS or ANSI_PADDING setting for an existing
table?
These settings are just set with a seemingly "global" SET statement;
apparently the environment (or something in the ether) is set with this
setting, and then that is used when tables are created. The same is true
of options like NUMERIC_ROUNDABORT.
Thanks.
David Walker"DWalker" <none@.none.com> wrote in message
news:eWsmaohUIHA.4752@.TK2MSFTNGP05.phx.gbl...
> 1) How do I determine the ANSI_PADDING setting for an existing table?
> (SQL
> 2000 or SQL 2005.)
>
You can either use:
EXEC sp_help Foo (look at TrimTrailingBlanks)
or
SELECT [name], typestat
FROM syscolumns
WHERE id IN ( OBJECT_ID('Foo') )
ANSI_PADDING is ON when typestat is 2, 0 if OFF.
> 2) How do I change the ANSI_NULLS or ANSI_PADDING setting for an existing
> table?
>
You can use ALTER TABLE and setting the corresponding setting before the
alter statement, here is an example:
SET ANSI_PADDING OFF
GO
CREATE TABLE Foo(col1 VARCHAR(10) NOT NULL)
GO
INSERT Foo VALUES ('Test ')
GO
SET ANSI_PADDING ON
GO
ALTER TABLE Foo ALTER COLUMN col1 VARCHAR(10) NOT NULL
GO
INSERT Foo VALUES ('Test ')
GO
SELECT '|' + col1 + '|'
FROM Foo
GO
DROP TABLE Foo
And here is a note from BOL:
"In a future version of SQL Server ANSI_PADDING will always be ON and any
applications that explicitly set the option to OFF will produce an error.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Determine ANSI_PADDING for a table?

1) How do I determine the ANSI_PADDING setting for an existing table? (SQL
2000 or SQL 2005.)
Objectproperty doesn't show ANSI_PADDING, although it shows ANSI_NULLS.
2) How do I change the ANSI_NULLS or ANSI_PADDING setting for an existing
table?
These settings are just set with a seemingly "global" SET statement;
apparently the environment (or something in the ether) is set with this
setting, and then that is used when tables are created. The same is true
of options like NUMERIC_ROUNDABORT.
Thanks.
David Walker
"DWalker" <none@.none.com> wrote in message
news:eWsmaohUIHA.4752@.TK2MSFTNGP05.phx.gbl...
> 1) How do I determine the ANSI_PADDING setting for an existing table?
> (SQL
> 2000 or SQL 2005.)
>
You can either use:
EXEC sp_help Foo (look at TrimTrailingBlanks)
or
SELECT [name], typestat
FROM syscolumns
WHERE id IN ( OBJECT_ID('Foo') )
ANSI_PADDING is ON when typestat is 2, 0 if OFF.

> 2) How do I change the ANSI_NULLS or ANSI_PADDING setting for an existing
> table?
>
You can use ALTER TABLE and setting the corresponding setting before the
alter statement, here is an example:
SET ANSI_PADDING OFF
GO
CREATE TABLE Foo(col1 VARCHAR(10) NOT NULL)
GO
INSERT Foo VALUES ('Test ')
GO
SET ANSI_PADDING ON
GO
ALTER TABLE Foo ALTER COLUMN col1 VARCHAR(10) NOT NULL
GO
INSERT Foo VALUES ('Test ')
GO
SELECT '|' + col1 + '|'
FROM Foo
GO
DROP TABLE Foo
And here is a note from BOL:
"In a future version of SQL Server ANSI_PADDING will always be ON and any
applications that explicitly set the option to OFF will produce an error.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Determening the setting

We can use this to change the setting
SET IDENTITY_INSERT T1 ON
How can I find out if it is on or off ?sp_help (tablename) will give you all the table information. The third
result grid will show identity seed and value if on
"Alur" wrote:

> We can use this to change the setting
> SET IDENTITY_INSERT T1 ON
> How can I find out if it is on or off ?|||It doesn't look there is a way to check this. However, as a SET option, if
you set it on, it only applies to your connection, so there is no way
someone else could have set it without you knowing.
HTH
Kalen Delaney, SQL Server MVP
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:B673336F-D4DC-4AAF-B8D2-80F32E098B38@.microsoft.com...
> We can use this to change the setting
> SET IDENTITY_INSERT T1 ON
> How can I find out if it is on or off ?|||Nope, sp_help will give the same information whether IDENTITY_INSERT is on
or off. It basically tells you whether or not the table has an identity
column, but it doesn't tell you whether you can supply explicit values for
the identity column.
--
HTH
Kalen Delaney, SQL Server MVP
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:1C99D06F-40F5-48BF-8935-460ECDD19026@.microsoft.com...
> sp_help (tablename) will give you all the table information. The third
> result grid will show identity seed and value if on
> "Alur" wrote:
>|||It would be nice if there is features to get those informations like
OpenTran gives all oldest active transactions
Madhivanan
Kalen Delaney wrote:
> Nope, sp_help will give the same information whether IDENTITY_INSERT is on
> or off. It basically tells you whether or not the table has an identity
> column, but it doesn't tell you whether you can supply explicit values for
> the identity column.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Derekman" <Derekman@.discussions.microsoft.com> wrote in message
> news:1C99D06F-40F5-48BF-8935-460ECDD19026@.microsoft.com...

details on Linked server?

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar Balasubramanian
Hi
See reply to later post..
John
"sasikumar" wrote:

> Hi,
> Any one know about Linked server setup in sql server.
> I want to know more detail on setting up a sqlserver to connect it directly
> to an oracle server. So that I can directly access data's from Oracle using
> Stored procedure.
>
> Regards,
> Sasikumar Balasubramanian
>
>

details on Linked server?

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar BalasubramanianHi
See reply to later post..
John
"sasikumar" wrote:
> Hi,
> Any one know about Linked server setup in sql server.
> I want to know more detail on setting up a sqlserver to connect it directly
> to an oracle server. So that I can directly access data's from Oracle using
> Stored procedure.
>
> Regards,
> Sasikumar Balasubramanian
>
>

details on Linked server?

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar BalasubramanianHi
See reply to later post..
John
"sasikumar" wrote:

> Hi,
> Any one know about Linked server setup in sql server.
> I want to know more detail on setting up a sqlserver to connect it directl
y
> to an oracle server. So that I can directly access data's from Oracle usin
g
> Stored procedure.
>
> Regards,
> Sasikumar Balasubramanian
>
>

details on Linked server setup

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar Balasubramanian
Hi
There is an example using the Microsoft Driver in sp_addlinked server
http://msdn.microsoft.com/library/de...adda_8gqa.asp.
Depending on the driver you are using you may also need to install SQL*NET
etc..
This article gives the steps required:
http://support.microsoft.com/default...b;en-us;280106
Oracle OLEDB drivers can be found at
http://www.oracle.com/technology/sof..._db/index.html
John
"sasikumar" wrote:

> Hi,
> Any one know about Linked server setup in sql server.
> I want to know more detail on setting up a sqlserver to connect it directly
> to an oracle server. So that I can directly access data's from Oracle using
> Stored procedure.
>
> Regards,
> Sasikumar Balasubramanian
>
>

details on Linked server setup

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar BalasubramanianHi
There is an example using the Microsoft Driver in sp_addlinked server
a_8gqa.asp." target="_blank">http://msdn.microsoft.com/library/d.../>
a_8gqa.asp.
Depending on the driver you are using you may also need to install SQL*NET
etc..
This article gives the steps required:
http://support.microsoft.com/defaul...kb;en-us;280106
Oracle OLEDB drivers can be found at
http://www.oracle.com/technology/so...e_db/index.html
John
"sasikumar" wrote:

> Hi,
> Any one know about Linked server setup in sql server.
> I want to know more detail on setting up a sqlserver to connect it directl
y
> to an oracle server. So that I can directly access data's from Oracle usin
g
> Stored procedure.
>
> Regards,
> Sasikumar Balasubramanian
>
>

details on Linked server setup

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar BalasubramanianHi
There is an example using the Microsoft Driver in sp_addlinked server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp.
Depending on the driver you are using you may also need to install SQL*NET
etc..
This article gives the steps required:
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
Oracle OLEDB drivers can be found at
http://www.oracle.com/technology/software/tech/windows/ole_db/index.html
John
"sasikumar" wrote:
> Hi,
> Any one know about Linked server setup in sql server.
> I want to know more detail on setting up a sqlserver to connect it directly
> to an oracle server. So that I can directly access data's from Oracle using
> Stored procedure.
>
> Regards,
> Sasikumar Balasubramanian
>
>

Details on Linked server

Hi,
Any one know about Linked server setup in sql server.
I want to know more detail on setting up a sqlserver to connect it directly
to an oracle server. So that I can directly access data's from Oracle using
Stored procedure.
Regards,
Sasikumar Balasubramanianhttp://msdn.microsoft.com/library/d... />
a_8gqa.asp
There's an example there on how to create a link to an Oracle server.
ML
http://milambda.blogspot.com/