How to Search for Text inside PDF Files in SQL Serve part2 – Solution to Syntax error near '' in the full-text search condition '’ in Sql Server

Hi Guys, In my previous post
http://pnsoftwarestudies.blogspot.com/2017/03/how-to-search-for-text-inside-pdf-files.html

I had documented in detail on how to implement search inside pdf files, the full text query shared supported searching for a single word,  but when we try to search with multiple words, we will face below error as -

Msg 7630, Level 15, State 3, Line 40

Syntax error near 'are' in the full-text search condition 'brothers are'.

How to Search for Text inside PDF Files in SQL Server – Configuring Adobe PDF iFilter and implementing Full Text Search

Hi Guys,  In this post I will explore the implementation details for searching the contents inside the pdf's,
Writing this post based on a real-time requirement in my project, the requirement was to provide a UI where the user can enter the text to search, and display the results i.e. pdf's in a grid view that contains the search keyword with the option to download the pdf files.

Initial plan was to search by reading the pdf's from physical file system one by one, but since No of pdfs were large we decided to store the pdf files in the database.

Below Third-party libraries options were initially explored for searching pdf 's stored in File system –

Sl No	Library	Type	Remarks
1	iText	Paid
2	BitMiracle	Paid
3	FreeSpire.PDF	Free	Page limit per pdf is limited to 10 Pages in the Free version
4	Spire.PDF	Paid

To Search pdf files a software called as PDF iFilter is provided by Adobe so that we can search for pdf files that was stored and indexed in Sql Server.
Adobe PDFiFilter provides the ability for SQL Server to read the pdf contents.

Adobe PDFiFilter uses the Microsoft iFilter interface and allows third-party indexing tools to extract text from Adobe PDF files.

Below steps will all the configurations steps for configuring the Adobe PDF iFilter -  

Step 1) Full Text Search Installation check -

To check whether Full Text Search is installed or not, query the IsFullTextInstalled parameter.

select serverproperty('IsFullTextInstalled')

It should return 1

If it returns 0, the feature needs to be installed from SQL Server installation. install the Full Text Search in the feature selection page of Sql server installation

Steps to create a maintenance plan in SQL Server to clear old SQL Backup files or Log files

Hi Everyone in this post I will show how to use Maintenance Plan in SQL Server to delete old SQL Server backups periodically.

Writing this post as we were facing the issue in our office where the SQL Backups were taken periodically and because of backups the size in the drive reduces, hence we had to delete the old backups

 

Database Maintenance plans can be created through a wizard in SQL Server Management Studio (SSMS)

They are handled mainly through the Business Intelligence Studio (BIDS),

Two Plans that are of much interest–

1.       History Cleanup Task

2.       Maintenance Cleanup Task

1) History Cleanup Task - By using the History Cleanup task, a package can delete historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans.

This task executes the sp_delete_backuphistory system stored procedure and passes the specified date to the procedure as an argument.

2) The Maintenance Cleanup Task – A package that can remove the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete.

This task executes the sp_delete_file system stored procedure

We will focus on Maintenance Cleanup task and below are the steps to create

Step 1) In Object Explorer, expand the server, and then expand Management.
Right-click Maintenance Plans and select New Maintenance Plan

How to read data from text file and insert into a table in Microsoft SQL Server

Hi everyone in this post i will show you -

How to read the text data present inside all the text files in a directory and sub directory and store into a table using SQL Server.

 

If we need to read text data from a particular text file, then use the bulk insertion as below -

bulk insert [myfiles] from 'C:\Log Files\24-Jan-2017.log'

The above query will read a particular file and display in the Result window

We will use the technique of executing the "dir" command in the Stored procedure xp_cmdshell to get list of all files in a directory and sub directory.

The same has been explained in my prev post

SQL Query - To Read all the Text data from all the files in a directory and store it into a table

--CREATE THE PROCEDURE ALTER PROCEDURE dbo.sp_GetAllTextFromFiles       @FileDirectory VARCHAR(80) AS BEGIN    --CREATE TEMP TABLE AND OTHER VARIABLES    CREATE TABLE #myfiles             (ID BIGINT IDENTITY(1,1) PRIMARY KEY,             FullPath VARCHAR(2000))

SQL Query to Get List of files in a Directory and Subdirectory in Microsoft SQL Server

Hi everyone in this post I will explain - How to get all file names present in a directory and a sub directory in SQL Server by using "dir" dos command and Stored Procedure xp_cmdshell.

To understand the query, first we need to have complete understanding of dir command. Lets explore the dir command first.

If you have complete understanding of dir command then you can skip Step 1

Also I will using the stored procedure xp_cmdshell To execute the DOS command from SQL Server.

Step 1) Understanding DOS Command dir.

DOS Command to list files in a directory are - dir "C:\Log Files"

Output Analysis – As you can see only files and Folders of particular directory shown Also Hidden files are not show.

Display Parameters - /A - Displays files with specified attributes.                   -  Prefix meaning not      Attributes   D  Directories                R  Read-only files                   H  Hidden files               A  Files ready for archiving                   S  System files               /B - Uses bare format (no heading information or summary). /S - Displays files in specified directory and all subdirectories. Command - dir "C:\Log Files" /A-D Displays Only Files in the Directory with Header information. Command - dir "C:\Log Files" /A-D-H Displays Only Files in the Directory and Removes Hidden Files but Header information will be displayed (Note - 27-Jan-2017.log is hidden file) Command - dir "C:\Log Files" /A-D /B Displays Only Files in the Directory without any Header information Command - dir "C:\Log Files" /A-D /B /S Displays Only Files in the Directory and Sub Directory without any Header information

How to Execute Sql Function that returns a Table EXECUTING Table-valued functions in Sql Server

Must declare the scalar variable was the error initially faced while executing Tabled Value function like Normal Function Execution, Hence writing this post.

As we all know that Scalar-valued functions return a single value whereas Table-valued functions returns tabular result sets that is it returns a table as output.

Summarizing - User-defined functions that return a table are referred to as table-valued functions.

Difference between SET and SELECT Statements in Microsoft SQL Server and when to use them SQL Server Interview Question

Hi, this post explores the difference between SET and SELECT Statement in SQL Server and when to use them.

SET	SELECT
STANDARDS
SET is the ANSI standard for assigning values to variables	SELECT is not ANSI Standard.
MULTIPLE VALUES ASSIGNMENT
SET allows you to assign data to only one variable at a time Example: DECLARE @varFirstName nvarchar(30),@varLastName nvarchar(30); SET @varFirstName = (SELECT FirstName FROM EmployeeMaster where EmployeeId = 1) SET @varLastName =  (SELECT LastName FROM EmployeeMaster where EmployeeId = 1) PRINT @varFirstName;--Prints Alpha PRINT @varLastName;--Prints A Example2: SET @varFirstName = 'Alpha'; SET @ varLastName = A;	We you can use SELECT to assign values to more than one variable at a time. In performance SELECT is better. Example: DECLARE @varFirstName nvarchar(30),@varLastName nvarchar(30); SELECT @varFirstName = FirstName, @varLastName = LastName FROM EmployeeMaster where EmployeeId = 1 PRINT @varFirstName;--Prints Alpha PRINT @varLastName;--Prints A Example2: SELECT @varFirstName = 'Alpha', @varLastName = 'A'
SUB QUERY RETURNING MULTIPLE VALUES
SET Statement throws error when the sub query returns more than one value Example: DECLARE @varFirstName nvarchar(30); SET @varFirstName = (SELECT FirstName FROM dbo.EmployeeMaster) SELECT @varFirstName--Returns Error PRINT @varFirstName--Returns Error Errror Returned – Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.	SELECT Statement will not return when sub query returns more than one value Example: DECLARE @varFirstName nvarchar(30) SELECT @varFirstName =  FirstName FROM dbo.EmployeeMaster PRINT @varFirstName--Returns Juliet
NULL VALUE ASSIGNMENT
SET Statement will assign Null values to the variable when the result is not found Example: DECLARE @varFirstName nvarchar(30) --Assign Dummy values SET @varFirstName = 'TEST' SET @varFirstName = (SELECT FirstName FROM dbo.EmployeeMaster WHERE EMPLOYEEID = 45 )   PRINT @varFirstName--Prints NULL	SELECT Statement will not assign Null values to the variable when the result is not found instead it leaves the value unchanged Example: DECLARE @varFirstName nvarchar(30) --Assign Dummy values SET @varFirstName = 'TEST' SELECT @varFirstName = FirstName FROM dbo.EmployeeMaster WHERE EMPLOYEEID = 45   PRINT @varFirstName--Prints TEST

Hot to Add identity column or values to an existing table in SQL Server with Example

One simple question that used to strike me is whether we can add a column to a table and write a procedure or use any other method so that we can assign each row of the column with a different value like a Serial No 1,2,3,4…

The answer to the above question is the below post –

YES, we can add a column, that is an identity column to an existing table with records, when we add the new identity column on the table then the column will have values like a Serial No 1,2,3…

Syntax-

IDENTITY [ (seed, increment) ] 

here,

seed - is the value that is used for the very first row loaded into the table.

Increment - Is the incremental value that is added to the identity value of the previous row that was loaded.

--CREATE THE DATABASE - TestDatabase, IF IT DOES NOT EXISTS

IF NOT EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name='TestDatabase')

BEGIN

   CREATE DATABASE TestDatabase

END

--DROP THE TABLE - TestTableTemp, IF IT EXISTS

IF OBJECT_ID('TestDatabase.dbo.TestTableTemp', 'U') IS NOT NULL

BEGIN

  DROP TABLE TestDatabase.dbo.TestTableTemp;

END

How to Check or Trace Stored Procedures SP’s fired or executed on Microsoft SQL Server database using SQL profiler tool

Hi Everyone, In this post I will explore on how to use SQL Profiler Trace tool to check the SP’s executed on SQL Server Database.

Step 1) Open SQL Profiler - In SQL Server Management Studio -> Click on Tools Menu and Select -> SQL Server Profiler

Step 2) Under General Tab -> Select "TSQL_SPs”  in “Use the template” drop down.

How to Check or Trace Select statements fired or executed on Microsoft SQL Server database using SQL profiler tool

Hi Everyone, this is a post on using SQL Profiler to check the Statements executed on SQL Server DB.

In this post I have tried to put very less theory and have tried to explain with screenshot. Hope you will find this approach useful.

This technique not only shows Standalone Select statements executed but also the Select statements executed inside Stored Procedures.

Step 1) Open SQL Profiler - In SQL Server Management Studio -> Click on Tools Menu and Select - SQL Server Profiler

Step 2) Under General Tab -> Select “Blank” Template in the “Use the template” drop down

If “Blank” Template is not available in the drop down select “Tuning” template from the drop down.

Using DBCC CHECKIDENT to Fetch current Identity column value of a table or Reset identity column value of a table in Sql Server / DBCC reseed in sql server

Hi, in this post I will explore using DBCC CHECKIDENT Command to manually Set and update the value of an Identity column and check the current identity value for a table in Sql Server.

Note – DBCC stands for Database Console Commands for SQL Server

Syntax to create an identity column

<Column_Name> <Data_Type> IDENTITY(seed,increment)

here ,

seed -  seed specifies the initial value of the identity column.

increment - increment specifies the value to be incremented between each row of insertion.

Script to create Test_Table

CREATE TABLE Test_Table2 (                 TestId bigint identity(1,1),                 Column1 nvarchar(max) )

Create a New table by copying all rows or column data from an Existing table that is another table OR Copying only selected columns OR Copying Selected Columns and Selected Rows from an Existing table

1) Creating a New Table by copying all the columns/data from an Existing table – Here the table "NewTable" is newly created

Example:

Select * INTO NewTable FROM ExistingTable

Note - For the above query the table "NewTable" need not exist in the database, The schema will be automatically created based on the table "ExistingTable"

The Data types of the Newly Created table will be exactly same as the Existing table.

All the constraints wont be copied like DEFAULT Constraint wont be preserved where as NULL Constraints will be preserved

2) Creating a New Table by Copying selected Columns from an Existing table

Example:

Select Employee_Id,First_Name,Last_Name INTO NewTable FROM ExistingTable

Note - Here Employee_Id,First_Name and Last_Name are few of the many columns that are present in the table ExistingTable.

3) Creating a New Table by Copying Selected columns and selected Rows from an Existing table using where condition