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

2
votes
1answer
520 views

Most efficient way to insert rows into a temp table in a stored procedure

I am working in SQL Server 2008. I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it ...
2
votes
1answer
103 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
2
votes
2answers
993 views

Pass parameters to a procedure with SQLcmd

I use this SQL command: sqlcmd -v first_date="30/09/2015" last_date="15/09/2016" And I would like to pass that parameters to a stored procedure: ALTER PROCEDURE [dbo].[_testProcedure] WITH ...
2
votes
1answer
110 views

How can I pass object to procedure from trigger? [closed]

I am using mysql 5.6. After insert or update, inside the trigger I am calling procedure, So how can i pass NEW and OLD object from trigger to procedure ?
2
votes
1answer
3k views

How to update a table with a stored procedure with XML parameter in SQL 2008?

I have the following Stored Procedure query. This query is incomplete. I need help completing this query. Problem: In this SP, I am receiving a well-formed XML string into the SP. My goal is to ...
2
votes
1answer
720 views

How to ignore sql errors in stored procedure ? (Not handle)

I have a list of records I want to process and the cursor iterates through them from a table. The procedure just exits when it hits an insert statement and it fails. Is there a way I can ignore the ...
2
votes
1answer
320 views

Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Background I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements. The search queries the database restricting the data to a single month. Most ...
2
votes
1answer
2k views

How to write a procedure /job to kill all session running for more than 5mins in Oracle?

How to write a procedure /job to kill all session running for more than 5mins in Oracle? So, I query v$session and find out that for a particular user, program pair if the session has been active for ...
2
votes
1answer
260 views

Query returning unused stored procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries. When I run the following query, the results show the ...
2
votes
1answer
4k 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 ...
2
votes
1answer
185 views

Can comments in stored procedures cause problems

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
2
votes
2answers
138 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
2
votes
1answer
13k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> ...
2
votes
1answer
2k views

Oracle Packages, Procedures and Functions Not Respecting Roles

When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
2
votes
3answers
8k views

How to avoid using the IN clause in sql query used in a stored procedure

Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the ...
2
votes
2answers
910 views

How do I save functions to individual files in PostgreSQL?

I maintain a legacy application that uses a PostgreSQL database. The application is heavily dependent on stored procedures (aka functions). I want to save these functions to files named after the ...
2
votes
1answer
3k views

How to modify a stored procedure using SQLCMD for MS SQL?

Suppose a stored procedure as follows have been created using the Microsoft SQL Management Studio. use testbase go create procedure testtable_pricesmaller @pricelimit money as select * from ...
2
votes
1answer
276 views

stored procedure times out while subsequent runs take 1/6 the time

So I have this stored procedure which is running very slow (and timing out) in one particular production environment. This stored procedure produces an xml of profile data. In all other ...
2
votes
1answer
63 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
2
votes
1answer
59 views

Calling stored Procedure from one db to give me a result in another db

CREATE DEFINER=root@localhost PROCEDURE AccountReqs( IN IsNewAccountStart VARCHAR (255) , In AccountName varchar (255) , IN AccountNumber varchar(255) ...
2
votes
1answer
163 views

SELECT … INTO variable results in NULL or idk

you can find all the relevant code on pastebin. The problem: the variable to_spend stays default. If I leave the default 0, it is 0, if change it to NULL, it is NULL. So the statement if(to_spend ...
2
votes
1answer
304 views

How to get an array as output parameter from stored procedure?

This is my stored procedure: CREATE PROCEDURE `getAttritionData`( IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT ) DETERMINISTIC ...
2
votes
1answer
193 views

Periodically update a table with new records from another server

I have databases on two different servers. I need to regularly retrieve new records from a table on server A, and process them in order to use them to update a table on server B (with a different ...
2
votes
2answers
413 views

Sql Agent Jobs executes too slow

I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure USE [xxxx] GO /****** ...
2
votes
1answer
221 views

stored procedures vs inline SQL in a Job step SQL SERVER 2014

I have created a job with two steps. Step 1 deletes 50000 rows from an error table older than 90 days. Step 2 creates and emails a quick report on the number of new records created in the error ...
2
votes
1answer
878 views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
2
votes
1answer
648 views

SQL Server stored procedures fast in SQL but slow when called by ASP.NET

I am using ASP.NET with MVC3 with a SQL server database. From time to time, one of our stored procedures become incredibly slow when called by the web platform, but stay as fast when called directly ...
2
votes
1answer
4k 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; ...
2
votes
1answer
114 views

MySQL Stored Procedure Requiring SUPER

I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user ...
2
votes
1answer
287 views

How can I load 'utf8' into plperl by default?

I am attempting to solve a problem explained in another question here. On Debian 7.4 with Postgres 9.1.12, I am able to use the Perl qr operator just fine. On Solaris 5.11 with Postgres 9.2.4, ...
2
votes
2answers
16k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
2
votes
2answers
2k views

MySQL Workbench Alter Procedure

i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right ...
2
votes
1answer
3k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # ...
2
votes
1answer
942 views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
2
votes
2answers
1k views

Bulk grant execute permissions for scalar functions & stored procedures

We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
2
votes
1answer
1k views

performance issues when concurrent requests

We are using SQL Server 2008 R2 on a Windows Server 2008 server. We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it. I'll take one SP as an ...
2
votes
1answer
1k views

How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization. I currently have around 160 procedures and about 30 functions. I am using the select benchmark() to check the ...
2
votes
1answer
723 views

high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005

I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity) SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ...
2
votes
1answer
52 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
2
votes
0answers
33 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
2answers
101 views

SQL Job is running slow after a few days

I have a simple SQL job that runs a stored procedure that doesn't take any parameters. The job runs once a day and it normally takes less than 3 minutes to complete. What's odd is that this job runs ...
2
votes
0answers
33 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
24 views

Insufficient Prilivege for Function

I'm using a Function that I can't read, when I try to open it, I can't see the definition. I get following error Error : Insufficient privilege. Nevertheless, it is being executed by my app ...
2
votes
1answer
1k views

Get values from Select statement and Insert them in Temp table Stored procedure [closed]

Here is my stored procedure. I want to select data from a table, save it in a variable then insert in other variable. However, my select statement has where. In it the value for the day should come ...
2
votes
0answers
20 views

Sum of points and rank according to points with join three tables [duplicate]

I am creating a game where I am calculating the user points on the basis of user activities. Everything working fine I got the sum of points and on the basis of points I calculated the rank. I have ...
2
votes
1answer
112 views

MySQL stored procedure updates twice

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
2
votes
1answer
17 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
2
votes
0answers
597 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
1answer
165 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
2
votes
1answer
2k views

How to call stored procedure inside trigger?

I have made stored procedure that will update product quantity as per product id. That works fine for me. Following is code : DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE ...