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
2answers
407 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
2answers
2k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
0
votes
2answers
11k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
0
votes
1answer
498 views

Stored procedure arguments get immediately truncated

I have a stored procedure with a single SELECT statement inside: DROP PROCEDURE `pos_neg_test`// CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP) BEGIN SELECT ...
0
votes
2answers
335 views

Use event schedule and stored procedure to automatically average every hour MySQL

I have a database in Mysql Workbench about weather that updates every second. It contains temperature and wind speed. This is my database: CREATE TABLE `test`.`new_table` ( `id` INT(10) NOT NULL, ...
0
votes
1answer
59 views

Using of OPTIMIZE FOR hints in a sql query

as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Tabele WHERE param=@param1 We have two parameter S1 with high selectivity and s2 with low ...
0
votes
1answer
24 views

Can't cast a SPROC parameter?

I have several sprocs that take a date/time input, one of which takes a date only in the form of an nvarchar because it utilizes dynamic SQL. When I try to cast the variable going into the sproc I'm ...
0
votes
2answers
125 views

Stored Procedure never completes with Exec, but i can run the Query in SSMS

Im with a problem here. If i run "exec pr_BuscaLog -2', it will run forever ( days, without end )... BUT, If i select the Query and F5, it runs in seconds. I updated STATS but it didn't work. Some ...
0
votes
2answers
281 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
1answer
3k 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 ...
0
votes
1answer
514 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
2answers
157 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
0
votes
2answers
4k views

How can I change collation for all tables and columns in MySQL database?

I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, ...
0
votes
1answer
38 views

Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
0
votes
1answer
42 views

Getting error while installing log4db2 utility

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be ...
0
votes
2answers
107 views

Executing stored procedures asynchronously using tasks in SSIS

I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using ...
0
votes
1answer
186 views

PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

We are using a software that needs to request the max primary key column value to add a new row into the table that was requested. I know that there are some alternatives like: Sequences, and GUIDs ...
0
votes
1answer
266 views

EXEC Multiple procedures in parallel?

SQL Server 2008. I have several stored procedures. I would like to execute them all in parallel, not serially. How can I do this?
0
votes
1answer
279 views

SQL Server 2012: stored procedures permissions

Is it possible to control permissions on a single stored procedure level in SQL Server 2012? It seems that SQL Server Manager doesn't expose such functionality via GUI. Can somebody advice me how to ...
0
votes
1answer
83 views

Problem with procedure performance

I'm having problem in Oracle, where I have two procedures that are executed one after the other. Where the first procedure, I have a cursor that does insert into a table. This insert has about 30 ...
0
votes
2answers
82 views

How can i branch based on a columns value in oracle?

I am trying to have a dynamic inner join..on in an Oracle database. For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for ...
0
votes
2answers
53 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
0
votes
1answer
2k views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
1answer
2k 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
1answer
1k 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 ...
0
votes
2answers
926 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 ...
0
votes
1answer
309 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
0
votes
1answer
7k 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
88 views

An SQL Server 2008 R2 based program fails when updating stored procedures?

I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure. Is there any place in SQL Server 2008 R2 / Windows where I could see an error message ...
0
votes
1answer
351 views

Memory is not released after the execution stored procedure followed by a process end

I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after ...
0
votes
3answers
2k views

SQL Server, how to execute a job against all databases on server?

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan. I have a stored procedure that i'd like to run on these databases, but ...
0
votes
3answers
3k views

MySQL Continue Handler Problems inside a Function

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I ...
0
votes
1answer
133 views

Different Results between stored proc and udf

I am getting different results between my stored proc and my SVF. The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, ...
0
votes
1answer
1k views

Difference between return next and return record

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
0
votes
1answer
2k views

Calling stored procedure with select and update statement

I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like create procedure testproc as select ...
0
votes
2answers
6k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
0
votes
1answer
19 views

mysql error handling stored procedure doesn't catch error

NOTE: I also tried CONTINUE instead of EXIT with same results. I am running mysql 5.5 and am trying to create a stored procedure that sends a variable out based on if it supports full text. ...
0
votes
1answer
29 views

How to send an email with embedded values

I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100. ...
0
votes
1answer
61 views

Why is my PL/SQL procedure so slow?

I have this sub-procedure in a fairly large program, and it is taking hundreds of times longer than comparable sub-procedures. Is there any way I could improve efficiency here? None of this ...
0
votes
1answer
98 views

Simultaneous READ COMMITTED transactions updates a select for update columns

I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below CREATE ...
0
votes
2answers
65 views

Executing a script via Powershell possibility

Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it ...
0
votes
1answer
32 views

Working with variables in stored proc

I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow. My code looks like this: SET @table_ = ...
0
votes
1answer
44 views

How can I limit a user to only be able to run procs in a schema, which access another db?

In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything ...
0
votes
1answer
61 views

ORA-01722 Error when running stored procedure

I am trying to run a stored procedure however when I run it with an a valid Number it gives me an ORA-01722 error. I checked to make sure that the columns in the where clause where the parameter is ...
0
votes
1answer
213 views

SQL Server : execute stored procedure from another stored procedure

I have two stored procedures. Proc1 executes a SQL statement and results a data table as a result set. I need to use the datatable in a select SQL that resides in proc2 as a datasource. like, ...
0
votes
1answer
60 views

Call a stored procedure for every select result

I have an stored procedure to delete from a long series of tables. It uses one main ID as parameter. I have to delete a bunch of rows based on a view that show that lot of tables. I want to apply the ...
0
votes
2answers
88 views

Executing sp in ssms much faster than executing in job step

My stored procedure takes 23 secs in ssms but takes 4 minutes when exec via job. Can you please help me in finding out. Below is SP code ALTER procedure [ops].[trailers_sitting_builder_mod] --WITH ...
0
votes
1answer
147 views

“Incorrect Syntax near S” in Stored Procedure [closed]

I'm creating a SQL Server stored procedure and getting error Incorrect Syntax near S I'm not able to find the exact reason of the error. CREATE PROCEDURE RPT_IVR_CALL_SUMMARY ...
0
votes
1answer
273 views

Need help with my MySQL PROCEDURE #1193 - Unknown system variable

I've this simple table: CREATE TABLE IF NOT EXISTS `testing` ( `testing_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, `meter` INT NOT NULL, `price` DECIMAL(12,2) NOT NULL, ...
0
votes
1answer
32 views

Function to handles firstname and lastname to a specific format

I'm trying to figuring out a way to show a name properly. Let's say I have 2 variable : @Firstname = 'Marc-Oliver' @Lastname = 'Saint-Onge' I would like to have name shown as; M.-O. St.Onge Or ...