|
You have to install the client side components of SQL on the client box, and enable one of the network transports on both the server and client boxes. Your connection string would then need to point to the server box. If you expected to use SQL as a LOCAL database server (same box, no network connection to a central server box running SQL) then you do need to install it on that box
|
|
|
|
|
Hi,
I have been trying to get rid of the following error message since last 4 days and there is no progress yet. Here is the error message, code and related table structure. Please help me to solve the problem. I use SQL SERVER EXPRESS 2008 with Visual Studio 2008. The strored procedures are not functioning if they called from inside a web page  but when I use ADO.NET to initiate a stored procedure, it is running. I perfomed extensive search in google but I couldn't find an accurate solution to problem.
ERROR MESSAGE
<br />
<br />
Server Error in '/' Application.<br />
--------------------------------------------------------------------------------<br />
<br />
Procedure or function StoredProcedure1 has too many arguments specified. <br />
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. <br />
<br />
Exception Details: System.Data.SqlClient.SqlException: Procedure or function StoredProcedure1 has too many arguments specified.<br />
<br />
Source Error: <br />
<br />
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. <br />
<br />
Stack Trace: <br />
<br />
<br />
[SqlException (0x80131904): Procedure or function StoredProcedure1 has too many arguments specified.]<br />
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826<br />
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747<br />
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194<br />
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392<br />
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204<br />
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954<br />
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162<br />
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175<br />
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137<br />
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386<br />
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +227<br />
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +86<br />
System.Web.UI.WebControls.ListView.HandleInsert(ListViewItem item, Boolean causesValidation) +277<br />
System.Web.UI.WebControls.ListView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +652<br />
System.Web.UI.WebControls.ListView.OnBubbleEvent(Object source, EventArgs e) +233<br />
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37<br />
System.Web.UI.WebControls.ListViewItem.OnBubbleEvent(Object source, EventArgs e) +112<br />
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37<br />
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118<br />
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166<br />
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10<br />
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13<br />
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36<br />
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565<br />
<br />
<br />
<br />
<br />
--------------------------------------------------------------------------------<br />
Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053 <br />
CODE IN WEB PAGE
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HolidayDBConnectionString %>" <br />
InsertCommand='StoredProcedure1' <br />
<br />
<br />
SelectCommand="SELECT DISTINCT [ActivityName] FROM [Activities] ORDER BY [ActivityName]" <br />
InsertCommandType="StoredProcedure"><br />
<InsertParameters><br />
<asp:Parameter Name="GetActivityName" Type="String" Size="50"/><br />
</InsertParameters><br />
</asp:SqlDataSource><br />
<br />
STORED PROCEDURE
<br />
ALTER PROCEDURE dbo.StoredProcedure1<br />
<br />
(<br />
@GetActivityName nvarchar(50)<br />
)<br />
<br />
AS<br />
INSERT INTO [HolidayDB].[dbo].[Activities]<br />
([ActivityName])<br />
VALUES<br />
(@GetActivityName)<br />
<br />
RETURN<br />
<br />
TABLE STRUCTURE
<br />
PK ActivityNumber Bigint (IDENTITY) ALLOW NULLS FALSE<br />
ActivityName nvarchar(50) ALLOW NULLS FALSE<br />
<br />
<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
The parameter name for the stored procedure is '@GetActivityName' te '@' is missing in the web page specification...
|
|
|
|
|
Thanks for responding. I tried it with @ sign but it didn't work out too. I think something is wrong with SQL SERVER EXPRESS 2008 (may be related to a policy of something).
I am not an expert about SQL SERVER. If query is working fine but storedprocedures are not called, it means to me that the problem is related to SQL SERVER. I enabled CLR and it didn't work out too.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
Since im not sure if im an expert or not, im not sure whether to answer. You may prefer to not limit yourself in future. you never know who might give you the right answer.
Just in case it helps, there appears to be nothing wrong with the extracts of code that you've posted. You certainly seem to have defined the SqlDataSource OK. Perhaps its something the control that is bound to this datasource is doing "under the hood" as it were. eg/ if you've bound to a DetailsView and you have a bunch of other fields bound to the datasource, it will try to turn them into parameters too.
|
|
|
|
|
J4amieC wrote: you never know who might give you the right answer.
You got my five.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
In Management studio, modify the table definition and then select from the menu Table Designer / Generate Change Script... This gives you details what Management Studio is trying to do and possibly why.
I tested the same modification and it just added the column by altering the table, as expected.
The column to be added cannot be NOT NULL if default is not present at the same time so make sure that you define both when designing the table in Management Studio.
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Hmm, must be me then...
I have testet this on two different database on the same server, with the same result.
I should say that the server is running SQL 2005 and as the headline indicates im using SQL Manager 2008
I know that the column being added may not be NOT NULL if the default value is not present, so thats not an ussue here.
well i guess i must look further, for this one...
but thanks for the reply Mika
With great code, comes great complexity, so keep it simple stupid... 
|
|
|
|
|
No problem...
I made a simple test with Management Studio 2008 against SQL Server 2005 Express Edition and still ok. Here's what was generated:
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Test ADD
ToBeAdded nchar(10) NOT NULL CONSTRAINT DF_Test_ToBeAdded DEFAULT N'Some kind of value...'
GO
COMMIT
However I do have SP 2 on SQL 2005. Perhaps that would be the problem?
Mika
The need to optimize rises from a bad design
|
|
|
|
|
|
What I did in Management Studio was the following:
- selected the table from Object Explorer
- from Context menu for the selected table: "Design"
- added the necessary field and a default value for the field, but I didn't save
- then from the menu bar I selected "Table Designer" and then "Generate Change Script..."
- the code block in the previous post was copied from the dialog that opened
[Addition]
The build number for me is different: 9.00.3068.00 so I take it there are some differences in the engine.
[/Addition]
Hope this helps,
Mika
The need to optimize rises from a bad design
modified on Wednesday, September 24, 2008 3:45 PM
|
|
|
|
|
Ahh ok im with you, i found it... But now you are going to laugh, when i select the "Generate Change Script" the absolute same error box pops up.
heres the full error code:
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.
You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
With great code, comes great complexity, so keep it simple stupid...
|
|
|
|
|
|
Actually I cannot laugh because it would wake up the children... 
But seriously, have a look at this Save (Not Permitted) Dialog Box[^]
Could the first bullet be the reason?
Also did you notice my modification about the build number in the previous post
The need to optimize rises from a bad design
|
|
|
|
|
hehe we found the same site... funny... thanks..
To work around this error do the following:
To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers.
Select or clear the Prevent saving changes that require the table to be re-created check box.
With great code, comes great complexity, so keep it simple stupid...
|
|
|
|
|
You're welcome
Mika
P.s. Just a tip: Downvoting on some people may cause that they don't want to help any further
The need to optimize rises from a bad design
|
|
|
|
|
Downvoting ??? i didnt... i set the first answer to "Did not help"...
With great code, comes great complexity, so keep it simple stupid...
|
|
|
|
|
Hi, I have Picture object in .NET, I need to save it in Direct Text based query like,
"update table set picimage = " & ObjectName & " WHERE code = '111'"
What is the way.
|
|
|
|
|
|
hi all,
I want to open my back up database into other client systems.
Clearly I did one of my database into back up...that back up file open into others systems .
|
|
|
|
|
I am having kinda the same problem as the one already mentioned here:
Connection Pooling nightmare[^]
This is what's happening for me. Suppose that I've got a connection pool with minimum size of 10 and maximum size of 50. I'm monitoring the connections to database using SP_WHO stored procedure as well. When the first request is made to the web site, I can see that 10 connections are created in the pool. The thing that is confusing me is that by making a few subsequent requests, new connections are added to the pool even though all of 10 initial connections are in 'sleeping' mode and 'AWAITING COMMAND' as executing SP_WHO tells me. By executing subsequent calls to database the size of pool grows until it reaches the maximum number of connections (say 50). From this point on, any attempt for connecting to database results in the following exception:
DAL:Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
This may have occurred because all pooled connections were in use and max pool size was reached.
This is happening while there are 50 connections in the pool whose status is 'sleeping' and AWAITING COMMAND.
I'm using Microsoft Enterprise Library (which I think automatically handles closing connections for me), and wherever I use DataReader, I am closing it in a finally block.
Am I missing something? Please somebody help me!
_
|
|
|
|
|
Are you using your own connection pool or the built in connection pooling (that happens transparently)?
Are you connecting using multiple SQL accounts (this will prevent connections being used by multiple accounts).
Are you disposing of all resources?
Are you actually using any of the features of the EL? (Apart from ExecuteDataSet).
|
|
|
|
|
I think I've found the problem. One of the data access functions was acquiring a DataReader without closing it. I don't know why I hadn't see it so far 
Thanks for your help though ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
_
|
|
|
|
|
Are you certain the connection strings are identical - even the case of individual letters matters. ANY difference will cause a new connection.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
