create table fails in loop (SQL Server)

Question

I have a sql script that creates tables for each db in my sql server. However it fails on one create table script. Eg the

CREATE TABLE [dbo].[Mx_Poll_Tags]

command. What is wrong with this? I don't see it.

The weird part is when I one by one run the scripts in a query window they all work fine. Only is this particular proc it fails.

The error states:

Msg 173, Level 15, State 1, Line 71
The definition for column 'Unit' must include a data type.

The create script is made by SQL Server itself by scripting an existing Mx_Poll_Tags table as CreateTo.

Does anyone see what the error is?

BEGIN
declare @proc nvarchar(max)
set @proc='if ''?'' like ''Client_%''

begin 
    use [?]
    print ''?''

    DROP TABLE [dbo].[ManualMetersInput]
    DROP TABLE [dbo].[ManualMeterActions]
    DROP TABLE [dbo].[ManualMeters]

        DROP TABLE [dbo].[MX_Poll]
    --DROP TABLE [dbo].[Mx_Poll_Tags]
    DROP TABLE [dbo].[MX_Poll_Info]
    DROP TABLE [dbo].[MX_Poll_Logs]
    DROP TABLE [dbo].[MX_Poll_QA]
    --DROP TABLE [dbo].[MX_Poll_Vars]

    CREATE TABLE [dbo].[ManualMeters]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Ean] [varchar](max) NULL,
        [Period] [int] NULL,       
        [TagTable] [varchar](max) NULL,
        [TagTableId] [varchar](max) NOT NULL,
        [Overflow] [int] NULL,
        [TZ] [varchar](max) NULL,
    ) ON [PRIMARY] 

    CREATE TABLE [dbo].[ManualMetersInput]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Timestamp] [datetime2](7) NOT NULL,
        [ManualMeterId] [int] NOT NULL,
        [Value] [decimal](18, 3) NOT NULL,
        [IsOverflow] [bit] NOT NULL,
        [ImportDate] [datetime2](7) NOT NULL,
    ) ON [PRIMARY] 

    CREATE TABLE [dbo].[ManualMeterActions]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ManualMeterId] [int] NOT NULL,
        [UserId] [int] NOT NULL,
        [Type] [nvarchar](max) NOT NULL,
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[MX_Poll]
    (
        [timestamp] [datetime2](7) NOT NULL,
        [localtimestamp] [datetime2](7) NOT NULL,
    ) ON [PRIMARY] 

    CREATE TABLE [dbo].[MX_Poll_Info]
    (
        [timestamp] [datetime2](7) NOT NULL,
        [info] [nvarchar](max) NOT NULL,
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[MX_Poll_Logs]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [timestamp] [datetime2](7) NOT NULL,
        [Message] [nvarchar](max) NULL,
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[MX_Poll_QA]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [timestamp] [datetime2](7) NOT NULL,
        [tag] [nvarchar](max) NULL,
        [QA] [int] NULL
    ) ON [PRIMARY] 

    -- error here    
    CREATE TABLE [dbo].[Mx_Poll_Tags]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](800) NOT NULL,
        [Unit] [int] NOT NULL,
        [FieldName] [nvarchar](100) NULL,
        [ScaleFromMin] [decimal](18, 3) NULL,
        [ScaleFromMax] [decimal](18, 3) NULL,
        [ScaleToMin] [decimal](18, 3) NULL,
        [ScaleToMax] [decimal](18, 3) NULL,
        [DeltaOfKwhCounter_Id] [int] NULL,
        [Visible] [int] NULL,
        [Type] [nvarchar](50) NULL,
        [Enable] [int] NULL,
        [Content] [int] NULL,
        [Quantity] [int] NULL,
        [Signal] [int] NULL,
        [SignalDescription] [nvarchar](max) NULL,
        [Connection] [nvarchar](max) NULL,
        [Cable] [nvarchar](max) NULL,
        [Comments] [nvarchar](max) NULL,
        [UsedForPrediction_0] [bit] NOT NULL,
        [RelatedToPrediction_0] [bit] NOT NULL,
        [CalculatedByPredictionNo] [int] NULL,
    ) ON [PRIMARY] 

end';

--print @proc;
exec sp_MSForEachDB @proc

END
GO

You do have the drop table statement commented out, though I would've expected a different error message... — HoneyBadger
– HoneyBadger, Commented Apr 19, 2016 at 15:43
Yeah, I commented it out because the table doesn't exist yet and else the drop gives an error. So that has nothing to do with the error in my question.. — PitAttack76
– PitAttack76, Commented Apr 19, 2016 at 15:44
Based on the error message I would have expected there to be a text row like this [Unit] NOT NULL, where the data type was missing. Are you sure that the generated error message does not correspond to a query where this is indeed left off/omitted? Could happen as a copy/past error or maybe editing the query, something like that. — Igor
– Igor, Commented Apr 19, 2016 at 15:50
You could also take a look at the result of the print @proc. I can't really imagine you loose your datatype there, but it's an easy test. — HoneyBadger
– HoneyBadger, Commented Apr 19, 2016 at 15:57
Is unit a keyword by any chance? Something which the compiler sees differently in this case? Try units — I_am_Batman
– I_am_Batman, Commented Apr 19, 2016 at 16:06

Arthur D · Accepted Answer · 2016-04-19 16:01:26Z

4

Can you try it with just creating that table?

Also, can you eliminate the "begin" and "end"? Based on this thread: http://www.sqlservercentral.com/Forums/Topic808714-8-1.aspx

I don't have enough reputation to make this a comment.

EDIT:

By default, sys.sp_MSforeachdb @command1 has a parameter length of nvarchar(2000). Even though you're passing in a varchar(max), anything over 2000 is being truncated.

edited Apr 19, 2016 at 16:01

answered Apr 19, 2016 at 15:48

Arthur D

6125 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

PitAttack76 Over a year ago

That doesn't help either. The weird part is when I copy the create script directly in de query window it does work. Only not when it's in that loop. I shall add that to the description of my problem.

Arthur D Over a year ago

I think it could be the length of your @proc variable. Can you reduce the proc down to just the exec sp_msforeach with your table. I believe by default, sp_msforeachdb has a max character length.

HoneyBadger Over a year ago

You are right, except the query would be truncated somewhere in the CREATE TABLE [dbo].[MX_Poll_QA] statement. So the error is in the wrong statement, it seems...

dcreight Over a year ago

When I paste the query into word it is 2,184 characters. The 2000th character is after Comments in the Mx_Poll_Tags Create Table statement. Seems like this answer is valid.

PitAttack76 Over a year ago

@ArthurDaniels; you're right. That was the issue. I've split it up and now it works as expected. Lesson learned :)

|

Collectives™ on Stack Overflow

create table fails in loop (SQL Server)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related