I'm just wondering what people think of these two design methods for calling sql stored procedures from an asp.net web app.
When returning data.
1. Having one call to the database (a stored procedure) that returns many recordsets (datatables) for each item on the form / page
vs.
2. Creating DAL objects for each action (many calls to stored procedures for each form /page.
Is there anyone with experience with #1 that can tell me if it was a good idea or not.
My database admin is strongly against the application making any more then one stored procedure call per page request. I would argue that the time saved execution wise isn't significant enough. The other problem with #1 is that business logic become part of the stored procedure and that can make your application less modular.
Using the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .
This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases. The previous 1.1 driver (2006), suceeds in both cases.
Here is a test case which demonstrates this problem.
CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0 and len(ltrim([ServiceProvider])) > 0 and len(ltrim([SecondaryID])) > 0 and len(ltrim([PanoramaObject])) > 0 and [salary] <> 0.0))) Stored procedure id defined as follows:
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help. CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate )
I have created a stored procedure only with an insert statement in sql server 2005. How can i call this stored procedure through code in ASP.NET page using vb. i want to pass one parameter that comes from a text box in asp.net page. my emailid is: g12garg@yahoo.co.in pls reply. Thank you Gaurav
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong?? Thanks
Even though this may be not right place with this issue I would like to try! I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form. I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
i created a stored procedure and below is the code segment of it;
create Procedure test1 @price as varchar(50) output, @table as varchar(50) = '' AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table. i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Hi there. My problem is: I have two stored procedures. 1. SELECT A FROM B 2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets? P.S. I already tried to use return instead of select in the first procedure. Same result. Thank you
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Hi, I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables. Those values are not needed immediately. The calculation takes between 20 or 30 minutes. Do you have any idea how to do it ? Thanks
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString); System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn); commChk.CommandType = System.Data.CommandType.StoredProcedure; commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString()); commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text); commChk.Connection.Open(); commChk.ExecuteNonQuery(); conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
Hi All, I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions: 1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject. 2) Can someone provide any links to online tutorials on t-sql? Thanks in advance. Curtis
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):
i am trying to call sp_replicationdboption from another stored procedure that i wrote myself but it is giving me the following error "sp_replicationdboption cannot be executed within a transaction" note that i'm not using begin or commit transaction in my stored procedure is there a way to do this
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
