Hot answers tagged

8

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically. You don't have an explicit return value in the event that ...


4

Well, you could parse the procedures (as long as they aren't encrypted) and jobs on server A looking for the name of the linked server - but keep in mind these requests could be passed in through an app, coming in from ad hoc SQL, built dynamically, etc. so this won't catch everything. This can also produce false positives if the linked server name is a ...


4

This is the syntax to drop it if it exists DROP PROCEDURE IF EXISTS db.sp_tmp_90days;


4

You don't have a BEGIN/END for the IF statement so it is executing the RAISEERROR regardless of the ERROR_NUMBER. I've added the block around the SELECT and the RAISEERROR and added a RETURN (0); in an ELSE block. ALTER PROCEDURE [dbo].[pSetUserItemLike] @UserID int ,@ItemID int AS BEGIN SET NOCOUNT ON; -- for exception handling DECLARE ...


3

I use this script that I developed to help me find linked server references, but can be used to find any text string in multiple objects. /********************************* Name: Text Searcher Author: Jonathan Fite Created: 1/16/2015 Purpose: To enable one to more easily search for objects by an indicated string. Usage: - ...


2

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL. A few points to get you started on the performance of your query: Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain. tblMetaData ...


2

Your problem is due to the same variable name is used for the parameter and local variable. i.e, wynik used as INT in the local variable as well as DOUBLE in the parameter of the nowyRanking function. In this case changing the parameter name will solve the issue. In the below code I renamed the parameter name wynik DOUBLE to wynikDouble DOUBLE and handled ...


2

Depending on what you are trying to achieve with this, you may be out of luck. First of all, names cannot be parametrised in MySQL otherwise than using a prepared statement. – So, can we use a prepared statement here? No. Prepared statements are not allowed in triggers, according to the manual: SQL syntax for prepared statements can be used within ...


1

This is probably best to insert all items at once or at least avoid have to split string on the SQL Server side. You could convert json to xml similar to this: <Order OrderId="" OrderDate="" CustomerId="" Price="300"> <Items> <Item ItemId="103" Qty="2" Price="100" /> <Item ItemId="123" Qty="2" Price="100" /> ...


1

This cannot be accomplished. Why ? According to Can MySql stored proc call a system cmd? (from July 19, 2009 11:47AM) This question is asked several times a week. The answers are (i) not out of the box, for very good reasons, (ii) you could write and link into your server a UDF which calls the operating system, but that would create a glaring security ...


1

SELECT populateIfNotAlreadyIn(1, 'val1'), populateIfNotAlreadyIn(2, 'val2'), populateIfNotAlreadyIn(3, 'val3'), populateIfNotAlreadyIn(4, 'val4'), populateIfNotAlreadyIn(5, 'val5') ; Meanwhile, you could shorten the function to just RETURN EXISTS(SELECT * FROM t WHERE id = idArg);


1

Beware when using the same name: WHERE `language_code` = language_code Suggest you precede any arguments or locally DECLAREd variables with a _. You can probably combine both procedures into a single SQL statement.



Only top voted, non community-wiki answers of a minimum length are eligible