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 2, 2010 6:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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 2, 2010 7:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
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 2, 2010 7:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
@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 3, 2010 6:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
Guyz any updates on this?? do u guys needs more info on the error, i can give..
Post #858586
Posted Wednesday, February 3, 2010 6:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
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