March 12, 2014 at 5:51 pm
Hi all,
I'm trying to install a sql server database with a script. This is going from one machine to another in a totally different environment/network.
First time doing this, much thanks. I'm getting the following error:
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #7: Cannot find data type dbo.BOOLEAN.
CREATE TABLE [dbo].[HierarchyGroups](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RepHierarchyID] [int] NOT NULL,
[ParentID] [int] NULL,
[Name] [varchar](32) NOT NULL,
[Goal] [numeric](20, 4) NULL,
[QueryID] [int] NULL,
[IncludeAttribs] [dbo].[BOOLEAN] NOT NULL DEFAULT (0),
[ExcludeAttribs] [dbo].[BOOLEAN] NOT NULL DEFAULT (0),
[Sequence] [int] NULL,
[CatergoryFilterType] [int] NULL DEFAULT (1),
[FundFilterType] [int] NULL DEFAULT (1),
[CampaignFilterType] [int] NULL DEFAULT (1),
CONSTRAINT [PK_HierarchyGroups] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[BB_rule_BOOL_Value]', @objname=N'[dbo].[HierarchyGroups].[IncludeAttribs]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[BB_rule_BOOL_Value]', @objname=N'[dbo].[HierarchyGroups].[ExcludeAttribs]' , @futureonly='futureonly'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier assigned by the application.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'FK to REPORTHIERARCHIES.ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'RepHierarchyID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key to the parent - the ID of the record.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'ParentID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of this group' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The goal of this group.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'Goal'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The query to use' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'QueryID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TRUE when you are including based on attributes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'IncludeAttribs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TRUE when you are excluding based on attributes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'ExcludeAttribs'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates the ordinal position within a set of records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'Sequence'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'All = 1, Selected = 2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'CatergoryFilterType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'All = 1, Selected = 2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'FundFilterType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'All = 1, Selected = 2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups', @level2type=N'COLUMN',@level2name=N'CampaignFilterType'
GO
EXEC sys.sp_addextendedproperty @name=N'Comment', @value=N'Stores fund groups contained within a reporting hierarchy.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HierarchyGroups'
GO
March 12, 2014 at 5:54 pm
You seem to have a problem with a user defined datatype (dbo.boolean). You need to create it before running the script or you need to change the script to use system data types.
March 12, 2014 at 6:05 pm
You're as helpful as ever Luis.
I should have been more specific. How would I create a proper boolean type without knowledge of how the boolean was defined on the other environment? Sorry if this a dumb question but I've never needed to work with user defined data types.
A little background:
I've been given a script to generate a database. This is on a completely different environment. So far I've had to create filegroups and now I'm stuck on user defined data type.
March 12, 2014 at 6:17 pm
I can give you the script to create a "boolean" data type. However, it might not be the expected definition and you must ask for it with the person that handed you with the script.
Here's an example:
CREATE TYPE [dbo].[boolean] FROM [bit] NOT NULL
GO
And here's the reference to go beyond that: http://msdn.microsoft.com/en-us/library/ms175007(SQL.105).aspx
March 12, 2014 at 6:22 pm
So I created the boolean type and now I run into the following error:
Msg 15017, Level 16, State 1, Procedure sp_bindrule, Line 104
The rule '[dbo].[BB_rule_BOOL_Value]' does not exist.
I assume I have to also make a rule? Of course I also don't have the information.
Any recommendations how to recreate a database for development purposes short of a staging environment? There is confidential information on the original DB so I am not privy to a full backup. I want to ask one time for all the requirements/information needed to make the db work on my machine. Therefore, I'm going to do what I need to get the DB installed on my machine then work backwards and add the missing information that I've currently falsified.
Thanks
March 12, 2014 at 6:36 pm
You need to ask for a complete script, not just the tables' scripts.
Every object from the original DB should be scripted in the proper order to avoid the problems that you're facing.
March 12, 2014 at 7:38 pm
I ended running the script multiple times until the only errors were related to objects already existing.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply