Problem
You get error «201: A syntax error has occurred.» when trying to create a table.
Symptom
c1 int,
primary int); 201: A syntax error has occurred.
Error in line 1
Near character position 34
Cause
This could be because you are using the following reserved words as a column name:
- CHECK
- DISTINCT
- FOREIGN
- PRIMARY
- UNIQUE
Resolving The Problem
Although you can use these reserved words as an identifier, syntactic ambiguities can result from using keywords as identifiers in SQL statements. The statement might fail or might not produce the expected results. However, if you must use these reserved words as column names, you can use the following workaround:
Create the table without the column that has the keyword name, then ALTER TABLE to ADD the column with the reserved word. For example:
create table t1 (c1 int);
ALTER TABLE t1 ADD primary int;
Related Information
[{«Product»:{«code»:»SSGU8G»,»label»:»Informix Servers»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF016″,»label»:»Linux»},{«code»:»PF022″,»label»:»OS X»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.0;11.1;11.5;11.7″,»Edition»:»»,»Line of Business»:{«code»:»»,»label»:»»}}]
Home > SQL Server Error Messages > Msg 201 — Procedure or function ‘<Procedure Name>’ expects parameter ‘<Parameter Name>’, which was not supplied. |
||
SQL Server Error Messages — Msg 201 — Procedure or function ‘<Procedure Name>’ expects parameter ‘<Parameter Name>’, which was not supplied. |
||
To illustrate, the following call to the sp_changedbowner system stored procedure will generate the error if no parameter is passed: EXECUTE [dbo].[sp_changedbowner] Msg 201, Level 16, State 4, Procedure sp_changedbowner, Line 0 Procedure or function 'sp_changedbowner' expects parameter '@loginame', which was not supplied. The sp_changedbowner system stored procedure changes the owner of the current database. The syntax of sp_changedbowner is as follows: EXECUTE [dbo].[sp_changedbowner] [ @loginname = ] 'login' [, [ @map = ] remap_alias_flag ] The @loginame parameter is the login ID of the new owner of the current database and is required when calling this system stored procedure. The optional @map parameter, which accepts a value of true or false, indicates whether existing aliases assigned to the old database owner (dbo) are mapped to the new owner of the current database or dropped. Another way of getting this error using the same sp_changedbowner system stored procedure is using the following script: EXECUTE [dbo].[sp_changedbowner] @loginname = 'sql2008' Msg 201, Level 16, State 4, Procedure sp_changedbowner, Line 0 Procedure or function 'sp_changedbowner' expects parameter '@loginame', which was not supplied. Although the login ID is passed to the stored procedure, the name of the parameter used when executing the stored procedure in this particular script was “@loginname” (with 2 n’s) instead of “@loginame” (with just 1 n). Solution / Work Around: The simplest way to avoid this error is to make sure that all parameters expected by a stored procedure or function that don’t have any default values are assigned a value and that the name of the parameter, if specified, matches the name of the parameter as defined in the stored procedure or function. To know what parameters are expected by a stored procedure or function, the sp_help system stored procedure can be used, passing the name of the stored procedure or function as a parameter. The sp_help system stored procedure reports information about a database object, a user-defined data type or a data type. EXECUTE [dbo].[sp_help] 'sp_changedbowner' Name Owner Type Created_datetime ------------------ ------- ------------------ ------------------ sp_changedbowner sys stored procedure 2011-02-25 17:05:47.043 Parameter_name Type Length Prec Scale Param_order Collation --------------- -------- ------- ----- ------ ------------ ---------- @loginame sysname 256 128 NULL 1 SQL_Latin1_General_CP1_CI_AS @map varchar 5 5 NULL 2 SQL_Latin1_General_CP1_CI_AS In the example shown above of executing the sp_changedbowner system stored procedure, by specifying the required parameters with the correct parameter name, this error message will be avoided. EXECUTE [dbo].[sp_changedbowner] @loginame = 'sql2008' There are 2 ways of passing parameters to a stored procedure. The first method is using the “@parametername=value” convention wherein the name of the parameter is passed together with the value. Here’s a sample using the sp_changeobjectowner system stored procedure: EXECUTE [dbo].[sp_changeobjectowner] @objname = 'Customer', @newowner = 'sql2008' The second method is by simply passing the parameter value without the parameter name. Using the same sp_changeobjectowner, here’s how the call to the sp_changeobjectowner will look like using this method: EXECUTE [dbo].[sp_changeobjectowner] 'Customer', 'sql2008' Using the second method, you must pass the parameter values in the same order as the parameters as specified in the stored procedure. In the first method, the order of the parameters need not match the order of the parameters list of the stored procedure. The preferred method between these two is the first method where the parameter value is passed together with the parameter name. Using the “@parametername = value” convention, even if new parameters with default values are added in a stored procedure somewhere in the parameter list not necessarily at the end, the call to the stored procedure will still work and generate the expected result. Also, the list of parameters in the stored procedure definition can also be rearranged without affecting any calls to it. |
||
Related Articles : | ||
|
Job name : Expired subscription clean up
What does this job do ?
sp_expired_subscription_cleanup is executed by the Expired Subscription Clean Up job to detect and remove expired subscriptions from publication databases every 24 hours. If any of the subscriptions are out-of-date, that is, have not synchronized with the Publisher within the retention period, the publication is declared expired and the traces of the subscription are cleaned up at the Publisher.
SP name - EXEC sys.sp_expired_subscription_cleanup
Error Message :
Executed as user: WINNTDOMsqlserveragent. Procedure or function ‘sp_MSsubscription_cleanup’ expects parameter ‘@cutoff_time’, which was not supplied. [SQLSTATE 42000] (Error 201). The step failed.
Reason of failure :
This is because of Bug reported from MSSQL 2012 SP3 CU 9 onwards and hasn’t been even fixed in MSSQL 2012 SP4 as well ( latest release ) .
Is Microsoft planning to fix it ?
Microsoft doesn’t plan to fix it for MSSQL 2012 version as they normally releases new hotfix only for 5 years since the product has been launched. As this is MSSQL 2012 version , they wont release further hotfixes after 2017
Solution is to upgrade the SQL version to next version but only if it is needed
Microsoft Connect Link
Published by Abhishek Dwivedi
Hi There,
I am Abhishek Dwivedi , a Microsoft certified MSSQL/Azure professional . I work as a Database SME for an Asset management firm in Singapore. I started working in SQL Server with SQL Server 7.0 and had been working on all versions from then. I also have knowledge on basic Oracle database administration and my latest passion is working on cloud based technologies . As part of keeping myself up to date with SQL Server technology and other related technologies, I keep reading many books, articles, blogs, white papers, case studies and user forums only to get amazed by the vast amount of knowledge we have at our disposal. I feel privileged to give back to the SQL Server Community by writing blogs on database related topics, that I come across in my day to day work.
To connect you can also reach me on my linkedIn profile -> www.linkedin.com/in/abhishek-dwivedi-178b1050
View all posts by Abhishek Dwivedi
Published
December 15, 2017March 12, 2018