Hi all, I am currently having problems calling a stored procedure async from within a insert-update-trigger. For this I m using the service broker. --message type CREATE MESSAGE TYPE [TheMessage] VALIDATION = NONE --contract CREATE CONTRACT [TheContract] ([TheMessage] SENT BY ANY); --queue CREATE QUEUE [TheQueue] WITH ACTIVATION (STA...
I have the following sql code. Is it guaranteed that MyTable is going to be sorted by MyTable.data? Basically the question is - if I am inserting multiple rows with one INSERT statement, can other connection get in the middle of my insertion and insert something else in between my rows? CREATE TABLE MyTable( id bigint IDENTITY(1,1) ...
When it comes to denormalizing data in a transactional database for performance, there are (at least) three different approaches: Push updates through stored procedures which update both the normalized transactional data and the denormalized reporting/analysis data; Implement triggers on the transactional tables that update the seconda...
I am working on a legacy code where there are several methods with calls to Stored procedures from the service layer itself as opposed to hibernate. Is it possible to test those methods in any manner so that my code coverage can increase ? I am using java 1.5 and sql server 2005. ...
What does this icon mean is Visual studio, visual Data Set designer. I have several stored procedures. can you help me understand, why the one on top of the list has a small "check mark" why I can't delete it if I need to. This is not the case with the rest. Why is this "special" ? Thanks ...
What is the full syntax for a stored procedure using SubSonic 3.0? Here is my code which is not working: DataSet ds = new DataSet(); StoredProcedure sp = test.Usersp_Stock_Search("", 0, ""); sp.ExecuteDataSet(); ...
Say we have stored procedure(s) performing simple operations CREATE PROCEDURE [dbo].[AddNewAuthorReturnID] ( @Author_Name VARCHAR(MAX), @Author_ID int OUTPUT ) AS SET NOCOUNT OFF; BEGIN INSERT INTO AUTHORS (@Author_Name) VALUES (@Author_Name) SET @Author_ID = SCOPE_IDENTITY() SELECT @Author_ID END in above procedure...
I have an encrypted SQL Server stored proc that I run with (or the vb .net equivalent code of): declare @p4 nvarchar(100) set @p4=NULL declare @p5 bigint set @p5=NULL exec AA_PAY_BACS_EXPORT_RETRIEVE_S @PS_UserId=N'ADMN', @PS_Department=N'', @PS_PayFrequency=2, @PS_ErrorDescription=@p4 output select @p4, @p5 this returns 2 datasets a...
I have similar methods, in the Business layer. New to unit testing and sometimes get confused. For an idea, can you suggest, what will be a better approach to test this method behaviour? I am using C# NUnit and Moq public int? AddNewCatRetID(string categoryName) { int? categoryID = 0; Adapter.AddNewBlogCategoryReturnID(categ...
I recently had to root out the cause of intermittent ODBC errors in a 12 year old SQL Based application. The culprit turned out to be this statement at the end of one of the stored procedures: DUMP TRAN NotTheRealDBName WITH NO_LOG I wasn't familiar with this command, so I did some research and found that it makes a backup of the tra...
hi Is there any way to send null to an optional stored procedure parameter via crystal reports8.5 and vb6? I use CRXParamDef.AddCurrentValue method to send stored procedure parameter values. ...
is there an sql query that can give me the fields that are used in most stored procedures or updated, selected most in a given table. I am asking this cause i want to figure out which fields to put indexes on. thanks ...
I've come across an example of doing this on the net which also fails on my DB (10g) but here's my version. ... TYPE prog_rec_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; prog_rec_list prog_rec_type; begin ... EXECUTE IMMEDIATE 'SELECT PROGRESS_RECID FROM ' || v_table_name || v_where BULK COLLECT INTO prog_rec_list; --ERROR FOUND...
Me and my colleagues have a question regarding SQL Server 2008 query length and the SQL Server Optimizer. We are planning to generate some stored procedures that potentially have a lot of parameters. Inside of our stored procedure we will simply select some values from a table joining other tables. Our stored procedures will look like ...
I have a method that uses Entity Framework to do some changes/inserts in different entities, all this inside a single transaction scope. These changes works very well. My problem has began when I needed to use a stored procedure in the middle of these operations. The procedure does only an insert in one table, and has no explicit declar...
I have a simple insert select which insert _TABLE_B_ data in _TABLE_A_ new row INSERT INTO _TABLE_A_(_USERNAME_,_ID_) SELECT _USERNAME_,_ID_ FROM _TABLE_B_ I want to insert a row in a table named _TABLE_C_ each time i insert a row in _TABLE_A_ and add the current inserted _TABLE_C_ id in _TABLE_A_. i'll try to explain it in an ot...
Hi All, I need to convert procedures written in Progress 4GL to Oracle stored procedures. Is there any tool other than SQLWays using which I can achieve this? ...
I'm looking into stored procedures at the moment. According to this article (page 8) in the dev section of the mysql website... Ordinarily, it's not normal to put SELECT statements in stored procedures, this is for illustration. I decided that some procedure should simply select from our table, so that when you call the pr...
My sproc signature is defined as: ALTER PROCEDURE [dbo].[AuthenticateUser] -- Add the parameters for the stored procedure here @UserName varchar(64), @UserPassword varchar(64), @Result INT OUT, @ErrorMessage VARCHAR(25) OUT AS BEGIN.... Calling this sproc with SubSonic: DataSet dsResults = new DataSet(); Stored...
When calling a stored procedure I am concatenating values together, my question is how do you call a stored procedure but send a 'NULL' value in one of the params? Lets say that AID = null, but if I pass that to my query I get an error?! QueryConn.Execute("Search_Res " & Count & "," & AccessList("InvoiceLevel") & "," & AID) Ok, so my...