March 28, 2014 at 9:28 am
This my not be as simple as it seems in that the default can be created on one server but not on another. This is part of an SSIS package where it runs fine on my local DB server however when run on the Dev server it fails. Any field that has a user defined data type cannot be assigned a default value. Both servers are SQL 2008R2. Didn't do anything special in creating the database that these tables are created in.
March 28, 2014 at 10:01 am
buddy__a (3/28/2014)
This my not be as simple as it seems in that the default can be created on one server but not on another. This is part of an SSIS package where it runs fine on my local DB server however when run on the Dev server it fails. Any field that has a user defined data type cannot be assigned a default value. Both servers are SQL 2008R2. Didn't do anything special in creating the database that these tables are created in.
Table and UDDT definitions please.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 10:25 am
This is being run in a brand new database on both servers that I'm testing it on.
CREATE TYPE [dbo].[MYGUID] FROM [uniqueidentifier] NOT NULL
CREATE TABLE [dbo].[EmergencyContact](
[EmergencyContactID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[EmployeeID] [dbo].[MYGUID] NOT NULL,
[ContactName] [varchar](50) NOT NULL,
[IsPrimaryContact] [int] NOT NULL,
[Address1] [varchar](55) NOT NULL,
[Address2] [varchar](55) NOT NULL,
[City] [varchar](30) NOT NULL,
[State] [varchar](6) NOT NULL,
[ZipCode] [varchar](12) NOT NULL,
[RelationshipID] [dbo].[MYGUID] NOT NULL,
[CreatedBy_EmployeeID] [dbo].[MYGUID] NOT NULL,
[CreatedDate] [smalldatetime] NOT NULL,
[ModifiedBy_EmployeeID] [dbo].[MYGUID] NOT NULL,
[ModifiedDate] [smalldatetime] NOT NULL,
[Inactive] [int] NOT NULL
)
ALTER TABLE [dbo].[EmergencyContact] ADD CONSTRAINT [DF_EmergencyContact_RelationshipID] DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [RelationshipID]
March 28, 2014 at 10:48 am
buddy__a (3/28/2014)
This is being run in a brand new database on both servers that I'm testing it on.
CREATE TYPE [dbo].[MYGUID] FROM [uniqueidentifier] NOT NULLCREATE TABLE [dbo].[EmergencyContact](
[EmergencyContactID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[EmployeeID] [dbo].[MYGUID] NOT NULL,
[ContactName] [varchar](50) NOT NULL,
[IsPrimaryContact] [int] NOT NULL,
[Address1] [varchar](55) NOT NULL,
[Address2] [varchar](55) NOT NULL,
[City] [varchar](30) NOT NULL,
[State] [varchar](6) NOT NULL,
[ZipCode] [varchar](12) NOT NULL,
[RelationshipID] [dbo].[MYGUID] NOT NULL,
[CreatedBy_EmployeeID] [dbo].[MYGUID] NOT NULL,
[CreatedDate] [smalldatetime] NOT NULL,
[ModifiedBy_EmployeeID] [dbo].[MYGUID] NOT NULL,
[ModifiedDate] [smalldatetime] NOT NULL,
[Inactive] [int] NOT NULL
)
ALTER TABLE [dbo].[EmergencyContact] ADD CONSTRAINT [DF_EmergencyContact_RelationshipID] DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [RelationshipID]
Can you elaborate on what the problem is? I assume you must be getting an error message? What version are you running?
What you posted has no primary key and no clustered index. From what I see here it looks like you are using guids as your primary keys on all your tables? Please tell me you are using a different column as your clustered index?
I would recommend too that you look closely at your datatypes. You are using int for Inactive and IsPrimaryContact. Wouldn't a bit be more suitable? State as varchar(6)? I hope you are storing the state abbreviation or the full name won't fit. Since all states in the US have a 2 character abbreviation I would use char(2) instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 10:48 am
The only difference between the two servers is that my local server is running SQL Developer edition while the Dev server is running Standard Edition.
March 28, 2014 at 10:53 am
buddy__a (3/28/2014)
The only difference between the two servers is that my local server is running SQL Developer edition while the Dev server is running Standard Edition.
That is the edition not the version. What is returned from the following?
select @@version
Are you getting error messages? If so, please post them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 10:55 am
Can you elaborate on what the problem is? I assume you must be getting an error message? What version are you running?
What you posted has no primary key and no clustered index. From what I see here it looks like you are using guids as your primary keys on all your tables? Please tell me you are using a different column as your clustered index?
I would recommend too that you look closely at your datatypes. You are using int for Inactive and IsPrimaryContact. Wouldn't a bit be more suitable? State as varchar(6)? I hope you are storing the state abbreviation or the full name won't fit. Since all states in the US have a 2 character abbreviation I would use char(2) instead.
I'm not sure what elaboration you need. The creation of the default on any column on any table that uses the specified user defined data type fails. I'm am not in a position to change any data types. This table doesn't show any other constraints because it is what the state of the table is in during the data transfer process in the SSIS package that I'm running. The table is created devoid of all constraints, then the data is piped in (from a database on the same server) then the defaults and eventually all other constraints are created. Bottom line is that this runs on my local DB server but fails on the Dev server.
March 28, 2014 at 10:57 am
Sean Lange (3/28/2014)
buddy__a (3/28/2014)
The only difference between the two servers is that my local server is running SQL Developer edition while the Dev server is running Standard Edition.That is the edition not the version. What is returned from the following?
select @@versionAre you getting error messages? If so, please post them.
As I stated in the original post they're both 2008 R2 servers and have the same SP version.
March 28, 2014 at 10:58 am
buddy__a (3/28/2014)
Can you elaborate on what the problem is? I assume you must be getting an error message? What version are you running?
What you posted has no primary key and no clustered index. From what I see here it looks like you are using guids as your primary keys on all your tables? Please tell me you are using a different column as your clustered index?
I would recommend too that you look closely at your datatypes. You are using int for Inactive and IsPrimaryContact. Wouldn't a bit be more suitable? State as varchar(6)? I hope you are storing the state abbreviation or the full name won't fit. Since all states in the US have a 2 character abbreviation I would use char(2) instead.
I'm not sure what elaboration you need. The creation of the default on any column on any table that uses the specified user defined data type fails. I'm am not in a position to change any data types. This table doesn't show any other constraints because it is what the state of the table is in during the data transfer process in the SSIS package that I'm running. The table is created devoid of all constraints, then the data is piped in (from a database on the same server) then the defaults and eventually all other constraints are created. Bottom line is that this runs on my local DB server but fails on the Dev server.
How do you know it fails? You must be getting an error message to know it fails?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 11:00 am
How do you know it fails? You must be getting an error message to know it fails?
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
March 28, 2014 at 12:30 pm
buddy__a (3/28/2014)
How do you know it fails? You must be getting an error message to know it fails?
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
That is the message when trying to add a second default to a column.
Are you sure the dev server doesn't already have a default for that column?
select *
from sys.default_constraints
where parent_object_id = object_id('EmergencyContact')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2014 at 10:12 am
I finally found the problem. Someone in their infinite wisdom decided that every database should have the UDDT in it so they created it in the model database with a default defined on it. So, in my script when I was testing for existence of this UDDT it found it so it didn't create it as I had specified it to be created (i.e. with no default value). So the UDDT had a default and therefore the column had a default as well so obviously another one couldn't be added.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply