How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql

How to edit a asp.net2.0/C# project in VS2005, Plz HELP

hi all,

I created a new sql server 2005 database name wsat_demo in the

(lESSQL1)\sql2k5 enterprise server

in the default website properties i added a connection string named WSAT1 to wsat_demo database using aspnet_user account with SA priveledge

i modified the machine.config to use WSAT1 connection string

Then i run the WSAT (website admin tools) to create users.

this error appears. "Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. "

i've tried attaching the database created by the asp.net project located at app_data folder named it App_data1 database

do the same configuration as stated above. Viola! the WSAT connects to it.

After comprehensive investigation, App_data1 database shows that it contains many security objects builtin to it by asp.net 2 application.

QUESTION: How can I have WSAT_demo database to have the same object as App_data1 given the fact that my connection is enjoying SA priviledge?

thanks,

joeydj

Pareng joey...musta na buhay natin?

Are you creating a database that will store user profiles? If you are, you might wanna start by creating a blank SQL Server database. Then, run the aspnet_regsql.exe and point to this blank database you just created. This is a wizard that creates all these database objects in SQL Server(whether it's 2000 or 2005). Then you can point your ASP.NET 2.0 web app to this database in your web.config. But before you can do this, make sure you override the settings in th machine.config. For a complete step-by-step guide on this, check out Scott Guthrie's blog as this was my guide when I was working on a similar task using the Beta 2 version of Visual Studio 2005

|||

okay lang. sounds like i'm at the

msforums it used to be msdn.ph

running the aspnet_regsql.exe is just like attaching the database

from the app_data on the remote server

|||

Hi,

How can i run or edit my ASP.NET 2.0/C# project in VS2005. I have source code files (including database) but it doesn't have .sln file. Please guide me how can i edit or run this in VS2005

How to drop default value Constrain in query?

What's query dropping Default value constraint from a column?I'd use ALTER TABLE DROP CONSTRAINT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) myself.

-PatP

How to DROP DEFAULT constraint?

We initially created a table (SQL Server 2000) having a column with a
DEFAULT value of 0. We want to drop the column, but we cannot until the
DEFAULT constraint is removed. Documentation indicates we should be able to
use the command:
"ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
but it gives us a syntax error.
Other documentation indicates the "DROP DEFAULT" option is deprecated and we
shouldn't use it.
Can anyone tell me how to do this?Hi
ALTER TABLE...DROP CONSTRAINT......
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
> We initially created a table (SQL Server 2000) having a column with a
> DEFAULT value of 0. We want to drop the column, but we cannot until the
> DEFAULT constraint is removed. Documentation indicates we should be able
> to use the command:
> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
> but it gives us a syntax error.
> Other documentation indicates the "DROP DEFAULT" option is deprecated and
> we shouldn't use it.
> Can anyone tell me how to do this?
>|||Thanks Uri...
Unfortunately, when the DEFAULT contraint was added, it wasn't given a name:
"ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
Apparently, the system created its own named constraint which I found in
sysobjects. Am I stuck querying the sysobjects table for the name of the
constraint (based on the table/column names) and then using the retrieved
name in the DROP CONSTRAINT clause?
...mcs
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi
> ALTER TABLE...DROP CONSTRAINT......
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>> We initially created a table (SQL Server 2000) having a column with a
>> DEFAULT value of 0. We want to drop the column, but we cannot until the
>> DEFAULT constraint is removed. Documentation indicates we should be able
>> to use the command:
>> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
>> but it gives us a syntax error.
>> Other documentation indicates the "DROP DEFAULT" option is deprecated and
>> we shouldn't use it.
>> Can anyone tell me how to do this?
>|||Hi
Yes , run sp_helpconstraint 'tablename'
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri...
> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
> name:
> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
> Apparently, the system created its own named constraint which I found in
> sysobjects. Am I stuck querying the sysobjects table for the name of the
> constraint (based on the table/column names) and then using the retrieved
> name in the DROP CONSTRAINT clause?
> ...mcs
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> ALTER TABLE...DROP CONSTRAINT......
>> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
>> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>> We initially created a table (SQL Server 2000) having a column with a
>> DEFAULT value of 0. We want to drop the column, but we cannot until the
>> DEFAULT constraint is removed. Documentation indicates we should be able
>> to use the command:
>> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
>> but it gives us a syntax error.
>> Other documentation indicates the "DROP DEFAULT" option is deprecated
>> and we shouldn't use it.
>> Can anyone tell me how to do this?
>>
>|||Okay, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uWngbCEoGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes , run sp_helpconstraint 'tablename'
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
>> Thanks Uri...
>> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
>> name:
>> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
>> Apparently, the system created its own named constraint which I found in
>> sysobjects. Am I stuck querying the sysobjects table for the name of the
>> constraint (based on the table/column names) and then using the retrieved
>> name in the DROP CONSTRAINT clause?
>> ...mcs
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> ALTER TABLE...DROP CONSTRAINT......
>> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
>> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>> We initially created a table (SQL Server 2000) having a column with a
>> DEFAULT value of 0. We want to drop the column, but we cannot until
>> the DEFAULT constraint is removed. Documentation indicates we should be
>> able to use the command:
>> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
>> but it gives us a syntax error.
>> Other documentation indicates the "DROP DEFAULT" option is deprecated
>> and we shouldn't use it.
>> Can anyone tell me how to do this?
>>
>>
>

How to DROP DEFAULT constraint?

We initially created a table (SQL Server 2000) having a column with a
DEFAULT value of 0. We want to drop the column, but we cannot until the
DEFAULT constraint is removed. Documentation indicates we should be able to
use the command:
"ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
but it gives us a syntax error.
Other documentation indicates the "DROP DEFAULT" option is deprecated and we
shouldn't use it.
Can anyone tell me how to do this?Hi
ALTER TABLE...DROP CONSTRAINT......
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
> We initially created a table (SQL Server 2000) having a column with a
> DEFAULT value of 0. We want to drop the column, but we cannot until the
> DEFAULT constraint is removed. Documentation indicates we should be able
> to use the command:
> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
> but it gives us a syntax error.
> Other documentation indicates the "DROP DEFAULT" option is deprecated and
> we shouldn't use it.
> Can anyone tell me how to do this?
>|||Thanks Uri...
Unfortunately, when the DEFAULT contraint was added, it wasn't given a name:
"ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
Apparently, the system created its own named constraint which I found in
sysobjects. Am I stuck querying the sysobjects table for the name of the
constraint (based on the table/column names) and then using the retrieved
name in the DROP CONSTRAINT clause?
...mcs
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi
> ALTER TABLE...DROP CONSTRAINT......
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>|||Hi
Yes , run sp_helpconstraint 'tablename'
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri...
> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
> name:
> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
> Apparently, the system created its own named constraint which I found in
> sysobjects. Am I stuck querying the sysobjects table for the name of the
> constraint (based on the table/column names) and then using the retrieved
> name in the DROP CONSTRAINT clause?
> ...mcs
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>|||Okay, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uWngbCEoGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes , run sp_helpconstraint 'tablename'
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
>

How to DROP COLUMN with a unamed default...

Friends,
We need to drop a column that also has a "default", but the "default" was
unnamed during creation, so SQL generates a name for it. Since we need to
do this programatically on many customer machines, we cannot assume the name
of the "default object". I imagine we would need to acquire the name of the
dependent "default", then drop it, and then we could drop the column.
Does anybody have a reliable technique for doing this sort of thing? Am I
on the right track?
Thanks in advance for your thoughts,
James
select s1.name
from SysObjects s1
inner join SysObjects s2 on s1.parent_obj = s2.id
where s2.name = 'Table1'
and s1.xtype = 'D'
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:utWUUmrlFHA.576@.TK2MSFTNGP15.phx.gbl...
> Friends,
> We need to drop a column that also has a "default", but the "default" was
> unnamed during creation, so SQL generates a name for it. Since we need to
> do this programatically on many customer machines, we cannot assume the
> name of the "default object". I imagine we would need to acquire the name
> of the dependent "default", then drop it, and then we could drop the
> column.
> Does anybody have a reliable technique for doing this sort of thing? Am I
> on the right track?
> Thanks in advance for your thoughts,
> James
>
|||That's great, thanks! Sadly, I have several unamed "default" values in that
table, and I cannot tell which is which. I really only want to drop the on
that is on the column being dropped. Somewhere there must be information on
which column a constraint applies to. Perhaps it is not useable to mortals
though... Any thoughts?
And, thanks again for replying!
James
|||Hello, James
Try this:
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL='ALTER TABLE '+QUOTENAME(o1.name)
+' DROP '+QUOTENAME(o2.name)
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
--PRINT @.SQL
EXEC (@.SQL)
Razvan

How to DROP COLUMN with a unamed default...

Friends,
We need to drop a column that also has a "default", but the "default" was
unnamed during creation, so SQL generates a name for it. Since we need to
do this programatically on many customer machines, we cannot assume the name
of the "default object". I imagine we would need to acquire the name of the
dependent "default", then drop it, and then we could drop the column.
Does anybody have a reliable technique for doing this sort of thing? Am I
on the right track?
Thanks in advance for your thoughts,
Jamesselect s1.name
from SysObjects s1
inner join SysObjects s2 on s1.parent_obj = s2.id
where s2.name = 'Table1'
and s1.xtype = 'D'
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:utWUUmrlFHA.576@.TK2MSFTNGP15.phx.gbl...
> Friends,
> We need to drop a column that also has a "default", but the "default" was
> unnamed during creation, so SQL generates a name for it. Since we need to
> do this programatically on many customer machines, we cannot assume the
> name of the "default object". I imagine we would need to acquire the name
> of the dependent "default", then drop it, and then we could drop the
> column.
> Does anybody have a reliable technique for doing this sort of thing? Am I
> on the right track?
> Thanks in advance for your thoughts,
> James
>|||That's great, thanks! Sadly, I have several unamed "default" values in that
table, and I cannot tell which is which. I really only want to drop the on
that is on the column being dropped. Somewhere there must be information on
which column a constraint applies to. Perhaps it is not useable to mortals
though... Any thoughts?
And, thanks again for replying!
James|||Hello, James
Try this:
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL='ALTER TABLE '+QUOTENAME(o1.name)
+' DROP '+QUOTENAME(o2.name)
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
--PRINT @.SQL
EXEC (@.SQL)
Razvan

How to DROP COLUMN with a unamed default...

Friends,
We need to drop a column that also has a "default", but the "default" was
unnamed during creation, so SQL generates a name for it. Since we need to
do this programatically on many customer machines, we cannot assume the name
of the "default object". I imagine we would need to acquire the name of the
dependent "default", then drop it, and then we could drop the column.
Does anybody have a reliable technique for doing this sort of thing? Am I
on the right track?
Thanks in advance for your thoughts,
Jamesselect s1.name
from SysObjects s1
inner join SysObjects s2 on s1.parent_obj = s2.id
where s2.name = 'Table1'
and s1.xtype = 'D'
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:utWUUmrlFHA.576@.TK2MSFTNGP15.phx.gbl...
> Friends,
> We need to drop a column that also has a "default", but the "default" was
> unnamed during creation, so SQL generates a name for it. Since we need to
> do this programatically on many customer machines, we cannot assume the
> name of the "default object". I imagine we would need to acquire the name
> of the dependent "default", then drop it, and then we could drop the
> column.
> Does anybody have a reliable technique for doing this sort of thing? Am I
> on the right track?
> Thanks in advance for your thoughts,
> James
>|||That's great, thanks! Sadly, I have several unamed "default" values in that
table, and I cannot tell which is which. I really only want to drop the on
that is on the column being dropped. Somewhere there must be information on
which column a constraint applies to. Perhaps it is not useable to mortals
though... Any thoughts?
And, thanks again for replying!
James|||Hello, James
Try this:
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL='ALTER TABLE '+QUOTENAME(o1.name)
+' DROP '+QUOTENAME(o2.name)
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
--PRINT @.SQL
EXEC (@.SQL)
Razvan|||It's perfect! Thanks so much!
James

How to drop a column that has a DEFAULT clause

Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
Michael
You first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>
|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:

> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>
>
|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...[vbcol=seagreen]
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
'rv'.[vbcol=seagreen]
this[vbcol=seagreen]
it[vbcol=seagreen]
|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...[vbcol=seagreen]
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:

> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> 'rv'.
> this
> it
>
>
|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:

> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>
>
|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...[vbcol=seagreen]
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
s2.id[vbcol=seagreen]
that[vbcol=seagreen]
programmatically.[vbcol=seagreen]
access[vbcol=seagreen]
constraints[vbcol=seagreen]
|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...[vbcol=seagreen]
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
|||Tibor
Thanks for the helpful advice .
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:

> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>
>

How to drop a column that has a DEFAULT clause

Hi,
I have a table with a column:
rv smallint default 1 not null
I want to drop it:
alter table myTable
drop column rv
But I get an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rv failed because one or more objects access this
column.
The 'object' mentioned above is the default constraint.
How do I just drop the column (without having to know waht constraints it
has)?
Thanks
MichaelYou first have to drop the default constraint
alter table myTable
drop constraint DF__ccAstPublica__rv__27C3E46E
Then drop the cilumn
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> Hi,
> I have a table with a column:
> rv smallint default 1 not null
> I want to drop it:
> alter table myTable
> drop column rv
> But I get an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> column.
> The 'object' mentioned above is the default constraint.
> How do I just drop the column (without having to know waht constraints it
> has)?
> Thanks
> Michael
>|||Armand,
Thanks. Yes, I know I have to drop the constraint but...
This is in a script and I don't know the auto generated name of the
constraint, so I can't readily drop it.
Is there a way to find out the constraint name for the DEFAULT for that
column?
That way, I could fid the name then frop the constraint programmatically.
BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
Thanks
Michael
"Armando Prato" wrote:
> You first have to drop the default constraint
> alter table myTable
> drop constraint DF__ccAstPublica__rv__27C3E46E
> Then drop the cilumn
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > Hi,
> > I have a table with a column:
> > rv smallint default 1 not null
> >
> > I want to drop it:
> > alter table myTable
> > drop column rv
> >
> > But I get an error:
> > Server: Msg 5074, Level 16, State 1, Line 1
> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> > Server: Msg 4922, Level 16, State 1, Line 1
> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> > column.
> >
> > The 'object' mentioned above is the default constraint.
> > How do I just drop the column (without having to know waht constraints it
> > has)?
> > Thanks
> > Michael
> >
>
>|||Sure
What you can do is query the sysconstraints table in your db
and join it to syscolumns
select s2.name, object_name(s1.constid)
from sysconstraints s1
inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
and s1.colid = s2.colid and s1.status = 133141)
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
> > You first have to drop the default constraint
> >
> > alter table myTable
> > drop constraint DF__ccAstPublica__rv__27C3E46E
> >
> > Then drop the cilumn
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > Hi,
> > > I have a table with a column:
> > > rv smallint default 1 not null
> > >
> > > I want to drop it:
> > > alter table myTable
> > > drop column rv
> > >
> > > But I get an error:
> > > Server: Msg 5074, Level 16, State 1, Line 1
> > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
'rv'.
> > > Server: Msg 4922, Level 16, State 1, Line 1
> > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
this
> > > column.
> > >
> > > The 'object' mentioned above is the default constraint.
> > > How do I just drop the column (without having to know waht constraints
it
> > > has)?
> > > Thanks
> > > Michael
> > >
> >
> >
> >|||you can run sp_help tablename or sp_helpconstraint tablename to view the
default name.
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
>> > 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
>> > this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints
>> > it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Armando,
Thanks. What does status 133141 mean?
"Armando Prato" wrote:
> Sure
> What you can do is query the sysconstraints table in your db
> and join it to syscolumns
>
> select s2.name, object_name(s1.constid)
> from sysconstraints s1
> inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id = s2.id
> and s1.colid = s2.colid and s1.status = 133141)
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> > > You first have to drop the default constraint
> > >
> > > alter table myTable
> > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > >
> > > Then drop the cilumn
> > >
> > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > Hi,
> > > > I have a table with a column:
> > > > rv smallint default 1 not null
> > > >
> > > > I want to drop it:
> > > > alter table myTable
> > > > drop column rv
> > > >
> > > > But I get an error:
> > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> 'rv'.
> > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> this
> > > > column.
> > > >
> > > > The 'object' mentioned above is the default constraint.
> > > > How do I just drop the column (without having to know waht constraints
> it
> > > > has)?
> > > > Thanks
> > > > Michael
> > > >
> > >
> > >
> > >
>
>|||Richard,
I assume this would be helpful interactively, but not if I want to do this
in a script?
"Richard Ding" wrote:
> you can run sp_help tablename or sp_helpconstraint tablename to view the
> default name.
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> >> > 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access
> >> > this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints
> >> > it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||It means that the particular row represents a DEFAULT constraint. It's a
bitmap, actually.
I have notes that say the status = 5 for defaults but my SQL Server
represents it
as 133141 bitmap
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:2118DFBB-8625-452C-BAB3-D6CA804E905F@.microsoft.com...
> Armando,
> Thanks. What does status 133141 mean?
> "Armando Prato" wrote:
> > Sure
> >
> > What you can do is query the sysconstraints table in your db
> > and join it to syscolumns
> >
> >
> > select s2.name, object_name(s1.constid)
> > from sysconstraints s1
> > inner join syscolumns s2 on (s1.id = object_id('mytable') and s1.id =s2.id
> > and s1.colid = s2.colid and s1.status = 133141)
> >
> >
> > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > > Armand,
> > > Thanks. Yes, I know I have to drop the constraint but...
> > > This is in a script and I don't know the auto generated name of the
> > > constraint, so I can't readily drop it.
> > > Is there a way to find out the constraint name for the DEFAULT for
that
> > > column?
> > > That way, I could fid the name then frop the constraint
programmatically.
> > > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > > Thanks
> > > Michael
> > >
> > > "Armando Prato" wrote:
> > >
> > > > You first have to drop the default constraint
> > > >
> > > > alter table myTable
> > > > drop constraint DF__ccAstPublica__rv__27C3E46E
> > > >
> > > > Then drop the cilumn
> > > >
> > > > "Mic" <micspam@.jadegroup.co.uk> wrote in message
> > > > news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> > > > > Hi,
> > > > > I have a table with a column:
> > > > > rv smallint default 1 not null
> > > > >
> > > > > I want to drop it:
> > > > > alter table myTable
> > > > > drop column rv
> > > > >
> > > > > But I get an error:
> > > > > Server: Msg 5074, Level 16, State 1, Line 1
> > > > > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column
> > 'rv'.
> > > > > Server: Msg 4922, Level 16, State 1, Line 1
> > > > > ALTER TABLE DROP COLUMN rv failed because one or more objects
access
> > this
> > > > > column.
> > > > >
> > > > > The 'object' mentioned above is the default constraint.
> > > > > How do I just drop the column (without having to know waht
constraints
> > it
> > > > > has)?
> > > > > Thanks
> > > > > Michael
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
This is why you should name your constraint in the first place... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> Armand,
> Thanks. Yes, I know I have to drop the constraint but...
> This is in a script and I don't know the auto generated name of the
> constraint, so I can't readily drop it.
> Is there a way to find out the constraint name for the DEFAULT for that
> column?
> That way, I could fid the name then frop the constraint programmatically.
> BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> Thanks
> Michael
> "Armando Prato" wrote:
>> You first have to drop the default constraint
>> alter table myTable
>> drop constraint DF__ccAstPublica__rv__27C3E46E
>> Then drop the cilumn
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> > Hi,
>> > I have a table with a column:
>> > rv smallint default 1 not null
>> >
>> > I want to drop it:
>> > alter table myTable
>> > drop column rv
>> >
>> > But I get an error:
>> > Server: Msg 5074, Level 16, State 1, Line 1
>> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> > Server: Msg 4922, Level 16, State 1, Line 1
>> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> > column.
>> >
>> > The 'object' mentioned above is the default constraint.
>> > How do I just drop the column (without having to know waht constraints it
>> > has)?
>> > Thanks
>> > Michael
>> >
>>|||Tibor
Thanks for the helpful advice :).
Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
not DEFAULTs. I think this particular aspect of SQL Server's design is not
particularly helpful. To my eye, it's much clearer to define:
myColumn smallint default 1
than
myColumn smallint,
constraint MyTab_DF_MyColumn default 1 for MyColumn
Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
allow me to drop a column that has an impicit not null, but not one with an
implicit DEFAULT.
Ho hum...
Bet this isn't 'fixed' in 2005...
Regards
Michael
"Tibor Karaszi" wrote:
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> This is why you should name your constraint in the first place... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
> > Armand,
> > Thanks. Yes, I know I have to drop the constraint but...
> > This is in a script and I don't know the auto generated name of the
> > constraint, so I can't readily drop it.
> > Is there a way to find out the constraint name for the DEFAULT for that
> > column?
> > That way, I could fid the name then frop the constraint programmatically.
> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
> > Thanks
> > Michael
> >
> > "Armando Prato" wrote:
> >
> >> You first have to drop the default constraint
> >>
> >> alter table myTable
> >> drop constraint DF__ccAstPublica__rv__27C3E46E
> >>
> >> Then drop the cilumn
> >>
> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
> >> > Hi,
> >> > I have a table with a column:
> >> > rv smallint default 1 not null
> >> >
> >> > I want to drop it:
> >> > alter table myTable
> >> > drop column rv
> >> >
> >> > But I get an error:
> >> > Server: Msg 5074, Level 16, State 1, Line 1
> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
> >> > Server: Msg 4922, Level 16, State 1, Line 1
> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
> >> > column.
> >> >
> >> > The 'object' mentioned above is the default constraint.
> >> > How do I just drop the column (without having to know waht constraints it
> >> > has)?
> >> > Thanks
> >> > Michael
> >> >
> >>
> >>
> >>
>
>|||Yes, DEFAULTs are handles like constraints in SQL Server. Where in ANSI SQL, they are just column
attributes. Just one of those things to get used to... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:0B2F4A00-65E5-4DF1-B2C6-86CA9C21C4D8@.microsoft.com...
> Tibor
> Thanks for the helpful advice :).
> Actually I always have explicitly named PK, FK, CHECK etc. constraints, but
> not DEFAULTs. I think this particular aspect of SQL Server's design is not
> particularly helpful. To my eye, it's much clearer to define:
> myColumn smallint default 1
> than
> myColumn smallint,
> constraint MyTab_DF_MyColumn default 1 for MyColumn
> Incidently, NOT NULL is treated differently to DEFAULT: SQL Server will
> allow me to drop a column that has an impicit not null, but not one with an
> implicit DEFAULT.
> Ho hum...
> Bet this isn't 'fixed' in 2005...
> Regards
> Michael
> "Tibor Karaszi" wrote:
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> This is why you should name your constraint in the first place... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> news:D9C9F97B-2660-47D8-BCC7-43076688F5D6@.microsoft.com...
>> > Armand,
>> > Thanks. Yes, I know I have to drop the constraint but...
>> > This is in a script and I don't know the auto generated name of the
>> > constraint, so I can't readily drop it.
>> > Is there a way to find out the constraint name for the DEFAULT for that
>> > column?
>> > That way, I could fid the name then frop the constraint programmatically.
>> > BTW, this is against SQL 7.0, so I can't use and INFORMATION_SCHEMA
>> > Thanks
>> > Michael
>> >
>> > "Armando Prato" wrote:
>> >
>> >> You first have to drop the default constraint
>> >>
>> >> alter table myTable
>> >> drop constraint DF__ccAstPublica__rv__27C3E46E
>> >>
>> >> Then drop the cilumn
>> >>
>> >> "Mic" <micspam@.jadegroup.co.uk> wrote in message
>> >> news:7E45448B-E30D-4D47-9A03-75EE568E6928@.microsoft.com...
>> >> > Hi,
>> >> > I have a table with a column:
>> >> > rv smallint default 1 not null
>> >> >
>> >> > I want to drop it:
>> >> > alter table myTable
>> >> > drop column rv
>> >> >
>> >> > But I get an error:
>> >> > Server: Msg 5074, Level 16, State 1, Line 1
>> >> > The object 'DF__ccAstPublica__rv__27C3E46E' is dependent on column 'rv'.
>> >> > Server: Msg 4922, Level 16, State 1, Line 1
>> >> > ALTER TABLE DROP COLUMN rv failed because one or more objects access this
>> >> > column.
>> >> >
>> >> > The 'object' mentioned above is the default constraint.
>> >> > How do I just drop the column (without having to know waht constraints it
>> >> > has)?
>> >> > Thanks
>> >> > Michael
>> >> >
>> >>
>> >>
>> >>
>>

How to do this in Stored Procedure

Hi,
I want to do something like this is my SP. If ' ' emplty string parameters are sent, I want the SP to use certain default values and use them in the query. code below is a rough idea..

CREATE PROCEDURE CabsSchedule_ViewLatest
(
 @.SiteCode smallint,
 @.YearMonth int
)
AS
 IF @.YearMonth = ' ' OR @.SiteCode = ' '
BEGIN
@.YearMonth = DateTime.Now()
@.SiteCode = 'GN'
END
 SELECT JulianDate, CalendarDay, BillPeriod, WorkDay,CalDayBillRcvd, Remarks
 FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO

CREATE PROCEDURE CabsSchedule_ViewLatest
(
 @.SiteCode smallint = NULL,
 @.YearMonth int = NULL
)
AS
SET NOCOUNT ON
IF @.YearMonth IS NULL OR @.SiteCode IS NULL
 BEGIN
 SET @.YearMonth = Getdate()
 SET @.SiteCode = 'GN'
 END
SELECT...
SET NOCOUNT OFF

hth|||You have numeric parameters, so checking for ' ' is not meaningful. Try using default values for the SP:

CREATE PROCEDURE CabsSchedule_ViewLatest
(
 @.SiteCode smallint = -1,
 @.YearMonth int = -1
)

This presumes -1 is a reasonable default (probably not)|||Hi Dinakar,
Thanks for the reply. I want to assign the latest YearMonth available in the my table to the @.YearMonth field. The format of YearMonth would be 200408 representing Year=2004 and Month=08 respectively, could this formatting be done using T-SQL functions??

Thanks,|||yeah there are some datefunctions available in SQL Server. check out BOL. in your case you wud prbly need Month(Getdate()).

hth|||REPLACE(CONVERT(nvarchar(10),DATEPART(yy,GetDate())) + Str(DATEPART(mm,GetDate()),2),' ','0')

You could do it more easily using Month() and Year() if you always want to use todays date.|||Douglas and Dinakar,
Thanks for the replies but, What I'm really looking for is the the latest YearMonth value available in the Table field YearMonth. I need this b'cos I need to extract all the rows corresponding to the YearMonth field as all the rows in a particular schedule could be classified by this field. What this means is for the Year 2004 and Month 08 I've got the number of rows corresponding to the number of working days in month represented by 08.
So, what I need is to pull all the rows with latest YearMonth field. Any ideas on how to pull the latest group of rows classified by the YearMonth field??

Thanks|||SELECT * FROM mytable WHERE YearMonth=(SELECT MAX(YearMonth) FROM myTable)|||Thanks for the reply Doug. I'm wiht another problem now. What I'm trying to do is to pass values from a SP as alias. ie. if I come across a value -1 in one of my rows in Column Called WorkDay, Then I want to pass string 'HOLIDAY' instead. I've been playing around with the CAST and CASE statements in my SP but couldn't make them to work. Any help with this??

The check syntax throws an error saying that the syntax is incorrect near '='.

Thanks,

CREATE PROCEDURE CabsSchedule_ViewLatest
(
@.SiteCode smallint = 0,
@.YearMonth int = NULL
)
AS
IF @.YearMonth IS NULL OR @.YearMonth = 0
BEGIN
SET @.YearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
SELECT CAST(BillPeriod AS VARCHAR(7)) AS NewBillPeriod=
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE BillPeriod
END,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO

|||What is NewBillPeriod?

SELECT
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE BillPeriod
END as SomeOtherFieldName,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode

BTW, What you are doing (overloading values to mean something "special" other than what they normally mean) is a bad thing...|||Thanks Doug,
But I really neeed to use this for storing optimal data in the table. I mean just to store just 2 different kinds of strings that too which can occur only a cpl of times, I thought of having alias values. Yes it does mean that a value being stored into the table means something special, but I thought that I'm better of storing special values instead of storing strings which have few occurances.
Also, before getting to your solution, I tried this and got it working but was having a few problems accessing it from my program. The error said that a string could not be implicitly converted into a smallint. Why is it saying that??

Thanks,|||It is saying that because you are using a string where the system expects a smallint. If you have a smallint field, it cannot store "FRED" or even "123", but can store 123. A string is a string, and a smallint is a smallint, and if you are converting from one to the other, you need to do it explicitly, using a cast.|||Doug,
I had tried using CAST on the fields in which I wanted to have alias values. I did domething like this but it didn't work and was saying that there was some incorrect syntax at the '=' on the CAST line in the below code. Could this be corrected to get what I'm looking for?? Also, the error says that there is not function called VARCHAR, but this is not a function.
What is going wrong here?

CREATE PROCEDURE CabsSchedule_ViewSchedule
(
@.SiteCode smallint = 0,
@.YearMonth int = NULL
)
AS
IF @.YearMonth IS NULL OR @.YearMonth = 0
BEGIN
SET @.YearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
SELECT CAST(BillPeriod AS VARCHAR(7)) =
CASE
WHEN BillPeriod = '32' THEN 'NB'
WHEN BillPeriod = '33' THEN 'Holiday'
ELSE BillPeriod
END,
CAST(WorkDay AS VARCHAR(7))=
CASE
WHEN WorkDay = '-1' THEN ''
WHEN WorkDay = '0' THEN 'Holiday'
ELSE WorkDay
END,
JulianDate, CalendarDay, CalDayBillRcvd, Remarks
FROMCabsSchedule
WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO

Thanks|||In a previous post, I gave an example of how to do what you wish (I added a CONVERT call here, since that might be required, depending upon your data):

SELECT
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE CONVERT(nvarchar(20),BillPeriod)
END as SomeOtherFieldName,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode

The syntax you are continuing to try to use will NEVER work.|||Thanks Doug,
The above change in my SP worked, but I was wondering why it wouldn't work if I CAST the BillPeriod in the CASE statement itself. Since, I CAST it to VarChar, the options for assignment it would have would still be the same and I did not care about CASTING the field BillPeriod in the ELSE statement (as you did) as smallint to varchar and Vice versa conversion needs no explicit CASTING(From what I read in BOL). What am I missing in my learning here?

Thanks again!

How to do a backup with sqlmaint in a different directory

Somobody know how to say to sqlmaint in which directory do the backup to avo
id use the default directory?Go to database maintenance plans in enterprise manager and edit your plan
there. There's an option to specify the directory where you want your
backups stored. If you're storing backup files on a different server, use a
UNC path, for example \\remoteserver\sharename
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Alfonso Gmez" <anonymous@.discussions.microsoft.com> wrote in message
news:61C9EEB0-9639-48A2-B0E4-6C2AAC7A9330@.microsoft.com...
> Somobody know how to say to sqlmaint in which directory do the backup to
avoid use the default directory?