On the first issue, since you only appear to be using this table to output data, you can simply declare it in the stored procedure instead of as a parameter, e.g.: CREATE PROCEDURE [dbo].[sp_tblUsers_Dim_InsertUpdate] AS BEGIN DECLARE @MergeAction TABLE ([UserId] INT, MergeAction VARCHAR(100)) END For the second issue, your UPDATE statement is incorrect. Its listing the target columns but not what to set them as. They should be t.[column] = s.[column]. Also, you should remove t.[User_ID] from the UPDATE list since it is the key column you're matching on, so doesn't need to be updated.

Your updated actual execution plan is also indicating you have no indexes on your extractreports.dbo.alldistSuppliersourceid table because it is currently using a Table Scan operation against it. It should at the minimum have a clustered index. If that clustered index makes sense to be on the field your query is currently joining on SourcingNotMappedPartsID (depending on how often you query by that column) then you could create it like so: USE extractreports; CREATE CLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID); If SourcingNotMappedPartsID is unique in extractreports.dbo.alldistSuppliersourceid then you can also specify the UNIQUE keyword after theCREATE keyword. Otherwise if another field makes sense to be the clustered index on extractreports.dbo.alldistSuppliersourceid, then use the above script, substituting out that column, to create the clustered index and you can create an additional nonclustered index on it instead like so: USE extractreports; CREATE NONCLUSTERED INDEX IX_alldistSuppliersourceid_ ON dbo.alldistSuppliersourceid (SourcingNotMappedPartsID); Again, using the UNIQUE keyword if it's applicable as well. That one index should at least help your performance to a degree.

In a transaction ALTER the table. You will get an exclusive schema lock for the duration of the transaction. Requiring a transaction in SSIS may be enough, alternatively set RetainSameConnection on the connection manager and handle the transaction and schema lock in a TSQL step. You can get the schema lock with any alter table, like ALTER TABLE . . . SWITCH, or with SP_RENAME. A cheap and harmless ALTER TABLE for any table that doesn't have any disabled constraints is something like alter table SomeTable with nocheck check constraint all Which is a noop, except that it has the side effect of requiring your transaction to hold an SCH-M lock. A more intrusive method is to put the database in SINGLE USER mode killing all the other connections.

I think what you're looking for is https://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/ : Cross database ownership chaining is an extension of ownership chaining, except it does cross the database boundary. Please see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/cross-db-ownership-chaining-server-configuration-option?view=sql-server-ver15 for more information. It is possible to turn it on only for individual databases which may make more sense for your use case: You can set cross-database ownership chaining for individual databases using the SET clause of the ALTER DATABASE statement. If you are creating a new database, you can set the cross-database ownership chaining option for the new database using the CREATE DATABASE statement. Alternatively, another solution could be to use the https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15 statement to impersonate a Login who has access to the table in the Final database. You would put it inside the procedure that the Staging User has access to execute, and then only that procedure would have access to modify the table in the Final database. With this solution the Staging User would need access to be able to impersonate the Login being used though. This is better than directly granting Final access to the Staging User because you are controlling your database through stored procedures, and ideally the Login it will be impersonating only has limited access (just the single table you need to update?) in the Final database.

Your issue doesn’t really have anything directly to do to with the TOP 5. You have a GROUP BY and HAVING clause. Both of those happen before your SELECT TOP, and might touch thousands of rows to produce their results, before you even choose the top 5 from those results. You only provided an estimated plan, so there’s no way to know how many rows are being read. The estimated plan has a Key Lookup because you don’t not have an index covering all of the columns being referenced in your query. Looking at your indexes, consider adding this index to avoid the key lookups… CREATE NONCLUSTERED INDEX [IX_NotMapped_VCompanyId_sourcetypeid] ON [Parts].[SourcingNotMappedParts] ( [VCompanyId], [sourcetypeid], [PriorityLevel] ) INCLUDE ( [GivenPartNumber_Non], [sourcetypeid] ) Drop this index DROP INDEX IDX_SourcingNotMappedParts_VCompanyId]

One way would be using exists: select PartNumber,sourcetype,stockid from #stockyes s where exists ( select null from #stockyes s1 where s.PartNumber=s1.PartNumber and s1.sourcetype ='Website' ) and exists ( select null from #stockyes s2 where s.PartNumber=s2.PartNumber and s2.stockid =1 ); https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e08aabf2b903fcaf92093e209180e7c0 EDIT To get distinct partnumber only use: select distinct PartNumber from #stockyes s where exists ( select null from #stockyes s1 where s.PartNumber=s1.PartNumber and s1.sourcetype ='Website' ) and exists ( select null from #stockyes s2 where s.PartNumber=s2.PartNumber and s2.stockid =1 ); https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3ca1f48614a4a402b0f16fff57716f52

https://www.postgresql.org/docs/current/sql-createtrigger.html have always been using functions, not procedures. Unfortunately, the terminology is fuzzy, many use "stored procedure" as umbrella term for both functions and procedures. Thus the widespread misnomer "procedure" had crept in, spreading even to the syntax of CREATE TRIGGER ... EXECUTE PROCEDURE where a function name is required. That was a merely academic nuisance while Postgres only had functions, not procedures. See: https://dba.stackexchange.com/questions/2357/what-are-the-differences-between-stored-procedures-and-stored-functions/2381#2381 Postgres 11 eventually added true https://www.postgresql.org/docs/current/sql-createprocedure.html , which elevated that misleading terminology to a real problem. So the manual fixed its wording, and the proper syntax variant CREATE TRIGGER ... EXECUTE FUNCTION was added, which is preferred going forward. The old variant is still accepted for compatibility reasons - and will be for quite a while longer until that can finally be dropped for good. Related: https://dba.stackexchange.com/questions/251189/trigger-function-does-not-exist-but-i-am-pretty-sure-it-does/255319#255319

Your INSERT creates a local transaction, in which SQL Server is trying to enlist the remote procedure invocation using a distributed transaction, which is failing. So disable distributed transaction promotion for the linked server, eg EXEC master.dbo.sp_serveroption @server=N'[MyLinkedServer]', @optname=N'remote proc transaction promotion', @optvalue=N'false' then it should work, eg exec [MyLinkedServer].SomeDb.sys.sp_executesql N'create proc foo as select ''hello'' msg' go drop table if exists #TempTable create table #TempTable( m varchar(200)) INSERT #TempTable EXEC ('EXEC SomeDb.dbo.foo') AT [MyLinkedServer] select * from #TempTable Or you can use an output parameter to capture a single scalar value from the remote proc, declare @msg varchar(200); exec [MyLinkedServer].SomeDb.sys.sp_executesql N' declare @t table(msg varchar(200)) insert into @t(msg) exec dbo.foo set @msg = (select msg from @t)', N'@msg varchar(200) output', @msg = @msg output; select @msg msg

Plan A: Add timing code in each proc. Plan B: Add timing code around the CALLs. Plan Use the slowlog (with a low value in long_query_time) to capture the timings for individual calls; then use pt-query-digest to summarize the results. long_query_time = 0 will flood the disk with the slowlog; be cautious. =0.5 will catch only queries (such as CALL) that take longer than half a second. If you are primarily looking for the "slowest", this may be a better option. See: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Could be done using CROSS APPLY? SELECT d1.categoryname AS categoryc ,d2.categoryname AS categoryx FROM #category c CROSS APPLY #categorydetails AS d1 CROSS APPLY #categorydetails AS d2 WHERE d1.categoryid=c.categoryc and d2.categoryid=c.categoryx; By the way, in your query there is no self join. Self join is when you do TableA AS Ta1 Join TableA AS Ta2 on Ta1.x = Ta1.y. In your query you are simply doing two join between category and details on two different fileds.

You need a semi-colon after the query. I.e.: DELIMITER $$ CREATE PROCEDURE get_null_flags (col_name VARCHAR(40)) BEGIN (select sum(case when ',[' + col_name + ']' IS NULL then 1 else 0 END) as 'null_flag' from city); END $$ DELIMITER ; By the way, you don't need to put the query inside a parenthesis.

I'd be interested in the number of total rows you have in your Parts.ManufacturingData table and what the total runtime currently is for your query. Does the number of rows the execution plan says it's returning (roughly 32 million) actually make sense for your query? Maybe you'll find a filtered index saves you a little bit of time since it'll pre-materialize only the data you want. How about an index with this definition, does it make any difference? CREATE NONCLUSTERED INDEX IX_ManufacfuringData_MSLID_Filtered ON Parts.ManufacturingData (MSLID) WHERE MSLID IS NOT NULL; Note you shouldn't need to do anything different to your query to use the above filtered index once it's created. But you should check the execution plan to ensure the optimizer chose this new index over any other indexes on your table when the query runs. If that doesn't make any difference, the other thing you can try is adding the https://www.mssqltips.com/sqlservertip/3296/why-the-sql-server-forcescan-hint-exists/ hint to your query like so: select partid,mslid into ExtractReports.dbo.manufactureparts from parts.manufacturingdata m with(nolock, FORCESCAN) where mslid is not null This would tell the optimizer to use a scan operation instead of seek against your data. This would generally be more performant if roughly a majority of your data in the table meets the criteria of your WHERE clause. I.e. it's generally faster to scan the entire table at that point and filter out the unwanted rows, than to seek against so many rows. But hard to say if this'll help your circumstances without knowing your data or testing it. Please note https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15 should be used cautiously and only in circumstances where alternative optimization methods are not possible. Some query hints limit the number of available execution plans that the optimizer can choose from, and therefore can result in an error being thrown when certain queries using those hints try to execute. In this case, I think using the FORCESCAN hint is likely ok, as your query is simple, and I don't believe it limits the number of query plans as much as other hints do.

It seems that your server is using sql_mode=''. OVHCloud MySQL server has sql_mode ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES. ANSI activates the ANSI_QUOTES mode which interprets double quotes (") as fields name. Your jaro winkler stored procedure uses double quotes to define strings string="value" (l.16, 17 and 38). Without ANSI_QUOTES, MySQL reads: variable string is equal to value. With ANSI_QUOTES, MySQL reads: variable string is equal to value of field called "value". You need to update your stored procedure to use single quotes (') for string definition. ... set common1=''; set common2=''; ... set in2 = concat(substr(in2,1,curSub-1),concat('0',substr(in2,curSub+1,length(in2)-curSub+1)));

|| is not for string concatenation in MySQL/MariaDB. Use the CONCAT(..., ...) function. Here is what MySQL says: mysql> select 'Total outstanding log inserted successfully ' || CHAR(CURRENT_DATE); +----------------------------------------------------------------------------------+ | 'Total outstanding log inserted successfully ' || CHAR(CURRENT_DATE) | +----------------------------------------------------------------------------------+ | 0 | +----------------------------------------------------------------------------------+ 1 row in set, 3 warnings (0.00 sec) mysql> show warnings -> ; +---------+------+-------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------+ | Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead | | Warning | 1292 | Truncated incorrect DOUBLE value: 'Total outstanding log inserted successfully ' | | Warning | 1292 | Truncated incorrect DOUBLE value: '\x014\x88\xCE' | +---------+------+-------------------------------------------------------------------------------------------------------+

Create the following index: create index pf_idx on Parts.Nop_PartsFamilyAttribute([Key],PartFamilyID) include ([Value]) The problem currently is that even though it is "seeking", it's not doing so efficiently, plus it is estimating +4MM rows returned from those seeks into the hash-join, which basically is where all of the time is going. It appears that the Seek is only matching the [Key] value and then relying on the hash-join to do the PartFamilyID filtering, but at 4MM rows it's a lot of overhead. The index that I suggested above should alleviate much of that by enabling it to more efficiently seek (or maybe even scan) the rows that it needs, matching both [Key] and PartFamilyID. ---- As @MartinSmith pointed out, you have a much bigger problem with your second join which I missed: INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.Value The problem here is that while pl.ZIPID is an INT, fm.Value is an NVARCHAR which makes the whole JOIN https://www.brentozar.com/blitzcache/non-sargable-predicates/ . The solution to this is as follows: Assuming that you cannot just change the Nop_PartsFamilyAttribute.Value column to INT, then add a persisted calculated column: -> `ALTER Parts.Nop_PartsFamilyAttribute ADD IntValue AS (Try_Parse([Value] AS INT)) PERSISTED;` Create the following index (instead of the one I proposed above). -> create index pf_idx2 on Parts.Nop_PartsFamilyAttribute([Key],IntValue,PartFamilyID) Change your second join to: INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.IntValue Finally, change the column list of your query to this: -> SELECT DISTINCT FT.PartId, TN.Code, FT.CodeTypeID, FT.SourceTypeID, FT.RevisionID, CAST(fm.IntValue AS Nvarchar(2000)) AS [Value], FT.PartLevel, GETDATE(), 1

There is a very strong likelihood you need one more grant: GRANT SELECT ON mysql.proc TO `dump`@`localhost`; REASON : Dumping Stored Procedures requires reading from mysql.proc UPDATE 2021-12-25 12:20 According to https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_routines --routines, -R Include stored routines (procedures and functions) for the dumped databases in the output. This option requires the global SELECT privilege. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines. You may need to adjust grants as follow GRANT PROCESS,SELECT ON *.* TO `dump`@`localhost` or create a separate user for the sole purpose of dumping stored procedures GRANT PROCESS,SELECT ON *.* TO `dump_storedprocs`@`localhost`

If you look at the line that your print command return, it looks like this: powershell -command "C:\SQL_Powershell\General\MoveFile.ps1" ""C:\BPA Exports\Change Point 1\Processing\"" "ExportCP1Data" ".csv" Note the "" before and after the path... I guess those should be simple " (not double). Take them out of you @sqlFilePath variable and try again.

I have to use case inside case conditions No you don't. You are over-complicating your logic. And "Best" is never a good qualifier to include when your code has syntax errors. So let's start with something that is correct. Notice the conditions in your JOIN. Does it make any sense to include the opposite conditions in your CASE expression? With the join, those first three comparisons will never be TRUE, so why do you include them? This is how I would write your query. I split the logic for setting the column to 1 into two different search conditions since it is clear and it is easily maintained. It does not need to be split but my experience is that things like this evolve over time. UPDATE a SET IS_ACTIVE = CASE WHEN a.TYPE = 1 and a.AMOUNT <> b.TOTAL_D THEN 1 WHEN a.TYPE = 2 and a.AMOUNT <> b.TOTAL_K THEN 1 ELSE 0 END FROM dbo.SEA a INNER JOIN dbo.MOUNT b ON a.CODE = b.CODE AND a.YEAR = b.YEAR_POSITION AND a.MONTH = b.MONTH_POSITION; I also added a schema name to your tables and a statement terminator - both best practices. I assumed dbo was the correct schema - adjust for your situation. I'm not a fan of single character aliases generally, especially when those letters have little obvious relevance to the table names. That's more of a style concern though it does make a "readability" difference in longer, more complicated queries.

Since something needs to first populate the table variable that is then passed to the stored procedure, unless the procedure is doing more than what you posted, why not populate the table directly? Skipping the use of the table variable and sproc?

See https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html#:%7E:text=If%20you%20get%20Commands%20out,you%20have%20called%20mysql_free_result()%20 . That page says that you are fetching the results from the Stored Proc incorrectly. An SP returns multiple "resultsets" and you need extra client code to look in each resultset to get the rows that were produced. It looks like your SP has only 1 resultset with some number of rows.