need a little help
-
Monday, April 09, 2012 3:08 PM
Hello every body
I face a little problem I have a system programmed with ASP
I want to uploaded it to my domain and I don’t know how
I think there is a problem with the unpack script which is the name of it is smsdatabase
Kindly can anyone help on this I only want to make the script work to make the system be ready to upload it to my domain
Here is the read me file I should use it to exctract the system
1- extract the .rar file in your virual directory for the application
2- open sqlserver2005 managment studio and run the script included in the "smsdatabase.txt" file
3-login creadentails is in the web.config file
note : please donot change the login name "smslogin" as it owns a function schema
but you can change its password
5- be sure that the Roles table contains data and users table contains data
6- login by username : admin and password " admin
And here the script of smsdatabase.txt “ not working ??!!“
/****** Object: Database [SMS] Script Date: 01/10/2010 10:14:49 ******/
CREATE DATABASE [SMS] ON PRIMARY
( NAME = N'SMS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SMS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'SMS', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SMS].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SMS] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SMS] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SMS] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SMS] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SMS] SET ARITHABORT OFF
GO
ALTER DATABASE [SMS] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SMS] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SMS] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SMS] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SMS] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SMS] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SMS] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SMS] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SMS] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SMS] SET ENABLE_BROKER
GO
ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SMS] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SMS] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SMS] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SMS] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SMS] SET READ_WRITE
GO
ALTER DATABASE [SMS] SET RECOVERY SIMPLE
GO
ALTER DATABASE [SMS] SET MULTI_USER
GO
ALTER DATABASE [SMS] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SMS] SET DB_CHAINING OFF
GO
USE [SMS]
GO
/****** Object: User [smslogin] Script Date: 01/10/2010 10:14:49 ******/
CREATE USER [smslogin] FOR LOGIN [smslogin] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: Table [dbo].[Roles] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[RoleID] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](50) NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Roles values ('Admins')
Go
insert into Roles values ('Users')
/****** Object: Table [dbo].[test] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [int] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[trueperc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[trueperc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where (postid = @postid) and (Answer = 1)
select @allusers = count(*) from answers where (postid = @postid)
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
/****** Object: Table [dbo].[Posts] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Posts](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[Post] [nvarchar](1000) NULL,
[Post_Date] [datetime] NULL,
[Expiration_Date] [datetime] NULL,
[UserID] [int] NULL,
[Correct_Answ] [bit] NULL CONSTRAINT [DF_Posts_Correct_Answ] DEFAULT ((0)),
CONSTRAINT [PK_Posts_1] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Answers] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Answers](
[Answ_ID] [int] IDENTITY(1,1) NOT NULL,
[Answer] [bit] NULL CONSTRAINT [DF_Answers_Answer] DEFAULT ((0)),
[PostID] [int] NULL,
[UserID] [int] NULL,
CONSTRAINT [PK_Answers_1] PRIMARY KEY CLUSTERED
(
[Answ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Extra_Admins] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Extra_Admins](
[Id] [int] IDENTITY(1,1) NOT NULL,
[admin_Email] [nvarchar](200) NULL,
[admin_Tel] [nvarchar](50) NULL,
CONSTRAINT [PK_Extra_Admins] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[gateway_setting](
[ID] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NULL,
[userpassword] [nvarchar](50) NULL,
[api_id] [nvarchar](100) NULL,
[baseurl] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Go
/****** Object: UserDefinedFunction [dbo].[getfromdate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
GO
/****** Object: UserDefinedFunction [dbo].[gettodate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
GO
/****** Object: Table [dbo].[Users] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Tel] [nvarchar](50) NULL,
[Job_Title] [nvarchar](100) NULL,
[Address] [nvarchar](500) NULL,
[Username] [nvarchar](50) NULL,
[UserPassword] [nvarchar](50) NULL,
[Active] [bit] NULL,
[RoleID] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Users values('administrator','esbtan@yahoo.com','01234569874','developer','23 hassan street','admin','admin',1,1)
Go
/****** Object: UserDefinedFunction [dbo].[perc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[perc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where postid = @postid
select @allusers = count(*) from users where Active = 1
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
create function [smslogin].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
Go
create function [smslogin].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
All Replies
-
Monday, April 09, 2012 4:51 PMwhat error do you get ?
-
Monday, April 09, 2012 5:47 PM
thank u Daniel for your respond
well this is the error i got
Major Error 0x80040E14, Minor Error 25501
> /****** Object: Database [SMS] Script Date: 01/10/2010 10:14:49 ******/
CREATE DATABASE [SMS] ON PRIMARY
--C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf
( NAME = N'SMS', FILENAME = N'C:\Test\SMS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SMS_log', FILENAME = N'C:\Test\SMS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
There was an error parsing the query. [ Token line number = 3,Token line offset = 23,Token in error = ON ]
Major Error 0x80040E14, Minor Error 25501
> EXEC dbo.sp_dbcmptlevel @dbname=N'SMS', @new_cmptlevel=90
There was an error parsing the query. [ Token line number = 1,Token line offset = 9,Token in error = . ]
Major Error 0x80040E14, Minor Error 25501
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SMS].[dbo].[sp_fulltext_database] @action = 'enable'
end
There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ANSI_NULL_DEFAULT OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ANSI_NULLS OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ANSI_PADDING OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ANSI_WARNINGS OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ARITHABORT OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET AUTO_CLOSE OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET AUTO_CREATE_STATISTICS ON
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET AUTO_SHRINK OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS ON
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET CURSOR_CLOSE_ON_COMMIT OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET CURSOR_DEFAULT GLOBAL
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET CONCAT_NULL_YIELDS_NULL OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET NUMERIC_ROUNDABORT OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET QUOTED_IDENTIFIER OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET RECURSIVE_TRIGGERS OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ENABLE_BROKER
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET DATE_CORRELATION_OPTIMIZATION OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET TRUSTWORTHY OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET ALLOW_SNAPSHOT_ISOLATION OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET PARAMETERIZATION SIMPLE
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET READ_WRITE
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET RECOVERY SIMPLE
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET MULTI_USER
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET PAGE_VERIFY CHECKSUM
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80040E14, Minor Error 25501
> ALTER DATABASE [SMS] SET DB_CHAINING OFF
There was an error parsing the query. [ Token line number = 1,Token line offset = 7,Token in error = DATABASE ]
Major Error 0x80004005, Minor Error 25046
The database file cannot be found. Check the path to the database. [ File name = SMS ]
Major Error 0x80040E14, Minor Error 25501
> /****** Object: User [smslogin] Script Date: 01/10/2010 10:14:49 ******/
--Create login [smslogin] with password='TodayIS09-Apr' --Added by Manish on 09-April
There was an error parsing the query. [ Token line number = 3,Token line offset = 2,Token in error = ]
Major Error 0x80040E14, Minor Error 25501
> CREATE USER [smslogin] FOR LOGIN [smslogin] WITH DEFAULT_SCHEMA=[dbo]
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = USER ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: Table [dbo].[Roles] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[Roles](
[RoleID] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](50) NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = Roles ]
Major Error 0x80040E37, Minor Error 0
> insert into Roles values ('Admins')
The specified table does not exist. [ Roles ]
Major Error 0x80040E14, Minor Error 25501
> insert into Roles values ('Users')
/****** Object: Table [dbo].[test] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
There was an error parsing the query. [ Token line number = 5,Token line offset = 1,Token in error = SET ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [int] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = test ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: UserDefinedFunction [dbo].[trueperc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 25501
> create FUNCTION [dbo].[trueperc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where (postid = @postid) and (Answer = 1)
select @allusers = count(*) from answers where (postid = @postid)
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = FUNCTION ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: Table [dbo].[Posts] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[Posts](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[Post] [nvarchar](1000) NULL,
[Post_Date] [datetime] NULL,
[Expiration_Date] [datetime] NULL,
[UserID] [int] NULL,
[Correct_Answ] [bit] NULL CONSTRAINT [DF_Posts_Correct_Answ] DEFAULT ((0)),
CONSTRAINT [PK_Posts_1] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = Posts ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: Table [dbo].[Answers] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[Answers](
[Answ_ID] [int] IDENTITY(1,1) NOT NULL,
[Answer] [bit] NULL CONSTRAINT [DF_Answers_Answer] DEFAULT ((0)),
[PostID] [int] NULL,
[UserID] [int] NULL,
CONSTRAINT [PK_Answers_1] PRIMARY KEY CLUSTERED
(
[Answ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = Answers ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: Table [dbo].[Extra_Admins] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[Extra_Admins](
[Id] [int] IDENTITY(1,1) NOT NULL,
[admin_Email] [nvarchar](200) NULL,
[admin_Tel] [nvarchar](50) NULL,
CONSTRAINT [PK_Extra_Admins] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = Extra_Admins ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[gateway_setting](
[ID] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NULL,
[userpassword] [nvarchar](50) NULL,
[api_id] [nvarchar](100) NULL,
[baseurl] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = gateway_setting ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: UserDefinedFunction [dbo].[getfromdate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 25501
> create function [dbo].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = function ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: UserDefinedFunction [dbo].[gettodate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 25501
> create function [dbo].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = function ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: Table [dbo].[Users] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Tel] [nvarchar](50) NULL,
[Job_Title] [nvarchar](100) NULL,
[Address] [nvarchar](500) NULL,
[Username] [nvarchar](50) NULL,
[UserPassword] [nvarchar](50) NULL,
[Active] [bit] NULL,
[RoleID] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = Users ]
Major Error 0x80040E37, Minor Error 0
> insert into Users values('administrator','esbtan@yahoo.com','01234569874','developer','23 hassan street','admin','admin',1,1)
The specified table does not exist. [ Users ]
Major Error 0x80040E14, Minor Error 25569
> /****** Object: UserDefinedFunction [dbo].[perc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
Invalid set option. [ Set option = ANSI_NULLS ]
Major Error 0x80040E14, Minor Error 25569
> SET QUOTED_IDENTIFIER ON
Invalid set option. [ Set option = QUOTED_IDENTIFIER ]
Major Error 0x80040E14, Minor Error 25501
> CREATE FUNCTION [dbo].[perc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where postid = @postid
select @allusers = count(*) from users where Active = 1
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = FUNCTION ]
Major Error 0x80040E14, Minor Error 25501
> Create Schema [smslogin] -- Added by Manish on 09-April
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = Schema ]
Major Error 0x80040E14, Minor Error 25501
> create function [smslogin].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = function ]
Major Error 0x80040E14, Minor Error 25501
> create function [smslogin].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
There was an error parsing the query. [ Token line number = 1,Token line offset = 8,Token in error = function ]
-
Monday, April 09, 2012 8:36 PM
where did you get the script from ?
if you look at the sample C on the MSDN page describing CREATE DATABASE statement
http://msdn.microsoft.com/de-de/library/ms176061.aspx
you see that the FILENAME attribute is not a (native) string with single quote but a normal string constant
your code has to look like
and please ensure that the directory already exists and SQL Service account has correct permission.CREATE DATABASE [SMS] ON PRIMARY ( NAME = SMS, FILENAME = N'C:\Test\SMS.mdf', SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON ( NAME = SMS_log, FILENAME = N'C:\Test\SMS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
- Edited by Daniel_Steiner Monday, April 09, 2012 8:36 PM
-
Tuesday, April 10, 2012 9:13 AM
Hello Daniel
Thank you for your great help man
Well I uploaded the inter system to this link
http://www.mediafire.com/?pbzgbq6c3mmzmp8
I only want to upload that system to my domain name I have FTP program and a domain name and a windows hosting plan
Can you kindly make any modification that u see and return it back to upload it with the ftp Programs with easy way because man I am not fimialer with that things SQL
Here ia my email
Thank u my friend for your help
Yours
Marooo
-
Tuesday, April 10, 2012 11:52 AM
hi Marooo
I've check the files and it does run on SQL 2005 and later.
could you please check that you're connecting to a SQL 2005 instance with SSMS and not a SQL Compact edition?
just start SSMS and log onto the SQL Server, open a new query window and execute the following statement:
select @@version
-
Tuesday, April 10, 2012 5:02 PM
Hello Daniel
i started the SSMS but i could not log in the sql server i don't know how to do that
and i execute the statement in a new query window and it gave me that
Major Error 0x80040E14, Minor Error 25623
> select @@version
The global variable name is not valid. [ Global variable Name = @@version ]
so....????
kindly be know that I am not familiar with SQL kindly be step be step for Marooo who is a poor SQL man
yours
Marooo
- Edited by marooo Tuesday, April 10, 2012 5:30 PM
-
Tuesday, April 10, 2012 6:48 PM
please read this tutorial http://msdn.microsoft.com/en-us/library/ms166996.aspx about SSMS to connect to a SQL Server instance.
By attentation that you select SQL Databaes Engine and not Analysis Services, Integration Services or Reporting Services.
-
Tuesday, April 10, 2012 9:47 PM
Daniel
i tried to do what it say
In the Connect to Server dialog box, verify the default settings, and then click Connect. To connect, the Server name box must contain the name of the computer where SQL Server is installed. If the Database Engine is a named instance, the Server name box should also contain the instance name in the format <computer_name>\<instance_name>.
what it means the name of the computer where SQL Server is installed.???
what should i put in the server name in my case ??
Marooo
-
Wednesday, April 11, 2012 9:00 AM
to use a database you definitively need a SQL Server somewhere.
Did you install a SQL Server Express edition locally ? if you don't know whether you've installed one or not, use SQL Server Configuration Manager tool to list all local SQL Server services.
Probably you've installed one locally and therefore just use these two possible server names
- localhost (or the name of your computer)
- localhost\sqlexpress (or the name of your computer instead of localhost)
-
Wednesday, April 11, 2012 10:14 AM
Daniel
thank u for helping me
well
I have in my computer sql server business intelligence development studio
And
sql server management studio
all
2005
When I try to put the name server localhost in sql server management studio
It gaves me
Cannot connect to localhost
A networkrelated or instance-specific error
The server was not found or was not accessible verify that the instance name is correct and that sql server is configured to allow remote connections
(provider: namedpipes provider, error 40- could not open a connection to sql server )(Microsoft sql server, error 2)
Marooo
-
Wednesday, April 11, 2012 12:11 PM
Marooo
please use SQL Server Configuration Manager tool and post all the listed SQL Services
-
Wednesday, April 11, 2012 3:48 PM
Daniel
Under the sql server 2005 services it show in the next page
There are no items to show in this view
Under the sql server 2005 network configuration it show in the next page
There are no items to show in this view
Under the sql native client configuration it show in the next page
Client protocols
Aliases
Under Client protocols
Shared memory 1 enabled
Tcp/ip 2 enabled
Named pipes 3 enabled
Via
Under Aliases
There are no items to show in this view
thats all
Marooo
-
Wednesday, April 11, 2012 4:00 PM
this means that you've no SQL Server installed.
Please install a SQL Server - eg. SQL Server Express 2008R2 - from this link http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26729 but you may need to install SSMS 2008 as well as SSMS 2005 does not allow to connect to SQL 2008 or higher or just search for SQL Express 2005 download
-
Wednesday, April 11, 2012 6:26 PM
Daniel
i intealled the SQL EXPRESS 2005
and i did what u say befour
it gaves me that errors
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'C:'.
Msg 15010, Level 16, State 1, Procedure sp_dbcmptlevel, Line 47
The database 'SMS' does not exist. Supply a valid database name. To see available databases, use sys.databases.
Msg 911, Level 16, State 1, Line 3
Could not locate entry in sysdatabases for database 'SMS'. No entry found with that name. Make sure that the name is entered correctly.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'SMS', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SMS'. No entry found with that name. Make sure that the name is entered correctly.
Msg 15023, Level 16, State 1, Line 2
User, group, or role 'smslogin' already exists in the current database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Roles' in the database.
(1 row(s) affected)
(1 row(s) affected)
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test' in the database.
Msg 2714, Level 16, State 3, Procedure trueperc, Line 17
There is already an object named 'trueperc' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Posts' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Answers' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Extra_Admins' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'gateway_setting' in the database.
Msg 2714, Level 16, State 3, Procedure getfromdate, Line 9
There is already an object named 'getfromdate' in the database.
Msg 2714, Level 16, State 3, Procedure gettodate, Line 10
There is already an object named 'gettodate' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Users' in the database.
(1 row(s) affected)
Msg 2714, Level 16, State 3, Procedure perc, Line 16
There is already an object named 'perc' in the database.
Msg 2714, Level 16, State 3, Procedure getfromdate, Line 9
There is already an object named 'getfromdate' in the database.
Msg 2714, Level 16, State 3, Procedure gettodate, Line 11
There is already an object named 'gettodate' in the database.
Maroo
-
Thursday, April 12, 2012 5:38 PM
Hello Daniel
i started the SSMS but i could not log in the sql server i don't know how to do that
and i execute the statement in a new query window and it gave me that
Major Error 0x80040E14, Minor Error 25623
> select @@version
The global variable name is not valid. [ Global variable Name = @@version ]
so....????
kindly be know that I am not familiar with SQL kindly be step be step for Marooo who is a poor SQL man
yours
Marooo
after installation of SQL Express 2005 does it work now
select @@version
-
Thursday, April 12, 2012 5:43 PM
did you use SSMS 2005 and connected to the SQL 2005 Express instance ? (check instance name using SQL Server Configuration Manager)
-
Thursday, April 12, 2012 8:59 PM
thank you Daniel for your replay
yes SQL EXPRESS 2005 working now good
-
Friday, April 13, 2012 11:12 AM
what should we do next Daniel?
Marooo
-
Friday, April 13, 2012 8:15 PM
step-by-step or even more realistic trial-and-error.
could you start SSMS again, connect to SQLExpress instance and execute this code again:
select @@version
just to ensure that you've a valid query window.
now try the statement to create the database named SMS
CREATE DATABASE [SMS]
ON PRIMARY ( NAME = N'SMS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SMS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO
replace the path "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA" above by the directory specified as SQL Server database default location - or just create a folder somewhere and you that path but in this case you need give the SQL Server service account (NT AUTHORITY\Network service) Full Access to the folder.
If the above create statement fail try just create the database with all default option
CREATE DATABASE [SMS]
GO
does this second create statement work and where are the files created ? in SSMS select database SMS and then right button -> Properties -> Files
-
Friday, April 13, 2012 9:47 PM
Daniel
thank you for replaying me
i did what u say
when i do the right signal it gave me
Command(s) completed successfully.
but when do execute
it gaves me
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf' because it already exists. Change the file path or the file name, and retry the operation.
after that under the database i can't find SMS
it is :
master
model
msdb
tempdb
when i do to master right button -> Properties -> Files
it gaves me
master - data - primary - 4 - by 10 percent, urestricted - c:\program files\microsoft SQL server\MSSQL.1\MSSQL\DATA - master.mdf
mastlog - log - not applicable - 2 - by 10 percent, urestricted - c:\program files\microsoft SQL server\MSSQL.1\MSSQL\DATA - mastlog.ldf
so...??
what do u think ???
Marooo
-
Saturday, April 14, 2012 9:34 AM
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf' because it already exists. Change the file path or the file name, and retry the operation.
after that under the database i can't find SMS
you've orphaned database files from previous tests.
Either give them a different name in the scripts (e.g. SMS1.mdf and SMS1.ldf instead of SMS.mdf/SMS.ldf) or delete the two files SMS.mdf and SMS.ldf in that directory. But better move the files to a different directory in case there're referenced by someone.
-
Saturday, April 14, 2012 10:48 AM
Daniel
Thank you for replaying me
well
i makes a change to SMS.mdf to be sms4.mdf and .ldf too
finally data base SMS is now shown
then
in SSMS select database SMS and then right button -> Properties -> Files
it gaves me :
SMS - Data - PRIMARY - 3 - By 1 mb,unrestricted growth - c:\program files\microsoft SQL server\MSSQL.1\MSSQL\DATA - sms4.mdf
SMS_log - log - not applicable - 1 - by 10 percent, restricted growth to 2097152 mb - c:\program files\microsoft SQL server\MSSQL.1\MSSQL\DATA - SMS4_log.ldf
now
what we should do next ?
Marooo
-
Sunday, April 15, 2012 9:36 AM
either copy & paste the rest of the command listened in the file "smsdatabase.txt" into a new query window having current database = SMS (e.g using drop down pullbox) and than execute all the statements.
Alternatively
delete the database SMS using SQL Server Management Studio GUI, correct the two filenames in "smsdatabase.txt" from SMS.mdf and SMS_.log.ldf to SMS5.mdf and SMS5_log.ldf and copy the changed content of "smsdatabase.txt" into a new query and execute the the statements.
-
Sunday, April 15, 2012 10:06 AM
Thank you Daniel for replaying me even if in your weekend
well, man i did execute that code like u said and cut the order for database making that we did befour
and it gives me that :
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 2760, Level 16, State 1, Procedure getfromdate, Line 9
The specified schema name "smslogin" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure gettodate, Line 11
The specified schema name "smslogin" either does not exist or you do not have permission to use it.
so what we should do ??
the code is :
EXEC dbo.sp_dbcmptlevel @dbname=N'SMS', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SMS].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SMS] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SMS] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SMS] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SMS] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SMS] SET ARITHABORT OFF
GO
ALTER DATABASE [SMS] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SMS] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SMS] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SMS] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SMS] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SMS] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SMS] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SMS] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SMS] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SMS] SET ENABLE_BROKER
GO
ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SMS] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SMS] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SMS] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SMS] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SMS] SET READ_WRITE
GO
ALTER DATABASE [SMS] SET RECOVERY SIMPLE
GO
ALTER DATABASE [SMS] SET MULTI_USER
GO
ALTER DATABASE [SMS] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SMS] SET DB_CHAINING OFF
GO
USE [SMS]
GO
/****** Object: User [smslogin] Script Date: 01/10/2010 10:14:49 ******/
CREATE USER [smslogin] FOR LOGIN [smslogin] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: Table [dbo].[Roles] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[RoleID] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](50) NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Roles values ('Admins')
Go
insert into Roles values ('Users')
/****** Object: Table [dbo].[test] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [int] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[trueperc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[trueperc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where (postid = @postid) and (Answer = 1)
select @allusers = count(*) from answers where (postid = @postid)
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
/****** Object: Table [dbo].[Posts] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Posts](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[Post] [nvarchar](1000) NULL,
[Post_Date] [datetime] NULL,
[Expiration_Date] [datetime] NULL,
[UserID] [int] NULL,
[Correct_Answ] [bit] NULL CONSTRAINT [DF_Posts_Correct_Answ] DEFAULT ((0)),
CONSTRAINT [PK_Posts_1] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Answers] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Answers](
[Answ_ID] [int] IDENTITY(1,1) NOT NULL,
[Answer] [bit] NULL CONSTRAINT [DF_Answers_Answer] DEFAULT ((0)),
[PostID] [int] NULL,
[UserID] [int] NULL,
CONSTRAINT [PK_Answers_1] PRIMARY KEY CLUSTERED
(
[Answ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Extra_Admins] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Extra_Admins](
[Id] [int] IDENTITY(1,1) NOT NULL,
[admin_Email] [nvarchar](200) NULL,
[admin_Tel] [nvarchar](50) NULL,
CONSTRAINT [PK_Extra_Admins] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[gateway_setting](
[ID] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NULL,
[userpassword] [nvarchar](50) NULL,
[api_id] [nvarchar](100) NULL,
[baseurl] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Go
/****** Object: UserDefinedFunction [dbo].[getfromdate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
GO
/****** Object: UserDefinedFunction [dbo].[gettodate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
GO
/****** Object: Table [dbo].[Users] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Tel] [nvarchar](50) NULL,
[Job_Title] [nvarchar](100) NULL,
[Address] [nvarchar](500) NULL,
[Username] [nvarchar](50) NULL,
[UserPassword] [nvarchar](50) NULL,
[Active] [bit] NULL,
[RoleID] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Users values('administrator','esbtan@yahoo.com','01234569874','developer','23 hassan street','admin','admin',1,1)
Go
/****** Object: UserDefinedFunction [dbo].[perc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[perc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where postid = @postid
select @allusers = count(*) from users where Active = 1
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
create function [smslogin].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
Go
create function [smslogin].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
-
Sunday, April 15, 2012 1:10 PM
from the message it assume the there're some orphan users and schema left over from earlier test.
Drop the database SMS again and check in SQL Server Management Studio if there exist other user databases as we need to drop logins and schema?
Please expand the node below Server -> Security -> Logins
check if there's already a user smslogin and to which database it is mapped as database user.
Delete this user and the associates schema.
then run the database script in "smsdatabase.txt" again
if you still have problems please execute the following statements to provide information about the databases and logins
select name from sys.logins
select name from sys.databases
select name from sys.schemas
-
Sunday, April 15, 2012 3:28 PM
Daniel
thank you for your replay
well
i deleted the smslogin
and then run the scribt again it gaves me
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 15007, Level 16, State 1, Line 2
'smslogin' is not a valid login or you do not have permission.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Roles' in the database.
(1 row(s) affected)
(1 row(s) affected)
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test' in the database.
Msg 2714, Level 16, State 3, Procedure trueperc, Line 17
There is already an object named 'trueperc' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Posts' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Answers' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Extra_Admins' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'gateway_setting' in the database.
Msg 2714, Level 16, State 3, Procedure getfromdate, Line 9
There is already an object named 'getfromdate' in the database.
Msg 2714, Level 16, State 3, Procedure gettodate, Line 10
There is already an object named 'gettodate' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Users' in the database.
(1 row(s) affected)
Msg 2714, Level 16, State 3, Procedure perc, Line 16
There is already an object named 'perc' in the database.
Msg 2760, Level 16, State 1, Procedure getfromdate, Line 9
The specified schema name "smslogin" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure gettodate, Line 11
The specified schema name "smslogin" either does not exist or you do not have permission to use it.
i try to run the script
-
Sunday, April 15, 2012 3:48 PM
beside Login, Schema you need to check and delete 'smslogin' object in the node Security->Roles in the databases
and the logins can be queried
select name from sys.syslogins
did you delete the SMS database first before executing the sql scripts ? and did you include the create database statement?
if you've still problems than I would recommand that you delete the SMS database again and run the script again but that time each batch (= a sequence of T-SQL statements) individually.
As such start at the first line and select all lines up-to and including the first GO statement at the beginning of a line and than run Execute. Repeat this for every block and post the first batch which gives an error.
- Edited by Daniel_Steiner Sunday, April 15, 2012 3:51 PM
-
Sunday, April 15, 2012 5:21 PM
hello man thank you for replaying me
well
i deleted SMS
and start all over again
and make the smsdatabase.txt be parts
all works good but the last code not working:
/****** Object: Table [dbo].[Users] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Tel] [nvarchar](50) NULL,
[Job_Title] [nvarchar](100) NULL,
[Address] [nvarchar](500) NULL,
[Username] [nvarchar](50) NULL,
[UserPassword] [nvarchar](50) NULL,
[Active] [bit] NULL,
[RoleID] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Users values('administrator','amrehassan3333@yahoo.com','01285937364','developer','23 torant street','admin','admin',1,1)
Go
/****** Object: UserDefinedFunction [dbo].[perc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[perc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where postid = @postid
select @allusers = count(*) from users where Active = 1
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
create function [smslogin].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
Go
create function [smslogin].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
it gaves me
(1 row(s) affected)
Msg 2760, Level 16, State 1, Procedure getfromdate, Line 9
The specified schema name "smslogin" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure gettodate, Line 11
The specified schema name "smslogin" either does not exist or you do not have permission to use it.
i think the problem is in the smslogin i think that
i think the code of smslogin in not working good the problem is on it
Marooo
-
Sunday, April 15, 2012 5:31 PMModerator
Before you create functions in the smslogin schema, you need to first create the schema:
CREATE SCHEMA smslogin;
GO
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
-
Sunday, April 15, 2012 5:42 PM
thank you Dan to trying to help
i execute the code
-
Sunday, April 15, 2012 9:28 PMModerator
It looks like the schema already exists. Do you still get the error when you try to create the functions in the smslogin schema?
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
-
Sunday, April 15, 2012 11:46 PM
thank you for your replay Dan
and many thanks for Daniel he is trying his very very best to help me, he has a big heart and very kinds
well Dan
here is the orignal code u can check it in your computer and tel me what to do but please step by step man becouse i am not that good in SQL
and u can find all the database files in this ZIP file
http://www.mediafire.com/?pbzgbq6c3mmzmp8
the code is :
/****** Object: Database [SMS] Script Date: 01/10/2010 10:14:49 ******/
CREATE DATABASE [SMS] ON PRIMARY
( NAME = N'SMS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SMS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'SMS', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SMS].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SMS] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SMS] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SMS] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SMS] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SMS] SET ARITHABORT OFF
GO
ALTER DATABASE [SMS] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SMS] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SMS] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SMS] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SMS] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SMS] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SMS] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SMS] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SMS] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SMS] SET ENABLE_BROKER
GO
ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SMS] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SMS] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SMS] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SMS] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SMS] SET READ_WRITE
GO
ALTER DATABASE [SMS] SET RECOVERY SIMPLE
GO
ALTER DATABASE [SMS] SET MULTI_USER
GO
ALTER DATABASE [SMS] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SMS] SET DB_CHAINING OFF
GO
USE [SMS]
GO
/****** Object: User [smslogin] Script Date: 01/10/2010 10:14:49 ******/
CREATE USER [smslogin] FOR LOGIN [smslogin] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: Table [dbo].[Roles] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[RoleID] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](50) NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[RoleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Roles values ('Admins')
Go
insert into Roles values ('Users')
/****** Object: Table [dbo].[test] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [int] NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[trueperc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[trueperc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where (postid = @postid) and (Answer = 1)
select @allusers = count(*) from answers where (postid = @postid)
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
/****** Object: Table [dbo].[Posts] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Posts](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[Post] [nvarchar](1000) NULL,
[Post_Date] [datetime] NULL,
[Expiration_Date] [datetime] NULL,
[UserID] [int] NULL,
[Correct_Answ] [bit] NULL CONSTRAINT [DF_Posts_Correct_Answ] DEFAULT ((0)),
CONSTRAINT [PK_Posts_1] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Answers] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Answers](
[Answ_ID] [int] IDENTITY(1,1) NOT NULL,
[Answer] [bit] NULL CONSTRAINT [DF_Answers_Answer] DEFAULT ((0)),
[PostID] [int] NULL,
[UserID] [int] NULL,
CONSTRAINT [PK_Answers_1] PRIMARY KEY CLUSTERED
(
[Answ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Extra_Admins] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Extra_Admins](
[Id] [int] IDENTITY(1,1) NOT NULL,
[admin_Email] [nvarchar](200) NULL,
[admin_Tel] [nvarchar](50) NULL,
CONSTRAINT [PK_Extra_Admins] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[gateway_setting](
[ID] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NULL,
[userpassword] [nvarchar](50) NULL,
[api_id] [nvarchar](100) NULL,
[baseurl] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Go
/****** Object: UserDefinedFunction [dbo].[getfromdate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
GO
/****** Object: UserDefinedFunction [dbo].[gettodate] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
GO
/****** Object: Table [dbo].[Users] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Tel] [nvarchar](50) NULL,
[Job_Title] [nvarchar](100) NULL,
[Address] [nvarchar](500) NULL,
[Username] [nvarchar](50) NULL,
[UserPassword] [nvarchar](50) NULL,
[Active] [bit] NULL,
[RoleID] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Users values('administrator','amrehassan3333@yahoo.com','01285937364','developer','23 torant street','admin','admin',1,1)
Go
/****** Object: UserDefinedFunction [dbo].[perc] Script Date: 01/10/2010 10:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[perc] (@postid int)
RETURNS decimal(18,3)
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret decimal(18,3)
declare @answ decimal(18,3)
declare @allusers int
select @answ = count(*) from answers
where postid = @postid
select @allusers = count(*) from users where Active = 1
--select @ret = (@answ/@allusers)*100
select @ret = ISNULL(@answ/NULLIF(@allusers,0),0)
set @ret = @ret *100
return @ret
END
GO
create function [smslogin].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @fromdate datetime
set @fromdate = @yea + '-' + @mon +'-01'
return @fromdate
end
Go
create function [smslogin].[gettodate](@mon nvarchar(50) , @yea nvarchar(50))
RETURNS datetime
as
begin
declare @todate datetime,@tempdate datetime
set @tempdate = @yea + '-' + @mon +'-01'
SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0))
return @todate
end
-
Monday, April 16, 2012 2:06 AMModerator
I added a conditional DROP DATABASE and CREATE LOGIN as well as CREATE SCHEMA added. This script ran successfully on my system.
USE master; GO IF DB_ID(N'SMS') IS NOT NULL BEGIN ALTER DATABASE [SMS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [SMS]; END GO IF SUSER_ID(N'smslogin') IS NULL BEGIN CREATE LOGIN [smslogin] WITH PASSWORD = 'smsl0ginP@ssw0rd'; END GO CREATE DATABASE [SMS] ON PRIMARY ( NAME = N'SMS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SMS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SMS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [SMS].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [SMS] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [SMS] SET ANSI_NULLS OFF GO ALTER DATABASE [SMS] SET ANSI_PADDING OFF GO ALTER DATABASE [SMS] SET ANSI_WARNINGS OFF GO ALTER DATABASE [SMS] SET ARITHABORT OFF GO ALTER DATABASE [SMS] SET AUTO_CLOSE OFF GO ALTER DATABASE [SMS] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [SMS] SET AUTO_SHRINK OFF GO ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [SMS] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [SMS] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [SMS] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [SMS] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [SMS] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [SMS] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [SMS] SET ENABLE_BROKER GO ALTER DATABASE [SMS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [SMS] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [SMS] SET TRUSTWORTHY OFF GO ALTER DATABASE [SMS] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [SMS] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [SMS] SET READ_WRITE GO ALTER DATABASE [SMS] SET RECOVERY SIMPLE GO ALTER DATABASE [SMS] SET MULTI_USER GO ALTER DATABASE [SMS] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [SMS] SET DB_CHAINING OFF GO USE [SMS] GO /****** Object: User [smslogin] Script Date: 01/10/2010 10:14:49 ******/ CREATE USER [smslogin] FOR LOGIN [smslogin] WITH DEFAULT_SCHEMA=[dbo] GO /****** Object: Table [dbo].[Roles] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Roles]( [RoleID] [int] IDENTITY(1,1) NOT NULL, [Role] [nvarchar](50) NULL, CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ( [RoleID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO insert into Roles values ('Admins') Go insert into Roles values ('Users') /****** Object: Table [dbo].[test] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[test]( [id] [int] IDENTITY(1,1) NOT NULL, [test] [int] NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: UserDefinedFunction [dbo].[trueperc] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create FUNCTION [dbo].[trueperc] (@postid int) RETURNS decimal(18,3) AS -- Returns the stock level for the product. BEGIN DECLARE @ret decimal(18,3) declare @answ decimal(18,3) declare @allusers int select @answ = count(*) from answers where (postid = @postid) and (Answer = 1) select @allusers = count(*) from answers where (postid = @postid) --select @ret = (@answ/@allusers)*100 select @ret = ISNULL(@answ/NULLIF(@allusers,0),0) set @ret = @ret *100 return @ret END GO /****** Object: Table [dbo].[Posts] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Posts]( [PostID] [int] IDENTITY(1,1) NOT NULL, [Post] [nvarchar](1000) NULL, [Post_Date] [datetime] NULL, [Expiration_Date] [datetime] NULL, [UserID] [int] NULL, [Correct_Answ] [bit] NULL CONSTRAINT [DF_Posts_Correct_Answ] DEFAULT ((0)), CONSTRAINT [PK_Posts_1] PRIMARY KEY CLUSTERED ( [PostID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Answers] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Answers]( [Answ_ID] [int] IDENTITY(1,1) NOT NULL, [Answer] [bit] NULL CONSTRAINT [DF_Answers_Answer] DEFAULT ((0)), [PostID] [int] NULL, [UserID] [int] NULL, CONSTRAINT [PK_Answers_1] PRIMARY KEY CLUSTERED ( [Answ_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Extra_Admins] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Extra_Admins]( [Id] [int] IDENTITY(1,1) NOT NULL, [admin_Email] [nvarchar](200) NULL, [admin_Tel] [nvarchar](50) NULL, CONSTRAINT [PK_Extra_Admins] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[gateway_setting]( [ID] [int] IDENTITY(1,1) NOT NULL, [username] [nvarchar](50) NULL, [userpassword] [nvarchar](50) NULL, [api_id] [nvarchar](100) NULL, [baseurl] [nvarchar](200) NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Go /****** Object: UserDefinedFunction [dbo].[getfromdate] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create function [dbo].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50)) RETURNS datetime as begin declare @fromdate datetime set @fromdate = @yea + '-' + @mon +'-01' return @fromdate end GO /****** Object: UserDefinedFunction [dbo].[gettodate] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create function [dbo].[gettodate](@mon nvarchar(50) , @yea nvarchar(50)) RETURNS datetime as begin declare @todate datetime,@tempdate datetime set @tempdate = @yea + '-' + @mon +'-01' SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0)) return @todate end GO /****** Object: Table [dbo].[Users] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Users]( [UserID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NULL, [Email] [nvarchar](250) NULL, [Tel] [nvarchar](50) NULL, [Job_Title] [nvarchar](100) NULL, [Address] [nvarchar](500) NULL, [Username] [nvarchar](50) NULL, [UserPassword] [nvarchar](50) NULL, [Active] [bit] NULL, [RoleID] [int] NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO insert into Users values('administrator','amrehassan3333@yahoo.com','01285937364','developer','23 torant street','admin','admin',1,1) Go /****** Object: UserDefinedFunction [dbo].[perc] Script Date: 01/10/2010 10:14:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[perc] (@postid int) RETURNS decimal(18,3) AS -- Returns the stock level for the product. BEGIN DECLARE @ret decimal(18,3) declare @answ decimal(18,3) declare @allusers int select @answ = count(*) from answers where postid = @postid select @allusers = count(*) from users where Active = 1 --select @ret = (@answ/@allusers)*100 select @ret = ISNULL(@answ/NULLIF(@allusers,0),0) set @ret = @ret *100 return @ret END GO CREATE SCHEMA smslogin; GO create function [smslogin].[getfromdate](@mon nvarchar(50) , @yea nvarchar(50)) RETURNS datetime as begin declare @fromdate datetime set @fromdate = @yea + '-' + @mon +'-01' return @fromdate end Go create function [smslogin].[gettodate](@mon nvarchar(50) , @yea nvarchar(50)) RETURNS datetime as begin declare @todate datetime,@tempdate datetime set @tempdate = @yea + '-' + @mon +'-01' SELECT @todate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tempdate)+1,0)) return @todate end GO
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Proposed As Answer by Daniel_Steiner Monday, April 16, 2012 6:59 AM
- Marked As Answer by marooo Monday, April 16, 2012 8:02 AM
-
Monday, April 16, 2012 6:59 AM
I added a conditional DROP DATABASE and CREATE LOGIN as well as CREATE SCHEMA added. This script ran successfully on my system.
I thought I've run the complete script he provided initially both on SQL 2005 and 2008R2 and got no errors. But it seems that I only tested the first few lines (CREATE DATABASE etc.) as he'd already problems to the create the database itself.
Dan's updated script works on SQL Servers too.
I'd planned to test the latest of his script version today morning but Dan provided the fixed script already.- Edited by Daniel_Steiner Monday, April 16, 2012 7:01 AM
- Marked As Answer by marooo Monday, April 16, 2012 8:02 AM
-
Monday, April 16, 2012 8:03 AM
thank you my friends Daniel and Dan for helping me thank you guys u r great helpers
well
dan
i deleted the SMS database
and start execute Dan code
it gaveis me:
Msg 15023, Level 16, State 1, Line 2 User, group, or role 'smslogin' already exists in the current database.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
so..what do u think guys?
Marooo
- Edited by marooo Monday, April 16, 2012 8:10 AM
-
Monday, April 16, 2012 11:14 AM
i deleted the SMS database
and start execute Dan code
it gaveis me:
Msg 15023, Level 16, State 1, Line 2 User, group, or role 'smslogin' already exists in the current database.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
this message appears if the login already exists and the following script is executed another time.
CREATE USER [smslogin] FOR LOGIN [smslogin] WITH DEFAULT_SCHEMA=[dbo]
have you executed the complete script from Dan ? just copy & paste to a new query window and execute it again.
-
Monday, April 16, 2012 11:35 AM
Daniel
thank you for replaying me
i did execute the complete script from Dan
one time only and at the end of execute it gave me that :
Msg 15023, Level 16, State 1, Line 2 User, group, or role 'smslogin' already exists in the current database.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
well,i think the problem is in smslogin in my computer from earlier tests,
how can i delete the smslogin from all in my computer ? and then start to execute Dan script in a very clean start??
Marooo
-
Monday, April 16, 2012 11:43 AMModerator
well,i think the problem is in smslogin in my computer from earlier tests,
how can i delete the smslogin from all in my computer ? and then start to execute Dan script in a very clean start??
You can remove the login with:
DROP LOGIN [smslogin];
I'm not sure why you are getting the error though since the script creates the login conditionally. I ran the script successfully with and without the smslogin existing beforehand. Are you running the entire script?
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
-
Monday, April 16, 2012 12:05 PM
do you've other database hosted on this SQL Server instance ?
if the database had been dropped either manually or by the script any user 'smslogin' is dropped. group and role are stored in the database too and therefore they're removed as well.
-
Monday, April 16, 2012 3:12 PM
Finally my friends
it works it works
thank you Dan thank you Daniel many thanks guys
finally it gave me :
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
with no errors
every thing is ok now
go to the next step
i want to upload it to my windows hosting plan
how i should do that ?
Marooo
-
Monday, April 16, 2012 5:23 PM
Finally my friends
it works it works
go to the next stepi want to upload it to my windows hosting plan
how i should do that ?
Marooo
please start a new thread for that issue.
but one note, if you plan to publish this project as an ASP.net application you need a hosting product with ASP.NET and MS SQL Database support - usually a SQL Express which does use a slightly different connect string.
Please contact your hosting provide how you could upload, switch to SQL Server Express and publish your app.
