Sql Server - Migrating Stored Procedures Into Application
Jul 22, 2010
All of those disadvantages of stored procedures (database portability etc.) I am now facing. We are going to migrate our VB.Net/ASP/SQL Server application to something like Mono/Postgresql and fully internationalize.
One of the many issues we face is that we have 800-900 stored procedures. What we are thinking of doing is moving the logic in these SP's in to the application code. I have searched around for how people have done this or how it can be done however I have found very little. Most of the info on stored procedures is about what should be in them etc. So I have come to the conclusion that the best way to approach this is to leave the SQL retrieve/update type queries as stored procedures and move the ones with business logic in to the application.
I am using stored procedures in my application using vb.net, while executing stored procedures on mysql database server thru application, the response time from the server is more. Is there any settings that i need to set on mysql db server or any changes in connection string or in code.
Some question about Ado.net and SQL Server stored procedures. We have a stored procedure in SQL Server as described below
Procedure dob.PendingCalls @UsID As Char(10), @Sts As Integer OUTPUT , @Msg As VarChar(100) OUTPUT AS Begin
[code]....
we are using vb.net in visual studio 2003. Now if ExecuteNonQuery is used then we do not get rows. If ExecuteReader is used then we get rows [ with forward-only access which is of no use to us] but we cannot get output parameters values returned by stored procedure?
I'm trying to figure out a way to save all the stored procedures on a SQL Server 2005instance to text files. One of our developers has made a huge amount of StoredProcedures,and theseneed to be managed somehow between our test / integration and production environments. I know how to retrieve / run / create stored procedures, it's just the "save-to-file" i need.
I want to create a stored procedure in SQL server 2005 in order to check if there a specific value in a table. As I don't know how to do this can someone give me a link or an example on how to create and store the stored procedure? Secondly I wrote this for my the above stored procedure: CREATE PROCEDURE dbo.dokimi -- Add the parameters for the stored procedure here
[Code]....
One of the things that I am not sure is that I want to check the value that the user passes throuhg my application that I am creating. In the above code this exists in the select query @txtUserName.text. Is this right? Can I use it like this or should I declare something more after the create procedure statement?
In my VB.NET code I construct a ODBC.COMMAND to call a stored procedure that eliminates a record from table in SQLSERVER 2008, I use this code in a FOR NEXT loop, it may contain 1 to 20 records_ID to pass to stored procedures to delete the records.
This code work well for several months, but since the last compilation it only deletes the first 2 records.
I have an application that is currently using DAO and would like to move it to ADO.Net. The problem I'm having is that when I do, I cannot do any queries that take variables if the database is Access or SQL Server Compact Edition. For example: SELECT RecipeName FROM tblMasterRecipe WHERE CategoryID = iCategoryID..The database is a local standalone database. Of course if I use the Standard SQL Server I can add parameters just fine using the TableAdapter Query Configuration Wizard. How do I do something similar for Access or SQL Server Compact Edition? The Create new stored procedure radio button is greayed out when the database is Access or SQL Server Compact Edition.
I am trying to test a number of stored procedures through SQL Server Query Analyzer. But am stuck when I try to test stored procedures that contain Output parameters.
calling stored procedures using ADO.NET. We have an application developed, which uses SQL Sever as backend and Visual foxpro as front end. In this application every thing is done by database only. That is all insert, update, delete operations are done by SQL Server only. So we have to provide some input and output paramaters to stored procedures. Some time these procedure carry out insert, update, delete operations as well as return some data in the form of tables.
I want to call a Stored Procedure using SqlDataSource. I've been reading the library documentation of the classes but i'm getting slightly confused. To call a stored procedure, I understand that I have to change the selectcommandtype and then set the selectcommand property to the name of the procedure.
'Change command type to stored procedure SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure 'set name of stored procedure
For some reason my stored procedures are all executing twice! I have a static function that runs an SP given its name and the parameters and fills a datatable.
Public Shared Function RunSP(ByVal spName As String, ByRef spParams As Dictionary(Of String, String), ByRef pDataTable As DataTable) As Integer Dim cmd As New SqlCommand Dim lAdapter As SqlDataAdapter
[code]....
Is there something wrong with the code? I've checked with the debugger and the appropriate SPs are definitely only being called once, i.e. this function only runs once for a particular insertion function, but two things are inserted into the table.
I am trying to use stored procedures with LINQ to SQL to perform insert, update, and delete operations. My update and delete procedures implement optimistic concurrency by checking the original values retrieved from the database with the current values in the database. A row count is returned to indicate if the row was updated. When I execute SubmitChanges on the data context to update the database, everything works fine except that the generated code doesn't check for concurrency. (I configured the insert, update, and delete operations to use the data context methods created from the stored procedures using the O/R Designer, and I assigned the original object properties to the method arguments.) I can customize the generated code so it works properly, but I know that I shouldn't do that because I'll lose the changes if the code is regenerated. I also know that I can add a partial class to extend the data context, but I don't know how to implement the methods correctly. All I really need to do is to have the methods return the row count, but, of course, that's not an acceptable method overload. I know that I can also call the methods directly rather than calling SubmitChanges, but it seems like this is intended to work with SubmitChanges.
I noticed in the Entity Framework designer that you can map stored procedures for Insert, Update, and Delete operations. Is there any way to do this for Select operations as well, or is there a new direction for database access code where we don't really write stored procedures for our basic select operations any more?The company I work for is pretty adamant about always using stored procedures for every database operation, even though the Entity Framework makes the calls safe by calling sp_executesql.It seems like both LINQ to SQL and Entity Framework have moved away from using stored procedures for selecting the data. Is this an accurate statement?Just to clarify my question:I have a table in my database called Product. I use the wizard in the Entity Framework to generate my models...so I now have an Entity called Product. When I do the following query:
db.Products.SingleOrDefault(p => p.Id == 1);
It generates code similar to:
EXEC sp_executesql N'SELECT * FROM Product'[code].....
If it's not possible to do that using SingleOrDefault I'm fine with that. I'd much rather have the following:
db.Products.GetProduct(1);
Is this something that is normally done or do most people just have it dynamically generate the SQL?
I'm creating a database installer where I have some code to create database, tables, stored procedures, etc.I don't have problems when creating the database, tables and inserting required data. My problem is when my code gets to the stored procedures section.
Here is what I do:I have different text files (database.txt, tables.txt, insertdata.txt, storeprocedures.txt) where my sql syntax is located.I execute every process/text file using the following:
ExecuteSql("master", GetSql("database.txt")) ' Creating 1 database. ExecuteSql("mynewdatabase", GetSql("tables.txt")) ' Creating 15 tables. ExecuteSql("mynewdatabase", GetSql("insertdata.txt")) ' Inserting data to many tables. ExecuteSql("mynewdatabase", GetSql("storeprocedures.txt")) ' Creating 6 stored procedures.
Here is part of the error:In exception handler: Incorrect syntax near the keyword 'PROCEDURE'. "This is the second stored procedure because the variables I get below belong to the second one"
Must declare the scalar variable "@intVariable1". Must declare the scalar variable "@intVariable1". Must declare the scalar variable "@intVariable1". Must declare the scalar variable "@intVariable1".
When trying to create 6 or even 2 stored procedures using the same file 'storeprocedures.txt' is not possible. If I leave just 1 it works but I really want to keep them together.
Creating 15 tables using the same file 'tables.txt' works, inserting data in different tables using the same file 'insertdata.txt' works.This is the general syntax I use:
Create Procedure sp_stored1 @intVariablex int As
[code].....
Is is possible to create more than 1 stored procedure the way I'm trying to?Do I need to end every stored procedure with specific sql syntax, other than the 'end'?
I'm new user of SQL 2005 Stored Procedures and i start to create my new projects trying use stored procedures instead of writeing sql statments in my VB project.
first of all my idea is to have stored procedures for each table in my database handle Adding records or edit or delete.if i have a table name (Persons) as example i will Creat 3 stored Procedures called (sp_Persons_add) and (sp_pesons_edit) and (sp_persons_del)each stored procedure will have its input Parameters and also output parameters . this procedures will take care of everything .. check dupicates .. and check if any value is not valid because of any forign key related to that table .. and then the procedure pass me value into output parameter to tell me if it do the jop to the end . or it not .. and also can pass me message text through output parameter whitch i will display directly in my VB form .
I am working on a project that requires me to hop into to separate DB's. So I have figured that I need to have multiple functions inside of my VB page. The only problem I am having,is I am not to sure how to get this all accomplished. So far I have figured out the overall structure, just need to implement that structure.
Here is my idea: The main Function would call two other functions. We can Call them Sub Function 1 and Sub Function 2. So, the main Function takes the saved sessions information for the E-mail address and dumps in into Sub Function 1. It needs to open up a new connection to the db/stored procedure and RUN the following procedure and then return the result. Here is the stored procedure and what I think is correct.
CREATE PROCEDURE WEB_User (@EMAIL_ADDRESS varchar(80) = [EMAIL_ADDRESS]) AS SELECT MEMBER_NUMBER FROM WEB_LOGIN WHERE EMAIL_ADDRESS = @EMAIL_ADDRESS
So my question is, what is the function suppose to look like? how do I send the session information to the procedure? and finally, how do I return the stored procedure results and push back into the main function so it can be carried into sub function 2?
I am working on a Tag system for a news page designed in ASP.NET. For the system I require a TagExists method to check for tags within the database. The stored procedure I have written is below.
ALTER PROCEDURE [dbo].[Tags_TagExists]( @Tag varchar(50)) AS BEGIN
[code]....
I have tried switching the procedure to return 0 if the tag exists and 1 if it does not and it still returns 0 despite the exact same testing conditions. I have also returned the actual select query and it has complained of the Tag "news" (my test item) not being an int on execution showing the select itself is definitely properly formed.
On a ASP.net form, I need to add a textbox to update a date field in MS SQL server via stored procedure. I want to verify user input cant figure out the correct syntax to do so (please see below)
If d.Text <> "" Then cmd.Parameters.Add("@date", SqlDbType.DateTime) cmd.Parameters("@date").Value = Date.Parse(d.Text)
I know that it's possible to Call a stored procedure from a Windows Application made in VS 2008. But, is it also possible to Create one? If so, isn't it possible for someone to create a SP that will delete all the data in a DB,when executed? Is there a real danger that someone could use an application that communicates with a DB to create such a procedure?
I have just started to learn how to use SMO in some cases. Below is some code I found from a site to list the servers and databases for each. What I would like to do is go a step further and list all of the databases and stored procedures in their own listboxes when I select a database. I would like to not include the system ones or at least have a checkbox to decide to include them or not. I searched and could not find how to do this.
