?
Solved

Error running query script on MSDE

Posted on 2005-04-24
4
Medium Priority
?
413 Views
Last Modified: 2012-06-21
Hey all

Hope U canhelp me with this one..
I get the following error when running sql script below (suggestions?) Tnx
The following error occured while executing the query:
Server: Msg 156, Level 15, State 1, Line 3
wrong syntaxis at word CREATE
---------------------------------------
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Informex]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Informex](
      [WreckID] [int] IDENTITY(1,1) NOT NULL,
      [Reference] [nvarchar](9) NULL,
      [Make] [nvarchar](20) NULL,
      [Model] [nvarchar](18) NULL,
      [Plate] [nvarchar](10) NULL,
      [PlateNote] [nvarchar](250) NULL,
      [VIN] [nvarchar](20) NULL,
      [VIN_x0020_Note] [nvarchar](30) NULL,
      [MFG] [nvarchar](50) NULL,
      [Company] [nvarchar](30) NULL,
      [Address] [nvarchar](50) NULL,
      [Code] [nvarchar](10) NULL,
      [KM] [nvarchar](20) NULL,
      [Registered] [nvarchar](50) NULL,
      [Expiry] [nvarchar](50) NULL,
      [Validity] [nvarchar](50) NULL,
      [Photo] [nvarchar](50) NULL,
      [Notes] [nvarchar](250) NULL,
      [Info] [nvarchar](250) NULL,
      [Interested] [nvarchar](50) NULL,
      [PurgedBy] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [DELETED] [tinyint] NULL CONSTRAINT [DF_Informex_DELETED]  DEFAULT ((0))

) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Local]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Local](
      [WreckID] [int] IDENTITY(1,1) NOT NULL,
      [Reference] [nvarchar](9) NULL,
      [Make] [nvarchar](20) NULL,
      [Model] [nvarchar](18) NULL,
      [Plate] [nvarchar](10) NULL,
      [PlateNote] [nvarchar](250) NULL,
      [VIN] [nvarchar](20) NULL,
      [VIN_x0020_Note] [nvarchar](30) NULL,
      [MFG] [nvarchar](50) NULL,
      [Company] [nvarchar](30) NULL,
      [Address] [nvarchar](50) NULL,
      [Code] [nvarchar](10) NULL,
      [KM] [nvarchar](20) NULL,
      [Registered] [nvarchar](50) NULL,
      [Expiry] [nvarchar](50) NULL,
      [Validity] [nvarchar](50) NULL,
      [Photo] [nvarchar](50) NULL,
      [Notes] [nvarchar](250) NULL,
      [Info] [nvarchar](250) NULL,
      [Interested] [nvarchar](50) NULL,
      [PurgedBy] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [DELETED] [tinyint] NULL CONSTRAINT [DF_Local_DELETED]  DEFAULT ((0))

) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OwnCar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OwnCar](
      [WreckID] [int] IDENTITY(1,1) NOT NULL,
      [Reference] [nvarchar](9) NULL,
      [Make] [nvarchar](20) NULL,
      [Model] [nvarchar](18) NULL,
      [Plate] [nvarchar](10) NULL,
      [PlateNote] [nvarchar](250) NULL,
      [VIN] [nvarchar](20) NULL,
      [VIN_x0020_Note] [nvarchar](30) NULL,
      [MFG] [nvarchar](50) NULL,
      [Company] [nvarchar](30) NULL,
      [Address] [nvarchar](50) NULL,
      [Code] [nvarchar](10) NULL,
      [KM] [nvarchar](20) NULL,
      [Registered] [nvarchar](50) NULL,
      [Expiry] [nvarchar](50) NULL,
      [Validity] [nvarchar](50) NULL,
      [Photo] [nvarchar](50) NULL,
      [Notes] [nvarchar](250) NULL,
      [Info] [nvarchar](250) NULL,
      [Interested] [nvarchar](50) NULL,
      [PurgedBy] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [DELETED] [tinyint] NULL CONSTRAINT [DF_OwnCar_DELETED]  DEFAULT ((0))

) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Printed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Printed](
      [WreckID] [int] IDENTITY(1,1) NOT NULL,
      [Reference] [nvarchar](9) NULL,
      [Make] [nvarchar](20) NULL,
      [Model] [nvarchar](18) NULL,
      [Plate] [nvarchar](10) NULL,
      [PlateNote] [nvarchar](250) NULL,
      [VIN] [nvarchar](20) NULL,
      [VIN_x0020_Note] [nvarchar](30) NULL,
      [MFG] [nvarchar](50) NULL,
      [Company] [nvarchar](30) NULL,
      [Address] [nvarchar](50) NULL,
      [Code] [nvarchar](10) NULL,
      [KM] [nvarchar](20) NULL,
      [Registered] [nvarchar](50) NULL,
      [Expiry] [nvarchar](50) NULL,
      [Validity] [nvarchar](50) NULL,
      [Photo] [nvarchar](50) NULL,
      [Notes] [nvarchar](250) NULL,
      [Info] [nvarchar](250) NULL,
      [Interested] [nvarchar](50) NULL,
      [PurgedBy] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [DELETED] [tinyint] NULL CONSTRAINT [DF_Printed_DELETED]  DEFAULT ((0))

) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS ON
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempPrinted]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[TempPrinted](
      [WreckID] [int] IDENTITY(1,1) NOT NULL,
      [Reference] [nvarchar](9) NULL,
      [Make] [nvarchar](20) NULL,
      [Model] [nvarchar](18) NULL,
      [Plate] [nvarchar](10) NULL,
      [PlateNote] [nvarchar](250) NULL,
      [VIN] [nvarchar](20) NULL,
      [VIN_x0020_Note] [nvarchar](30) NULL,
      [MFG] [nvarchar](50) NULL,
      [Company] [nvarchar](30) NULL,
      [Address] [nvarchar](50) NULL,
      [Code] [nvarchar](10) NULL,
      [KM] [nvarchar](20) NULL,
      [Registered] [nvarchar](50) NULL,
      [Expiry] [nvarchar](50) NULL,
      [Validity] [nvarchar](50) NULL,
      [Photo] [nvarchar](50) NULL,
      [Notes] [nvarchar](250) NULL,
      [Info] [nvarchar](250) NULL,
      [Interested] [nvarchar](50) NULL,
      [PurgedBy] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL

) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[InsertInformexWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[InsertInformexWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
INSERT INTO dbo.Informex (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[InsertLocalWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[InsertLocalWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
INSERT INTO Local (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[InsertOwnCarWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[InsertOwnCarWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
INSERT INTO OwnCar (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[InsertPrintedWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[InsertPrintedWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
INSERT INTO Printed (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City)
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[InsertTempPrintedWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[InsertTempPrintedWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
INSERT INTO dbo.TempPrinted (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City)
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[UpdateInformexWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[UpdateInformexWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
UPDATE Informex SET Expiry=@Expiry,Info=@Info,Make=@Make,KM=@Mile,Validity=@MinValidity,Model=@Model,Notes=@Notes,Photo=@Photo,Plate=@Plate,PlateNote=@PlateNote,Registered=@Registered,VIN=@Vin,VIN_x0020_Note=@VinNote,Company=@Company,Address=@Address,Code=@PostCode,City=@City
WHERE Reference= @Reference
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[UpdateLocalWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[UpdateLocalWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
UPDATE Local SET Expiry=@Expiry,Info=@Info,Make=@Make,KM=@Mile,Validity=@MinValidity,Model=@Model,Notes=@Notes,Photo=@Photo,Plate=@Plate,PlateNote=@PlateNote,Registered=@Registered,VIN=@Vin,VIN_x0020_Note=@VinNote,Company=@Company,Address=@Address,Code=@PostCode,City=@City
WHERE Reference= @Reference
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[UpdateOwnCarWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[UpdateOwnCarWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
UPDATE OwnCar SET Expiry=@Expiry,Info=@Info,Make=@Make,KM=@Mile,Validity=@MinValidity,Model=@Model,Notes=@Notes,Photo=@Photo,Plate=@Plate,PlateNote=@PlateNote,Registered=@Registered,VIN=@Vin,VIN_x0020_Note=@VinNote,Company=@Company,Address=@Address,Code=@PostCode,City=@City
WHERE Reference= @Reference
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sys.objects where object_id = object_id(N'[dbo].[UpdatePrintedWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = CREATE PROCEDURE [dbo].[UpdatePrintedWreck]
      @Expiry [nvarchar](50),
      @Info [nvarchar](250),
      @Make [nvarchar](20),
      @Mile [nvarchar](20),
      @MinValidity [nvarchar](50),
      @Model [nvarchar](18),
      @Notes [nvarchar](250),
      @Photo [nvarchar](50),
      @Plate [nvarchar](10),
      @PlateNote [nvarchar](250),
      @Reference [nvarchar](9),
      @Registered [nvarchar](50),
      @Vin [nvarchar](20),
      @VinNote [nvarchar](30),
      @Company [nvarchar](30),
      @Address [nvarchar](50),
      @PostCode [nvarchar](10),
      @City [nvarchar](50)
WITH EXECUTE AS OWNER
AS
UPDATE Printed SET Expiry=@Expiry,Info=@Info,Make=@Make,KM=@Mile,Validity=@MinValidity,Model=@Model,Notes=@Notes,Photo=@Photo,Plate=@Plate,PlateNote=@PlateNote,Registered=@Registered,VIN=@Vin,VIN_x0020_Note=@VinNote,Company=@Company,Address=@Address,Code=@PostCode,City=@City
WHERE Reference= @Reference
'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
0
Comment
Question by:adp01
2 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13855593
Here are 2 sections that have errors.  Given these fixes, you should be able to fix the rest.

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
if not exists (select * from sysobjects where id = object_id(N'[dbo].[InsertInformexWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertInformexWreck]
     @Expiry [nvarchar](50),
     @Info [nvarchar](250),
     @Make [nvarchar](20),
     @Mile [nvarchar](20),
     @MinValidity [nvarchar](50),
     @Model [nvarchar](18),
     @Notes [nvarchar](250),
     @Photo [nvarchar](50),
     @Plate [nvarchar](10),
     @PlateNote [nvarchar](250),
     @Reference [nvarchar](9),
     @Registered [nvarchar](50),
     @Vin [nvarchar](20),
     @VinNote [nvarchar](30),
     @Company [nvarchar](30),
     @Address [nvarchar](50),
     @PostCode [nvarchar](10),
     @City [nvarchar](50)
--WITH EXECUTE AS OWNER
AS
INSERT INTO dbo.Informex (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City) '
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if not exists (select * from sysobjects where id = object_id(N'[dbo].[InsertLocalWreck]') and type in (N'P', N'PC'))
BEGIN
execute sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertLocalWreck]
     @Expiry [nvarchar](50),
     @Info [nvarchar](250),
     @Make [nvarchar](20),
     @Mile [nvarchar](20),
     @MinValidity [nvarchar](50),
     @Model [nvarchar](18),
     @Notes [nvarchar](250),
     @Photo [nvarchar](50),
     @Plate [nvarchar](10),
     @PlateNote [nvarchar](250),
     @Reference [nvarchar](9),
     @Registered [nvarchar](50),
     @Vin [nvarchar](20),
     @VinNote [nvarchar](30),
     @Company [nvarchar](30),
     @Address [nvarchar](50),
     @PostCode [nvarchar](10),
     @City [nvarchar](50)
--WITH EXECUTE AS OWNER
AS
INSERT INTO Local (Expiry,Info,Make,KM,Validity,Model,Notes,Photo,Plate,PlateNote,Reference,Registered,VIN,VIN_x0020_Note,Company,Address,Code,City)
VALUES (@Expiry, @Info, @Make, @Mile, @MinValidity, @Model, @Notes, @Photo, @Plate, @PlateNote, @Reference, @Registered, @Vin, @VinNote , @Company, @Address, @PostCode, @City)'
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13856033
I have three questions about your code...

1.  Why are you setting ansi_nulls and quoted_identifier on then immediately setting them back off?

2.  Why are you using sp_executesql instead of just "create procedure..."?  When you run code from sp_executesql or exec(), that code runs in its
own context.  This means that your specifications of "SET ..." are irrelevant... the code will use the system defaults (since it's in its own context).

3.  Do you really need to store your data as Unicode?  The "nvarchar" datatype requires two bytes of storage for each character.  Obviously, if you need
unicode then this is correct, but if not you're wasting disk space, and more importantly, bandwidth for all your queries (increased network traffic).

-- Lynn
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question