Hi all, In a SQL database I got storage information for each user, for each customer. I need to write a stored procedure that sums the disc usage (MB) for the customers users. When I got the total sum of all users for a single customer (totalDiscUsage), I need to perform a calculation (simple example): x = numberOfUsers * 200 y = (tot...
I wrote a trigger that updates local table and similar table on linked server. CREATE TRIGGER myTtableUpdate ON myTable AFTER UPDATE AS IF (COLUMNS_UPDATED() > 0) BEGIN DECLARE @retval int; BEGIN TRY EXEC @retval = sys.sp_testlinkedserver N'my_linked_server'; END TRY BEGIN CATCH SET @retval = sign(@@error); END CAT...
For a given MySQL DB that I use and modify occasionally I had to recently make some changes to some tables and stored procedures. There are places in this DB where procedures make calls to other procedures. I found the task of hunting down everywhere that I needed to modify the parameters to these modified procedures a hassle and reso...
When I drag a particular stored procedure into the VS 2008 dbml designer, it shows up with Return Type set to "none", and it's read only so I can't change it. The designer code shows it as returning an int, and if I change that manually, it just gets undone on the next build. But with another (nearly identical) stored procedure, I can...
Forgive my naivety, but I am new to using Delphi with databases (which may seem odd to some). I have setup a connection to my database (MSSQL) using a TADOConnection. I am using TADOStoredProc to access my stored procedure. My stored procedure returns 2 columns, a column full of server names, and a 2nd column full of users on the serve...
1.Database platform: SqlServer 2.Data Access: nHibernate 1.2 Now we need access the store procedure by nHibernate,like this: ALTER PROCEDURE TestProc() AS BEGIN Select * From User Return 1234 END I know I can get the User List by IQuery, And I want to get the default return value "1234" too. Question: How to...
I love your tool. I have been using it a lot, but just today I ran into a problem... I wrote a stored procedure that returns some values via OUT parameters, but SubSonic does not seem to generate the out parametes of the stored procedure method. For example, for SPI like this: CREATE PROC dbo.MyProc @param1 int, @param2 int out, @param...
I'm looking for sort of a 'best practice' way to tackle this common scenario. I think the question is best asked with an example. Let's assume the following: The goal is to write an 'order summary' report which displays a listing of orders based on various filtering criteria. Example: The user wants to report on all orders created ...
I have to write a deployment script which will work if a stored procedure exists or does not exist. i.e. if it exists, then I need to alter it, otherwise create it. How can I do this in the sql. I am using SQL Server 2005 ...
We have SQL Server 2005 database with full backup and transaction logs. We have a problem with the database - and need the SQL CSI Forensic team to help. Is there a way to look at the transaction logs and identify whether a stored procedure was executed? We know the time that it happened (if it happened) but there is a dispute wheth...
I have a trigger that will be dynamically created, as it will be attached to a view that is also dynamically generated. I don't want my stored procedure to have the entire trigger within it, so I would like to move most of it to a stored procedure, but I won't know the fields in the inserted and deleted tables. The trigger is about 90 ...
I need to make a SELECT with a call of a stored procedure in the WHERE clause. It should be something like that.... SELECT distinct top 10 i.x, d.droit FROM v_droit d, v_info i WHERE d.nomdroit='yy' AND i.id<>2 AND (select val from (exec up_droits(i.x, d.droit)) <>3 But it does not work... Any idea? Don't say to replace t...
For an app my team is developing, I have created a copy of the database to work independently on some possible features for the system. In doing so, I have changed some stored procedures. Meanwhile, other members of the team continue to work on the other database and change some of those stored procedures. The code is still calling ...
I have a datacontext say myDataContext, it has on several classes (tables) and stored procedures, one of which deletes directly from the database (the logic requires this), however, the deleted rows remain in my myDataContext unless I initialize it again. Is there a way to avoid this? The stored procedure removes the record from the data...
Hi folks, I have a stored procedure that returns a number of fields, pew row. Is there anyway i can define the class which the stored procedure 'goes into', instead of it using the auto-generated class? The class i want the results to populate is a POCO, not another LinqToSql table. Is this possible? i'm assuming that the poco class p...
Hey All, I have been using SS2.1 for quite a while now and have been loving it. However, I noticed that 2.2 was out so I thought it would be best to upgrade. After dropping 2.2 into my bin folder, it no longer builds the stored procedures from MySQL that were working so perfectly with v2.1. What happened? What am I missing here? On...
Hi, I am having problems with a stored procedure that I am writing for SQL Server. I am calling a function within the stored procedure which returns a 2 column table with a value. This value I want to assign to a VARCHAR variable to be used throught the rest of my store procedure. DECLARE @TEAM VARCHAR(100) SET @TEAM = (SELECT DISTI...
I have a database I have inherited, and my best practice is to create the scripts to create the objects (tables, stored procedures and views) and then version control these. It would like to have all the objects in this database scripted out, and put into source control; but on SQL Server 2005 (SQL Server Management Studio) it appears -...
Hi! Is there a decent way to delay execution of mysql trigger? WHILE @condition = 0 sleep for awhile insert into some_table values(NEW.value1, NEW.value2); ...
Hi I am writing a large stored procedure, which creates a dynamic report table, of n columns in size, the first 6 are constant the remainder depend on a few arguments passed to the procedure to create the table with the required columns. The problem that I am having is with the following TSQL DECLARE @columnname VARCHAR(50) SET @column...