What I want to do is simulate right clicking a stored procedure an selecting Modify, then execute so that my stored procedure runs. Some of the tables in our database have changed and not all the sp's have been modified. ie old SP = ALTER PROCEDURE [dbo].[myProcedure] SELECT name, address, typename from names GO Then the names tabl...
Hey guys, I am building a suggestive tagging system (sounds seedy), I pass in a string to the db and if there is a match it sends back that tag. I want to stop duplicate tags, so if one tag is WEB, I only want it to be returned once. So if I had WEB as a tag and then add WEEKLY, typing "WE" doesn't bring up WEB for a second time as a s...
Hello all, I am putting together a job on SQL Enterprise Manager 2000 to copy and delete records in a couple database tables. We've run a straight up mass copy and delete stored procedure, but it could be running it on millions of rows, and therefore hangs the server. I was interested in trying to run the service in 100-ish record chunk...
I'm getting the following error when calling a stored procedure: Cannot find the object "XXX" because it does not exist or you do not have permission. I've checked the database and the SP is there with the correct permissions yet I'm still getting the error. Any suggestions? ...
When sending a string of comma separated ids, as a varchar, to a MySQL stored procedure I can not use the string as part of an IN clause with the correct results returned. The string is truncated as a decimal and only the first value is being used. I thought I would be able to get around this by preparing and then executing the statemen...
I have a web-service that calls a stored procedure from a MS-SQL2005 DB. My Web-Service was timing out on a call to one of the stored procedures I have (this has been in production for a couple of months with no timeouts), so I tried running the query in Query Analyzer which also timed out. I decided to drop and recreate the stored pro...
Hello folks, i'm working in an app that was originally built to connect to SQL Server by using ADO.NET SqlClientProvider. Recently, i had to generalize the data provider code to work with either SQL Server or Oracle (by using DbClasses instead of SqlClasses and so on...). My problem is: Oracle SPs need to use ref cursors to retrieve ta...
Please excuse me if this has been asked before (I did not find the question if it has) But what is faster in SQL Server 2005/2008, a Stored Procedure or a View ? EDIT: As many of you pointed out, I am being too vague. Let me attempt to be a little more specific. I wanted to know the performance difference for a particular query in a V...
How can I get the number of a day in a week fro example: 2009-10-21 would be 4 assuming that: Sun - 1 Mon - 2 Tue - 3 Wed - 4 Thu - 5 Fri - 6 Sat - 7 I am using MySql Server 5.0.37 ...
What value in the SqlDbType enumeration should I use for the numeric T-SQL data type? ...
Hi all, I need to exec a storeproc and store its scalar result to a local variable inside a storedproc. How to implement? E.G. CREATE PROCEDURE [dbo].GetNthNo AS DECLARE @a INT DECLARE @d INT DECLARE @n INT DECLARE @S INT SET @S=EXEC spGetNthNo @a,@d,@n SELECT @S Please help. ...
Hello, I'm trying to archive records from a table in a database to an identical table in an archive database. I need to be able to do an insert for all records with a date greater than three years ago, and then delete those rows. However, this table has millions of records which are live, so I want to run this in a loop of roughly 100 ...
Hello Oracle Experts I have a Stored Procedure like this procedure P_IssueUpdate ( Id in integer, ModifiedDate in date, Solution in varchar2 ) AS BEGIN update T_Issue Set ModifiedDate = ModifiedDate, Solution = Solution where id = id; END P_IssueUpdate; my problem is that the parameter name is the same name as the Tab...
I have a table that has a Trigger associated with it for Update, Insert and Delete. The Trigger works fine and executes. Here is the code for my trigger: CREATE trigger [dbo].[trg_audit_TableName] ON [dbo].viewLayers FOR INSERT, UPDATE, DELETE AS SET NOCOUNT ON declare @inputbuffer table (EventType nvarchar(30),Parameters int,EventInf...
I need to include several stored procedure in a single transaction in a single database, if any of stored procedure fail then roll back transaction of all stored procedure procesed in the scope. I work with SQL-SERVER 2008 ...
Got a strange problem created a little stored proc which need to execute a couple of other stored procs to get some values before executing the main select statement see below, set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_get_ApplicationUserServiceRoles] @UserId int, @ApplicationName varchar(50), ...
I came across something odd that I feel is to blatant to be a bug so I am guessing that I am doing something wrong: I have the following Table: CREATE TABLE BlurbTest ( ID SERIAL NOT NULL ,Blurb TEXT NOT NULL ); With the following stored proc: DELIMITER $$ CREATE PROCEDURE spInsertBlurbTest ( OUT ID INT ...
I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests): create or replace procedure p_xml_test_1( p_xml out nocopy clob ) is begin p_xml := '<?xml version="1.0" encoding="utf8" ?>' || '<test><something>some value</something></test>'; end p_xml...
I want to create a postgres function that builds the set of columns it returns on-the-fly; in short, it should take in a list of keys, build one column per-key, and return a record consisting of whatever that set of columns was. Briefly, here's the code: CREATE OR REPLACE FUNCTION reports.get_activities_for_report() RETURNS int[] AS $F...
Have a temp table with schema: ID | SeqNo | Name ID - Not unique SeqNo - Int (can be 1,2 or 3). Sort of ID+SeqNo as Primary key Name - Any text And sample data in the table like this 1 | 1 | RecordA 2 | 1 | RecordB 3 | 1 | RecordC 1 | 2 | RecordD 4 | 1 | RecordE 5 | 1 | RecordF 3 | 1 | RecordG Need to select from this ...