Problem in Drop Table in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Problem in Drop Table in SQL

Author

Topic

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-20 : 05:43:22

Hi,

I am trying to drop table in below code in dynamic way, but facing error.

Looking for your support.

/**********************************RESET************************/

if @TypeOfUpdate='Reset'

Begin
Drop table [' + @DestinationTable + ']
exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @TABLENAME + ''', ' + ''' select * from '+@SourceSheet + ''')')

PRINT 'Congratulations!!!! Database is Reset.';

END

-----------------------------------------------

above is part of SP code where I am getting an error if i run the below code.

EXEC [ImportExcelFile_Reset] 'C:\temp\TestFiles\OPENASNs.xlsx','Sheet1', 'test', 'Reset'

------------Error-------

(1 row(s) affected)
Msg 3701, Level 11, State 5, Procedure ImportExcelFile_Reset, Line 53
Cannot drop the table '' + @DestinationTable + '', because it does not exist or you do not have permission.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test' in the database.
--------------------------

Looking for your support.

thanks,

SQL_Support_2015

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2015-01-20 : 07:50:27

set @query = 'Drop table ' + '[' + @DestinationTable + ']'
EXEC (@QUERY)

Hema Sunder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources