Aug 18, 2005
Any SQL guru's out there know a better way of writing this SP, its very slow.....CREATE PROCEDURE SP_LIST_ACTIVITY_CLASS_BY_ENROLLMENT( @int_activity_class_Id INT,)
asDECLARE @ENROLL_COUNT INTDECLARE @WAITLIST_COUNT INTDECLARE @CONFIRM INTDECLARE @PENDING INTDECLARE @WITHDRAWN INTDECLARE @APPROVED INTDECLARE @DELETED INT
SET @ENROLL_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID NOT IN (4,5,6)) --TAKES 5 SECONDSSET @WAITLIST_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 5) --TAKES 5 SECONDSSET @CONFIRM = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 3) --TAKES 5 SECONDSSET @PENDING = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 2) --TAKES 5 SECONDSSET @WITHDRAWN = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 4) --TAKES 5 SECONDSSET @APPROVED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 1) --TAKES 5 SECONDSSET @DELETED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 6) --TAKES 5 SECONDS
Select A.ACTIVITY_NAME, A.DESCR, C.CUSTOMER_ID, C.CUSTOMER_NAME, cast(A.PROVIDER_CODE as varchar) + '-' + cast(FY.FISCAL_YY as varchar) + '-' + cast(AC.CEU_ACTIVITY_CODE as varchar) + '-' + cast(isnull(ax.activity_seq_number,'XXX') as varchar) as ACTIVITY_CODE, MIN(S.SCHEDULE_DATE) AS SCHEDULE_DATE, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.ECOMMERCE_IND, @ENROLL_COUNT AS ENROLLMENT, @WAITLIST_COUNT AS WAITLIST, @CONFIRM AS CONFIRMED, @PENDING AS PENDING, @WITHDRAWN AS WITHDRAWN, @APPROVED AS APPROVED, @DELETED as DELETED, AX.WAITLIST_INDFrom ACTIVITY_CLASS AS AX JOIN ACTIVITY AS A ON (AX.ACTIVITY_ID = A.ACTIVITY_ID) JOIN CUSTOMER AS C ON (A.CUSTOMER_ID = C.CUSTOMER_ID) JOIN FISCAL_YEAR AS FY ON (AX.FISCAL_YEAR_ID = FY.FISCAL_YEAR_ID) JOIN ACTIVITY_CODE AS AC ON (AX.ACTIVITY_CODE_ID = AC.ACTIVITY_CODE_ID) JOIN SCHEDULE AS S ON (AX.ACTIVITY_CLASS_ID = S.ACTIVITY_CLASS_ID) JOIN ACTIVITY_STATUS AS SS ON (AX.ACTIVITY_STATUS_ID = SS.ACTIVITY_STATUS_ID)Where AX.ACTIVITY_CLASS_ID= @int_activity_class_Id GROUP BY A.ACTIVITY_NAME, A.DESCR,C.CUSTOMER_NAME,C.CUSTOMER_ID, A.PROVIDER_CODE, FY.FISCAL_YY, AC.CEU_ACTIVITY_CODE, ax.activity_seq_number, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.WAITLIST_IND,AX.ECOMMERCE_IND
--TAKES 1 SECONDS--TOTAL 36 SECONDS
View 1 Replies
View Related
May 7, 2007
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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNetManage_SQL_Data.MDF' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = N'NetManage_SQL_Log', FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNetManage_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..
View 3 Replies
View Related