0

i'm trying to create a blank table which already exists in another SQL instance. so i just right clicked on the table and choose "SCRIPT CREATE TO" to export the structure to a query window, and it procuced this

USE [SIC30_CTS_LIVE]
GO

/****** Object:  Table [dbo].[Personnel]    Script Date: 07/06/2019 17:10:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Personnel](
    [Pers_Ref] [varchar](8) NOT NULL,
    [Pers_Link_to_CD] [varchar](8) NOT NULL,
    [Pers_Name] [varchar](30) NOT NULL,
    [Pers_Salutation] [varchar](30) NULL,
    [Pers_Position] [varchar](30) NULL,
    [Pers_Add1] [varchar](30) NULL,
    [Pers_Add2] [varchar](30) NULL,
    [Pers_Add3] [varchar](30) NULL,
    [Pers_Add4] [varchar](30) NULL,
    [Pers_PostCode] [varchar](8) NULL,
    [Pers_Telephone] [varchar](20) NULL,
    [Pers_Fax] [varchar](20) NULL,
    [Pers_Mobile] [varchar](20) NULL,
    [Pers_Mobile_Type] [varchar](10) NULL,
    [Pers_Email] [varchar](100) NULL,
    [Pers_Pager] [varchar](30) NULL,
    [Pers_Pager_Type] [varchar](10) NULL,
    [Pers_Notes] [text] NULL,
    [Pers_Technician] [bit] NOT NULL,
    [Pers_PC_Access] [bit] NOT NULL,
    [Pers_Password] [varchar](31) NULL,
    [Pers_Mobile_Access] [bit] NOT NULL,
    [Pers_Mobile_Code] [varchar](8) NULL,
    [Pers_Mobile_Terminal_Type] [varchar](10) NULL,
    [Pers_Cost_Rate_1] [money] NULL,
    [Pers_Cost_Rate_2] [money] NULL,
    [Pers_Cost_Rate_3] [money] NULL,
    [Pers_Cost_Rate_4] [money] NULL,
    [Pers_Mileage_Cost] [money] NULL,
    [Pers_Overhead_Multiplier] [float] NULL,
    [Pers_Site_Cost_Time_Band] [varchar](10) NULL,
    [Pers_Travel_Cost_Time_Band] [varchar](10) NULL,
    [Pers_Grade] [varchar](8) NULL,
    [Pers_Login_Name] [varchar](30) NULL,
    [Pers_Warehouse_Code] [varchar](4) NULL,
    [Pers_SMS_Code] [varchar](8) NULL,
    [Pers_Leaver] [bit] NULL,
    [Pers_Logged_In] [bit] NULL,
    [Pers_Logged_In_Computer] [varchar](50) NULL,
    [Pers_Nag_Day] [smallint] NULL,
    [Pers_Lotus_Notes_User_Name] [varchar](100) NULL,
    [Pers_Lotus_Notes_Password] [varchar](200) NULL,
    [Pers_Sales_Person] [bit] NULL,
    [Pers_PO_DisValue] [float] NULL,
    [Pers_PO_MaxValue] [float] NULL,
    [Pers_PO_MaxPercent] [float] NULL,
    [Pers_Vehicle_Registration] [varchar](20) NULL,
    [Pers_Work_Slots_AM_Sunday] [smallint] NULL,
    [Pers_Work_Slots_AM_Monday] [smallint] NULL,
    [Pers_Work_Slots_AM_Tuesday] [smallint] NULL,
    [Pers_Work_Slots_AM_Wednesday] [smallint] NULL,
    [Pers_Work_Slots_AM_Thursday] [smallint] NULL,
    [Pers_Work_Slots_AM_Friday] [smallint] NULL,
    [Pers_Work_Slots_AM_Saturday] [smallint] NULL,
    [Pers_Work_Slots_PM_Sunday] [smallint] NULL,
    [Pers_Work_Slots_PM_Monday] [smallint] NULL,
    [Pers_Work_Slots_PM_Tuesday] [smallint] NULL,
    [Pers_Work_Slots_PM_Wednesday] [smallint] NULL,
    [Pers_Work_Slots_PM_Thursday] [smallint] NULL,
    [Pers_Work_Slots_PM_Friday] [smallint] NULL,
    [Pers_Work_Slots_PM_Saturday] [smallint] NULL,
    [Pers_Default_Despatch_Method] [varchar](30) NULL,
    [Pers_Despatch_Days] [smallint] NULL,
    [Pers_Stock_Pick_Sunday] [bit] NULL,
    [Pers_Stock_Pick_Monday] [bit] NULL,
    [Pers_Stock_Pick_Tuesday] [bit] NULL,
    [Pers_Stock_Pick_Wednesday] [bit] NULL,
    [Pers_Stock_Pick_Thursday] [bit] NULL,
    [Pers_Stock_Pick_Friday] [bit] NULL,
    [Pers_Stock_Pick_Saturday] [bit] NULL,
    [Pers_Authorise_Requisition_Value] [float] NULL,
    [Pers_Work_Slots_Target_Sunday] [smallint] NULL,
    [Pers_Work_Slots_Target_Monday] [smallint] NULL,
    [Pers_Work_Slots_Target_Tuesday] [smallint] NULL,
    [Pers_Work_Slots_Target_Wednesday] [smallint] NULL,
    [Pers_Work_Slots_Target_Thursday] [smallint] NULL,
    [Pers_Work_Slots_Target_Friday] [smallint] NULL,
    [Pers_Work_Slots_Target_Saturday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Sunday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Monday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Tuesday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Wednesday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Thursday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Friday] [smallint] NULL,
    [Pers_Work_Slots_Capacity_Saturday] [smallint] NULL,
    [Pers_Department_Code] [varchar](10) NULL,
    [Pers_Payroll_Reference] [varchar](10) NULL,
    [Pers_Schedule_Template] [text] NULL,
    [Pers_PO_Min_MisMatch] [float] NULL,
    [Pers_Language] [varchar](50) NULL,
    [Pers_Unlimited_Order_Value] [bit] NULL,
    [Pers_Unlimited_Requisition_Value] [bit] NULL,
    [Pers_Default_Authoriser] [varchar](8) NULL,
    [Pers_Created_By] [varchar](8) NULL,
    [Pers_Created_Date] [datetime] NULL,
    [Pers_Modified_By] [varchar](8) NULL,
    [Pers_Modified_Date] [datetime] NULL,
    [PERS_Longitude] [float] NULL,
    [PERS_Latitude] [float] NULL,
    [Pers_Mapping_User] [bit] NULL,
    [Pers_Payroll_Lock] [varchar](8) NULL,
    [Pers_Unlimited_Service_Calls] [bit] NULL,
    [Pers_Unlimited_Projects] [bit] NULL,
    [Pers_PO_Service_Calls_Percent] [float] NULL,
    [Pers_PO_Projects_Percent] [float] NULL,
    [Pers_Password_Date_of_Last_Change] [datetime] NULL,
    [Pers_Password_Change_Period] [smallint] NULL,
    [Pers_Password_Change_Immediate] [bit] NULL,
    [Pers_ServicePower_Username] [varchar](30) NULL,
    [Pers_ServicePower_Password] [varchar](50) NULL,
    [Pers_Authorise_AOV_Value] [float] NULL,
    [Pers_Unlimited_AOV_Value] [bit] NULL,
    [Pers_Country_Code] [varchar](2) NULL,
    [Pers_Cost_Centre] [varchar](50) NULL,
    [Pers_Date_of_Birth] [datetime] NULL,
    [Pers_Line_Manager] [varchar](8) NULL,
    [Pers_Picture] [varchar](500) NULL,
    [Pers_Leaving_Date] [datetime] NULL,
    [Pers_Start_Date] [datetime] NULL,
    [Pers_Certification_Authoriser] [bit] NULL,
    [Pers_SubCon_No_Reallocate] [bit] NULL,
    [Pers_Authorisation_Limit] [float] NULL,
    [Pers_Renewal_Limit] [float] NULL,
    [Pers_Manual_Adjustment_Value] [float] NULL,
    [Pers_Authorisation_Credit_Limit] [float] NULL,
    [Pers_MTA_Limit] [float] NULL,
    [Pers_MTA_Credit_Limit] [float] NULL,
    [Pers_Renewal_Credit_Limit] [float] NULL,
    [Pers_Manual_Adjustment_Credit_Limit] [float] NULL,
    [Pers_Admin_Only_Licence] [bit] NULL,
    [Pers_Web_Portal_Access] [bit] NULL,
    [Pers_Do_Not_Contact] [bit] NULL,
    [Pers_Manual_Adjustment_Percent] [float] NULL,
    [Pers_Call_Contract_Manager] [bit] NULL,
    [Pers_Project_Manager] [bit] NULL,
    [Pers_AccountManaged_Changeable] [bit] NULL,
    [CTS_Portal_Last_Logon] [datetime] NULL,
    [CTS_Portal_Last_Passwd_Change] [datetime] NULL,
 CONSTRAINT [Pers_Ref] PRIMARY KEY NONCLUSTERED 
(
[Pers_Link_to_CD] ASC,
    [Pers_Ref] 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

SET ANSI_PADDING OFF
GO

however when i then try and execute this on the new SQL instance i get

Msg 170, Level 15, State 1, Line 145
Line 145: Incorrect syntax near '('.

line 145 is

 CONSTRAINT [Pers_Ref] PRIMARY KEY NONCLUSTERED 
(
[Pers_Link_to_CD] ASC,

is there something i have forgotten to enable on the new SQL instance? or do i have to do this in two separate steps.. ie create the table first then add the constaints?

2
  • both SQL servers are the same version, with the same patch level - SQL 2000 8.0.2040 (yes i know they old but it just runs a local application - no path to upgrade at the moment). the script i posted was created from the source SQL server using the export table as script. if i remove the constraint part then it creates the table, just without the primary key settings etc - which is fine for creating just the 1 table, but i have a few tables to create and don't want to have to manually adjust each one to get it to work. – chenks Jul 6 '19 at 17:57
  • i used SQL Management Studio 2008 to create the script, and using the same to try and create the table – chenks Jul 6 '19 at 21:03
1

i've worked out what was going wrong. in the management studio settings it was set to script for SQL 2008, which is why it was failing. i've changed it to script to SQL 2000 and now when i run the create script it executes all ok.

tools > options > scripting > script for server version

Not the answer you're looking for? Browse other questions tagged or ask your own question.