Tag Info

New answers tagged

0

The answer to my specific instance of this issue was permissions. The DBA had to add datareader rights to the system schema on master for my user profile. Once this was done, everything worked as intended. He tried adding dbo owner rights, and granting view definition to just sys.procedures and that did not work. The bizarre symptom is that I could still ...


0

If you implement my stored procedures: Map GetFamilyTree against the Answers table Run GetFamilyTree Create IN() list from output of GetFamilyTree Using IN() list, join it back to Answers and Questions. Give it a Try !!!


5

To ensure you can see the objects you are looking for: Make sure you are in the right database / right instance - seems simple and silly but it happens all the time. This also includes not using things like AttachDbFileName in your connection string - this means that two different programs will actually have two separate copies of your database, so if one ...


2

I did a little bit more research and found the answer to the problem. Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text'). The actual parameter names for stored procedures can ...


0

That sounds like it is using an inappropriate query plan. There are two obvious paths this query could take and it is presumably deciding between them depending on the values of @row_offset and @row_count: for a small offset it is probably better to scan SomeCol,SomeOtherCol in order and apply the complex filtering clauses (in the WHERE and JOIN ON clauses) ...


0

Check the indexes on the query that is running slowly. Specifically, look for indexes on the columns used in your WHERE clauses, as well as indexes on your Foreign Keys used in your JOINS. By default MySQL should index the Foreign Keys automatically (on InnoDB), but if those indexes are missing you might be doing a table scan on your JOIN, which would ...


1

That depends on what else happens in the transaction. With the for share there, nobody can update the selected get_offer_data row until the transaction commits or rolls back. Without the context, it's really impossible to say what the original intended purpose of adding the lock there was.


4

I don't think it ever is (unless you are stuck in SQL Server 2000 and are looking for pity - don't worry, you'll have it). I would opt for statement-level OPTION (RECOMPILE) every time. I'll quote the relevant bit from Paul White's blog post, Parameter Sniffing, Embedding, and the RECOMPILE Options: When a parameter-sensitivity problem is encountered, a ...


1

First, drop the triggers you have. There is absolutely no reason to be doing this with multiple triggers. Then: CREATE TRIGGER dbo.FlightInput ON dbo.Reservation AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; UPDATE r SET Depature_Airport = CASE i.flight_code WHEN 'EM0088' THEN 'LDN' WHEN 'MH0007' THEN 'something else' END, ...


1

Your trigger is designed to only fire after an insert not an update. Try changing the code to:- create trigger flightinput2 on reservation AFTER INSERT, UPDATE as begin update reservation set Departure_Airport='LDN',Arrival_Airport='KUL' where Flight_Code='EM0088' end EDIT:- As pointed out, the code above will update all records within the database ...


0

If you want to dump ONLY one procedure from a database, this line should do the trick. It is not the mysqldump utility, but the mysql interactive application itself. mysql --user=root mydb -e "SHOW CREATE PROCEDURE myprocedure;" > myprocedure.sql Here you can read my full answer for Windows and batch: http://stackoverflow.com/a/25629708/488117


2

You only need one set: update reservation set destination=@destination, Flight_code=@flight where ID=@ID This is outlined pretty well in the documentation.


0

Looks like a case of Parameter Sniffing! Check google on it. Running these commands will have the same effect: DBCC FREEPROCCACHE This command removes all of the cached query plans and execution contexts from the plan cache. It is not advisable to run this command on a production server because it can adversely affect performance of running applications. ...


5

Cursors are loops, but they can be more complex for the database engine to implement depending on the options you chose. There are many options for cursors in TSQL that can have a significant impact on performance depending on the SELECT that feeds the cursor with information (see the TSQL docs for detail). The options will change what locks and other ...


3

Your solution, although a WHILEloop, is essentially still a cursor in the sense that you loop over a number of records and execute one or more statements once for each record. It's not the WHILE loop in itself, but rather the coding pattern where you loop over rows in a table and execute a statement for each row. Typically, "traditional" programming ...


1

Syntax issue in the CREATE OR REPLACE. I've not written any procs with this notation so for me it rang alarm bells. I checked both with and without and dropping the 'OR REPLACE' allowed the proc to be created. You can 'DROP PROCEDURE' in advance of creating is for the same effect. Try with; DELIMITER // drop procedure if exists getEmpInfo1; create ...


2

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then. As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL: Compatibility Levels and Stored Procedures When a ...


1

I use modules in two projects I have developed. With modules, I can organize the code more easily, and hide routines from the users. Also, modules provide me a way to have a standard name for the module, and the schema provide the version (https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/appupgrade?lang=en_us) ...



Top 50 recent answers are included