Tag Info

New answers tagged

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


0

Merge is actually valuable for what the OP is trying to do, because UPDATE is highly inefficient, as well as committing an Update then Insert separately could have a concurrent write between commands that the Db may misassign rows; i.e. it is non deterministic at times. What merge does is do an upsert, but it only hits each row ONCE. it also errors on ...


0

I would use something like the following. This is pretty much what I have been using for years, with your error message logging worked into the CATCH block. CREATE PROCEDURE [SchemaName].[ProcedureName] ( @Param DataType ... ) AS SET NOCOUNT ON; DECLARE @InNestedTransaction BIT; BEGIN TRY IF (@@TRANCOUNT = 0) ...


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


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

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


0

You can use this to significantly increase the readability of your queries. Rather than using a short alias, use your alias to describe the data that you are joining to, for example SELECT transaction.unique_id, authorisingUser.name AS authorising_user_name, requestingUser.name AS requesting_user_name FROM transactions AS transaction JOIN users ...


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


0

I resolved the issue - but do not understand it. The system has a View corresponding to each Table. One of the Views was used in my FOR XML PATH view. I substituted the no-permission Table for the View, and it works. The View has Public SELECT permissions. The Table has no named permissions. I think the SQL Server Agent Job should be able to SELECT ...


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


0

If i am getting you right and what i also witnessed when the job did not ran an update code because of the limit of the text size with hold by sql server agent job: Therefore you can try something like limiting the amount of data returned via sql agent job as The default TEXTSIZE for SQL Agent is 512. You can specify the text size as per output you get ...


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


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


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


0

You need to alias your tables UPDATE Notes SET Notes.Username = NTU.NoteText, Notes.NoteText = NTU.NoteText, Notes.IsDeleted = NTU.IsDeleted FROM Notes INNER JOIN @notesToUpsert AS NTU ON Notes.NoteId = NTU.NoteId WHERE Notes.Modified < NTU.Modified


0

You need to define a table type for dbo.TVP_Notes and then use that to pass into the stored procedure CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) ...


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


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


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.


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


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


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';


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


0

Actually, it seems that the necessary option to add DROP PROCEDURE in mysqldump output (at least in mysqldump Ver 10.13 Distrib 5.6.21-70.1, for debian-linux-gnu (x86_64)) is --add-drop-table: mysqldump --quick --no-create-db --no-create-info --no-data --triggers --routines --no-data --all-databases --skip-opt --add-drop-trigger --create-options | grep -c ...



Top 50 recent answers are included