June 8, 2009 at 4:34 am
Hi All,
I've created three table with relationship.after completed successfully i get that script which is successfully created tables. then using this script in stored procedure. but doesnt work properly, its through syntax,invalid object name etc... error.
But that script is successfully created table script.
my need is
* how to use successfully created scripts in Stored procedure
* how to create database in SP using create database dbName
Regards
Rafeek Jeyaraj M
😛
June 8, 2009 at 9:20 pm
rafejeyaraj (6/8/2009)
but doesnt work properly, its through syntax,invalid object name etc... error.
Hi,
Your are created the temp table or the DB table, (why because the users who run the stored procedure doesn’t having the permission to create the DB table)
However post your coding.
ARUN SAS
June 8, 2009 at 10:48 pm
Hi Arun,
Thanks For Ur Reply. i have post my code with details. kindly do it need full
/* My Need is just create database then create table using already created table successfully script */
/* Procedure Name -- spAuthentication80
Database Name -- its input parameter whatever it is
table Name --tblProduct,tblOrder,tblCustomer */
CREATE PROCEDURE spAuthentication80
-- Add the parameters for the stored procedure here
@strDatabaseName varchar(100)
AS
declare @tempVariable varchar(100)
BEGIN
set @tempVariable='CREATE'+' '+ 'DATABASE'+' '+ @strDatabaseName
exec(@tempVariable)
END
GO
BEGIN
declare @tempUse varchar(20)
set @tempUse='USE' + '' + '@tempUse'
exec(@tempUse)
/****** Object: ForeignKey [FK__tblOrder__Custom__0AD2A005] Script Date: 06/05/2009 16:15:07 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__tblOrder__Custom__0AD2A005]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblOrder]'))
ALTER TABLE [dbo].[tblOrder] DROP CONSTRAINT [FK__tblOrder__Custom__0AD2A005]
GO
/****** Object: ForeignKey [FK__tblProduc__produ__0DAF0CB0] Script Date: 06/05/2009 16:15:07 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__tblProduc__produ__0DAF0CB0]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProduct]'))
ALTER TABLE [dbo].[tblProduct] DROP CONSTRAINT [FK__tblProduc__produ__0DAF0CB0]
GO
/****** Object: Table [dbo].[tblProduct] Script Date: 06/05/2009 16:15:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblProduct]') AND type in (N'U'))
DROP TABLE [dbo].[tblProduct]
GO
/****** Object: Table [dbo].[tblOrder] Script Date: 06/05/2009 16:15:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblOrder]') AND type in (N'U'))
DROP TABLE [dbo].[tblOrder]
GO
/****** Object: Table [dbo].[tblCustomer] Script Date: 06/05/2009 16:15:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblCustomer]') AND type in (N'U'))
DROP TABLE [dbo].[tblCustomer]
GO
/****** Object: Table [dbo].[tblCustomer] Script Date: 06/05/2009 16:15:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblCustomer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblCustomer](
[SID] [int] NOT NULL,
[Last_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[First_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__tblCustomer__07F6335A] PRIMARY KEY CLUSTERED
(
[SID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (1, N'Rafeek', N'Jayaraj')
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (2, N'jeyaraj', N'Rafeek')
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (3, N'Kamal', N'Kannan')
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (4, N'Sajith', N'Rahman')
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (5, N'Rahman', N'sajith')
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (6, N'Sachin ', N'tendulakar')
INSERT [dbo].[tblCustomer] ([SID], [Last_Name], [First_Name]) VALUES (7, N'Dhoni', N'Shewag')
/****** Object: Table [dbo].[tblOrder] Script Date: 06/05/2009 16:15:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblOrder]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblOrder](
[Order_ID] [int] NOT NULL,
[Order_Date] [datetime] NULL,
[Customer_SID] [int] NULL,
CONSTRAINT [PK__tblOrder__09DE7BCC] PRIMARY KEY CLUSTERED
(
[Order_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (1, CAST(0x000077BD00000000 AS DateTime), 2)
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (2, CAST(0x000077BD00000000 AS DateTime), 2)
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (3, CAST(0x000077BD00000000 AS DateTime), 1)
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (4, CAST(0x000077BD00000000 AS DateTime), 1)
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (5, CAST(0x000077BD00000000 AS DateTime), 2)
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (6, CAST(0x000077BD00000000 AS DateTime), 3)
INSERT [dbo].[tblOrder] ([Order_ID], [Order_Date], [Customer_SID]) VALUES (7, CAST(0x000077BD00000000 AS DateTime), 5)
/****** Object: Table [dbo].[tblProduct] Script Date: 06/05/2009 16:15:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblProduct]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblProduct](
[Product_No] [int] NOT NULL,
[product_SID] [int] NULL,
[Product_Name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__tblProduct__0CBAE877] PRIMARY KEY CLUSTERED
(
[Product_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[tblProduct] ([Product_No], [product_SID], [Product_Name]) VALUES (1, 2, N'Soap')
INSERT [dbo].[tblProduct] ([Product_No], [product_SID], [Product_Name]) VALUES (2, 2, N'Bike')
INSERT [dbo].[tblProduct] ([Product_No], [product_SID], [Product_Name]) VALUES (3, 1, N'Car')
INSERT [dbo].[tblProduct] ([Product_No], [product_SID], [Product_Name]) VALUES (4, 3, N'Flight')
INSERT [dbo].[tblProduct] ([Product_No], [product_SID], [Product_Name]) VALUES (5, 3, N'Fridge')
INSERT [dbo].[tblProduct] ([Product_No], [product_SID], [Product_Name]) VALUES (6, 1, N'cot')
/****** Object: ForeignKey [FK__tblOrder__Custom__0AD2A005] Script Date: 06/05/2009 16:15:07 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__tblOrder__Custom__0AD2A005]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblOrder]'))
ALTER TABLE [dbo].[tblOrder] WITH CHECK ADD CONSTRAINT [FK__tblOrder__Custom__0AD2A005] FOREIGN KEY([Customer_SID])
REFERENCES [dbo].[tblCustomer] ([SID])
GO
ALTER TABLE [dbo].[tblOrder] CHECK CONSTRAINT [FK__tblOrder__Custom__0AD2A005]
GO
/****** Object: ForeignKey [FK__tblProduc__produ__0DAF0CB0] Script Date: 06/05/2009 16:15:07 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__tblProduc__produ__0DAF0CB0]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProduct]'))
ALTER TABLE [dbo].[tblProduct] WITH CHECK ADD CONSTRAINT [FK__tblProduc__produ__0DAF0CB0] FOREIGN KEY([product_SID])
REFERENCES [dbo].[tblCustomer] ([SID])
GO
ALTER TABLE [dbo].[tblProduct] CHECK CONSTRAINT [FK__tblProduc__produ__0DAF0CB0]
GO
END
GO
----------------------------
Hope send to me positive reponse
Regards
Rafeek Jeyaraj M
June 8, 2009 at 11:34 pm
rafejeyaraj (6/8/2009)
BEGINdeclare @tempUse varchar(20)
set @tempUse='USE' + '' + '@tempUse'
exec(@tempUse)
Hi,
You need to create the DB Tables in the New DB (in the Sp) or in the Current DB (Running DB)
BEGIN
declare @tempUse varchar(20)
set @tempUse='USE' + '' + ‘/* SET THE DB NAME HERE AND TRY*/’
exec(@tempUse)
and where you mention the DB name to execute for the Create table.
ARUN SAS
June 8, 2009 at 11:44 pm
Hi Arun,
Again thanks for take care of my post. that's ok but after created database successfully again through exception like below
Msg 208, Level 16, State 1, Line 3
Invalid object name 'sys.foreign_keys'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.foreign_keys'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.objects'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.objects'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.objects'.
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '('.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tblCustomer'.
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '('.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tblOrder'.
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '('.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tblProduct'.
Msg 4902, Level 16, State 1, Line 1
Cannot alter table 'dbo.tblOrder' because this table does not exist in database 'master'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.foreign_keys'.
Msg 4902, Level 16, State 1, Line 1
Cannot alter table 'dbo.tblProduct' because this table does not exist in database 'master'.
June 8, 2009 at 11:54 pm
Hi,
Bit question?
You’re working with 2000 or 2005?
ARUN SAS
June 9, 2009 at 12:09 am
Hi Arun,
i am using Sql Server 2005.
June 9, 2009 at 12:35 am
Hi,
Compile the attached sp, and then post the error.
ARUN SAS
June 9, 2009 at 12:43 am
Msg 156, Level 15, State 1, Procedure spAuthentication80, Line 6
Incorrect syntax near the keyword 'null'.
Msg 170, Level 15, State 1, Procedure spAuthentication80, Line 10
Line 10: Incorrect syntax near 'END'.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'USE@tempUse'.
Msg 208, Level 16, State 1, Line 6
Invalid object name 'sys.foreign_keys'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.foreign_keys'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'END'.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'END'.
June 9, 2009 at 12:59 am
rafejeyaraj (6/9/2009)
Msg 156, Level 15, State 1, Procedure spAuthentication80, Line 6Incorrect syntax near the keyword 'null'.
Msg 170, Level 15, State 1, Procedure spAuthentication80, Line 10
Line 10: Incorrect syntax near 'END'.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'USE@tempUse'.
HI,
Try to alter,
am just post this SP to get the real error line
Put the if @tempVariable IS not null insterd of if @tempVariable not null
and set @tempUse = 'DB'--Your db name
set @tempUse='USE' + char(13) + @tempUse
ARUN SAS
June 9, 2009 at 1:11 am
Hi Arun,
Thanks ur reply
Still in Same problem.
June 9, 2009 at 1:25 am
rafejeyaraj (6/9/2009)
Still in Same problem.
Hi,
Then undependably check this all create table statement works with out error
Like
CREATE TABLE [dbo].[tblOrder](
[Order_ID] [int] NOT NULL,
[Order_Date] [datetime] NULL,
[Customer_SID] [int] NULL,
CONSTRAINT [PK__tblOrder__09DE7BCC] PRIMARY KEY CLUSTERED
(
[Order_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ARUN SAS
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply