I have a table that has an insert trigger on it. If I insert in 6000 records into this table in one insert statement from a stored procedure, will the stored procedure return before the insert trigger completes? Just to make sure that I'm thinking correctly, the trigger should only be called (i know 'called' isn't the right word) once b...
I have a formview that executes a insert stored procedure through a table adapter as normally done. Except when the insert fails a output parameter on the sp called error_msg is set to "Insert failed because XXXX is required". how do I capture this value and print it on the screen for the user? I can't seem to find locate it. ...
Hi, I have a sproc that returns a single line and column with a text, I need to set this text to a variable, something like: declare @bla varchar(100) select @bla = sp_Name 9999, 99989999, 'A', 'S', null but of course, this code doesn't work... thanks! ...
Within a stored procedure, another stored procedure is being called within a cursor. For every call, the SQL Management Studio results window is showing a result. The cursor loops over 100 times and at that point the results window gives up with an error. Is there a way I can stop the stored procedure within the cursor from outputting an...
In Scott Hanselman's interview of the stack overflow guys, Scott and Jeff make mention that they may be seen as heretics for declaring that the days of the stored procedure are dead. Why might they say this? Is it because many of the new DAL techniques of dynamic data and Linq to SQL use T-SQL to communicate with the SQL server (SQL 20...
I have a stored procedure currently executing a complicated fetch that is frequently timing out when used. The proposed solution in my department has been to simply increase the timeout time length; which I don't really want to do. I'd like to refactor this sproc, but because it's so complicated and undocumented (yay legacy systems) I'm ...
Is there a query that returns the names of all the stored procedures in an MS SQL database (excluding "System Stored Procedures" would be a nice touch)? ...
I was asked for a comprehensive breakdown on space used within a specific database. I know I can use *sys.dm_db_partition_stats* in SQL Server 2005 to figure out how much space each table in a database is using, but is there any way to determine the individual and total size of the stored procedures in a database? (Short of opening each...
Given a table named "person" (in a MySQL database/schema), kind of like this one: code varchar(25) lastname varchar(25) firstname varchar(25) I'm trying to make a stored function to receive "code" or a part of "code" (which of course, is the code that identifies that person) and return a 'list' of suggestions of persons that have ...
Can you recommend a good MySQL stored procedure debugger? Extra points if it is open source, and works in linux :) Thanks! ...
VB.net web system with a SQL Server 2005 backend. I've got a stored procedure that returns a varchar, and we're finally getting values that won't fit in a varchar(8000). I've changed the return parameter to a varchar(max), but how do I tell the OleDbParameter.Size Property to accept any amount of text? As a concrete example, the VB co...
Suppose I have a bunch of varchar(6000) fields in a table and want to change those to text fields. What are the ramifications of the stored procedures whose arguments are of type varchar(6000). Does each stored procedure also need those argument data types changed? ...
Today i stumbled upon an interesting performance problem with a stored procedure running on Sql Server 2005 SP2 in a db running on compatible level of 80 (SQL2000). The proc runs about 8 Minutes and the execution plan shows the usage of an index with an actual row count of 1.339.241.423 which is about factor 1000 higher than the "real"...
Say I have a stored procedure that returns data from a SELECT query. I would like to get a slightly different cut on those results depending on what parameters I pass through. I'm wondering whether it is better design to have multiple stored procedures that take one or no parameters to do this (for example, GetXByDate or GetXByUser), or ...
I want to grant a user permission to create, alter, and execute stored procedures, but without the ability to create other database objects. I excluded them from the role db_ddladmin, but explicitly granted the permissions "Create procedure" and "Execute". These permssions appear in the effective permissions list. However, when I try ...
I'm attempting to determine the row length in bytes of a table by executing the following stored procedure: CREATE TABLE #tmp ( [ID] int, Column_name varchar(640), Type varchar(640), Computed varchar(640), Length int, Prec int, Scale int, Nullable varchar(640), TrimTrailingBlanks varchar(640), FixedLenNullI...
We have the following simple Stored Procedure that runs as an overnight SQL server agent job. Usually it runs in 20 minutes, but recently the MatchEvent and MatchResult tables have grown to over 9 million rows each. This has resulted in the store procedure taking over 2 hours to run, with all 8GB of memory on our SQL box being used up. T...
When using SubSonic, do you return the data as a dataset or do you put that in a strongly typed custom collection or a generic object? I ran through the subsonic project and for the four stored procs I have in my DB, it gave me a Sps.cs with 4 methods which return a StoredProcedure object. If you used a MVC, do you usually use the Stor...
I am thinking about creating stored procedures on the fly. ie running CREATE PROCEDURE... when the (web) application is running. What are the risks or problems that it can cause? I know that the database account needs to have the extra privileges. It does NOT happen everyday. Only from time to time. I am using sql server and inter...
We have been working on a complex database and client interface for the last 18 months. We are regularly adding new functionnalities to this application, and it is now used by tens of users on a daily basis in all our offices, including sites and overseas. This is just to tell you it is a REAL application with a REAL database. Until now...