Is there anybody who has successfully accessed a Web service from an Oracle stored procedure? If so, was it a Java stored procedure? A PL/SQL stored procedure? Is there any reason why I should not be trying to access a WS from a stored proc? Here are a couple refs that I found so far Database Web Services Calling external Web Servi...
Hi, i'm a long-time newbie to c#, and this question may be too obvious, so please forgive if i'm "doing it wrong." Using Visual Studio 2005 Pro, C#, SQL Server 2000 EE SP3. I have a stored proc that takes one input param and returns several output params pertaining to that input. I am also calling it successfully, and String.Format-ing...
In essence I'd like to store some rows in a temporary variable for the life of a procedure in MySQL. My procedure will grab a column of foreign keys at the beginning of the procedure. Once I'm done working with them I want to update the table to indicate that they have been processed. There may be inserts into this table while my proced...
Hi, I'm writing a CLR stored procedure to take XML data in the form of a string, then use the data to execute certain commands etc. The problem that I'm running into is that whenever I try to send XML that is longer than 4000 characters, I get an error, as the XmlDocument object can't load the XML as a lot of the closing tags are missin...
Some of the stored procedures we have contain conditional logic, like this: Create Procedure dbo.DoSomething(Some Parameters) As ... If (Some Condition) Begin Set @SomeVariable = SomeValue End ... Select ... When such a stored procedure is used as a recordsource for an MS Access form, and us...
I found that SQL stored procedures are very interesting and useful. I have written stored procedures but i want to write well crafted, good performance tuned and concise SPs for any sort of requirement and also would love to learn about any tricks or good practices for stored procedures. How do i move from the beginner to the advanced st...
Hi All, I have sproc 'up_selfassessform_view' which has the following parameters: in ai_eqidentkey SYSKEY in ai_acidentkey SYSKEY out as_eqcomments TEXT_STRING out as_acexplanation TEXT_STRING - which are domain objects - SYSKEY is 'integer' and TEXT_STRING is 'long varchar'. I can call the sproc fine from iSQL using the following...
I have a C#.net winform program which runs with a SQL Server database. I am using LINQ-to-SQL. Is it possible to rollback the call to one or more stored procedures inside a transaction within my program using LINQ-to-SQL? Initially I thought it would make sense to manage the transaction inside the stored procedure but if I need to r...
I need to transform an Oracle SQL statement into a Stored Procedure therefore users with less privileges can access certain data field: SELECT info_field, data_field FROM table_one WHERE some_id = '<id>' -- I need this <id> to be the procedure's parameter UNION ALL SELECT info_field, data_field FROM table_two WHERE ...
Can I ascertain the name of the stored procedure that caused the trigger to fire from within the trigger? ...
I have a sql query that runs super fast, around one second, when not using variables, like: WHERE id BETWEEN 5461094 and 5461097 But when I have: declare @firstId int declare @lastId int set @firstId = 5461094 set @lastId = 5461097 ... WHERE id BETWEEN @firstId and @lastId ... the query runs really slow, finishing only after ...
I am looking for any tool which will allow the performance testing/tuning of Sybase nested stored procedures. There are many tools around and of course Sybase's own for performance tuning and testing SQL but none of these can handle nested stored procedures (i.e. a stored proc calling another stored proc). Does anyone have/know of such a...
I'm creating a stored procedure to return search results where some of the parameters are optional. I want an "if statement" in my where clause but can't get it working. The where clause should filter by only the non-null parameters. Here's the sp ALTER PROCEDURE spVillaGet -- Add the parameters for the stored procedure here @accomod...
Hello, I have a table with almost 800,000 records and I am currently using dynamic sql to generate the query on the back end. The front end is a search page which takes about 20 parameters and depending on if a parameter was chosen, it adds an " AND ..." to the base query. I'm curious as to if dynamic sql is the right way to go ( does...
In the last two days I refactored a lot of Stored Procedures. Today I ran SubSonic and tried the application and I get this error that's driving me crazy: Could not locate entry in sysdatabases for database 'sp'. No entry found with that name. Make sure that the name is entered correctly. : at System.Data.SqlClient.SqlConnection.OnEr...
I am creating a data source for reporting model (SQL Server Reporting Services). The reports requires a lot of joins and calculations (let's say, calculating financial parameters like money spent on this, that, amount A vs amount B)...all this involves subobjects. It makes a lot of sense to me to write unit tests for this code (i.e. wal...
Is it better to use a stored procedure or doing it the old way with a connection string and all that good stuff? Our system has been running slow lately and our manager wants us to try to see if we can speed things up a little and we were thinking about changing some of the old database calls over to stored procedures. Is it worth it? T...
I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using: SELECT COUNT(Job) AS Jobs FROM dbo.Job WHERE (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1)) ...
I have this bit of code I found on the web at the end of each of my stored procedures: ROLLBACK TRANSACTION PRINT '-----START: ERROR DETAILS-----' PRINT ERROR_NUMBER() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() PRINT ERROR_PROCEDURE() PRINT ERROR_LINE() PRINT ERROR_MESSAGE() PRINT '-----E...
Hi all, Here's my scenario: I've got a table of (let's call them) nodes. Primary key on each one is simply "node_id". I've got a table maintaining a hierarchy of nodes, with only two columns: parent_node_id and child_node_id. The hierarchy is maintained in a separate table because nodes can have an N:N relationship. That is to say,...