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
113 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
75 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
60 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
40 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
705 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
5k 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
128 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
170 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
42 views

Query to find plan details for a specific stored procedure, searching by name

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
1
vote
0answers
10 views

Plpgsql function array plus replace

Please be indulgent to my question, 'cause I am newbee at plpgsql/sql. :) I need to do a replace in table and the insert 'replaced' data in the same table. How to replace some values using array with ...
1
vote
0answers
37 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
50 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
42 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
31 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
323 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
60 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
392 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 /tmp/exportcat/...
1
vote
0answers
813 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
59 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
33 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
268 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 command)....
1
vote
0answers
283 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
357 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
160 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
254 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
657 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
2 views

How can I log an error from a MariaDB scheduled event?

I've written a lot of Oracle PL/SQL, but I'm relatively new to MariaDB. My code looks like this: drop event if exists scs_system.persistent_jobs_processor; delimiter // create event scs_system....
0
votes
0answers
4 views

postgres: How to skip the fist and the last row of a queryset in a for-loop?

In a function I'm looping over the all rows a select statement returns. I want to skip the fist and the last row. How could I do this? ... FOR arow IN SELECT (dp).path[1] As index, ...
0
votes
0answers
10 views

SQL Sum column til value and then increment group and continue summation

I've run across an interesting problem that I'm not sure how to tackle. I've been charged with finding a solution to the following scenario, and I'm not really sure where to go with it. Here is the ...
0
votes
0answers
8 views

postgres create a view with data transformation rules

I was hoping someone could point me to a resource that can help me solve my problem. I have a largish dataset (25 Million rows), and I need to export the table based on a set of rules. 6 rules to ...
0
votes
0answers
20 views

How to return columns when using Dynamic Sql

In my project, i want to search input values and i think i should create a SP using dynamic Sql. When i call this SP in MVC, it return "int".I want it return columns as ProductName,ProductCat ... . ...
0
votes
0answers
10 views

Security of SQL Trigger Using dynamically stored table name

I'm working with PostgreSQL 9.0 and am learning how to use Triggers to enable some useful functionality. I have an insert trigger on a table that looks like this: feature_id | layer_name| latitude | ...
0
votes
0answers
19 views

How to delete/disable Extended SQL functions or turn off extended stored procedures in postgres

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure how to implement: Extended SQL functions ...
0
votes
0answers
29 views

Stored procedure generating dynamic SQL is slow for some paramaeters

I'm having issue with a stored procedure that generates dynamic SQL, it performs ok for some parameters but very slow for some parameters. This has been an issue everyday and we are making some ...
0
votes
0answers
48 views

DB2 While Loops

I have written some SQL for MS SQL Server that does what I want it to do. DECLARE @UpdateCount INT = 1; DECLARE @BatchSize INT = 1000; WHILE @UpdateCount > 0 BEGIN UPDATE tb SET tb....
0
votes
0answers
39 views

Export Stored Procedure Results To CSV

I have seen many options for doing such, however most of them involve using bcp or using exec master..xp_cmdshell which I am unable to use on my server (security restrictions). I was looking at ...
0
votes
0answers
15 views

Recursive Conditional trigger for insert in table MYSQL

user[ user_id, parent_user_id] user_id parent_user_id 1 null 2 1 3 1 4 1 5 1 user_assigned_role [user_assigned_role_id, role_id, ...
0
votes
0answers
32 views

Should I use mysql stored procedure or regular inline statement?

So here is the problem: I have been currently working on a project that would be handling millions of data and thousands of records would be inserted as well as retrieved at the same time. Before ...
0
votes
0answers
42 views

Mulit-part identifier could be found - stored procedure

Hopefully I can get some sight into to what's going, Apologies for my lack of terminology and poor TSQL skills, I'm very much a developer that's inside an IDE and using ORM's. So I took the plunge to ...
0
votes
0answers
15 views

Track updated columns postgresql trigger?

I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables. Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
0
votes
0answers
17 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
62 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
16 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
19 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 ...