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

0
votes
0answers
37 views

how to insert into multiple columns from one column in sql [duplicate]

suppose i have a column called [SpendPlanned] and [SpendGME] with data corresponding to its monthid column in such a fashion below say in 'x' table: i have only two columns[SpendPlanned] and ...
0
votes
1answer
59 views

Create SP for inserting data from a table to a table

I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the ...
0
votes
1answer
38 views

How to return a result set from oracle procedure ?

How can i return a result set from a stored procedure in oracle ? suppose i want to return a table from a simple select statement such as this : Select * from tblTest I tried this but this seems ...
0
votes
0answers
352 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 ...
2
votes
2answers
100 views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
0
votes
1answer
264 views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
1
vote
2answers
98 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
1
vote
2answers
235 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
0answers
138 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 ...
0
votes
2answers
216 views

create a stored procedure if it doesnt exist using a ddl in db2

My requirements are, I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it. What I tried is, IF EXISTS (select procname into ...
1
vote
2answers
92 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
210 views

Recursive calls in stored procedure and temporary tables

I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
1
vote
1answer
83 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. ...
0
votes
0answers
97 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
1answer
37 views

Error while creating procedure

This is my procedure: DELIMITER $$ CREATE PROCEDURE sp_test_final6() BEGIN INSERT INTO ...
-1
votes
1answer
69 views

My auto increment primary key is not automatically incrementing

I have the following database Whenever I try inserting values in the database I get the following error. Duplicate entry '0' for key 'PRIMARY' This means ID is not incrementing automatically. I ...
1
vote
1answer
435 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 ...
0
votes
1answer
159 views

“Access denied for user” error when creating a procedure

When I request my database server (MySQL), with "show grants for current_user" I notice that I have the grant to execute procedure: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK ...
4
votes
1answer
136 views

Is there a performance consideration if stored procedures are in a separate database than the source data?

This applies to a SQL Server 2012 instance. We are considering structuring things to where we have a database that contains our source data, and then separate database (on the same instance) that ...
2
votes
1answer
108 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 ...
1
vote
1answer
84 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, ...
0
votes
1answer
92 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
1
vote
1answer
612 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 ...
0
votes
0answers
34 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
43 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 ...
1
vote
0answers
112 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 ?
0
votes
1answer
82 views

Table Valued Parameter not pulling the correct data for my sp?

http://sqlfiddle.com/#!3/b2643/5/1 http://sqlfiddle.com/#!3/b2643/5/0 This link above has the sample database tables I created on SQLfiddle. I have 3 main tables in my stored procedure: ...
1
vote
1answer
91 views

Maintenance plan using external tool to execute query and stored procedures giving off no results

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ ...
0
votes
2answers
254 views

SQL Log Messages - FILESTREAM and xp_cmdshell

I was perusing the SQL logs today and found the following: FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'. Configuration option ...
-1
votes
1answer
105 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
1
vote
2answers
96 views

Is outputting to both a result set and a temporary table possible?

I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets. Here is a basic schema of what I ...
1
vote
0answers
72 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
3answers
354 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
0answers
93 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 ...
2
votes
1answer
96 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 ...
0
votes
1answer
1k views

Execute stored procedure in another server from sql agent job

I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2 select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]') Or EXEC ...
0
votes
1answer
534 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
2
votes
2answers
79 views

Stored Procedure Consolidation

I recently started working on a project using stored procedures from data vs. ORM's I haven't seen this project's procedure layout before and was wondering if it's ok / what would be better. Code ...
0
votes
1answer
69 views

Is this application creating stored procedures on the fly? [duplicate]

We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue. When looking at *sys.dm_exec_sql_text*, I ...
0
votes
1answer
367 views

Rollback parent procedure on failure in MySQL

I have the following two procedures on my server: CREATE DEFINER=`someone`@`localhost` PROCEDURE `NewMagnet`( IN `nick` VARCHAR(32), IN `tth` CHAR(39), IN `name` TINYTEXT, IN ...
0
votes
0answers
413 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 ...
3
votes
2answers
687 views

How to Log Debug Information About a Stored Procedure

We have a group of fairly complex stored procedures that process a transaction queue on a pretty much round the clock schedule. Currently these scripts use a number of print statements to document ...
1
vote
1answer
2k views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
1
vote
1answer
449 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 ...
0
votes
0answers
173 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
1answer
339 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 ...
1
vote
0answers
333 views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
0
votes
1answer
76 views

Stored Procedure with ROW instead of a list of parameters

i have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement. i would like to know if it is possible to declare ...
1
vote
4answers
125 views

Oracle How to find Debug Compiled Stored Procedures/Functions

How can I get list of stored procedures/Function which are compiled for debug? I do not see relevant column in following tables. DBA_PROCEDURES DBA_OBJECTS