MSBI Tutor

DECLARE @date DATETIME = GETDATE() SELECT  FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')    AS 'US',  FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN')    AS 'Hindi',  FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','kn-IN')   AS 'Kannada',   FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','te-IN')   AS 'Telugu',     FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ta-IN')   AS 'Tamil',     FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','or-IN')   AS 'Odia',     FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ml-IN')   AS 'Malayalam',     FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','gu-IN')   AS 'Gujarati' Results: US Hindi Friday, December 13, 2019 06:06:46 PM शुक्रवार, दिसम्बर 13, 2019 06:06:46 अपराह्न ...

Msg 9002, Level 17, State 2, Line 4 The transaction log for database 'MSBITutor' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Cause: Due to database log free space Solution: Have made database recovery mode as simple from full mode

TITLE: Microsoft SQL Server Management Studio ------------------------------ The database file with name 'SSISDB' already exists in directory 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\'. Make sure there is no existing database file with the name 'SSISDB' under this directory and try again. (Microsoft.SqlServer.Management.IntegrationServices) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProductVersion=14.0.3026.27+((SQLServer2017-CU7).180510-1901)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Solution: 1. Go to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ 2. Delete data files related to SSISDB 3. Try to create SSISDB again in SSIS catalogs

TITLE: Microsoft Visual Studio ------------------------------ Failure saving package. ------------------------------ DDITIONAL INFORMATION: An item with the same key has already been added. (Microsoft.SqlServer.ManagedDTS) ------------------------------ BUTTONS: OK ------------------------------ Cause: Have created a two SSIS packages. One package having package level connection managers and another package having  project level connection. Solution: Have a removed package level connection in 1st package and it's working fine.

TITLE: Microsoft Visual Studio ------------------------------ Failed to start project ------------------------------ ADDITIONAL INFORMATION: Exception deserializing the package "The process cannot access the file 'E:\SSASCube\HistoricalDataLoad\HistoricalDataLoad\bin\Development\HistoricalDataLoad.ispac' because it is being used by another process.". (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ The process cannot access the file 'E:\SSASCube\HistoricalDataLoad\HistoricalDataLoad\bin\Development\HistoricalDataLoad.ispac' because it is being used by another process. (mscorlib) ------------------------------ BUTTONS: OK ------------------------------ While running SSIS package i got the error “The process cannot access the file ‘*.ispac’ because it is being used by another process”. I tried to close SSDT and run it again but, I still got the same error while compiling. Then, after searching over internet, I got...

1. Open your SSIS project. 2. Right click on project and select project properties. 3. Go to general tab and select target SQL server version, Click on OK 4. Again right click and project and select build Build was success 6. Right click on project and deploy Deployment wizard will open as shown in bellow 7. Select deployment mode as shown in bellow.  Click on next 8. Provide server and select SSISDB catalog path Click on next 9. Before deployment please check SSISDB catalog in SSMS. No project are available in SSISDB catalog as shown above. 10. Now click on deploy 11. Now deployment project appaired in SSISDB catalog

Microsoft Virtual Labs: Microsoft providing free virtual labs to practice their products. These virtual labs are very useful to everyone. By using virtual labs, you can Acquire the cloud skills at your own pace. Enjoy hands-on learning on your schedule with our free, self-paced labs, and keep your cloud knowledge fresh. Link :  https://www.microsoft.com/handsonlabs/selfpacedlabs Instructor-led Labs: Review and schedule an Instructor-led lab or check the status of an existing requested lab. Link :  https://www.microsoft.com/handsonlabs/instructorledlabs/LogIn Microsoft Learn: The skills required to advance your career and earn your spot at the top do not come easily. Now there’s a more rewarding approach to hands-on learning that helps you achieve your goals faster. Earn points, levels, and achieve more! Link :  https://docs.microsoft.com/en-us/learn/

Join us at the free Microsoft Azure Training Day: Fundamentals to improve your understanding of cloud concepts and acquire the knowledge you need to earn the Microsoft Azure Fundamentals certification. Learn basic strategies for transitioning to the cloud along with concepts including security, high availability, scalability, elasticity, agility, fault tolerance, and disaster recovery. Please register in bellow link https://www.microsoftevents.com/profile/form/index.cfm?PKformID=0x7502208abcd

Join us at the free Microsoft Azure Training Day: Fundamentals to improve your understanding of cloud concepts and acquire the knowledge you need to earn the Microsoft Azure Fundamentals certification. Learn basic strategies for transitioning to the cloud along with concepts including security, high availability, scalability, elasticity, agility, fault tolerance, and disaster recovery. For registration: https://www.microsoftevents.com/profile/form/index.cfm?PKformID=0x7435651abcd

1. Indexes Check if useful indexes are present . Take the help of Database Tuning Advisor in SQL  to get index suggestions. 2. Statistics Index and column statistics are up to date or not . If they are not , then probably , query might not perform as required . In a frequently changing database , it may happen that statistics are out of date 3. Run the query in between      SET STATISTICS PROFILE ON             SET STATISTICS IO ON             SET STATISTICS TIME ON        [ Query ]             SET STATISTICS TIME OFF             SET STATISTICS IO OFF             SET STATISTICS PROFILE OFF

--Code Snippet --To Check Subscription Status. USE ReportServer GO SELECT dbo . Subscriptions . Description ,        dbo . [Catalog] . Name ,        dbo . Users . UserName ,        InactiveFlags FROM dbo . Subscriptions      INNER JOIN dbo . [Catalog]      ON dbo . Subscriptions . Report_OID = dbo . [Catalog] . ItemID      INNER JOIN dbo . Users      ON dbo . Subscriptions . OwnerID = dbo . Users . UserID --Temporary disables subscriptions while data warehouse is unavailable UPDATE dbo . Subscriptions SET InactiveFlags = 1 GO --After this you can find, all the subscription where disabled until you again enable it. --Again enable subscriptions. UPDATE dbo . Subscriptions SET InactiveFlags = 0 GO

USE master Go DECLARE @dbid INT , @spid INT , @STR NVARCHAR ( 500 ) --Replace dbname with your database name SET @dbid = DB_ID ( 'dbname' )   DECLARE rs SCROLL CURSOR FOR      SELECT spid FROM sysprocesses WHERE dbid = @dbid     OPEN rs     FETCH FIRST FROM rs INTO @spid             WHILE @@fetch_status = 0            BEGIN                   SELECT @STR = 'KILL '   + CONVERT ( VARCHAR ( 10 ) ,   @spid   )                    EXEC sp_executesql @STR                 FETCH NEXT FROM rs INTO @spid       ...

SELECT name as [Database Name], [Last Access Date] =(select MAX(temp.lastaccess) from ( select lastaccess = max(last_user_seek) where max(last_user_seek)is not null union all select lastaccess = max(last_user_scan) where max(last_user_scan)is not null union all select lastaccess = max(last_user_lookup) where max(last_user_lookup) is not null union all select lastaccess =max(last_user_update) where max(last_user_update) is not null) temp) FROM master.dbo.sysdatabases sysdb left outer join sys.dm_db_index_usage_stats Idxus on sysdb.dbid= Idxus.database_id group by sysdb.name

1. Connect SSMS 2. Go to integration service catalog folder and right click on   integration service catalog click on export option  3. Select folder where we needs to save export file 4. click on windows button and open sql server deployment wizard  5. click on next  6. Select .ispac file from folder  7. Provide server name  8. Select deployment folder path  click next  9. Click on deploy button

1. Open SSIS solution explorer 2. Right click on project and click on Convert to project deployment mode 3.  Click on next 4. Select list of packages 5. Select project name and project level 6. Click on next 7. Click on convert 8. Click on OK

1. Open SSIS solution explorer 2. Right click on project and click on Convert to package deployment mode 3. Click on OK

Summary Microsoft SQL Server 2019 powers your organization by providing a data hub that you can use to access structured and unstructured data sources from across your entire data estate through a consistent interface. The relational database engine scales to petabytes of data, and enhancements to PolyBase allow you to process diverse big data and relational data sources using Transact-SQL from SQL Server. Building on SQL Server on Linux in Docker containers, Apache Spark and the Hadoop ecosystem, and the rapidly- forming industry consensus on Kubernetes as a container orchestrator, with SQL Server 2019 Big Data Clusters you can deploy scalable clusters of SQL Server containers to read, write, and process big data from Transact-SQL, allowing you to easily combine your high-value relational data with high-volume big data with a single query. The SQL Server 2019 database engine supports an even wider choice of platform and programming language— including support for t...