Login to Microsoft SQL Server Error: 18456

This is the most common error in SQL Server. If you are connected to the network and have also a domain member then this error occurs when you set the SQL Server Authentication as "Windows Authentication mode" and trying to connect with SQL Server credentials. 

If we are trying to connect using "SQL Server Authentication" then Login to Microsoft SQL Server Error: 18456 is occurred. 

In this case, we must have to modify our server authentication by using the following steps in the Microsoft SQL Server Management Studio in the object explorer:

Right click on the SQL Server and click Properties as given below -

In SQL Server Properties window, Go to the Security page and under Server authentication section choose the SQL Server and Windows Authentication mode radio button and Click OK button to close window as given below - 

To immediate effect, just go the window services section and Restart the SQL Services as given below -

To avoid this kind of issues, we suggest to install SQL Server within mixed mode authentication  which always allow to enable both authentication modes (SQL Server and Windows Authentication mode ). 

TSQL - How to list files inside a folder with SQL Server

In this section, we are going to list all the files within the folder or directory by using T-SQL. This is very common scenario if you have SSIS environment to load the multiple files from the shared location. In absence of  SSIS or other ETL tools,  you have to write T-SQL codes to list down all the files in the file directory or folder before load their data into the system one by one. 

Database-level configuration options - Before using this code, we should change the global configuration settings for the current server as given below -

-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO

T-SQL Code through xp_DirTree command - After doing the needful database level configuration setting, we are able to run the below code to list all the files from the shared folder as given below –

----- Table Variable to Store the file List declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1)) ----- Folder or Directory Path Variable declare @inpFilePath varchar(200)='C:\Sample Data Pack\'; ---- Insert Values into Table variable INSERT INTO  @TotalFiles (SubDirectory, Depth , Files ) EXEC Master.dbo.xp_DirTree @inpFilePath,1,1

T-SQL Code through XP_cmdshell command - XP_cmdshell is the other option to list all the files from the shared folder but it requires more data filters as given below –

----- Table Variable to Store the file List declare @TotalFiles Table(SubDirectory Varchar(200), Depth Int, Files Int, ictr int primary key identity(1,1)) ----- Folder or Directory Path Variable declare @inpFilePath varchar(200)='E:\Sample Data Pack\'; ---- Set Dirctory command before folder path SET @inpFilePath= 'DIR '+ ' "' + @inpFilePath +'"' ---- Insert Values into Table variable INSERT INTO  @TotalFiles (SubDirectory) EXEC Master.dbo.XP_cmdshell @inpFilePath SELECT FilesName=Substring(SubDirectory,40,100) FROM @TotalFiles ----- Remove all directories where SubDirectory NOT Like '%<DIR>%' ----- remove bytes AND SubDirectory NOT Like '%bytes%' ---- file name will start from 40 AND LEN(SubDirectory)>40 ---- file name must have . extenion AND SubDirectory Like '%.%'

Database in SQL Server in recovery mode

There are many reasons to send your database in Recovery Mode. The most common cause is that to restart SQL Services forcefully due to long running data queries. Another common cause is that the database was restored with the NORECOVERY option from full, differential, and log backups but RECOVERY was not specified on the last restore.

Another common cause is that the transaction log filled due to a large data modification operation and SQL Server is rolling the transactions back to recover the database, which may take quite a bit of time. The error log will include recovery progress messages.

The best way to recover your database by using the below steps –

1. Stop SQL Server instance – just right on the SQL Server instance and click on Stop option

2. Move database files to another location/drive or rename database files including mdf and ldf files.

2. Start SQL Server Services – just go to the services and right click on the SQL Server Services and click on Restart.

3. Database again appears in recovery mode

4. Bring the database in offline mode - Once all connections have been removed you can then set the database to offline, using the following command (or right click on the database in SSMS and select ‘Take Offline’).

5. Delete the database – just right click on the database and click on delete option

6. Place the database files or rename database files back where it was

7. Attach the database – right click on the database and choose attach as given below –

8. Database is recovered and available. 

9. Checking in SQL error logs shows messages on dbcc checks & how many transactions were rolled back or forward during recovery.