Help Fixing Script.

Hello everybody... I have a SQL 2000 Script that is not working on SQL 2005.. Or 2000 for that matter.. I wanted to know if somebody can help me fix this script.

/****** Object: Database NetManage_SQL ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NetManage_SQL')
DROP DATABASE [NetManage_SQL]
GO

CREATE DATABASE [NetManage_SQL] ON (NAME = N'NetManage_SQL_Data', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NetManage_SQL_Data.MDF' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = N'NetManage_SQL_Log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NetManage_SQL_Log.LDF' , SIZE = 10, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'NetManage_SQL', N'autoclose', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'bulkcopy', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'trunc. log', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'torn page detection', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'read only', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'dbo use', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'single', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'autoshrink', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI null default', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'recursive triggers', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI nulls', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'concat null yields null', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'cursor close on commit', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'default to local cursor', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'quoted identifier', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI warnings', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'auto create statistics', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'auto update statistics', N'true'
GO

if( ( (@.@.microsoftversion / power(2, 24) = 8) and (@.@.microsoftversion & 0xffff >= 724) ) or ( (@.@.microsoftversion / power(2, 24) = 7) and (@.@.microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'NetManage_SQL', N'db chaining', N'false'
GO

use NetManage_SQL
GO
exec sp_addlogin 'NetManageAdmin', 'DigitalNetrixdbadmin', 'NetManage_SQL', 'us_english'
GO

/****** Object: Table [dbo].[Device_SwitchPorts] ******/
CREATE TABLE [dbo].[Device_SwitchPorts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_IPAddress_ID] [int] Default 0 ,
[SwitchPortDevice_Type_ID] [int] Default 0 ,
[SwitchPortSlotNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortDeviceID] [int] Default 0 ,
[SwitchPortDeviceName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Device_Type] ******/
CREATE TABLE [dbo].[Device_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_Type_ID] [int] Default 0 ,
[Device_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress] ******/
CREATE TABLE [dbo].[IPAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_ID] [int] Default 0 ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPDecimal] [float] Default 0 ,
[IPAddress_TypeID] [int] Default 0 ,
[Device_Type_ID] [int] Default 0 ,
[IPAddress_Host_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddressLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPComments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress_Type] ******/
CREATE TABLE [dbo].[IPAddress_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[IPAddress_Type_ID] [int] Default 0 ,
[Can_Edit] [int] NULL ,
[IPAddress_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[User_Activity] ******/
CREATE TABLE [dbo].[User_Activity] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login_Date_Time] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_IP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Users] ******/
CREATE TABLE [dbo].[Users] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree_Label] [nvarchar] (50) Default Contact_Name ,
[DisplayStyle] [int] Default 0 ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Full_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDelete] [int] Default 1 ,
[Node_Access] [ntext] Default 0 ,
[Priv] [int] Default 3
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[settings] ******/
CREATE TABLE [dbo].[settings] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LicenseKey] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[subnet] ******/
CREATE TABLE [dbo].[subnet] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parent_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company_Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Node_Location] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VLAN_Info] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created_By] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert into [settings](Company, LicenseKey, comments) values('DEMO COMPANY','|95|49|47|26|253|195|170|232|71|19|151|77|188|231|23|64|87|62|215|53|169|186|27|65|218|111|185|218|238|127|2|115|187|245','Product License Key')
Insert into [subnet](Subnet_Name, Subnet_Mask, Parent_Subnet) values('Network Enterprise','000000000000','0')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(0,'Not Assigned')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(1,'PC')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(2,'Printer')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(3,'Router')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(4,'Switch')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(5,'Hub')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(6,'Web Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(7,'FTP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(8,'Mail Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(9,'DNS Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(10,'DHCP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(11,'Other')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(12,'Virtual Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(13,'Other Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(14,'VoIP Phone')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(15,'SQL Server')

Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(0,0, 'Not Assigned')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(1,0, 'Static')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(2,0, 'DHCP')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(3,0, 'Reserved')

Insert into [Users](UserName, DisplayStyle, [Password],Full_Name, EmailAddress, CanDelete, Priv) values('Administrator',0,'admin','Administrator', 'admin@.company.com',0,1)

I keep getting error:

Msg 128, Level 15, State 1, Line 6

The name "Contact_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any help pleae..

Thanks..

This line in the Create statement for dbo.Users is your problem: [Tree_Label] [nvarchar] (50) Default Contact_Name ,

If it is your attention to have the default value be the text string Contact_Name, then the string must be enclosed in single quotes. Like so:

[Tree_Label] [nvarchar] (50) Default ('Contact_Name') ,

If you want it to default to a value in another column, then you need to use a trigger, not a default.

|||

Robert,

Thanks for the quick reply.. Just because I am new to this sql scripting thing.. how do I use a trigger and not default?

Thanks..

|||

You would probably have to do something like this:

CREATE TRIGGER TRG_SOmeTrigger ON SomeTable
FOR INSERT
AS
UPDATE SomeTable
SET SomeColumnInsteadOfDefault = AnotherValueorColumn
FROM SomeTable T
INNER JOIN INSERTED I
ON T.YOuPrimaryKeyhere = I.YourPrimaryKeyHere
WHERE SomeColumnInsteadOfDefault IS NULL

Although this is not the same logic as the Default value this could be an example for a trigger.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help ! How do i refer to the deleted table into a trigger statement ?

Hi there,

Could somebody post some simple example how one can refer to
a column in the 'deleted' temporary table within a trigger definition ?
Should the 'deleted' and 'inserted' temp tables be declared, or they are
'implied' ?

I keep having this msg when trying to create a trigger
"The column prefix 'deleted' does not match with a table name or alias name
used in the query"

Thanks for your inputs,
Eddy.[posted and mailed, please reply in news]

Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Could somebody post some simple example how one can refer to
> a column in the 'deleted' temporary table within a trigger definition ?
> Should the 'deleted' and 'inserted' temp tables be declared, or they are
> 'implied' ?

They are implied. You just use them.

> I keep having this msg when trying to create a trigger
> "The column prefix 'deleted' does not match with a table name or alias
> name used in the query"

May I guess that you are using dynamic SQL? Code executed in EXEC() or
sp_executesql is not part of the trigger, but constitutes a scope on
its own.

A practical workaround is to say:

SELECT * INTO #deleted FROM deleted
SELECT * INTO #inserted FROM inserted

first in the trigger.

This can even be useful in a trigger that does not use dynamic SQL,
particular if multi-row operations are common, because you can get
better performance. The virtual tables inserted and deleted are
not very efficient when they get some size.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Thanks Erland,
> I was trying to refer to a column in the deleted temp table with
> 'deleted.column_name'...
> My sql server does not like this !

Since you never posted the code to the trigger that gave you the
error message, I cannot say what your problem really was. Judging from
the code you post now, may you tried with:

> UPDATE DemographicData SET Lastupdate = getdate()
> WHERE ID = deleted.ID

which is not legal, but that has nothing to do with inserted/deleted,
but with the fact that you must name a table in a query in a FROM
clause or after INSERT/DELETE/UPDATE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp