Tag Info

Hot answers tagged

9

Don't. While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness. Instead use proper encryption hierarchy. Do not ...


8

Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ROLLBACK in the CATCH block will roll back all of the work. BUT here are some things to note about transactions (at least in SQL Server): There is only ever ...


7

I think this is because you only ever return the first row from the query's result. The select ... into ... will only retrieve one row and the query select * from result returns only that single record: You also don't need a PL/pgSQL function, a plain SQL function will work just fine: CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS ...


7

Kankuru is a great and FREE monitoring tool for SQL Server developed here in France. Aside from its monitoring capabilities, it also has a DB compare tool that is easy and simple to use. It will not only show you if your stored procedures, tables, indexes, types, etc. exist on two servers, but also what those differences are between those two servers. ...


4

You wrote "case closed", but I'll reopen. There is just too much gone wrong ... Database design and test setting CREATE TABLE patient ( patient_id int PRIMARY KEY , site_held_at int NOT NULL ); CREATE TABLE messageq ( messageq_id varchar PRIMARY KEY -- varchar ?! , patient_id int NOT NULL REFERENCES patient , message_body varchar NOT NULL ...


3

There are two things wrong First, use DELIMITER $$ so semicolons are interpreted inside the stored procedure and not the command line. After defining the stored procedure, switch the delimiter back to semicolon (;). Next, UPDATE clickactivity SET Clicks = Clicks + 1; will update every row and you don't want that. SUGGESTION #1 Add WHERE IP = ipaddress; ...


3

This is something you can easily test for yourself, but in my testing, no, there is no significant overhead in calling a stored procedure across a database boundary (I am sure you could make something noticeable though if you tried hard enough). However, I would say that a stored procedure should live closer to the data that it manipulates than the ...


2

This ought not happen, but there have been several bugs that mean it might, depending on your version of SQL Server, and which patches you have applied. The relevant Microsoft KB articles are listed below: FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 or SQL Server 2014 FIX: A query that uses parameters ...


2

The issue was the setting "Auto close" was set to true on a series of databases on the server. Even though the databases were unrelated their "auto close" setting was flushing all execution plans on the server. Here are the types of log issues you will see with auto_close set to true: SQL Server has encountered 24 occurrence(s) of cachestore flush ...


2

Yes, if due to any error rollback code in your master stored procedure's catch statement will execute, it will rollback all the operations performed by any direct statement or through any of your nested stored procedures in it. Even if you have not applied any explicit transaction in your nested stored procedures still these stored procedure will use ...


2

You just need to deny EXECUTE permissions on any object that you don't want the app login to see. Denying VIEW DEFINITION will only prevent the code of the object from being seen by that user. That is the point of VIEW DEFINITION: to allow users to run code that they can't see the code for. Denying EXECUTE on the object will removing it completely as far as ...


2

Your syntax is ok, you just need to temporarily change the end of statement delimiter, like this: mysql> delimiter // mysql> CREATE PROCEDURE Click( -> in ipaddress varchar(45)) -> BEGIN -> DECLARE ex int; -> SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress; -> IF ex = 0 THEN -> ...


1

I think that your simplest option is a job. You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need. Here's an example based on your requirements: USE master GO CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , ...


1

CREATE PROCEDURE sp_insert_allocated_time @p_project_id INT, @p_allocated_time INT AS BEGIN DECLARE @sum_alloc_time int, @alloc_hours int, @fld_id int select @sum_alloc_time = SUM(fld_allocated_time) from dbo.Timesheet where fld_project_id = @p_project_id; select @alloc_hours = p.fld_allocated_hours, @fld_id = p.fld_id FROM dbo.Project p INNER JOIN ...


1

Your use of ORDER BY is correct. However, You need to know that it has to go in order by how you specified! So your query will first order sd.Status FIRST, then it will order by distance SECOND. For example This is how you're expecting it to look (Just my random data I gave) sd.Status distance --------- -------- A 231 A 245 ...



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