Hi Leon

What is the exact error message that you're getting?

And what version of Umbraco 7 are you using? How did you install forms?

/Jan

Hi Jan.

Umbraco version 7.2.3

Installed the forms by using the umbraco "admin" section and clicking on forms.

Current error message: System.Data.SqlClient.SqlException Invalid column name 'UniqueId'. Invalid column name 'RecordData'

Background: THe initial error said it couldn't find a certain table in the database (can't remember which one now). I found a sql script to fix the error and ran that against my database. I now get the "Invalid column name" error.

Is there a way I can find which tables I need, drop them and then run a script to recreate them correctly.

I think the initial problem might have been caused because of the type of user I was logged in as in my connection string. I've since then switched to using the "sa" user, but no luck.

Hope you can help.

Leon

Hi Leon

Where did you find the script? Yeah you should be able to just use something like DROP <tablename> FROM <database> - I think that you should probably try starting all over so you should try deleting all the tables related to the forms module I guess. But make sure to keep a backup before deleting anything.

/Jan

Hi Jan.

I will try what you suggested. Here is the sql script I used: https://our.umbraco.org/projects/umbraco-pro/contour/documentation/Installation/resources/createsqlserver

Do you know if umbraco forms use any stored procedures that I need to drop first?

Leon

Hi Leon

Ok, but I'm not sure you can use the Contour SQL scripts since Forms is being rebuild from scratch - Don't know if the documentation on forms has been updated with SQL scripts yet but try having a look here https://github.com/umbraco/UmbracoFormsDocumentation

Hope this helps.

/Jan

Here is the create script for the Forms tables

 

CREATE TABLE [dbo].[UFRecords](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Form] [uniqueidentifier] NOT NULL,

[Created] [datetime] NOT NULL,

[Updated] [datetime] NOT NULL,

[CurrentPage] [uniqueidentifier] NULL,

[UmbracoPageId] [int] NULL,

[IP] [nvarchar](255) NULL,

[MemberKey] [nvarchar](255) NULL,

[UniqueId] [uniqueidentifier] NOT NULL,

[State] [nvarchar](50) NULL,

[RecordData] [ntext] NOT NULL,

 CONSTRAINT [PK_UFRecords] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

 

CREATE TABLE [dbo].[UFRecordFields](

[Key] [uniqueidentifier] NOT NULL,

[FieldId] [uniqueidentifier] NOT NULL,

[Record] [int] NOT NULL,

[Alias] [nvarchar](255) NOT NULL,

[DataType] [nvarchar](255) NOT NULL,

 CONSTRAINT [PK_UFRecordFields] PRIMARY KEY CLUSTERED 

(

[Key] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

 

CREATE TABLE [dbo].[UFRecordDataBit](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Key] [uniqueidentifier] NOT NULL,

[Value] [bit] NULL,

 CONSTRAINT [PK_UFRecordDataBit] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[UFRecordDataBit]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataBit_UFRecordFields_Key] FOREIGN KEY([Key])

REFERENCES [dbo].[UFRecordFields] ([Key])

GO

 

ALTER TABLE [dbo].[UFRecordDataBit] CHECK CONSTRAINT [FK_UFRecordDataBit_UFRecordFields_Key]

GO

 

 

CREATE TABLE [dbo].[UFRecordDataDateTime](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Key] [uniqueidentifier] NOT NULL,

[Value] [datetime] NULL,

 CONSTRAINT [PK_UFRecordDataDateTime] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[UFRecordDataDateTime]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataDateTime_UFRecordFields_Key] FOREIGN KEY([Key])

REFERENCES [dbo].[UFRecordFields] ([Key])

GO

 

ALTER TABLE [dbo].[UFRecordDataDateTime] CHECK CONSTRAINT [FK_UFRecordDataDateTime_UFRecordFields_Key]

GO

 

 

CREATE TABLE [dbo].[UFRecordDataInteger](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Key] [uniqueidentifier] NOT NULL,

[Value] [int] NULL,

 CONSTRAINT [PK_UFRecordDataInteger] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[UFRecordDataInteger]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataInteger_UFRecordFields_Key] FOREIGN KEY([Key])

REFERENCES [dbo].[UFRecordFields] ([Key])

GO

 

ALTER TABLE [dbo].[UFRecordDataInteger] CHECK CONSTRAINT [FK_UFRecordDataInteger_UFRecordFields_Key]

GO

 

 

CREATE TABLE [dbo].[UFRecordDataLongString](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Key] [uniqueidentifier] NOT NULL,

[Value] [ntext] NULL,

 CONSTRAINT [PK_UFRecordDataLongString] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[UFRecordDataLongString]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataLongString_UFRecordFields_Key] FOREIGN KEY([Key])

REFERENCES [dbo].[UFRecordFields] ([Key])

GO

 

ALTER TABLE [dbo].[UFRecordDataLongString] CHECK CONSTRAINT [FK_UFRecordDataLongString_UFRecordFields_Key]

GO

 

 

CREATE TABLE [dbo].[UFRecordDataString](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Key] [uniqueidentifier] NOT NULL,

[Value] [nvarchar](255) NOT NULL,

 CONSTRAINT [PK_UFRecordDataString] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[UFRecordDataString]  WITH CHECK ADD  CONSTRAINT [FK_UFRecordDataString_UFRecordFields_Key] FOREIGN KEY([Key])

REFERENCES [dbo].[UFRecordFields] ([Key])

GO

 

ALTER TABLE [dbo].[UFRecordDataString] CHECK CONSTRAINT [FK_UFRecordDataString_UFRecordFields_Key]

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks. You're the best!

Thanks for posting that Tim. Would you be able to give us an idea of what your migration package does?

We ran it and the forms are working but (presumably?) the schema for the Entries is wrong and we can't submit any entries.

It's all a bit confusing. Did something go wrong or are we supposed to work in the schema changes manually?

OK so I figured it out from reading through the script Tim posted.

You need to remove the UFRecordXXX tables in the database, and only those tables. You can either rename them and their keys, or drop them. I chose to rename them in the hope that someone might write a migration script or plugin. Once they are removed you can run the script to install the new Umbraco Forms records schema.

Here's the SQL:

Rename the Contour records tables:

EXEC sp_rename UFRecordDataBit, ContourUFRecordDataBit
EXEC sp_rename UFRecordDataDateTime, ContourUFRecordDataDateTime
EXEC sp_rename UFRecordDataInteger, ContourUFRecordDataInteger
EXEC sp_rename UFRecordDataLongString, ContourUFRecordDataLongString
EXEC sp_rename UFRecordDataString, ContourUFRecordDataString
EXEC sp_rename UFRecordFields, ContourUFRecordFields
EXEC sp_rename UFRecords, ContourUFRecords
EXEC sp_rename UFRecordsXml, ContourUFRecordsXml
EXEC sp_rename 'FK_UFRecordDataBit_UFRecordFields', 'FK_ContourUFRecordDataBit_UFRecordFields', 'OBJECT';
EXEC sp_rename 'FK_UFRecordDataDateTime_UFRecordFields', 'FK_ContourUFRecordDataDateTime_UFRecordFields', 'OBJECT';
EXEC sp_rename 'FK_UFRecordDataInteger_UFRecordFields', 'FK_ContourUFRecordDataInteger_UFRecordFields', 'OBJECT';
EXEC sp_rename 'FK_UFRecordDataLongString_UFRecordFields', 'FK_ContourUFRecordDataLongString_UFRecordFields', 'OBJECT';
EXEC sp_rename 'FK_UFRecordDataString_UFRecordFields', 'FK_ContourUFRecordDataString_UFRecordFields', 'OBJECT';
EXEC sp_rename 'PK_UFRecordFields', 'PK_ContourUFRecordFields', 'OBJECT';
EXEC sp_rename 'FK_UFRecordFields_UFFields', 'FK_ContourUFRecordFields_UFFields', 'OBJECT';
EXEC sp_rename 'FK_UFRecordFields_UFRecords', 'FK_ContourUFRecordFields_UFRecords', 'OBJECT';
EXEC sp_rename 'PK_UFRecords', 'PK_ContourUFRecords', 'OBJECT';
EXEC sp_rename 'FK_UFRecords_UFForms', 'FK_ContourUFRecords_UFForms', 'OBJECT';
EXEC sp_rename 'PK_UFRecordsXml', 'PK_ContourUFRecordsXml', 'OBJECT';
EXEC sp_rename 'FK_UFRecordsXml_UFForms', 'FK_ContourUFRecordsXml_UFForms', 'OBJECT';

Drop the Contour records tables (for those braver than myself):

DROP TABLE UFRecordDataBit
DROP TABLE UFRecordDataDateTime
DROP TABLE UFRecordDataInteger
DROP TABLE UFRecordDataLongString
DROP TABLE UFRecordDataString
DROP TABLE UFRecordFields
DROP TABLE UFRecords
DROP TABLE UFRecordsXml

Once those are out of the way you can run Tim Geyssens' script which will create the new tables.

The SQL to generate the new Umbraco Forms UFRecords tables (as posted by Tim): http://pastebin.com/sW4CeTCn

Excellent post Zac, incredibly useful! Many thanks !

is working on a reply...