Tag Info

New answers tagged

5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns. My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.


0

The problem was my select query was returning more than one row, and my code was not handling them via a "loop" so it was not working and giving me errors. This is my new Procedure... BEGIN DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler. DECLARE dateTime datetime DEFAULT NOW(); DECLARE dateTimeCounter ...


0

I believe I have found the solution, and that is to convert the Procedure to a Function. DELIMITER $$ CREATE FUNCTION fn_get_item_type_id( item_name varchar(45) ) RETURNS INT DETERMINISTIC RETURN (SELECT CASE WHEN LEFT(`item_name`,6) = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = ...


0

You can use Sql Server Data Tools (SSDT). Microsoft Visual Studio allows you to create a Sql Server project. One then imports the database into the project and then build the project. If there are any broken stored procedures or objects, you will get a compile error.


0

One possible way around it is to generate a query which always returns a value: SELECT company_settings.value FROM (SELECT 1 as VAL) AS V LEFT OUTER JOIN company_settings ON company_id = `setting_company_id` AND name = `setting_name` INTO setting_value; You treat the response as if it were part of an outer join and the empty set is now ...


1

Although it's all opinion I strongly favour using pull techniques for the following reasons: I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push". Most DBMSes have better control of transactions etc in ...


3

But why the recompile didn't help? Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits. ...


0

It turns out to be some kind of SQL Server bug (Maybe the SQL Server 2005 is quite old). The stored procedure just ended unexpectedly and does not return any code. So the problem is solved by moving all the logic before this msdb.dbo.sp_start_job procedure into a separate stored procedure. Hope it can help anyone who got this same issue.


4

I have another variation of the formula guaranteed to work (unless there is a power failure) SET @now = NOW(); SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY + INTERVAL 570 MINUTE + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200, IF(WEEKDAY(@now)=5,1,0),0) WEEK); In case you are wondering, 09:30:00 AM is ...


6

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming. Here what you have to do: SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY); The query meaning: With DATE_ADD you will add an interval between the parameter NOW() and one condition assuming my weekday for Saturday is 5, you ...


0

After some time i figured out how to do this. Lets put as sample data 2 recurrent tournaments: One daily at 16:20 and another every sunday at 14:00 insert into challenge_schedule(challenge_type_id, game_type_id, quorum, frequency_minute, frequency_hour, frequency_dom, frequency_month, frequency_dow, frequency_year, description) values (1, 4, 10, 20, 16, ...


1

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table. Table lotteryTBL and resultsTBL: mysql> SELECT * FROM test.lotteryTBL; +----+------+------+------+------+------+------+-------------+ | ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew | +----+------+------+------+------+------+------+-------------+ | 1 | 1 | ...


0

By using this DMV, you can returns metrics for stored procedures, but it does not directly identify the stored procedure by name. Instead, the DMV only identifies the object_id, and a database_id for each stored procedure: Therefore to identify the actual stored procedure name, that the performance figures belong to, you should either join the ...


1

Since this seems to be a data warehouse, is / can the recovery model be bulk logged (or simple)? That in addition with using TABLOCK hint on table_b could help a lot since the inserts could be minimally logged. I found a blog post by Itzik Ben-Gan on minimally logged inserts that could help.


1

CREATE Table dbo.[#TMP] (UId uniqueidentifier, Points int, DC datetime, CId int, Username nvarchar(50)) BEGIN insert into #TMP(UId, Points, DC, CId, Username) Select x.UId, sum(x.Points), x.DC, x.CId, x.Username from ( SELECT c.UId, (l.pointsup - l.pointsdown) as ...


2

For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat. To find unused stored procs, please see the answer as explained by Aaron Bertrand. Also, there is an good read to identify as explained here in the article How to find unused Stored Procedures in SQL Server 2005/2008 To tack down unused views: a) ...


2

Yes that's possible. Create the function with the user owning all those tables and use the SECURITY DEFINER modifier. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it. create or replace function foo() returns void as $body$ ... $body$ language plpgsql security definer; Also see the ...


0

I got these two examples to work from the command line: sqlcmd -S .\sql2014 -E -Q "EXEC tempdb.[dbo].[_testProcedure] $(first_date), $(last_date)" -v first_date = '30/09/2015' last_date = '31/12/2015' GO sqlcmd -S .\sql2014 -E -i "d:\temp\temp.sql" -v first_date = '30/09/2015' last_date = '31/12/2015' If you are using the -Q switch to pass in a query, ...


0

You open two cursors but close the outer one before the inner one open gen_row; -- open the outer cursor...this semicolon seems odd for data in(SELECT * from testnirmal) loop fetch gen_row into a1; open gen1_row(a1); fetch gen1_row into a2; dbms_output.put_line(a2); close gen1_row; end loop; close gen_row;--here you close the outer cursor close gen1_row; ...


4

PRINT @String; will yield that it says PhoneNumber = 123-4567 (which treats it like an expression, 123 subtract 4567, which equals -4444), not PhoneNumber = '123-4567' like in the example you hard-coded. So you should be escaping the values you're appending with two single-quotes: SELECT @String = ' UPDATE ' + @Location + ' SET PhoneNumber ...


5

Stop using things like varchar without specifying the length. Watch what happens here: CREATE PROCEDURE [dbo].[Reconciliation_broken] @Param1 varchar AS BEGIN SET NOCOUNT ON; PRINT @Param1; END GO EXEC dbo.Reconciliation_broken @Param1 = 'what happened?'; Results: w When you don't specify the length, you get silent truncation - in some ...


3

In SQL Server, a stored procedure's argument can be either a string literal or a variable. It certainly cannot be a string expression, like in your attempt. To resolve your issue, you could declare a variable, assign your expression to it and then pass the variable to the sp_send_dbmail stored procedure: DECLARE @myfilename nvarchar(255); SET @myfilename = ...


3

Declare a variable first - but use format 112 for this. Like this: DECLARE @query_attachment_filename VARCHAR(100) = 'pca-test-' + CONVERT(VARCHAR(10), GETDATE(), 112) + '.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'support', ... @attach_query_result_as_file = 1, @query_attachment_filename = @query_attachment_filename, @query_result_header =0, ...


0

The problem is, when you try to EXECUTE your DYNAMIC query the query returns NULL when there's not found rows. Test: mysql> CALL drop_tables_like( -> '6', #myint INT, -> 'onare', #pattern VARCHAR(255), -> 'PRUEBA'); #db VARCHAR(255)) +--------+ | sqlSTR | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) Query OK, 0 rows ...


-1

Use DECLARE CONTINUE HANDLER FOR SQLSTATE '1064' use link https://dev.mysql.com/doc/refman/5.0/en/declare-handler.html @Vietyank Your procedure working fine.The message showing is not an error it is an warning. Example: I have created a Database Test1; mysql>Create Database Test1; ...



Top 50 recent answers are included