Application Given En Error When Parameters In Stored Procedure Is Changes
Apr 26, 2008
Hi,
I am developing application in asp.net 2.0 using C#. My back end is sql server 2005. I also use microsoft enterprise library 2006.
My problem is:
First i pass three parameters to stored procedure from the application. After i increase one more parameter in both application as well as stored procedure, the application gives an error that parameter does not match with the stored procedures parameters.
after couple of hours when i restart the machine and again run the application it works fine with four parameters.
does sql server 2005 stores the parameters in cache??
Hi, I have a simple web application which calls a stored procedure. The stored procedure operates as a transaction and runs for several minutes. I've created a partial class to set the SQLcommand timeout property to avoid any timeouts, which works fine. Unfortunately though, when the application is run in the production environment, it ends in an error after a certain amount of time (maybe a couple of minutes - not exactly sure), which seems to be the same each run. It doesn't appear to end the stored procedure though, which results in locking the tables. It runs fine in the development environment, and it doesn't appear as though any error information is provided when the application crashes. I'm assuming that the ASP.NET application is timing out for some reason, but the stored procedure itself is fine. I can run it directly from SQL server without any dramas. In the Virtual Directory configuration within IIS, I have the script timeout period set to 1200 seconds. The Default Web-Site timeout property is set to 120 seconds, but I'm assuming that this is only for internet connection timeout, not database transaction timeouts. Any information as to what may be causing this is appreciated. Thanks
I have a stored procedure that works when executed in query analyzer. (It is also way too long to post here) When called from my application ado.net returns the error:
Invalid object name #idTable
If I run the proc in query analyzer using the same parameters (copied from quickwatch while debugging) there is no error.
While very complicated, this procedure runs quickly so timing out is not an issue.
Does anyone know why a proc would run in query analyzer and not in an asp.net/c# application?
The SqlParameter with ParameterName 'xxxxx' is already contained by another SqlParameterCollection.
I'am trying to create an dataset in which data of the different connectionpoints are separated into different tables. Herefor i'm using an stored procedure.
Below you'll find part of the main function and the full function which will execute the stored procedure.
kind regards
main function
Dim Parameters As SqlParameter() = { _ New SqlParameter("@ConnectionPointID", SqlDbType.NVarChar, 255)}
For x = 0 To myds.Tables("ConnectionPoints").Rows.Count - 1 connectionpointID = myds.Tables("connectionpoints").Rows(x).Item("Eancode") Parameters(0).Value = connectionpointID mydb.doStoredProcedure("SP_EDS_Dyomes_XML", Parameters,connectionpointID.ToString, myds) Next
Public Overloads Function doStoredProcedure( _ ByRef Mycommand As SqlCommand, _ ByRef myds As DataSet, _ ByVal Table As String)
Dim myda As New SqlDataAdapter
Mycommand.CommandTimeout = 180 If Mycommand.Connection Is Nothing Then Dim mydb As New Database Mycommand.Connection = mydb.generateconnection End If If Mycommand.Connection.State = ConnectionState.Closed Then Mycommand.Connection.Open() End If CheckParameters(Mycommand) myda.SelectCommand = Mycommand
Try myda.Fill(myds, Table) Catch ex As Exception Debug.Write(ex.Message) End Try myda.Dispose() Mycommand.Dispose()
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE): ///--MuCh14spInvTotal3.sql--/// USE AP --AP Database is installed in the SSMSE-- GO CREATE PROC spInvTotal3 @InvTotal money OUTPUT, @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' AS
IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices JOIN Vendors WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar) GO /////////////////////////////////////////////////////////////// Then I executed it and I got the following error: Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12 Incorrect syntax near the keyword 'WHERE'. I do not know what wrong with it and how to correct this problem.
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated. Thank you, Dave
I have an ODBC/C program and I want to call the stored proceduressp_addlogin and sp_adduser in it. Can someone please provide me withsome sample code showing the best way to do this.
Hey all, I've got a question and after doing some research I've found only a vague reference but no clear answer.
I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).
Can someone point me to a good source for executing java applications from a stored procedure?
Hi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?
I am searching for some information on achieving performance improvement by spawning multiple threads in single Stored procedure or rather say within Single Database connection. We have a batch process that updates around 200 tables and each table update takes around 2 mnts. I am trying to optimize this by running these updates in parallel rather than sequential. These all tables are mutually exclusive. I have written a stored procedure which updates these tables in loop. Concern is that every update statement waits for other to get over. I am calling this Sp from Java application. One crude way will be opening multiple connections to database each running separate T-SQL statement. It comes with lot of overhead in opening connections .Is there any way I can force explicitly in T-SQL stored procedure to spawn a new thread for every Update statement. In case I try to do same process from a Java connection.. is there a way I can open multiple threads for each statement under same database connection.
Hi,I have a problem with a call to stored procedure on SQL server. I created stored procedure for inserting a row to a table.In my application I read data from ASP.NET form and prepare parameters for calling my stored procedure. Like: SqlCommand sc = new SqlCommand("AddToMyTable"); try { sc.CommandType = CommandType.StoredProcedure; sc.Connection = new SqlConnection("myconnectionstringhere"); SqlParameter param; param = sc.Parameters.Add("@MyFirstParam", SqlDbType.NVarChar,50); param.Value = "something"; ..... here I repeate "add" and value assignment for all my parameters }...When I call ExecuteNonQuery(); I get exception that one of the parameters for stored procedure is expected but not supplied. However, I can find this very same parameter in my source code to be added to list of parameters. I think my problem is caused by large number of parameters. I have 55 parameters, half of them are of type nvarchar(50) and half of them of type bit. My reasoning is based on the fact that if I remove parameter that is reported to be missing from my stored procedure and from my application, I get the same error with next parameter in the list.Where should I increase maximum number of parameters that are allowed? Do I have some buffer problem? Where to specify that I need more space for parameters? Nenad
There is a form in my project which users can add their emails through it. The maximum numbers of emails are 60. I put a textbox and a button for adding email. But I don’t know which of the following solutions is suitable: 1. After each click by user , the insert stored procedure will be called 2. All the emails entered by users will be saved and then, the insert stored procedure will be called. This SP must have a loop but I am not sure about the right code for this SP because I have to define 60 input parameters for this SP.
Hi what's the difference between using parameters in these two ways: SELECT @PortalName = Portals.PortalName ... and SELECT Portals.PortalName... first one will NOT be a result set, and second one will Whats the basic difference between that? I need to use a NextResult method within a .cs filebut it is not working thanks
I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which having all 11 parameters. If I don’t enter any one of the value I am not getting the result and it will goes on exception handling.
Now what I want is even I enter one value this must be excute………….i think some validations should be done in Stored procedure's 'where' clause.but I don’t know how
i would be very happy if someone guides me with sample code for this.
I know there ar in parameters and out parameters, but are there any other kinds? Are there in/out parameters? If so, how would you declare one in the stored procedure?
I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters. If I don’t enter any one of the value I didn’t get the result and it is raising exceptions.
Parameters are passed to Stored Procedure from VB.NET Application like
Dim param As SqlParameter param = New SqlParameter("@name",DbType.String) param.Direction = ParameterDirection.Input param.Value = "ABC" comand.Parameters.Add(param)
But is there any way to specify parameters without specifying the parameter name and type like
Dim param As SqlParameter param = New SqlParameter param.Direction = ParameterDirection.Input param.Value = "ABC" comand.Parameters.Add(param)
But when i try like this i am getting an error saying that "Parameter1 is not a parameter in the procedure"... Can anybody tell me how to solve this . Thanx in advance
Hello, i have a problem regarding stored procedures and view server state.
I have an application with a lot of stored procedures, one of them checks data of the connected users. In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.
my stored procedure looks like this:
ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]
(
@DBName varchar(100)
)
WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON
DECLARE @sCollationMaster VARCHAR(128);
DECLARE @sSqlString VARCHAR(900);
-- Determine collation from master database because collation from master and ultimo database may differ
SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);
SET @sSqlString =
'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login
, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser
, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program
FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)
AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')
WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''
GROUP BY hostprocess
ORDER BY Login
';
EXEC(@sSqlString);
END
I've granted view server state permissions to my user 'dba' which is the db_owner. When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.
I seem to have the same problem with sp_who2 Executing it gives me information about everyone but when i put in a stored procedure like this:
Hi All, I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD. Thanks,
Hello everyone, I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance Errors 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' protected void Button1_Click(object sender, EventArgs e) { /* These two variables get the values of the textbox (i.e user input) and assign two local * variables, This is also a good strategy against any Sql Injection Attacks. * */ string Interview1 = TextBox1.Text; string Interview2 = TextBox2.Text;
//Create Sql connection variable that call the connection string SqlConnection SqlConnection = new SqlConnection(GetConnectionString());
//Create a sql command to excute SQL statement against SQL server SqlCommand Command = new SqlCommand();
// Set the command type as one that calls a Stored Procedure. Command.InsertCommandType = CommandType.StoredProcedure;
//Call the stored procedure so we can pass it on the user input to retrieve user details Command.InsertCommand = "Summaries";
//open the command connection with the connection string Command.Connection = SqlConnection;
// Pass the user input to the Stored Procedure to check if user exists in our system. Command.InsertParameters.Add("interview1", interview1); Command.InsertParameters.Add("interview2", interview2); Command.InsertParameters.Add("interview3", interview3);
Hi Y'all, On how many ways can i pass parameters IN a stored procedure? I want to boost my performance and get rid of 4 for loops in my page load. SO how can I create a resultset (grid) with the same result as looping? I hope i made myself clear.... Thanks in advance
I have two variables in my code behind .cs, string Zip;int MenuCode; I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object? error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string" <asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
How to write stored procedure with two parametershow to check those variables containing values or empty in SP if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one Select * from orders plz write this SP for me thanks
i built a stored procedure with inserting in to customers table. i have one column with identity. so want to take that identity column value in the same stored procedure. so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me. also how to get that value in ado.net 2.0. friends please tell me.
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters: SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt); param.Direction=ParameterDirection.Output; even if I only need the value of a single one. Is this right? Is there a way to avoid coding every one every time?
I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.
--I've been getting the following error in visual basic 6 when i try to call this: run-time error '-2147217900 (80040e14)': syntax access violation
--This is the error you get when you try to just run the code in query analyzer: [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
--what code i was trying to use (in query analyzer): {call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a', @BulkJit = 'a', @ID = '0', @RQSodfilFlag = '1', @BulkRanFlag = '0', @BulkForFlag = '0', @BulkJitFlag = '0', @DeleteFlag = '0', @ErrorNum = '0')}
--code that i was trying to use in vb 6: Public Sub TemporaryTables( _ ByVal bytRQSodfilFlag As Byte, _ ByVal bytBulkRanFlag As Byte, _ ByVal bytBulkForFlag As Byte, _ ByVal bytBulkJitFlag As Byte, _ ByVal bytDeleteFlag As Byte, _ ByVal cnPlant As String)
Dim objConn As ADODB.Connection Dim objCmd As ADODB.Command Dim objRQSodfil As Parameter Dim objBulkRan As Parameter Dim objBulkFor As Parameter Dim objBulkJit As Parameter Dim objParamID As Parameter Dim objRQSodfilFlag As Parameter Dim objBulkRanFlag As Parameter Dim objBulkForFlag As Parameter Dim objBulkJitFlag As Parameter Dim objDeleteFlag As Parameter Dim objErrorNum As Parameter Dim intErrorNum As Integer
' setup command variable Set objCmd = New ADODB.Command Set objConn = New ADODB.Connection objConn.Open cnPlant objCmd.CommandText = "EXEC CreateTempTables" objCmd.CommandType = adCmdStoredProc objCmd.ActiveConnection = objConn
