Error 166 Sql
Contents |
SERVER - Explain Error:166 : does not allow specifying the database name as a prefix to the object name - Puzzle to Win SQL Server Interview Questions and Answers Book create/alter view' does not allow specifying the database name December 14, 2011Pinal DaveSQL, SQL Interview Questions and Answers, SQL Server,
'create View' Must Be The First Statement In A Query Batch.
SQL Tips and Tricks19 commentsI was recently reading excellent Just Learned Tip regarding 3 part naming 'create/alter procedure' must be the first statement in a query batch. Cannot be used when dropping Views,Functions or Procedures. This is quite a well known tip however, every developer and DBA learns at sometime in their career with ‘hm…' moment. To illustrate this further here is a simple case scenario.Setup environment CREATE DATABASE TestDB GO USE TestDB GO CREATE TABLE TestTable (ID INT) GO CREATE PROCEDURE TestSP AS SELECT 1 Col GO
Drop TableThe drop table will works and gives success message. DROP TABLE TestDB.dbo.TestTable GO Drop ProcedureThe drop procedure will give following error. DROP PROCEDURE TestDB.dbo.TestSP GO Msg 166, Level 15, State 1, Line 1 ‘DROP PROCEDURE' does not allow specifying the database name as a prefix to the object name.WorkaroundYou can drop the procedure using following commands. USE TestDB GO DROP PROCEDURE dbo.TestSP GO PuzzleWhy does 3 part name works with dropping the table and not with dropping procedure?Give AwayTwo winners gets SQL Server Interview Questions and Answers book [Amazon|Flipkart|Kindle]. Leave your answer in the comment area. Winner will be announced before holiday season!Reference: Pinal Dave (http://blog.SQLAuthority.com) Tags: SQL Scripts534Related Articles SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision June 1, 2010Pinal Dave SQL SERVER - Size of Index Table for Each Index - Solution 2 May 9, 2010Pinal Dave SQL SERVER - 2005 - Find Database Collation Using T-SQL and SSMS August 30, 2007Pinal Dave 19 comments. Leave new Mohit Sharma December 14, 2011 10:11 amHi Pinal,Because DROP TABLE provides three naming convention as per below syntaxDROP TABLE
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server 2008: DROP PROCEDURE error up vote 2 down vote favorite I http://blog.sqlauthority.com/2011/12/14/sql-server-explain-error166-does-not-allow-specifying-the-database-name-as-a-prefix-to-the-object-name-puzzle-to-win-sql-server-interview-questions-and-answers-book/ try to execute this command: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS]') AND type in (N'P', N'PC')) DROP PROCEDURE dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS] GO But I'm getting this error: Msg 166, Level 15, State 1, Line 1 'DROP PROCEDURE' does not allow specifying the database name as a prefix to the object name What I'm trying to do is to delete all objects in a database, so that the database http://stackoverflow.com/questions/28761801/sql-server-2008-drop-procedure-error is empty. I'm using Microsoft SQL Server 2014 Management Studio. sql sql-server-2008 table ssms-2014 share|improve this question edited Jul 5 '15 at 13:07 Brian Tompsett - 汤莱恩 3,093122775 asked Feb 27 '15 at 9:39 Hakan Kiyar 3211516 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted As the error clearly says - you cannot use the database name as a prefix in a DROP PROCEDURE. You need to make sure you're connected to the correct database and then you need to execute IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS]') AND type in (N'P', N'PC')) DROP PROCEDURE [PROC_CHECK_TRACE_SETTINGS] GO share|improve this answer answered Feb 27 '15 at 9:43 marc_s 452k938641029 You actually can use the database, but he put it in wrong order. The correct order is: 1. database, 2. schema, 3. procedure name. –George Jul 5 '15 at 13:31 add a comment| up vote 0 down vote You need to change the order of database name and schema. This is wrong: DROP PROCEDURE dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS] This is correct: DROP PROCEDURE njams_test_sql_2.dbo.[PROC_CHECK_TRACE_SETTINGS] Or, you can just leave them away. Then the current database and the default schema (most likely dbo) are used: DROP PROCEDURE [PROC_CHECK_TRACE_SETTINGS] share|improve this ans
the error, the probable cause, and the recommended action. Each error code corresponds to an exception class. See "Runtime and Development Exceptions" for more information. Format: A description shown in the actual exception thrown. Cause: https://docs.oracle.com/cd/A97688_16/toplink.903/b10068/errorcod.htm The most probable cause for the error. Action: Suggestions for resolving the error. Descriptor Exceptions (1 - 176) Error code: 1 ATTRIBUTE_AND_MAPPING_WITH_INDIRECTION_ MISMATCH Cause:
truncated 01005 Encryption has not been enabled 01006 Passthrough SQL script failed 01F01 Position %1 is invalid (should be between %2 and %3) 01FW1 Polygon ring order has been reoriented 01FW2 CircularString with 3 points has been converted to use 5 points 01S02 Cursor option values changed 01W01 Invalid data conversion: NULL was inserted for column '%1' on row %2 01W02 Using temporary table 01W03 Invalid data conversion 01W04 Row has been updated since last time read 01W05 Procedure has completed 01W06 Value for column '%1' in table '%2' has changed 01W07 Language extension detected in syntax near '%1' on line %2 01W08 Statement cannot be executed 01W09 More information required 01W10 Transaction log backup page only partially full 01W11 Database option '%1' for user '%2' has an invalid setting 01W12 Character set conversion to '%1' cannot be performed; '%2' is used instead 01W13 Database server cannot convert data from/to character set '%1', conversion may not be correct 01W14 Language '%1' is not supported, '%2' will be used 01W15 Unsupported character set '%1' and unsupported language '%2'; language used is '%3' instead 01W16 Illegal user selectivity estimate specified 01W17 '%1' is an unknown option 01W18 The result returned is non-deterministic 01W20 Plan may contain virtual indexes 01W21 A row was dropped because it could not be converted to the new schema format 01W23 Cannot output the histogram for string column '%1' 01W24 Row was dropped from table %1 to maintain referential integrity 01W25 Publication predicates were not evaluated 01W26 Option '%1' specified more than once 01W27 Two rows with the same primary key have been downloaded for table '%1' 01W28 Database contains no tables to synchronize 01WC1 An invalid multi-byte input character was encountered when converting from %1 to %2 01WC3 Character substitution occurred when converting from %1 to %2 01WC4 The ICU collation '%1' has defaulted to another collation 02000 Row not found 04W07 A dbspace has reached its maximum file size 04W08 The server attempted to access a page beyond the end of the maximum allowable dbspace file size 07001 Not enough values for host variables 07002 Not enough fields allocated in SQLDA 07003 Error opening cursor 07005 Can only describe a SELECT statement 07009 Invalid descriptor index 07W01 Invalid type on DESCRIBE statement 07W02 Invalid statement 07W03 Invalid prepared statement type 07W04 SQLDA fields inconsistent for a multi-row SQLDA 07W05 SQLDA data type invalid 07W06 Invalid SQL identifier 07W07 Host variables must not be used within a batch 07W08 Invalid sqllen field in SQLDA 0800