Tag Info

New answers tagged

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 ...


2

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) ...


6

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause. CREATE VIEW tablesviews AS SELECT columns, 'TABLE1' as tablename FROM Table1 UNION ALL SELECT columns, 'TABLE2' as tablename FROM Table2 ... ...


2

one way i could think of is below.. create proc dynamictable ( @name varchar(100) ) as begin if @name ='tablename1' begin select columns from tablename1 end if@name='tablename2' begin select columns from tablename2 end end


1

Stored Procedures are physically stored in the mysql.proc table as a MyISAM table Simply copy /var/lib/mysql/mysql/proc.frm /var/lib/mysql/mysql/proc.MYD /var/lib/mysql/mysql/proc.MYI If the new installation is the same major version of mysql, copying should do it for you It would be safer to go back to the old setup, start mysql, and dump the stored ...


0

In DB2 9.5 for LUW, create or replace does not work. It is added in 9.7. For 9.5+ compatibility, which my shop still has to maintain, I have created the following approach. Essentially, I create a utility stored procedure that will determine whether or not the other stored procedure exists or not, and then drops it conditionally so no error messages will ...


0

Look here for a possible answer to this question. prevent sql injection inside stored procedure But the answer there is also a follow-up question.


0

by experience sometimes creating oracle tables by using ODBC produce errors. so please try to do the following: create the table manually on oracle db by using sql plus. try to use capital letters for table and column names without double quotes (example: use TABLE_NAME not table_name or Table_Name) try to run your application to check if any errors ...


1

You could just use the SQL statement: SELECT sum(out_nb_comm) AS sum_out_nb_comm FROM count_comm($in_id_alias); Or, if you want to wrap it in a function, a simple SQL function does the job: CREATE OR REPLACE FUNCTION sum_count_comm(in_id_alias int) RETURNS int AS $func$ SELECT sum(out_nb_comm)::int FROM count_comm($1)); $func$ LANGUAGE sql;


2

Start out with getting all the procedures from sys.procedures and then use EXEC sp_helptext on each to load the text of the procedures. Search for text to add, add if needed, load the updated text to a variable and execute dynamically.


-1

Your code looks like a good candidate for a recursive Common Table Expression (CTE). There are lots of examples out there, but here are a couple to start with: CodeProject Technet They can be a good way to work with hierarchies like manager -> employees, or in your case, Master -> Slaves.


1

You want to increase the speed of your cursors? Wrap them in a transaction. If you are processing millions of records and don't want/need them all in one transaction, you can commit it on occasion to reduce resources. I did this with a cursor that took an hour to run (this is an extreme case) and afterword it ran in 1 1/2 minutes. I know that does not ...


2

So, I found a workaround... First I made a proxy function on which the first proxy function will run : CREATE OR REPLACE FUNCTION p_count_comm( in_id_alias INTEGER ) RETURNS INTEGER AS $func$ BEGIN RETURN (SELECT sum(tmp_nb_commentaire) out_nb_commentaire FROM count_comm(in_id_alias)); END; $func$ LANGUAGE plpgsql; And I modified my first ...


0

the script I use will show you all objects (SP, Tables, Functions) for a specified Database name and all the users that have rights on them, but you can narrow the search and extract exactly what you need. DECLARE @EXEC_SCRIPT NVARCHAR(4000) DECLARE @DBNAME NVARCHAR(256) = 'DB name' SET @EXEC_SCRIPT = 'USE ['+ @DBNAME + '] select DB_NAME () ...


0

I wrote a script a while ago, that does just this. I've posted it on my blog, http://sqlsunday.com/downloads/. Remember that a Windows user can be a member of a Windows group, and in SQL Server, you can't see those memberships, so you'll have to look at Windows users and Windows groups separately, if you're doing a security audit, for instance. If you want ...


0

Off the top of my head (no MSSQL boxes up at the moment, sorry): EXECUTE AS <database user> ; SELECT * FROM sys.objects WHERE type = 'u' ; Metadata visibility means that if a database user queries sys.objects then they will only see the objects they have permissions to access or that they own. If you require the actual permissions they have on ...


2

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows. I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem). CREATE ...


1

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse. Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. ...


0

Yes, 'W' will do that -- replace the file. Use 'A' to append. I don't think you can "modify in place"; you might have to copy the file to a new one making the changes you desire, delete the original and then rename the copy. All of this begs the question why you're trying to do all of this in PL/SQL. It might be better -- certainly easier -- to do it in an ...


0

here is a solution how I fixed this: 1) Add "mysql" database to the user, you are logged in with Advice: now you can alter functions and procedures 2) Add the global privilege "SUPER" to your user Advice: otherwise you will get the following error if you save the procedure/function: "ERROR 1227: Access denied; you need (at least one of) the SUPER ...


1

I won't be able to answer all your questions, but for what it's worth: SQL modules seem to be a part of the SQL/PSM 2008 standard. At least they appear in the draft version, that can be found on the interwebs. Tools will eventually catch up with the server features. Meanwhile, you can use the Oracle-compatible PL/SQL dialect, currently supported by tools, ...


1

To answer your question; WHY? You probably already know this by now since the post is 2 years old. But I'll respond just for the record. The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the ...



Top 50 recent answers are included