Tag Info

Hot answers tagged

4

DBA_DEPENDENCIES view has all the answers to such questions. select * from DBA_DEPENDENCIES where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';


4

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially: UPDATE existing rows INSERT missing rows You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted. Beyond that there are different ways to ...


3

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind. You can say: EXEC dbo.myprocedure @foo = 'bar'; But you can't say: EXEC dbo.myprocedure @foo = 'b' + 'ar'; You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a ...


3

It depends on how strictly you are defining "programmatically". If you mean a process that knows nothing of the proc it is evaluating, then you aren't going to get very far. In addition to the obstacles mentioned by @Aaron in his answer (i.e. Linked Servers, Dynamic SQL, OPENQUERY / OPENROWSET, deferred name resolution, etc), you also have the issue of ...


2

When you are closing SSMS, it check for IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION Or you can use sys.dm_exec_requests SELECT open_transaction_count FROM sys.dm_exec_requests WHERE session_id = @SessionId In this case you have an open transaction so on connection closure process SQL Server will roll back that transaction. In this case you need to take ...


2

Found it. You have to declare a continue handler instead of an exit handler. That way even if there is an exception, it goes into the handler and continues with the next record. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ... Code that handles the exception/warning ... END


2

Using the dblink extension, this is possible. Just create a connection inside your function, supplying the database name: SELECT dblink_connect('conn', format('hostaddr=127.0.0.1 port=5435 dbname=%I user=test password=test', 'foreign_test')); SELECT dblink_exec('conn', 'CREATE TABLE t (id integer)'); SELECT dblink_disconnect(''); Note that using the ...


2

It is difficult to tell just from examining the code if it is running forever because of an infinite loop or blocking - my guess is the latter. But here is a slightly different approach that avoids some of the things that could be problematic: CREATE PROCEDURE [dbo].[pr_BuscaLog] @qtdDias INT AS BEGIN SET NOCOUNT ON; DECLARE @cmd NVARCHAR(MAX) = N'', ...


2

The .nodes() method must start at the root of your xml. It will then return one "row" per matching node for the SELECT clause to work on. It should look like this: from @notesToUpsert.nodes('/Notes/Note') as note(col) (BTW using "note" as an alias is confusing. Perhaps T(c) would be easier to debug?) Then the XQuery in the SELECT clause operates in the ...


2

The easiest programmatic way would be just to try and execute them (using TRY/CATCH) and determining the class of error that results. Of course you'd only catch the first error, and this assumes that there isn't error handling in any of those procedures for missing objects. Plus, as @srutzky points out, there is more to calling stored procedures than just ...


2

Finally I found the solution but it needed some optimization and tweaks to work with my case and I added sorting with ID to get the tree sorted too, the answer is mainly got from here so credit goes to @deceze, CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_recover`() MODIFIES SQL DATA BEGIN DECLARE currentId, currentParentId CHAR(36); ...


1

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join? Something like this: SELECT * INTO #temporary FROM ( SELECT CodJob, Quant, TipoCons, Perc FROM tblConsolidadoJob ) AS SourceTable PIVOT ( MAX(Perc) FOR TipoCoins IN ...


1

What a brittle solution. Does the return value of sfn_GetSecondDbName() really change that often? Where does its value come from? Would be much easier to hard-code the database in the trigger, and change the trigger when that changes, rather than change the function and try to "save work" by not having to update the trigger. Your current approach will ...


1

Is there a better way of achieving this, or am I on the right track? Yes there is and no, I don't think you are on the right track. There is almost no reason to use a cursor in this situation. If ShowLocation is a value on each record in the table then this is the kind of situation where a CASE statement comes in handy. SQL Server code would be ...


1

There are some caveats that apply to mixing transactional and non-transactional tables in the same query, but in this case, you're actually doing it because the side effects are desirable... so I see no problem with the following suggestion: The MEMORY storage engine seems like a good fit for this application. It's non-transactional, so your "status" ...


1

The select statement Select count(1) from #tmp in the while loop is effectively a constant value. In other words, it doesn't change each time round the loop. I'm guessing you really want to loop once per database, so you'll need to modify your while loop.


1

This appears to work - it's less elegant than @MindaugasRiauba's answer, but it does seem to find references from all packages. SELECT * FROM all_source where UPPER(TEXT) like UPPER('%STORED_PROCEDURE_NAME%') (From How can you tell if a PL/SQL Package, Procedure, or Function is being used?)



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