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
2answers
1k views

Issues with module signing and SSIS catalog internal procedures

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
2answers
1k views

create table with date name

I am writing a simple procedure to backup a table- CREATE PROCEDURE daily_backup() BEGIN DECLARE given_date VARCHAR(25); SET given_date = now(); CREATE TABLE given_date LIKE db1.table1; ...
1
vote
1answer
1k views

Cannot `create function` in plpython3u, permission denied

As postgres user, I have create extension plpython3u; in my database then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true but ...
1
vote
1answer
692 views

How to split the comma separated list in the stored procedure

I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
1
vote
1answer
90 views

SQL Server many active create procedure processes

While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures. Is this ...
1
vote
2answers
3k views

Print database_size from sp_spaceused

Using SQL Server 2008, I have found that the database size can be obtained using exec sp_spaceused This procedure returns two tables: database_name | database_size | unallocated space reserved | ...
1
vote
1answer
49 views

stored procedure vs. function in postgresql and which is better for executing dml statements

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure? My basic steps are: RENAME TABLE ...
1
vote
2answers
264 views

Stored procedure - problem with merge - Insert

I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct? Any help is appreciated. Here is ...
1
vote
4answers
2k views

Execute a Stored Procedure Daily

I am building a Web Application which is attached to a Database which is in a Web Server(which is at unknown remote location). I want to execute a Stored Procedure daily at a specific time. I searched ...
1
vote
2answers
1k views

multiple select query for the same tables with union [closed]

Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. ...
1
vote
1answer
2k views

column auto updated after 24 hours in mysql

i have a table T1 with column : Name State time mon 1 2014-01-09 11:23:00 tue 0 2014-01-07 14:40:00 wed 2 2014-01-08 09:23:00 thu ...
1
vote
1answer
6k views

Stored Procedure Performance using Temporary Tables

I have the following Stored Procedure I would like to improve the performance of DELIMITER $$ CREATE PROCEDURE NEXT_UNIT_OF_WORK ( IN batchId INT, IN size INT ) BEGIN start transaction; ...
1
vote
2answers
717 views

From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads

I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0. Now I am using features of SQL Server like: Common Table Expressions (I have a table ...
1
vote
1answer
813 views

How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008 I have a table that I am trying to use as a storage container. It has 3 columns: reportID integer, dataLabel ...
1
vote
1answer
142 views

oracle how to call procedure

This is very basic question, but I couldn't find an answer here... So I have this very useful procedure without any parameters: CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS IS v_statement ...
1
vote
1answer
160 views

transactional Replication of millions of updates

I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates ...
1
vote
1answer
2k views

Executing a stored procedure on remote database

I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in ...
1
vote
2answers
3k views

SSMS: Show execution plan for a stored procedure as a single tree

I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan. MSDN states the ...
1
vote
1answer
87 views

MySQL: Why is inserting data with transactions faster then without them

I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200") The stored procedure includes REPEAT loop, in ...
1
vote
3answers
3k views

How to create a SQL trigger to execute a stored procedure when a table is updated

i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an ...
1
vote
1answer
411 views

SQL Server stored procedure causing 100% CPU usage

I'm using SQL Server 2008 R2 We have a single stored procedure that causes 100% CPU usage when ran. The said SP is ran once a month and performs heavy calculation for approximately 5 minutes. Since ...
1
vote
1answer
46 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...
1
vote
3answers
725 views

Changing compatibility level of database causes performance improvement of stored procedure

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has ...
1
vote
3answers
786 views

best practice for 'staging' relation in stored procedure: use temp table, or view?

When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming ...
1
vote
2answers
439 views

Is it possible to define package procedure/function outside of package body block (for Oracle)?

Is it possible to define package procedure/function outside of package body block (for Oracle)? Simplified syntax for defining package body is: create or replace package body <package_name> IS ...
1
vote
2answers
2k views

Using MySQL EXPLAIN to analyze the performance of a function

You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
1
vote
3answers
2k views

SELECT Data Set Using Stored Procedure with BCP Error

All, I have a BCP export query. It is failing with the useless error mesage on BCP usage: usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f ...
1
vote
1answer
164 views

Stored procedures and network congestion

When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it ...
1
vote
1answer
8k views

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

What is the syntax for a stored procedure that outputs a refcursor, a varchar2, and a number? I would like to output all three in the result, for the purpose of displaying count, and a message which ...
1
vote
1answer
779 views

How can I trigger this stored procedure on insert?

I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous ...
1
vote
1answer
993 views

Merge multiple table output but indicate which tables the records came from

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not ...
1
vote
1answer
4k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
1
vote
2answers
2k views

Is there a way to find out how many times a stored procedure was invoked since last DB start

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably ...
1
vote
1answer
280 views

What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?

I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it. the system will have a asp.net UI, web services middle tier and SQL2005 back end. ...
1
vote
1answer
38 views

Setting query to run as a stored procedure and email the results [closed]

I have the following query: select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections' , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL ...
1
vote
1answer
34 views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
1
vote
1answer
37 views

Unable to update table after creating an After update trigger

I have a table like this /*Table: pageprivilege*/ ------------------------ /*Column Information*/ ---------------------- FIELD TYPE COLLATION NULL KEY DEFAULT Extra ...
1
vote
3answers
89 views

How to store a query result into a variable? SQL Server 2008

I have two tables and I want to store the result query of my stored procedure into a variable. UserTable: UserID int primary key identity CredentialsID int AccountType nvarchar(15) ModifiedDate date ...
1
vote
1answer
57 views

Executed a Stored Procedure in the Production Server and the following warning message is shown [closed]

I executed a stored procedure on the production server; and though it shows that query has been successfully completed it doesn't allow me to close SQL Server Management Studio and the below warning ...
1
vote
2answers
115 views

Is attaching a database on the same server under a different database name possible?

I recently offered to help a friend with some development. She isn't a technical person and it turned out what she needed was more a DBA/SQL developer. She gave me the IP and password to this server. ...
1
vote
1answer
204 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
1
vote
1answer
481 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
1
vote
1answer
1k 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 ...
1
vote
2answers
196 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
1answer
445 views

Table Variable and Database Scope

I am a junior DBA, and I am still confused by database scope. Now I am learning about local/global temp table and table variables. I saw an example as following: declare @T1 as table ( col1 ...
1
vote
2answers
245 views

SQL Server Login to access tables of three different database from single database

I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which ...
1
vote
1answer
253 views

Table variable not Recognized in stored procedure

The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. ...
1
vote
1answer
2k views

Stored Procedure to evaluate column if condition is true execute another stored procedure

I have a stored procedure that executes with Windows task manager and I want to add a condition statement that executes another stored procedure if the condition is true. I was thinking a case ...
1
vote
1answer
1k views

Is there anyway to make this stored procedure thread safe?

I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each ...
1
vote
1answer
264 views

MySQL: Why am I getting this warning?

I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe? 1 queries executed, 1 success, 0 errors, 1 warnings Query: call ...