Import database through sqlcmd
import_db.bat
sqlcmd -S localhost -d IERP_USSL -i IERP_USSL_23_04_2024.sql
-S = Server Name
-d = Database Name
-i = Database Path...
Mohammad Towhidul Islam
28-Apr-24 07:56:17 am
Mohammad Towhidul Islam
10-Jul-23 12:35:59 pm
Find duplicates column's data query in MSSQL
SELECT
id_card_number,
COUNT(*) occurrences
FROM hr_person_master
GROUP BY
id_card_number
HAVING
COUNT(*) > 1;
...
Mohammad Towhidul Islam
14-Aug-22 11:41:50 pm
To avoid jump identity column number after force restart server
First delete all the rows where number breaks the sequence
declare @id int = 0
SELECT @id = MAX(id) FROM [IERP_ECOM_HATBAZ].dbo.[accounting_sl_contra_account_type]
--print @id
DBCC CHECKIDENT ('[IERP_ECOM_HATBAZ].dbo.[accounting_sl_contra_account_type]', reseed, @id)
...
Mohammad Towhidul Islam
12-May-22 04:21:09 pm
Create and Change User, SQL Server
/* Create a user for a specified database */
create LOGIN isl_amira WITH PASSWORD='*****', CHECK_POLICY = OFF;
GO
USE master;
GO
DENY VIEW ANY DATABASE TO isl_amira;
GO
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::IBO_GAMCA_AMIRA TO amira;
GO
/* Change Password */
ALTER LOGIN i...
Mohammad Towhidul Islam
14-Mar-22 04:59:46 pm
Identity column start from a specified number
Changing the Identity Seed
DBCC checkident ('Employees', reseed, 1000)
The next row inserted will begin at 1001.
Repairing the Identity Seed in case of duplication
DBCC checkident ('Employees', reseed)
Retrieving current Identity Seed
DBCC checkident ('Employees...
Mohammad Towhidul Islam
20-Jan-22 07:16:35 pm
Import MSSQL Database with sqlcmd
Import sql script file
>sqlcmd -S localhost -d IERP_TEST -i E: \ db_ file.sql
Note: not forward slash must be use backslash
To see tables in a database
>qlcmd -S localhost
USE your_database
go
SELECT * FROM IERP_FASTMED.INFORMATION_SCHEMA.TABLES;
GO
go
If you need to select dat...
Mohammad Towhidul Islam
19-Sep-21 10:16:33 pm
SQL Server full text query from multiple tables
1. First enable fulltext with the following query:
SELECT SERVERPROPERTY('IsFullTextInstalled')
sp_fulltext_database 'enable'
2. Then make a following query:
select id, code, name, photo_file_name, category_name category from item_master where contains((search,barc...
Mohammad Towhidul Islam
20-Feb-21 06:39:54 pm
Copy Table In Microsoft SQL Server
SELECT * INTO IERP_GARMENT.dbo.mfg_bom_master FROM IERP_MODERN_WOOD.dbo.mfg_bom_master
...
Mohammad Towhidul Islam
12-Jul-17 01:12:35 pm
Create a unique constraint that also allows nulls in MSSQL Server
CREATE UNIQUE NONCLUSTERED INDEX uni_lot_master_doc_ref
ON item_lot_master(doc_ref)
WHERE doc_ref IS NOT NULL;
...
Mohammad Towhidul Islam
12-May-17 04:39:04 pm
Delete SQL Server Old Database using cmd
Connect to the SQL Server Step 1 Open command prompt Step 2 Command as follows:
C:UsersUserName> sqlcmd -S localhostsqlexpress
Remote login
C:UsersUserName> sqlcmd -S 28.147.169.237 -U sa -P password
Display all databases in the SQL S...