Scott Chang


Hi all,

In Object Explorer of my ComputerName\SQLEXPRESS, I have a Database "shcDB" with Table "dbo.MyFriends" which has fields "PersonID" (Int), "FirstName", "LastName", "StreetAddress", "City", "State", "ZipCode", "E-MailAddress" set up. When I executed the following query:

/////----shcSP-1.sql---////

USE shcDB

GO

EXEC sp_insertRecord 3, "Mary", "Smith", "789 New St.", "Chicago", "Illinos", "12345", "M_Smith@Yahoo.com"

GO

/////////////////////////////////////////////////////////////

I got the following error message:

Msg 8144, Level 16, State 2, Procure sp_insertRecord, Line 0

Procedure or Function sp_insertRecord has too many arguments specified.

(1) Why did I get this error

(2) Where can I get the documents for explaining the procedures like 'sp_insertRecord', 'sp_delecteRocord', 'sp_getRecord', 'sp_updateRecord', etc. and error messages

Please help and give me the answers for the above-mentioned questions.

Thanks in advance,

Scott Chang

P. S.

I used my PC at home to post the above message. I have not been able to post it by using my office PC in our LAN system. In my office LAN system, I have been struggling to figure out and solve the following strange thing: I log in http://forums.microsoft.com and "Sign Out" is on all the time. If I click on "Sign Out", I get "Logout: You have been successfully logged out of the the forums." page all the time. What is the cause to log me out How can I get it resolved Our Computer Administrator Or Microsoft What Department in Microsoft can help me to solve this problem Please advise on this strange thing. Thanks.





Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Arnie Rowland


In order to give you any 'real' help, we need the code for the stored procedures.

Try the following:

EXECUTE sp_helptext sp_insertRecord

And then post the returned stored procedure code. Then we may be able to help you.

My suspicsion is that you do NOT need to provide the PersonID value for an INSERT.

But I cannot be certain without seeing the code.







Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Madhu K Nair

(1) Why did I get this error : Coz , the input parameter you pass to this sp is more than its actually it requires. So check the number of input parameter it requires and pass accordingly.

EXEC sp_insertRecord 3, "Mary", "Smith", "789 New St.", "Chicago", "Illinos", "12345", "M_Smith@Yahoo.com"

(2) Where can I get the documents for explaining the procedures : Arnie has already mentioned SP_HelpText system SP will give you the source code of any procedure (provided its not encrypted.)

Madhu







Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Scott Chang

Hi Arnie and Madhu, Thanks for your responses.

I thought the procedures 'sp_insertRecord', 'sp_delecteRecord', 'sp_getRecord', 'sp_updateRecord', etc. were the Microsoft standard stored procedures used in the SQL Server Management Studio. Last year, I bought a book "SQL Server 2005 Express Starter Kit" and learned the sp_insertRecord from that book - it worked once before. I went on to learn the VB 2005 Express, ADO.NET 2.0 and SQL Server 2005 Programmings. Now I came back to refresh the stored procedures 'sp_insertRecord', 'sp_delecteRecord', 'sp_getRecord', 'sp_updateRecord', etc. and tried to use them in my ADO.NET 2.0 programming via the regular T-SQL or SQLCLR. Now I am greatly confused about 'sp_insertRecord', 'sp_delecteRecord', 'sp_getRecord', 'sp_updateRecord', etc. Are they the Microsoft standard stored procedures in the SQL Server Management Studio I searched "SP_HELPText" in the Google website and read some articles about "SP_HELPTEXT" - I did not see anything there can help me understand "SP_HELPText" and the stored procedures 'sp_insertRecord', 'sp_delecteRecord', 'sp_getRecord', 'sp_updateRecord', etc. I am completely lost now. Please help again and tell me who developed the stored procedures 'sp_insertRecord', 'sp_delecteRecord', 'sp_getRecord', 'sp_updateRecord', etc. and how to use them.

Thanks,

Scott Chang






Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Arnie Rowland

Those are NOT standard procedures. I suspect they were included with a sample or demonstration database that you obtained with some from of learning or training.

Again, they are totally 'unknown' to us, and we cannot with certainty tell you what they are for, and how they are used. You may find that information in the learning materials that you have been using. Check the source of the sample database that you are using.






Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Scott Chang

Hi Arnie, Thanks for your response.

I got the right ideas to do this procedures, after I read your response and went back to review my old stored procedure stuff - I should create the procedure first and then execute. Here is my revised shcSP_1:

USE shcDB

GO

CREATE PROC sp_insertRecord @procPersonID int, @procFirstName nvarchar(9), @procLastName nvarchar(9), @procAddress nvarchar(15), @procCity nvarchar(10), @procState nvarchar(10), @procZipCode nvarchar(10), @procEmail nvarchar(25) AS INSERT INTO MyFriends VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress, @procCity, @procCity, @procState, @procZipCode, @procEmail)

GO

EXEC sp_insertRecord 3, 'Mary', 'Smith', '789 New St', 'Chicago', 'Illinos', '12345', 'M_Smith@Yahoo.com'

GO

//////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following errors:

Msg 213, Level 16, State 1, Procedure sp_insertRecord, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

Msg 8144, Level 16, State 2, Procedure sp_insertRecord, Line 0

Procedure or function sp_insertRecord has too many arguments specified.

***************************************************************************************

I created the 'dbo.MyFriends' last year and I can not remember what I defined the table. And I do not know how to reveal the "table definition"

of my 'dbo.MyFriends. Could you please tell me how to reveal the "table definition" of my 'dbo.MyFriends in SQL Server 2005 Management Studio Express

Thanks again,

Scott Chang






Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Madhu K Nair

INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName,

@procAddress, @procCity, @procCity, @procState, @procZipCode, @procEmail)

To get the columns of MYFriends Table

sp_help Myfriends

or

Select Name from sys.Columns where object_id=Object_id('MyFriends')

you will get all the columns names. In the Insert statement mention the curresponding column name explictly.

INSERT INTO MyFriends (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9)

VALUES (@procPersonID, @procFirstName, @procLastName,

@procAddress, @procCity, @procCity, @procState, @procZipCode, @procEmail)

Madhu






Re: EXEC sp_insertRecord has too many arguments specified: Msg 8144, Level 16, State 2-Why I got this error?

Scott Chang

Hi Madhu, Thanks for your responses and instructions.

I used sp_help MyFriends to get the table definition for correcting the Length,etc. of my code statement.

I ran the following newly revised shcSP-1.sql:

USE shcDB

GO

CREATE PROC sp_insertRecord @procPersonID int, @procFirstName nvarchar(20), @procLastName nvarchar(20), @procAddress nvarchar(50), @procCity nvarchar(20), @procState nvarchar(20), @procZipCode nvarchar(50), @procE-mail nvarchar(50)

AS INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress, @procCity, @procCity, @procState, @procZipCode, @procEmail)

GO

EXEC sp_insertRecord 3, 'Mary', 'Smith', '789 New St', 'Chicago', 'Illinos', '12345', 'M_Smith@Yahoo.com'

GO

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I got the following new errors:

Msg 102, Level 15, State 1, Procedure sp_insertRecord, Line 1

Incorrect syntax near '-'.

Msg 137, Level 15, State 2, Procedure sp_insertRecord, Line 3

Must declare the scalar variable "@procEmail".

Msg 8144, Level 16, State 2, Procedure sp_insertRecord, Line 0

Procedure or function sp_insertRecord has too many arguments specified.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

(1) Msg 102: I had the last columnn "E-Mail" in my old dbo.MyFriehd table. It seems the "-" in the E-mail caused trouble for me.

(2) Msg 137: I do not understand why it showed "@procEmail" in the error message.

Please help and tell me how to solve these 2 problems.

Many thanks,

Scott Chang