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
votes
0answers
25 views

dBASE III Update query slow for 1000 entries

How to run chain update command on dbase i'm using code below but performance is very slow. is there any way to update using sp's. private static void UpdateRdTable(string rdTableName, ...
0
votes
1answer
15 views

Is there any way to perform atomic updates of stored procedures in MySQL?

I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588). In our project ...
1
vote
0answers
13 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 ...
5
votes
4answers
119 views

How to check if Alter view/stored proc script has run successfully

For the database deployments, I call all the db schema changes in a batch file and run them on the target database. Sometimes we have scripts for altering the views or stored procs. I'm wondering ...
1
vote
2answers
50 views

Stored procedure to check if username exists

I'm trying to write a stored procedure So far I have this, but it always returns 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`(IN `USERNAME` VARCHAR(64) CHARSET utf8mb4) SELECT CASE ...
0
votes
2answers
374 views

AWS RDS MSSQL Extremely Slow compared to EC2 with MSSQL

Background - I built a tool which grabs a lot of data from multiple third party APIs and stores it in a MSSQL DB. Then, I built a visualization to display this data in tabular and chart format. ...
0
votes
0answers
18 views

Report not pulling records

Stored procedure working fine. Procedure contains 5 parameters and 30 columns. I am using this stored procedure in my SSRS report. When I click preview, it is asking for parameter values I just ...
0
votes
0answers
8 views

When will store procedure/functions support result set as return type

I have noticed that MySql doesn't support result-set on a store procedure and functions, which means return a table. So I can't do things like CALL sp_my_store_procedure() ORDER BY id ASC; Or ...
1
vote
1answer
137 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
1
vote
2answers
45 views

Stored procedure big where clause or multiple procedures

Is it better to have one big query or N small ones? For example to select all the customers with multiple parameters: select id, name, firstname, city, birthday, ... from ...
0
votes
1answer
30 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 ...
0
votes
1answer
2k 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. ...
0
votes
1answer
39 views

How to create a stored procedure with columns from three different tables in SQL Server 2008?

I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from ...
1
vote
3answers
173 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 ...
0
votes
1answer
218 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
1answer
25 views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
1
vote
1answer
522 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; ...
1
vote
1answer
122 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
262 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
5
votes
2answers
2k views

Find out what setting were used for a given stored procedure

As a follow-up to my previous question: is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ? It seems my ...
1
vote
1answer
141 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
0answers
47 views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
2
votes
2answers
87 views

Database-agnostic stored procedure call passing array of name/value pairs

Background Looking to pass a set of name/value pairs into a stored procedure in a database-agnostic way, using JDBC. A database structure is defined as follows: CREATE TYPE array_parameters AS ( ...
1
vote
1answer
138 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 ...
2
votes
2answers
10k 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( ...
0
votes
1answer
299 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
1answer
48 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
0
votes
0answers
22 views

i always get o for my result.why is it so?

CREATE DEFINER=`root`@`localhost` PROCEDURE `roomAvailbility`( IN checkindate date, IN checkoutdate date, out roomtype char(20)) BEGIN select count(*) INTO roomtype from room_1401557b ...
0
votes
2answers
410 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...
1
vote
1answer
1k views

PostgreSQL stored function that returns arbitrary resultset

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
0
votes
2answers
58 views

Sql Server Stored procedure exicution taken long time

I have worked in a web based project, in my code sometimes i want to execute a stored procedure more than 1000 times in a single button click. The button is used for some type of approval process and ...
0
votes
1answer
58 views

Insert/Update SQL Server Stored Procedure

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and ...
2
votes
1answer
51 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 ...
0
votes
2answers
76 views

pg_stat_activity - is it possible to decompose a stored procedure further?

I got a long running query in pg_stat_activity: datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | ...
0
votes
1answer
117 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
6
votes
4answers
3k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
2
votes
1answer
50 views

Stored procedure with dynamic sql. Is it isolation safe by default?

I would appreciate confirmation on the answer I found digging through T-SQL's documentation. I have a stored procedure that references a view twice. Once in the procedure and then a second time when ...
1
vote
1answer
2k 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
1answer
18 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 ...
1
vote
0answers
23 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
2answers
86 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 ...
0
votes
1answer
42 views

Improving lots of Left Joins ( SQL Server 2008 - procedure )

This query is running with 8 minutes to complete. I'm trying to help the programmer with this task. I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us. ...
5
votes
3answers
20k views

"Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
3
votes
2answers
97 views

SQL Server stored procedure validity checking [closed]

How can I check the validity of a stored procedure programmatically? Meaning that if no errors are found it will execute. How can I detect things like invalid tables names in "from" or "join" clauses? ...
0
votes
2answers
163 views

Reviewing Stored Procedure template for handling transaction?

I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction. I have a stored procedure to log error in catch block in a table. How can I improve my ...
2
votes
1answer
140 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 ...
4
votes
1answer
208 views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL db. If the row already exists in the table, we update that ...
15
votes
4answers
1k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
3
votes
1answer
142 views

Stored Procedure to update an adjacency Model to Nested Sets Model

If you don't already know, these two models are the most common ways to store a tree in a relational DB. Adjacency Model: +-------------+----------------------+--------+ | category_id | name ...
-1
votes
2answers
88 views

Update code works in manual stored procedure but not when run as SQL Server Agent Job

I have a stored procedure that deletes all records from an existing table, and then inserts new records. After the insert I have several update operations that change the value of one field. If I run ...