Wednesday, June 21, 2017

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

Monday, June 19, 2017

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


Tuesday, June 13, 2017

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. 


Wednesday, May 10, 2017

Error authenticating proxy username or password is incorrect

We know that PackageExecuter is associated with a SQL Credential that was a former domain admin account. SQL Server saves that Account and Password and then applies it where appropriate. The domain account password used to create the package is different from the service account password used to run the package in QA. 
This is very common error in SQL Server and mostly occurs whenever you want to run your SQL job by domain user instead of SQL Server Agent.
If you want to run the SQL jobs with domain user account then you should be ensured the following points-
  1. Configure a User to Create and Manage SQL Server Agent Jobs
  2. Reset the SQL Server Agent Services
How to configure a user account in SQL Server?
Before creating or configure domain user account, you should have access rights to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts. You should have fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.
If non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts.
To configure a user to create and manage SQL Server Agent jobs, we should follow the following steps in SQL Server Management Studio-
Just go to security folder and check login you wish to add to a SQL Server Agent fixed database role, and select Properties.
In login properties window, go to User Mapping page and select the row containing msdb. After that check the appropriate SQL Server Agent fixed database role under Database role membership for: msdb

Just click OK Button to close login properties window.
How to configure a user Credentials in SQL Server?
After setting the membership, to run the SQL Server Agent jobs for a domain user we have to create the credentials as given below-
In Credential Window, we have to set the credential name and provide the identity of the login user with password as given below-
Click on OK button to close credential window.
How to configure a Proxy in SQL Server?
Now, we have to go SQL Server Agent to create a proxy under SSIS Package Execution as given below-
In Proxy Account window, we have to fill Proxy name and assign credential name which we have created in credential under security folder of SQL Server. After assigning credentials, we have to active all subsystems as given below-
Reset the SQL Server Agent Services
After creating the proxy account, click OK button to close proxy account window. Now, it is time to reset the SQL Server Agent Services. Just go to the window services, right click on SQL Server Agent Services and click on properties as given below-

In SQL Server Agent properties window, just go to Log On tab and set the domain user to run the SQL Server Agent services as given below-
After clicking on Apply button, click on OK button to close SQL Server Agent properties window. Now, we have to restart SQL Server Agent window services for again as given below-

Now we have done everything around this issue and we are able to run our all packages in SQL Server with the domain user account. We should take care that if domain user password will change then we need to change the new password in SQL Server Agent also.

Monday, May 1, 2017

Enable Indexes and Constraints in SQL Server

We know that Indexes are the booster to improve the query performance in SQL Server in case of fetching the data from database only otherwise they will decrease the performance for other operations such as insert, update or delete. In our last article disable indexes and constraints in sql server, we have observed to handle this situations. 
First of all, we know that the index definition remains in metadata when we are disabling an index and index statistics are kept on non-cluster indexes and it remains in a disabled state until it is rebuilt or dropped.


In this article, we are going through the different scenarios to enable any disabled index in SQL Server. Before enabling an index in SQL Server, we have to know all the limitations and restriction of it. SQL server supports the followings –
  1. If we enable or rebuild an index then all the associated constraints (disabled constraints) should be enabled manually except Primary Key and Unique Key constraints.  
  2. SQL Server does not allow us to enable Foreign Key constraints without enable associated index due to having referenced Primary Key or Unique Key constraints.
  3. In case of ONLINE option is set to ON then SQL Server does not allow rebuilding or enabling a disabled clustered index.
Before enabling indexes, we should have ALTER permission on the table or view. In case of using DBCC DBREINDEX, eser must either own the table or be a member of the sysadmin fixed server role or the db_ddladmin and db_ownerfixed database roles.


There are many ways to enable our disable indexes in SQL Server which are given below-
A.   Using SQL Server Management Studio – With the help of SSMS OR SSDT, we can easily enable a disabled index. We need to expend our index folders of the data table in the database as given below-



After expending the index folder, right click on the index and choose Rebuild. It will be launched Rebuild Indexes window to verify that the correct index is in the Indexes to rebuild grid and just click on OK button as given below-


B. If you want to enable all the indexes on a table then you just need to right click on the Indexes folder and choose rebuild as given below –

In the Rebuild Indexes dialog box, verify that the correct indexes are in the Indexes to rebuild grid and click OK. To remove an index from the Indexes to rebuild grid, select the index and then press the Delete key.
C.    Transact-SQL to enable a disabled index – It is very easy way to enable a disabled index where we need to use ALTER INDEX clause as given below-
USE DEMO; 
GO 
-- Enable IndCustItems index 
-- on the dbo.TB_SalesOrders table 
ALTER INDEX IndCustItems
ON dbo.TB_SalesOrders
REBUILD; 
D.   There is another way to create index by checking if index exists then drop and create the same index again as given below-
USE DEMO; 
GO 
-- Re-creates IndCustItems index 
-- on the dbo.TB_SalesOrders table 
CREATE INDEX IndCustItems
ON dbo.TB_SalesOrders (OrdNo)
--- and then deletes the existing
WITH (DROP_EXISTING = ON); 
GO
E.   If you want to enable all the indexes on a single table then we can easily do this in T-SQL as given below-
USE DEMO; 
GO 
-- Enables all indexes on dbo.TB_SalesOrders table
ALTER INDEX ALL
ON dbo.TB_SalesOrders
REBUILD; 
GO 

Popular Posts