Problem: User create SQL Account on local database. Unable to connect using SQL Account. Fix: Make sure “SQL Server and Windows Authentication mode” is selected in Server Properties of SQL Server. Restart database server.
Extract ISPAC file SSMS -> Integration Services Catalogs -> SSISDB -> MyProject01 -> right click and click on “Export” XXXX.ispac file will be generated. Rename the .ISPAC file to .ZIP, after that you can to extract all files from that zipfile. Sources: http://microsoft-ssis.blogspot.com/2016/04/get-packages-from-ssis-catalog.html
When a job fails, you have set to have it alert an operator. For the operator setting, you can add multiple email address to the “E-mail name” field separated by semicolon. E-mail name: Edit via script… Note: There is a 100 character limit. It is set in the sysoperators table. DO NOT try to ALTER... » read more
Error: Getting the following error when running a stored procedure… SELECT INTO failed because the following SET options have incorrect settings: ‘ANSI_WARNINGS’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML Fix: Sources: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95752aba-2c39-4aca-be2b-a2ee9e3d70da/select-into-failed-because-the-following-set-options-have-incorrect-settings-ansiwarnings?forum=sqldataaccess https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15
Getting foreign key constraint error when trying to repopulate a table…
Error: Column ‘xxxxx’ is partitioning column of the index ‘PK_xxxxxxxx’. Partition columns for a unique index must be a subset of the index key. Could not create constraint or index. Solution: Since both biTable01Id, dtStartDateTime columns are in the partition column, both must be in the Primary Key column as well.
Starting with SQL Server 2014 a new troubleshooting capability is to monitors real time query progress with the DMV sys.dm_exec_query_profiles which is the base for Live Query Statistics new functionality for SQL Server 2016, there are two ways to enable real time query monitoring: Session scope: By enabling SET STATISTICS XML ON; or SET STATISTICS PROFILE ON;... » read more
Execution Plan in SSMS Run Stored Procedure in SSMS with “Execution Plan” enabled. SSMS will recommend index to be created. Improve Query Statement Convert subqueries to main queries. Note: This may not always increase performance. Have to run the both queries side by side to test.
Note: Make sure the user you are about to delete does not own anything in the database, else you will get an error message saying unable to delete user. Make sure the user does not own any of the SQL jobs.