Is it problem with my permissions on SQL Server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Is it problem with my permissions on SQL Server

Hi, I have a problem. I want to create a stored procedure which should
check
whether that table already exists or not and then create the table. I
wrote
the stored procedure and executed, but its not creating table and when
I go
back to the stored procedure its just showing this CREATE PROCEDURE CreateTmpTable AS if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TmpCities]’) and OBJECTPROPERTY(id,
N’IsUserTable’) = 1)
drop table [dbo].[TmpCities1]
GO As I wrote this CREATE PROCEDURE CreateTmpTable AS if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TmpCities]’) and OBJECTPROPERTY(id,
N’IsUserTable’) = 1)
drop table [dbo].[TmpCities1]
GO CREATE TABLE [dbo].[Cities] (
[CityCode] [int] IDENTITY (1, 1) NOT NULL ,
[PlaceDescription] [varchar] (50) COLLATE Arabic_CI_AS_KS_WS NULL ,
[District] [int] NULL ,
[State] [int] NULL ,
[Country] [int] NULL
) ON [PRIMARY]
GO

Monitor the event with profiler/ Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Drop the first GO and try again. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
you cant use GO inside a stored procedure, since its a batch delimiter. Wrap each batch in its own stored proc (making sure you return error information where necessary!), and then have a wrapper stored proc which calls the individual sp’s in sequence
The above code works fine with me. Did you try capturing information through profiler as Satya suggested? Thanks
-Rajeev
Check the above for the problem with the first GO. But you’re checking for a table called TmpCities, and if it exists you drop a table named TmpCities1, so perhaps the procedure fails because of the incorrect name in the DROP TABLE statement? Also, any specific reason why you’re not using actual temporary tables, but user tables named tmp…?
]]>