Stored Procedures Or C# Code For My Purpose (Data Synchronisation)
Jul 4, 2005
Hi all,
I am in the position where I have to transfer data from an old database
schema to a new database schema. During the transfer process alot of
logic has to be performed so that the old data gets inserted into the
new tables and are efficiently done so that all foreign keys are
remained and newly created keys (as the new schema is Normalised alot
more) are correct.
Is it best if I perform all this logic in a Stored Procedure or in C# code (where the queries will also be run)?
Hi everyone,I would like to back up my whole database project inclkuding the codeofstored procedures in my application.Is there a waa to get the code of all stored procedures of a databaseprojectwith a SQL-statement or otherwise using a normal database-connection ?Hope someone can help.Best regards,Daniel
Hi,We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way". What i mean by using seperate stored procedures is:- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)- The body contains a DML statement that uses the parameters- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)For select statements they think our approach is best...I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...Thanks
I have some lengthy code that creates a #Temp file. I would like to access this code from different stored procedures. Is there such a thing as an INCLUDE statement for stored procedures, or is this what the EXEC statement accomplishes?
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
I have several stored procedures with a similar query. The SELECT clauses are identical, but the FROM and WHERE clauses are different.
Since the SELECT clause is long and complicated (and often changes), I want to place the SELECT clause in a separate file and then have the stored procedures include the block of text for the SELECT clause when they compile.
Is this possible? Looking through the docs and searching online, I don't see any way to do this.
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer 'that is the variable for the new id.Dim intNewID As Integer Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity" Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL 'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-( dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim) dbCommand.Connection = dbConnection TrydbConnection.Open()dbCommand.ExecuteNonQuery() 'here i want to return the new ID!Return intNewID Catch ex As Exception Throw New System.Exception("Error: " & ex.Message.ToString()) Finally dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose() End Try End Function
Hi,I'm working with two databases on to physicaly separated servers: #1is on a local machine en I can only connect to #2 via an ADSLconnection.I need to syncrhonise the data. This works fine via the wizard fromthe Enterprise Manager. But I want to get this done via a script formthe Query Analyzer that I can run whenever I want to. Something like:insert into srv1.database.dbo.table1select * from srv2.database.dbo.table1When I do this I get an error about "linked servers". That surprisesme, because I have no linked servers configured and yet the wizardworks fine?Can anyone tell me how to accomplish this?tavJan Willems
I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this?
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece. thanks.
Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot!
How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW. PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
We have a gaming application which generates transactional data in MongoDB which eventually sends the data to SQL Server and it is in JSON format. This data needs to be used for reporting tool but visualizing this data in forms of a table is proving to be difficult. One example of a column we receive is:
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!!
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message
Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0 Column or parameter #1: Cannot find data type udtcol1. Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0 Column or parameter #2: Cannot find data type udtcol2. Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0 Column or parameter #3: Cannot find data type udtcol3
Can you have user defined data types in stored procedures.
Store Procedure creation text
CREATE PROCEDURE spStoredproc @col1 udtcol1, @col2 udtcol2, @col3 udtcol3 AS INSERT INTO tblTempEmployee (col1 , col2 , Col3) VALUES (@col1 , @col2, @col3) GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF GO
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem? Thanks, Dan Collins
Su writes "I'm trying to use a stored procedure to dynamically update a table whenever other staff in other departments update their do any changes to their databaseds. and thanks for your web site taught me how to pass table names as parameters. But I still have problems withe sql command. You have an example in your article ('dynamic sql 2'), showing how to do a sql SELECTION using a table name and a local variable. But the sql command only use a local variable of varchar type. I'm trying to do INSERT with local variables with different data types. For example:
CREATE PROCEDURE KPISU_F_TotalByF @inputT_From varchar(10), @inputT_To varchar(10), @TableName varchar(1000) AS ----------------------------------------------------- --------input variable------------------------------- DECLARE @inputTerm_From varchar(10), @inputTerm_To varchar(10), @sql_empty varchar(2000), @sql_refresh varchar(2000) ---------------------------------------------------- IF EXISTS (select * from tempdb.dbo.sysobjects where id LIKE object_id('tempdb..#tmpOTLTotalByF')) DROP TABLE #tmpOTLTotalByF
Server: Msg 245, Level 16, State 1, Procedure KPISU_F_TotalByF, Line 256 Syntax error converting the varchar value 'INSERT KPISU_F_OTLTotalByF06 VALUES (14-19 Academy, 2005, ' to a column of data type int. -----------------------------------------------------------
I guess I could change all the columns in the table to data type of varchar. But are there any other way to solve this problem?
I am trying to write a stored procedure, which does a couple of things.
First thing is it looks up a persons Location based on an ID number, which is passed from an external Script. This will return four Values from a table.
I want to insert those four values into another table, along with another ID passed to the procedure from the same script.
My question is, what do I do to the script below to get the four values out of the first look up, into the insert?
The Field names returned from the SELECT are, AREA1, AREA2, AREA3, AREA4
oConn = New SqlClient.SqlConnection oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;" oCmd = New SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "TestStdInfo" 'parameters block oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString()) oParam1.Direction = ParameterDirection.Input oParam1.SqlDbType = SqlDbType.Int oCmd.Parameters.Add(oParam1) oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text)) oParam2.Direction = ParameterDirection.Input oParam2.SqlDbType = SqlDbType.DateTime oCmd.Parameters.Add(oParam2) oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text) oParam3.Direction = ParameterDirection.Input oParam3.SqlDbType = SqlDbType.VarChar oCmd.Parameters.Add(oParam3) oConn.Open() daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn) dsStudent = New DataSet daStudent.Fill(dsStudent) 'This line is highlighted when error happens oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
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.
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008 Date To: 24/02/2008 Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name -------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John 2 2/22/2008 4:16:45 PM Dean 3 2/23/2008 4:16:45 PM Rick 4 2/24/2008 4:16:45 PM Van
Hello, I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls. I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems ( @eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar, @eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar, @outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar, @q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar, @levent nvarchar, @eventdate nvarchar, @eventtime nvarchar ) AS UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile, q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn, outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3, qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName, CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime WHERE (ePRN = @ePRN) and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text); cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text); ((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text); cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text); cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text); cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text); So is there any way to do it better than this way ?? Thank you
Is there a way, through t-sql or through SQL Server Management Studio, to retrieve (or export to file) all procedure code for all my stored procedures?
