Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:
ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO
ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO
ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO
it works, but only locally.
I've tried with:
ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT
Err.: Incorrect syntax near the keyword 'DEFAULT'.
Can someone help me please?
Thanks in advance,
Giacomo
P.S.
We're using SQL Sever 2000 (version 8.00.194)
Hello all. I have a table with two coulmns CODE and DESCRITPION. Can anyone suggest how i can go about deleteing the entire record where two or more codes are the same?
I want to search Table 2 EmailAddress, with Table 1 EmailAddress, and if any of the Email Addresses in Table 1 are found in Table 2, delete the rows in Table 2.
Hi all, (I am using SQL Server 2005) I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column. Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later! I do not want to re-use the already used Identity value. I just want to have the last CustomerID to be higher that all the previous ones. Is there any way to do this? Thanking you in advance, Tomy
Hi i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table. regards vinod.t.v
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months: -- Purge records in small portions to avoid locking production tables -- for a long time. The process takes longer, but can co-exist with -- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45 Incorrect syntax near 'Failed:'.
I have a UDF that returns a char(10) random value. This runs fine when calling it directly via Query Analyzer, Stored Procs, etc.
I now want to create a new column in a table (via the Table Designer) and want to set the default value to the function. I have entered dbo.f_myfunction() for "Default Value", but I get an error:
Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors. [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
The function is in the same database as the table. This is all on my local database where I am the admin.
I have also tried database.dbo.f_myfunction(), but I get an Invalid Object error.
I have a table with the list of employee and 15 column with data type float or money , that represent the number of hours, airfare,gas,parking food, etc....
I have 2 choice: -Put 0 as a default value for each column, like that I do not have to use coalesce when I do SUM for each column. -Leave the default value null but I will have to use coalesce.
In term of performance, what is the best solution?
hi, i was wondering how to set default value in the datetime column of the database so that it will enter current date and time if one is not provided when a row is populated. is there a store procedure to do this? or built-in function? mp
I would like to have the date value 1/1/2499 entered as the default value in a date field if no value is specified. I have added 1/1/2499 as the default value for the column however when I insert a record into the table the date 1/1/1900 is entered even though I haven't specified a date. Any way to accomplish this? Thanks.
I am running a script against a couple of databases on my SQL Server 2000 Standard Edition Instance and I am getting the following in the results pane:
"Default bound to column" I have searched the MS Knowledge base and found a couple of vague references to this. Does anyone know why I might be getting this??? the script 'seems' to run fine.. except for the funky error in the results pane. Script is attached. Thank you!!
ALTER TABLE tm_spn_equip_def ADD Customer_No int NOT NULL DEFAULT dbo.f_spn_GetCustomerNo() WITH VALUES
Now when I attempt to do this...
ALTER TABLE tm_spn_equip_def DROP COLUMN Customer_No
I get an error:
Msg 5074, Level 16, State 1, Line 1 The object 'DF__tm_spn_eq__Custo__019E3B86' is dependent on column 'Customer_No'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN Customer_No failed because one or more objects access this column.
Does anybody know how I can change the default value for a column?I was trying to remove the default value in order to add the new oneafterwards. This is what I tried:alter table /table-name/ drop default for /column-name/alter table /table-name/ alter column /column-name/(/new-decl-without-default/)It did not work. I cannot find a solution in the documentation. Maybeyou can help me out?Thank you,Johan
If we have a column with a default value set, say GETDATE( ), how can weassure that value is reset on an UPDATE (not an INSERT) without changingthe client code that does the updating?I'd rather stay away from triggers, etc if possible.Thanks,Mike Husler
I have a char(11) for SSN, and I would like to default it to123-45-6789 so I can avoid having nulls in this column, and so I caneasily find the rows in which I need to have a 'correct' SSNentered/updated.I tried using just 123-45-6789, and SQL2005 doesn't seem to bedefaulting to this value, it seems to be keeping it as(((123)-(45))-(6789), and not placing it into this column when a newrow is created....Is there some special way I must specify defaults for a char(11) field(Yes, I will include the dashes).Thank you,Tom
I know that the correct syntax to set the default on a column in SQL Server 2005 is:
Alter Table <TableName> Add Constraint <ConstraintName> Default <DefaultValue> For <ColumnName>
But from what I can gather, the SQL-92 syntax is:
Alter Table <TableName> Alter Column <ColumnName> Set Default <DefaultValue>
This generates an error on SQL Server 2005.
Am I wrong about the standard syntax for this statement? If this is the standard, why doesn't SQL Server 2005 support it? I am trying to avoid code that will only work on certain database managers.
I have a table with a column: ThruDate datetime not null
I have a function call set up as default for the col ([dbo].[fn_DateHigh_Get1]())
I have a stored procedure that inserts into the table - it expects @ThruDate as a parm. I default the parm to NULL -
When the sp executes its insert stmt i get error 515 (null cannot be inserted....). When i just enter the row manually in enterprise manger (and just tab through the ThruDate col) all is well - ThruDate gets the default as calculated by function [dbo].[fn_DateHigh_Get1]()
Why does the sp fail - i.e. why isn't the default applied?
Is it possible to use a stored procedure to fill the default value of a column when i'm building the db? i mean if i can use a stored procedure for the "colum property": "default value or bnding" if yes how can i do it?
I have a integer column which has Default value alredy defined. How do i alter this column so that it's defult value will be removed. I want to do this using T-SQL statement. Not through design mode.
I can't seem to get the syntax correct for ALTERing an existing column with a default constraint. I've been to Help and BOL. There are examples that show how to use the ALTER command to add a column with a default constraint but not how to do it on an existing column.
From standard SELECT need to display specific column values normally, together with any NULLs in the same column not as NULL. My below effort parses however does not change to <new_value>.
SELECT[Server], [db_name], (CASE WHEN last_db_bkup_date IS NULL THEN '<new_value>' ELSE last_db_bkup_date END) AS last_bkup_date, [Backup Age (Hours)] FROM [tbl]
I created a column in a table with datatype uniqueidentifier which is set as RowGUID and the default value to newsequentialid(). I get the following error whenI try to save the table
Error validating default value for column 'ColumnName'.