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

3
votes
0answers
91 views

Read/Write Deadlock on Ms-Sql Server

We are facing deadlocks when store procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of sp. I have ...
3
votes
0answers
71 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
2
votes
0answers
43 views

Mysql stored procedure SQLEXCEPTION

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id. insert into folder (name)values("new"); update folder set count = count + ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
2
votes
0answers
668 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 ...
2
votes
0answers
4k views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
2
votes
0answers
127 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, ...
2
votes
0answers
166 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
30 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
1
vote
0answers
34 views

DB2 : Dynamic Result Sets, Duplication in Sending output to File

What I am trying to do here is to Generate insert scripts for selected tables in my database in DB2 10.5 LUW. I have modified a procedure available here as below. CREATE PROCEDURE Create_Inserts ( ...
1
vote
0answers
41 views

Stored procedure not running on the same database when using different login

I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. ...
1
vote
0answers
46 views

MySql - view out params of stored procedure when it selects from table

I have stored procedure: DELIMITER // CREATE PROCEDURE prcGetFemaleUsers() begin select firstName, lastName from tblUser where gender = 'female'; end // DELIMITER ; How can I get ...
1
vote
0answers
29 views

Oracle 11g XE - Giving granting privileges to a stored procedure

I need to use a stored procedure to give certain privilges over a view to users. However I don't know how to give the procedure the necessary privileges to grant users privileges.
1
vote
0answers
12 views

Adding new sproc to the exisiting database using connectors?

The project I'm currently working on is mostly pythonic, by this reason I'll likely use mysqldb python connector or sqlalchemy. The question arose when I've tried to find out the way of adding new ...
1
vote
0answers
290 views

Why my sql query run faster than my stored procedure?

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the ...
1
vote
0answers
55 views

Read show create table in mysql stored procedure

How can I read the output of following query and assign to a stored procedure's variable? SHOW CREATE TABLE users; Or, is there is an information schema from where I can draw the full table ...
1
vote
0answers
312 views

Return both Result set AND a warning from a stored procedure

In Data Studio when you call SYSPROC.ADMIN_CMD() directly and it returns a warning, the results panel shows both the warning and the result set: CALL SYSPROC.ADMIN_CMD('EXPORT TO ...
1
vote
0answers
680 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
1
vote
0answers
55 views

Is there a way to call sp_spaceused in a WHILE loop?

A legacy Sybase script moves records from production to archive in 1000-row batches, but it does not check for space exhaustion on the archive. Is there a way, maybe by calling sp_spaceused, to ...
1
vote
0answers
32 views

TSQL Stored Procedure - Granting general user access to specific sproc

I'm attempting to alter a sproc that needs the line added : grant exec on dbo.sproc_name to exec_proc. I believe the admin setup the exec_proc as a group. I added this grant line following the final ...
1
vote
0answers
246 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
1k 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
271 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
327 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
155 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
2k 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 ...
1
vote
0answers
242 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

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
646 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
2k 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
15 views

Is there a way to reference a column dynamically within a trigger?

I asked a similar question a few months back and I'm improving upon my solution! I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of ...
0
votes
0answers
10 views

sp_procoption seems to be failing on SQL 2005 instance

We have a SQL script that creates a Stored Procedure which is responsible for creating a SQL trace. On SQL Server 2008, the script runs fine, the SP gets created and the SP is configured to ...
0
votes
0answers
28 views

Sql Server Stored Procedure calling table-valued function - Performance

I'm using Sql Server 2012 and I have a stored procedure that does about 15 left joins and one of those joins is a CROSS APPLY to a table-valued function. The first 14 left joins are not gonna restrict ...
0
votes
0answers
13 views

How to point View to a dynamic database

I would like to create a view upon a database which changes on time For Example: initially, a view layer is to be created for the database B1_2016 in the next refresh, a new database is created and ...
0
votes
0answers
16 views

Is it possible to call java API from mysql stored procedure?

I am using stored procedure to migrate data from one schema to another schema. I need to use some complex logic which is already implemented as java API. Is it possible to call java API from mysql ...
0
votes
0answers
18 views

Is there an alternative to delete a procedure inside a procedure?

For my Bachelor Thesis I try to automate some recurring tasks in MySQL. One task consists of the deletion of a specific stored procedure. Normally I would create a stored procedure which takes one ...
0
votes
0answers
34 views

Syncing DB's between local and live server?

I need to sync two tables between MySQL databases. I have read about MySql Replication. But not sure whether it can be used in this scenario. What I want to achieve is explained below. There are two ...
0
votes
0answers
14 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the ...
0
votes
0answers
110 views

Change MySQL stored procedure 'Database Collation' name

I have imported a DB from Local machine to server machine. While importing the Database, the character set values of the DB are set by system default to "Latin". I have changed the character set to ...
0
votes
0answers
21 views

calling multiple procedures from my application Vs calling procedures inside another procedure

I have a scenario of saving user details into my database(MySQL 5.1); i have three tables for saving user details; user_data,user_contact,user_address, so if i insert one user data i have to check for ...
0
votes
0answers
38 views

MySQL performance affected by calling functions from inside procedures?

this is the very first question I post, so please bear with me ... When working on a project, I tried to get the LAST_INSERT_ID for a table, by invoking it from inside a procedure right after an ...
0
votes
0answers
57 views

MySQL prepared statement error

Good afternoon, I'm trying to use a prepared statement within a stored procedure in MySQL, but when I attempt to link the variable I get a syntax error. Here is the code SET _sql = ...
0
votes
0answers
29 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
0
votes
0answers
49 views

MySQL procedure with sys_eval (mysqludf) called by trigger

I'm executing a trigger after a row in a certain table has been updated. That trigger calls another procedure which is supposed to do some quick checks before executing a small pythonscript. If the ...
0
votes
0answers
90 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
15 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
0
votes
0answers
75 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
0answers
96 views

Postgres asynchronous procedure execution?

I have an ETL script entirely contained within a Postgres 9.3 procedure. The script takes several hours to process, which is fine for our purposes. What I haven't been able to find out is if there ...
0
votes
0answers
34 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...
0
votes
0answers
56 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...