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

Detect Existing Subscription on Subscriber

Hi all,
Is there a way to check the subscriber if a particular subscription exists
using SQLDMO ActiveX control? If not, how would it be done through stored
procedure?
Thanks,
Tom
Tom,
on the subscriber you could run sp_helppullsubscription or just query the
MSreplication_subscriptions table - I think this is the easiest method.
Using SQLDMO there is a TransPullSubscriptions collection that could be
iterated, although I'm not too sure if this works on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Design Question

Hi all,

I am currently building a project management application.
The users wants to save existing projects as templates.

My question is, what would be the better solution.
Create seperate tables to hold template information.
Or use the same table as the existing project but, flagged as Templates?My feeling is that having one table would be the better solution. Having 2 separate tables with the same file structure is asking for trouble maintenance-wise. Any schema changes would have to be done twice.

Terri|||Yes thats what the IT Manager said and I agree.
But the template data is only varchar name and description type stuff.
Where as the other table has datetime, bit, int etc type fields.
So if I combine the two table I will have to allow for null values.

Is this a problem?|||Well, now it is sounding like the template schema and the project schema are not the same. If you post the schema perhaps better advice can be offered.

And I don't see a problem with allowing for NULL values, as long as you account for them in your coding.

Terri|||Use the same table as your manager said. Fewer tables = simpler design = better program. Also you can easily change templates into project or vice versa if you ever see the need to. (Think easy template creation from existing campaigns?)|||Thanks guys|||Or you can normalize your project table into ProjectHeader and ProjectDetails. Include in the ProjectHeader those fields which are common to templates and projects, and add a ProjectType field to identify it as a project or template. The additional fields that only apply to projects can then be placed in your new ProjectDetails table, along with the foreign key reference to the ProjectHeader table.

This is generally how you would handle object generalizations (or inheritance, whatever terminology you want to use) like this. Shoving it all into one single table can cause problems later - what if you decide you now have more "special cases" of project records?