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
60 views
+50

Cannot execute as the server principal

I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user. I found this article which ...
1
vote
1answer
71 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
41 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
51 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
947 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
267 views

Cannot grant execute to stored procedure

When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below: Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5 Arithmetic overflow error ...
3
votes
0answers
239 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
70 views

Stored Procedure slow after weekend

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures ...
2
votes
0answers
98 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
156 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
216 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
131 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
78 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
98 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
244 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
118 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
762 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
131 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
441 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
838 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
18 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
25 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
18 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
12 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
21 views

Using 2 order by in procedure

I need to use 2 order by in my procedure. When I use only the second order by is working and not the first one. What mistake am I doing? Here is my procedure BEGIN SELECT sd.ShopID, ...
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
18 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
44 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
31 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
315 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
49 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
746 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
123 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
26 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
37 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
48 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
510 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
212 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
73 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
49 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
153 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
265 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
305 views

Does Netezza have functionality that is equivalent to a trigger?

Does Netezza have a feature that is similar to a trigger in SQL Server or Oracle? Right now we are using Netezza as a data warehousing database, but we would like to perform some audits and ...
0
votes
0answers
226 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
64 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
216 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
319 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
160 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 ? ...