I need to provide management with proof that a group of existing stored procedures that use cursors are the cause of much of our performance issues. Can someone point me in the right direction to find scripts and queries to accomplish this, please? Such as, how to monitor and measure cursors, etc. Using SQL Server 2005. Thanks. ====...
Hi all, I'm into writing a stored procedure which explodes a passed string by a passed delimiter and returns the n-th element of the result. n is passed too. So this is what I came up with: CREATE PROCEDURE SPLIT(IN strToSplit text, IN strDelimiter varchar(1), IN nPartToGet int,OUT strSlice varchar(255)) BEGIN SET strSlice = repla...
I have a procedure written in PLJava that sends out updates over JMS in my postgres database. What I would like to do is have that function called on an interval (every 15 seconds) internally in the database (preferably not from an outside process). Is this possible? Any ideas? ...
I've used a table valued parameter before, but I'm not sure how to use xml. I do not know the best way to format my xml but I think I would try this: <Car> <Name>BMW</Name> <Color>Red</Color> </Car> Then I would pass the xml (one or more car) to the stored procedure and it would insert one row for each car I pass (with ...
Hello, I'm calling a stored procedure from C# .net and one of the string parameters is null. When that is the case, I pass DBNull.Value. However, I get the above error. Any ideas? ...
After many Google and SO searches, I cannot find a definitive answer to this simple question: How can I define a procedure inside of another procedure to use? I know that there are nested blocks and nested procedures, but I haven't seen the exact syntax for what I want. i.e. create or replace PROCEDURE TOP_PROCEDURE (...) IS -- nested...
We are adding some stored procedures to our product that can be called by 3rd-party clients. Are there best practices for parameter validation, return values, RAISERROR, etc? 3rd-party clients will not have direct table access, only to certain sprocs. The table touched by the sprocs is well-constrained but we want to be as user-friendly...
I have a bunch of tables and I need to create basic INSERT stored procedures for all of them. Does anyone have anything that does this or a good start to do this? ...
The TrackingData table is inside a database named Tracking. The stored procedure is being run inside the same database. I get data back with the query, but not with the SP. SELECT * FROM dbo.TrackingData LEFT OUTER JOIN SSMain.dbo.EmailCampaignTracking ON (dbo.TrackingData.emailCampaginTrackingID = SShMain.dbo.EmailCampa...
Hi I have a requirement like this: I have to select a certain number of records from an Oracle table using a stored procedure. I will capture the entire resultset in ref cursors, but at the same time I have to update a flag in the selected records ( the ones stored in ref cursors). So I want to know whether this is possible in stored ...
I have create a stored procedure with VB.NET This procedure goes to a remote Server (for the moment on my near server Win 2003) until now everything are ok The procedure is created and i call her from my program The name is sp_AddStreamDB mycommand = new sqlcommand("EXEC sp_AddStreamDB 'C:\sqlDATA\', 'RemoteDB'",RemoteSQLConn) myComm...
I am trying to write what should ostensibly, be relative easy SQL queries - yet, I cant seeem to get them to work. Is it possible to write a query that looks something like this: select t.name from (select * from mystoredproc(1,2,3) as t) where t.name = 'foobar' Two problems with the above query: 1) First off all, th...
I'm doing the specification for an application that has to search a table with different conditions in the WHERE clause. For example (not an actual table): type 1 select name from employees where active = true; or type 2 select name from employees where idBoss = 3; I would rather create one stored procedure with a parameter "type...
Hi, just having some headaches implementing something in a trigger on a old version of Sybase and wondered if anyone could help. In a nutshell, I want to write a trigger that will call another sproc for each of the affected records (whether it be inserted, deleted or both [updated]). One way to do this in T-SQL (SQL Server) would be to ...
I have a list of Channels. I need to use an available one of these if there is one and allocate it to a session. If no available ones are left over I need to create a new one and allocate it to the session, and let the calling routines know that it is a new Channel. I have the following implementation, which seems to work, but I was won...
Whith the assistance of a very good fellow from this forum (Mr. DJHnz) i solve my first issue regarding the creation of a stream database Now i'm facing another issue I'm giving you the code: USE [master] GO /****** Object: StoredProcedure [dbo].[sp_AddStreamDB] Script Date: 12/21/2009 09:55:47 ******/ SET ANSI_NULLS ON GO SET QUO...
Hi all, I have the following query and it's not working exactly as i want it to and it is really slow so i figured i'd ask for some help. CREATE PROCEDURE [dbo].[SummaryReport] @event varchar(7) = null, @pet_num varchar(12) = null AS BEGIN WITH pet_counts AS (SELECT event, pet_num, pageid, ...
I am looking for a script that will write out all the sprocs in a SQL Server 2000 database to a textfile, preferably as a series of "CREATE PROCEDURE" statements. ...
If a statement return rows doing a simple select over the data base, is there performance difference between implement it using Function and Procedures? I know it is preferable to do it using function, but it is really faster? ...
How do I call a stored procedure from a web service method without having to wait for the stored procedure to finish, and just let the method finish all other activities? As an example (not actual situation but much simpler to understand): I have a table houses with 10 million rows, and each year I have to calculate what each house is ...