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
415 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 ...
4
votes
1answer
2k views

How can I create a stored procedure using xp_fixeddrives?

I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008 ALTER PROCEDURE freespace AS ...
1
vote
1answer
712 views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
8
votes
3answers
1k views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...
0
votes
1answer
1k 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 ...
2
votes
2answers
2k views

Event Scheduler: No data - zero rows fetched, selected, or processed?

MySQL 5.5.28 The error log keeps getting this error: 121004 12:08:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r ows fetched, selected, or ...
24
votes
4answers
2k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
2
votes
0answers
211 views

Stored procedure doesn't return results or any messages when executed as other privileged user [closed]

I have many stored procedures with... EXECUTE permission given to role XXXUser. Login XXXLogin is created and assigned to the role of XXXUser There is one particular stored procedure that I have ...
4
votes
1answer
423 views

Detecting if bit set in numeric value

I have a tinyint column that represents a set of flags. Assuming I wanted to know if a specific bit was set or not, how can I do bitwise AND/OR in a stored procedure?
1
vote
1answer
148 views

Access Drizzle databases through MySQL

This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot ...
3
votes
1answer
3k views

mysql.proc keeps crashing, cannot do a mysqldump?

Due to some problems with InnoDB, I'm going to dump all databases to a new server: mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server The dump process stopped with ...
3
votes
1answer
2k views

Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]

Possible Duplicate: Determining how a schema change occurred? I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to ...
1
vote
1answer
4k 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 ...
2
votes
1answer
118 views

How to know what to investigate and how for a VERY slow stored procedure

I have inherited a large and slow stored procedure and it's giving me a nightmare: I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck. I have ...
1
vote
1answer
1k views

Production SQL Server High CPU for a Single Query

I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008. I am not able to determine why the CPU is spiking for this single query. I can trigger an ...
0
votes
1answer
290 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 ...
26
votes
1answer
1k views

In SQL Server, what is the purpose of grouping stored procedures?

One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by ...
2
votes
2answers
391 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 ...
3
votes
1answer
790 views

Stored procedure to check for missing fields in a table

I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... ...
4
votes
2answers
18k views

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
5
votes
2answers
132 views

Will a SP which writes records fail if accessed by a db_datareader role?

I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that. Recently there ...
4
votes
1answer
91 views

Is it possible to encapsulate a table in MySQL?

I am new to Databases. I want the following: UPDATEs in a database to happen only through a stored procedure The user does not have GRANT UPDATE The user has GRANT EXECUTE ON PROCEDURE I tried it ...
0
votes
0answers
148 views

How to load XML data into an oracle db table ?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ? ...
2
votes
1answer
2k 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 ...
5
votes
3answers
12k 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, ...
6
votes
1answer
455 views

CATCH block is triggered when it should not

On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
11
votes
2answers
2k views

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: ...
0
votes
1answer
2k views

How to automatically regularly a week backup SQL Server database if changes exist in the database for the previous week

I´m new to SQL Server administration. I´d like to create automatically backup database every sunday night, if there was any change in previous week (if not, backup is unnecessary). Is there some way ...
6
votes
3answers
2k views

RETURN statement before DEALLOCATE CURSOR

Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure Consider this example: CREATE PROCEDURE [dbo].[test] @ReturnEarly BIT = 0 AS BEGIN SET NOCOUNT ...
6
votes
2answers
918 views

Why you want to avoid Dynamic SQL in stored procedure?

I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
3
votes
3answers
913 views

What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?

I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some ...
4
votes
1answer
6k views

Show procedure code in MySQL

I've tried to show the SQL code of a stored procedure called add_pixel: mysql> SHOW PROCEDURE CODE add_pixel; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ...
2
votes
2answers
521 views

Error on Oracle calling external procedures

I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it. the C source(libcprog.c) is like this: int exec_hmmftg ...
4
votes
1answer
785 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
150 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 ...
6
votes
2answers
595 views

SQL Server database : moving from 2008 to 2005 - encrypted procedure issue

I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move. Neither do I have older ...
8
votes
3answers
821 views

Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?

I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it. I am not a real DBA, I'm a software developer who is ...
2
votes
2answers
239 views

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures. I have the following tables setup. I normalized the tables to avoid using ...
3
votes
1answer
2k views

Function/procedure to use dblink to fetch remote data and insert to multiple local tables

Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table. But now I was thinking of creating a ...
2
votes
2answers
846 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 ...
3
votes
1answer
108 views

can sql generate a good plan for this procedure?

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
10
votes
1answer
8k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
1
vote
1answer
665 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
2
votes
1answer
8k views

MySQL user permission on stored procedure

I've created a simple stored procedure: mysql> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected, 1 ...
1
vote
0answers
394 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
0
votes
1answer
528 views

Need a stored procedure to check and update row when it does not already exists.

Need a stored procedure that will check and then update the rows (with username & email) except the id (auto incremented with a unique one). For example, if id = 5 has, the username abc and the ...
1
vote
1answer
572 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 ...
4
votes
2answers
9k views

Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all ...
6
votes
3answers
2k 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 ...
1
vote
1answer
659 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 ...