Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Default Constraint Error Expand / Collapse
Author
Message
Posted Tuesday, February 02, 2010 6:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
Hi all,

Please run the following code, u ll get 2 tables created;

one a real table and the other a table due to "select top 0 *"...

if object_id('Table1') is not null
drop table dbo.Table1
Create table dbo.Table1
(
c1 int
,c2 varchar(150)
,c3 varchar(150)
,c4 nvarchar(150)
)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

exec ('select top 0 * into dbo.table2 from dbo.Table1 ')
exec ('alter table dbo.table2 add constraint default_c4 default ''N/A'' for c4')

When i run the above in query window, i get no error; even i can insert data into table2 and the default constraint works pretty well.

but when i run the same code through SQL Server Agent, i get the following error:
Column 'c4' in table 'table2' is invalid for creating a default constraint. [SQLSTATE 42000]

Have u guys ever faced this issue?? if yes, please help me out in resolving this and explaning me why it happened..

Thanks in Advance!
Post #857885
Posted Tuesday, February 02, 2010 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
Maybe I missed a step or something, but I just tested your script against a test database using SQL Agent and it ran without errors.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #857906
Posted Tuesday, February 02, 2010 7:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
@Grant, even i faced similar result as results... when i run the code as a step in sql server agent, i am not getting the error **AT TIMES**... but when i put that inside a SP and run that, i get that error.. make me totally flummoxed..i dont know if my code is wrong or if my Agent is wrong.. have u heard of issues like that before Grant??

Oh, btw i use SQL Server 2005 SP2 Enterprise Edition
Post #858361
Posted Wednesday, February 03, 2010 6:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
Guyz any updates on this?? do u guys needs more info on the error, i can give..
Post #858586
Posted Wednesday, February 03, 2010 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
No, I'm still not sure what's up.

One difference is I ran it on 2008. But there's little reason it should be different.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #858606
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse