Managing Stored Procedures / Functions For Releases.
Jun 5, 2006
WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.
What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.
A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.
We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...
What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.
We have 8 developers in our team.
If anyone could help or give advice on this, it would be awesome.
Hi allTrying to figure out what you use ms sql functions for. I understand stored procedures and how to create them. the question is what is the real purpose of a ms sql function considering everything i have read so far makes me think that there is no valid use for them. You can do almost everything that a function does but in a stored procedure. If somebody can give me a good examplle of a sql function i would appreciate it very much.thanks
I hope I didnt POST in the wrong group. If I did sorry. Anyhoo, on to my question. I have searched the forums and didnt quite find what I was looking for so here goes.. I have create a function that is supposed to return the "SCOPE_IDENTITY" from a stored procedure that updates the database. I'm kinda lost as to how to get the SCOPE_IDENTITY into the function. I have the following line in the function:
Dim retcode As Integer = cmd.Parameters.Add("@retcode", SqlDbType.Int).Direction = ParameterDirection.ReturnValue ... cmd.ExecuteReader(CommandBehavior.CloseConnection)
If I run the stored procedure by itself I get @retcode = 135 @RETURN_VALUE = 135 So i know the stored procedure works but how do I get that return value into a asp.net function?
I have a linked server and I want to execute SQL statements on that linked server which contains millions of records. so I decided to use SELECT * from OPENQUERY(RmtSrv,'SELECT * FROM RmtTbl WHERE Col = ' + @ColValue) but Unforetunatly the query is not succeeded because the OpenQuery doesn't accept parameters.
I used Scalar valued functions and built the query dynamically, but the Scalar valued functions doesn't execute dynamic SQL
ET @LinkedServerName = 'RmtSrv'
SET @RemoteTable= 'RmtTbl'
SET @Query = 'SELECT * FROM OPENQUERY({0}, ''SELECT Col1 FROM {1} WHERE Col2 = ''''{2}'''''') '
SET @Query = REPLACE(@Query, '{0}', @LinkedServerName)
SET @Query = REPLACE(@Query, '{1}', @RemoteTable)
SET @Query = REPLACE(@Query, '{2}', @Col2Value)
EXEC(@Query)
the above code is not executed because the functions doesn't execute dynamic queries.
I deleted the function above and wrote a Stored Procedure with the same code. but I can't query the stored procedure
SELECT GetColValue(Table1.Col1) FROM Table1.
where the GetColValue is the name of the stored procedure.
Do you have any solution that I can use to perform Remote SQL statements considering the performancewise and the code shall be centralized in certain stored procedures and functions
the Remote server contains millions of records and I will use it from tens of applications and databases.
Hi There, I've written an inline table-valued function in SQL such as the following:
ALTER FUNCTION dbo.GetCityByID( @CityID int) RETURNS TABLE AS RETURN( SELECT Name, Url FROM Cities WHERE (CityID = @CityID) )
suppose that Cities table includes three fields (CityID, Name, Url).
By the way I wrote a store procedure as follow:
ALTER PROCEDURE MyProcedure ( @MyID int) AS SELECT CountryID, OriginCityID, DestCityID FROM MyTable WHERE (MyID = @MyID)
The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name and url of each city by its ID through this stored procedue by making relation to Cities table. so I call GetCityByID function in my stored procedure like this:
ALTER PROCEDURE MyProcedure ( @MyID int) AS SELECT CountryID, dbo.GetCityByID(OriginCityID), dbo.GetCityByID(DestCityID) FROM MyTable WHERE (MyID = @MyID)
this procedure dosn't work an returns error.
What's your solution for getting information from Cities table for OriginCityID and DestCityID? Thank you in advance.
Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.
surjeet writes "Sir My question is ; Sir i am a software Engineer.I want to know about the Stored Procedures.Please Give me briefly details and examples of Stored Procedures.How to use Conditions and Literals ,How to use Procedures,Triggres,Functions in Crystal Reports and Suitable method of Data Base Design."
Running into a brain problem here. I remeber reading an article a while back (2002?) on either Visual Studio Magazine or MSDN Magazine where there was a way to generate Stored Procedures from User Defined Functions. I need this information in order to do my job as it is also a way to cut down on time for this project I am trying to finish. Does anyone have the code or remeber what I am talking about. I just finished Kathleen Dollards article again on using XSLT to generate code but would really like to use the User Defined Functions.
I searched for the article on line but came up dry. Searched through all my magazines but could not find the article. Any help would be greatly appreciated. Bit of topic I guess but still relevant to the board.
Hi everyone.I'd like to know how stored procedures and table-valued functions compare when it comes to returning a resultant set of data. I know there is a link somewhere but I can't immediately find it.Thanks.
One advantage that I can see withh UDFs is that they are a bit a Views with parameters. You can perform joins on UDF columns (which you cannot do with a Stored Proc). You can do the same with Views but UDFs have the advantage that you restrict the number of rows with a parameterised WHERE (or HAVING) clause.
Hi to all, Can any body tell me what is the difference between Stored procedures and User Defined Functions ? In my assumption Function return a value or table, but SP doesn't return value instead of that SP use select statement or assign value to output statement. This is right?
Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time
Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e. DECLARE @sCellName VARCHAR(8) DECLARE @dDate DATETIME SET @sCellName = "CELL1M_1" SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL, SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate) FROM GSM.GSMCellDaily_vw WHERE CSSR BETWEEN 0 AND 85 AND FULLDATE = @dDate AND CM_SERV > 30 AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e. Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function. Does anyone have any idea on this, and what's the workaround for this?
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR Chapter 2 Building a Procedure Chapter 3 SQLCLR Strucutre & Common Tasks Chapter 4 Creating Objects Chapter 5 Compare & Contrast Chapter 6 Replacing TSQL Objects Chapter 7 Using the Base Library Chapter 8 Using Procedures in Apps Chapter 9 Error Handling Chapter 10 Administration Chapter 11 Case Study
We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.
Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?
I am a bit confused by the difference between a stored procedure and a table-valued function. Can somebody please either give me a simple explanation, or point me at something I can read.
I thought I had it worked out, and had coded some action queries as stored procedures, and I wrote a table-valued function that was effectively an encapsulated SELECT so that SELECT * FROM Spouse(@ID) worked fine. Then I wanted to use a function SpousePair, that was similar to Spouse, to power a Gridview. I discovered that I couldn't. It seems that a SQLDataSource requires either a SELECT statement or a stored procedure. So I wrote a stored procedure SpousePair(@ID1, @ID2).
I find that whereas I tested Spouse with SELECT * FROM SPOUSE(@ID) I tested SpousePair with EXEC SpousePair @ID1 @id2
Now I want to combine these: if I could I would write SELECT * FROM SPOUSE(@ID) WHERE SPOUSEID NOT IN (SELECT SPOUSEID FROM SpousePair(@ID1, @ID2))
However this is invalid because you can't put a stored procedure in a Select statement, and SELECT .... NOT IN (EXEC SpousePair @ID1 @ID2) is also invalid.
Is there any alternative to creating a table-valued function, SpousePairA, that is identical to SpousePair but coded as a function. I'm reluctant to do this because then I'll have two bits of quite complicated SQL logic to maintain.
I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.
I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?
For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.
For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).
For Views, I need to know the Name(s) and access the Views Source
For functions, I need to know the Name(s) and access the function source.
I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:
select * from sys.objects .. I get an error:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'sys.objects'.
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
The place where I work is moving to MS SQL Server from Lotus Notes. Ihave done a lot of coding in Lotus Notes, and have, I suppose,intermediate skills in basic SQL -- queries, insert, updates, tabledesign, etc. I have a couple of questions, however. First, storedprocedures vs. functions. In my world, a function is a body of codethat returns a value; a procedure is a body of code that does thingsbut does not return a value (other than an error if it fails). At firstblush, MS SQL seems to follow this distinction. But further reading ofthe Books Online and paper books (like Advanced T-SQL for SQL Server2000) demonstrate that stored procedures also return values. So, whyuse one over the other? Apparently, I can debug SQL stored procedures(haven't tried yet) and it looks like I can't debug functions. On theother hand, I can use a function in an SQL statement but I can't use aprocedure (I think). So, if I want to take two columns and do somehand-waving over them to make a third column for each row in a row set,I need to use a function.I guess the above gives you a sense for what little I know. What arepeople's thougts on functions vs. stored procedures. What's a good bookthat might give me better insight into these sorts of issues withoutspending a lot of time on what a left outer join or a derived table is?Thanks.BD
Does anyone know how to multiple select stored procedures and deal with the permission sin one go? I know you can do this easily in SQL 6.5 but not in SQL 7. I have to select each stored procedure individually to manage permissions.
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.
the documentation of SQL Server says that TRUNCATE_ONLY will be removed in further SQL Server Version. So what should i use now?
The following Backupplan is used on a Development SQL Server. The Plan has the following goals: if there an Hardware Issue i can go Back to the Start of the Day. If a Programmer or a Programm makes a mistake, i can Rollback to a specific point in Time or Checkpoint that was between the start of the week and the point in time where the Log got backed Up. (Backing up the LOG and make a Restore all Backups)
Heres the Plan: Do Fullbackup on Sunday Morning. (WITH INIT) Do Differential Backup every Day, except Sunday, morning.
WITH INIT is used to delete old Backups from the Week before.
Now the Problem: The Transaction Log growth in time, because it never gets truncated. so i made the decision to CUT IT before the Full Backup on Sunday morning gets executed. But if i should not use TRUNCATE_ONLY - what should i use ? Or do i have a (logical) Error in my Backupplan ?
I have an application using SQL server 7.0 and have it installed at numerous sites. At the moment when an upgrade is carried out it generally includes changes to the database schema, at the moment these changes are tracked by saving lots of change scripts and running them as batch files. This process of update is quite time consuming and any errors that occur are not obvious so the upgrades must be carried out by our own staff. I was wondering two things:-
1) How do people manage this kind of problem. 2) Are there any tools that help to cope with this problem.
The Microsoft Synchronization Services for ADO.NET v1.0 CTP release appeared without fanfare on January 22, 2006. The release contains "v1.0 early CTP" bits for the sync framework and SQL Server 2005 Compact Edition (SSCE) v3.5, the Orcas successor to the SSCE v3.1 RTM bits, which Microsoft released on January 11, 2006. The current code name for Sync Services is OCS (for Occasionally Connected Systems).
Seems to me to resemble a "stealth release."
For more, see http://oakleafblog.blogspot.com/2007/01/microsoft-releases-synchronization.html.
