Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.

learn more… | top users | synonyms

1
vote
1answer
97 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
1
vote
1answer
142 views

MySQL: IF statement check is a table exists

I run in the following issue and can't figure out how to resolve it. delimiter # create procedure pr1() begin declare v1 double default 0; start transaction; ...
1
vote
1answer
104 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
1
vote
1answer
124 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
1
vote
1answer
2k views

Creating an SSIS package that uses a stored procedure

We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing ...
1
vote
1answer
242 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
0
votes
1answer
73 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
3
votes
0answers
258 views

SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows

So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part: ALTER PROCEDURE ...
2
votes
0answers
102 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
1
vote
0answers
40 views

How to access another db from a stored procedure in db2

I want to query a table in another db from within a stored procedure in db2. For example, I am connected to db_a1, and call a stored procedure from within which I need to connect to a table in db_a2. ...
1
vote
0answers
255 views

Rolling Back Stored Procedures in Entity Framework

I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using ...
1
vote
0answers
391 views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH ...
1
vote
0answers
150 views

Storing changes to stored procedure and triggers in PostgreSQL

I want a mechanism where I can store/track changes in stored procedure and triggers. I want to track these logs in some table. What is the best possible way ?
1
vote
0answers
104 views

user rights validation inside stored procedure?

You have an application with a Users table, and the Users table has various columns to indicate what rights a User has within the application. Ignoring for the purposes of this question what should ...
1
vote
0answers
105 views

Using #temp to work around Reporting Services blocking issue

Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have ...
1
vote
0answers
301 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
1
vote
0answers
133 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
0answers
1k views

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that ...
1
vote
0answers
870 views

How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?

This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
1
vote
0answers
134 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
1
vote
0answers
453 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
1
vote
0answers
31 views

What might cause all objects in an Oracle database to suddenly have quoted identifiers?

I have an Oracle schema in which, at some point in the past ("the event"), all of the procedural code objects in the database have been re-created with quoted identifiers. All of the objects are ...
1
vote
0answers
880 views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
0
votes
0answers
22 views

Stored Procedure Issue in Simple Select Statement

Here is my Fiddle Here is My Table : CREATE TABLE IF NOT EXISTS `vehicle` ( `AutoID` int(11) NOT NULL AUTO_INCREMENT, `VehicleCode` varchar(20) NOT NULL, `NumberSeats` int(3) NOT NULL, ...
0
votes
0answers
25 views

User or Role is Missing

I'm using a stored procedure in oracle 11g r2 but when I call it from java it says that I'm missing the user or role name when it executes this sql sentence: no_dml := 'CREATE USER ' || no_carne || ' ...
0
votes
0answers
50 views

Service Broker received an error message on this conversation

I will first have to say, I am still trying to wrap my brain around all the components of the Service Broker and how they work together, so it's very likely I'm missing something obvious... it's just ...
0
votes
0answers
28 views

How to pass ouput parameter values in mysql procedure

I am new with mysql and i getting issues at time of using output parameters in mysql procedure. Scenario: I have two tables MyProcedure code: DELIMITER $$ CREATE ...
0
votes
0answers
13 views

Validate hierarchy of two tables

I have two tables in my mysql database: Questions question_id Answers answer_id parent_question_id child_question_id Basically, I'd like to pass in a question_id, and have all child ...
0
votes
0answers
31 views

Replication overview

My boss wants me to elaborate a comprehensive report about synchronisation/replication. I know that replication can be done with the DBMS (we are using SQL Server 2012), but he wants something manual ...
0
votes
0answers
24 views

MYSQL Stored Procedure Problem. Need Help

MySQL Stored procedure: Delimiter $$ DROP PROCEDURE IF EXISTS spGetCompanyNames $$ Create procedure spGetCompanyNames (IN Employer nvarchar(75), Out Employer_Namee nVarchar(75)) begin Select ...
0
votes
0answers
69 views

prevent sql injection inside stored procedure

I finally found a solution for the following question: reading input parameter values in stored procedures However I'm not sure, whether this solution is resistant to SQL injections. I'm using ...
0
votes
0answers
33 views

Function performance issue

This one is puzzling me since yesterday and maybe someone could shed some light on this. We've got this table that records GPS data. We're currently doing some overloading tests to see how the ...
0
votes
0answers
420 views

Can a stored procedure OUTPUT parameter return a table as output?

I would like to know whether we can use a stored procedure's OUTPUT parameter to a return table as result set? I would appreciate any practical example using it OUTPUT parameters in stored ...
0
votes
0answers
68 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
0
votes
0answers
1k views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...
0
votes
0answers
170 views

Create Drop Temporary Table very slow vs Truncate

I've got this stored Procedure that is called by an event that runs every 1 minute. The stored procedure selects some data from one device and puts it in a temporary table. Than it picks up other ...
0
votes
0answers
28 views

MySQL Procedure call is limiting parameters to 3 characters

I am running MySQL 5.5 using MAMP on my local machine. I have imported a database that was exported from a Windows Development environment. To get my Procs to run correctly locally, I had to change ...
0
votes
0answers
47 views

Stored procedures - Northwind database

First, I am totally new in programming, so excuse me if I ask dumb question. :) I am trying to make a new project in Visual Studio. I have Northwind database installed (probably came with VS ...
0
votes
0answers
60 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
0
votes
0answers
785 views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
0
votes
0answers
246 views

Execution time is different between normal query and query inside a stored procedure

I have following simplified query WITH Report1 AS ( SELECT SUM(t.X), SUM(t.Y) FROM Table1 t WHERE t.Date1 > :dateStart and t.Date1 < :dateFinish ) SELECT * FROM Report1 This query ...
0
votes
0answers
80 views

Dynamic Query creation by looping through Query-Result?

after my first question regarding pivoting a table (here) got great answers I'm now struggling with how to adapt this to variable table sizes. I need something like a "FOR EACH IN" which creates a ...
0
votes
0answers
52 views

Logical or in oracle PL/SQL

I have this code: declare error1 number; error2 number; error3 number; begin error1 := 1; error2 := 0; error3 := error1 | error2; -- should be error3 = 1 end; But I dont know how to ...
0
votes
0answers
199 views

db2 cannot execute C-function for Reason code 4

I've implemented a function as C code for db2. The library name is called libmyfoo.so and is stored id /usr/local/lib as declared in the External declaration. When I try to execute this function I get ...
0
votes
0answers
295 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
0
votes
0answers
253 views

Stored procedure to replace a string in an entire database

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is: BEGIN declare t, c ...
0
votes
0answers
66 views

Procedure after commit

I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
0
votes
0answers
272 views

DBA_SCHEDULER_Job is not giving any result

I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering. select job_name,status from dba_scheduler_jobs; ...
0
votes
0answers
383 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
0answers
167 views

How to load XML data into an oracle db table ?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ? ...