Microsoft SQL Server Desktop Engine - Stored Procedure Question

Author

Stored Procedure Question

Charles E Finkenbiner

2006-02-19, 9:23 am

Hi All,

I am learning SQL using MSDE 2000 and I have taken the below stored
procedure out of Books Online, making a few changes.

ALTER PROCEDURE StoredProcedure1 AS
CREATE TABLE Employees (
ID int CONSTRAINT PK_id PRIMARY KEY IDENTITY (1, 1) NOT NULL,
Name char (5) NULL,
Inactive bit DEFAULT 0 NULL
)
SET NOCOUNT ON

EXEC sp_addextendedproper
ty 'caption', 'Employee ID', 'user', dbo,
'table', Employees, 'column', ID
EXEC sp_addextendedproper
ty 'caption', 'Employee Name', 'user', dbo,
'table', Employees, 'column', Name
EXEC sp_addextendedproper
ty 'inputmask', '>LLLLL', 'user', dbo, 'table',
Employees, 'column', Name
EXEC sp_addextendedproper
ty 'caption', 'Inactive?', 'user', dbo,
'table', Employees, 'column', Inactive
EXEC sp_addextendedproper
ty 'format', 'True/False', 'user', dbo,
'table', Employees, 'column', Inactive

I execute this procedure through an ASP web page. This works fine and
the table is created. My question is this: When I use 'Design View' in
Access 2002 to look at the table structure I see that the column
properties Caption, Input Mask and Format are blank.

If I enter data directly in to the table I can see the Caption title,
the Input Mask works correctly and the True/False format works correctly
also.

Why do the column properties show nothing but everything seems to work?

Thanks for any help understanding,

Charles

Andrea Montanari

2006-02-19, 11:23 am

hi Charles,
Charles E Finkenbiner wrote:
>....
> EXEC sp_addextendedproper
ty 'format', 'True/False', 'user', dbo,
> 'table', Employees, 'column', Inactive
>
> I execute this procedure through an ASP web page. This works fine and
> the table is created. My question is this: When I use 'Design View'
> in Access 2002 to look at the table structure I see that the column
> properties Caption, Input Mask and Format are blank.
>
> If I enter data directly in to the table I can see the Caption title,
> the Input Mask works correctly and the True/False format works
> correctly also.
>
> Why do the column properties show nothing but everything seems to
> work?

all this kind of "features", as you already seen, is wrapped under extended
properties... but, as several extended properties can be bound to the very
same object, Microsoft decided to design a "standard" for they own uses in
Access..
so "Format" is not named that way, but "MS_Format", "Caption" is
"MS_Caption" and so on as "MS_InputMask", as this is what Access designers
look for...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Charles E Finkenbiner

2006-02-19, 11:23 am

Hi Andrea,
On 2/19/2006 9:45 AM, Andrea Montanari wrote:
> hi Charles,
> Charles E Finkenbiner wrote:
>
>
>
> all this kind of "features", as you already seen, is wrapped under extended
> properties... but, as several extended properties can be bound to the very
> same object, Microsoft decided to design a "standard" for they own uses in
> Access..
> so "Format" is not named that way, but "MS_Format", "Caption" is
> "MS_Caption" and so on as "MS_InputMask", as this is what Access designers
> look for...

Yes, that works great. Now for my next question. :) If I use a SQL
database tool to view/design my SQL databases will using the 'MS_'
format bite me? Should I use both forms of the property name? Or will
the 'MS_' form take care of both?

Thanks for your help,

Charles

Andrea Montanari

2006-02-19, 11:23 am

hi Charles,
Charles E Finkenbiner wrote:
>
> Yes, that works great. Now for my next question. :) If I use a SQL
> database tool to view/design my SQL databases will using the 'MS_'
> format bite me? Should I use both forms of the property name? Or
> will the 'MS_' form take care of both?
>

if you use, say, Enterprise Manager, then you're out of luck...
for instance, Access names the "description" "MS_Caption" where Enterprise
Manager names it "MS_Description"...
the other properties are not directly available in EM as it is not a
front-end for tasks like that, thus you do not have "input mask" or "cormat"
at all, as these are Access specific..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Charles E Finkenbiner

2006-02-19, 11:23 am

Hi Andrea,
On 2/19/2006 10:30 AM, Andrea Montanari wrote:
> hi Charles,
> Charles E Finkenbiner wrote:
>
>
>
> if you use, say, Enterprise Manager, then you're out of luck...
> for instance, Access names the "description" "MS_Caption" where Enterprise
> Manager names it "MS_Description"...
> the other properties are not directly available in EM as it is not a
> front-end for tasks like that, thus you do not have "input mask" or "cormat"
> at all, as these are Access specific..

Ok, thanks for the information. As of right now using Access to design
my SQL databases is all I need, since the user interface is handled by
ASP web pages.

Thanks for your help,

Charles

Sponsored Links