DataType can matter in where condition - a worst case scenario by serverku

We should use proper filter in where conditions as per data type. Like if the data type of filtered column is varchar then use filtered variable or value varchar, if filtered column is an integer then filtered variable/value should be an integer.

Worst case,

 
-- #1. 
-- If OrderId column is VARCHR, this cause table scan
-- because here OrderId column all values convert from varchar to int 
-- even if index created on it 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

Best case,

 
-- #1. 
-- If OrderId column is INT, this will work fine 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

-- #2. 
-- If OrderId column is INT, this will also work fine 
-- because here value '123' convert from varchar to int 
SELECT * FROM OrderDetails 
WHERE OrderId = '123'

Create a table with sample records with different data types as mentioned in above queries and check the execution plan, you may see the difference for both of them.

Table scan or Unexpected output due to improper where condition applied for DateTime filter by serverku

When you are using date filter with queries, like to find the records from tables for the particular year/month, we are approaching some extra data type conversion on fields. Below are some examples.

Should not use,

 
-- #1. : This cause to table scan
SELECT *  
FROM OrderDetails
WHERE datepart(month,Orderdate) =?
AND datepart(year,Orderdate) = ?

-- #2. : This cause to unexpected output 
SELECT * 
FROM OrderDetails
WHERE convert(varchar,OrderDate,112) between
'20110101' and '20110131' 

Should use,

 
-- #1.
SELECT * 
FROM OrderDetails
WHERE convert(varchar,Orderdate,112) >= '20110101' 
AND convert(varchar,Orderdate,112) < '20110201' -- EndDate + 1 

-- #2.
SELECT * 
FROM OrderDetails
WHERE cast (OrderDate as datetime) BETWEEN
 '2011-01-01 00:00:00.000' AND '2011-01-31 23:59:59.900'

Create table with sample records or use existing table which has datetime data type field, Run query above and check the results with execution plan. Hope you like it.

COUNT(*) with a LEFT JOIN may produce unexpected results - SQL Server by serverku

We should not use count (*) for left outer join two or more tables with group by. Instead we can use right table's column for count, otherwise it will come up with unexpected output.

Way cause for unexpected output

 
SELECT 
    a.id, 
    COUNT(*)
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO

Right way

 
SELECT 
    a.id, 
    COUNT(b.id) 
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO

Create tables with sample records, run above queries for them and see difference. Did you know this earlier?

Trace the query with specific session in SQL Server Profiler by serverku

One more enhancement i found with Query Editor of SQL Server 2012 and later version, we can trace the query with specific current session in SQL Server Profiler. For that just need to right click in Query Editor and you can find the option Trace Query in SQL Server Profiler or press CTRL + ALT + P, which will open SQL Profiler filtered with that current session.

Below is a screen shot for the same.

Did you know this or used ever?

Invoke Scheduled Job on demand in SQL Server by serverku

We are mostly scheduled the job for the any script, but I have prepared the SP which invoked Job on demand as required, below code which I have created by SP and called it from the UI.

USE MSDB
GO

DECLARE @JobName VARCHAR(100)  
SET @JobName = 'YourJobName'

EXEC MSDB.DBO.Sp_start_job @JobName  --You can also invoke with JobId
GO

When you run it gives message, "Job 'YourJobName' started successfully." On second run, if first request is in queue, it will return with the following message, "Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: Request to run job YourJobName (from User sa) refused because the job already has a pending request from User sa."

Run Script at once in all the databases of single instance of SQL Server by serverku

Created IO.BAT file with defining server, database, user name and password. Then Put your task batch or Stored procedures in IN.SQL file which you want to run in all the databases defined in IO.BAT. After that double click or run IO.bat file, which will execute scripts from IN.SQL and generate output for each run for every databases for any errors occurred or not.

IO.BAT 
-------Content--------------------------
OSQL -UUsername -PPassword -SServerName -dDatabasename1 -i C:\OSQL\IN.SQL -o C:\OSQL\DatabaseName1_Out.TXT
OSQL -UUsername -PPassword -SServerName -dDatabasename2 -i C:\OSQL\IN.SQL -o C:\OSQL\DatabaseName2_Out.TXT
OSQL -UUsername -PPassword -SServerName -dDatabasename3 -i C:\OSQL\IN.SQL -o C:\OSQL\DatabaseName3_Out.TXT
OSQL -UUsername -PPassword -SServerName -dDatabasename4 -i C:\OSQL\IN.SQL -o C:\OSQL\DatabaseName4_Out.TXT
OSQL -UUsername -PPassword -SServerName -dDatabasename5 -i C:\OSQL\IN.SQL -o C:\OSQL\DatabaseName5_Out.TXT
----------------------------------------

IN.SQL
-------Content--------------------------
Your tsql batch or stored procedures
----------------------------------------

DatabseNames_*.TXT
-------Content--------------------------
Output generated of IN.SQL for databases 
generated individual file for each
--------------------------------------------

Extract images from database by serverku

As we learned for the import the images into the database from folder in the last post. One more thing is also interesting to learn here as how can we extract images from database.

SP_CONFIGURE  'Ole Automation Procedures',1
GO
RECONFIGURE
GO

SELECT * FROM [IMAGEINSERT]
GO

DECLARE @Image VARBINARY(MAX)
SELECT @Image = (SELECT [Image] FROM [IMAGEINSERT] )
DECLARE @ObjectImage INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectImage OUTPUT
EXEC sp_OASetProperty @ObjectImage, 'Type', 1
EXEC sp_OAMethod @ObjectImage, 'Open'
EXEC sp_OAMethod @ObjectImage, 'Write', NULL, @Image
EXEC sp_OAMethod @ObjectImage, 'SaveToFile', NULL, 'D:\image\ImageExport.jpg', 2
EXEC sp_OAMethod @ObjectImage, 'Close'
EXEC sp_OADestroy @ObjectImage

Insert Image into database from Folder using T-SQL in SQL Server by serverku

For the some project workaround I experienced with insert, some of the images into a database for need, You can do it with OPENROWSET,

CREATE TABLE [dbo].[ImageInsert](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] IMAGE -- or VARBINARY(MAX)
) ON [PRIMARY]
GO

INSERT INTO [dbo].[ImageInsert]([Image])
SELECT * FROM
OPENROWSET(BULK N'D:\image\ImageInsert.jpg', SINGLE_BLOB) AS Images
GO

Disable Guest User in all databases in SQL Server by serverku

As a database security, we should disable guest account from databases, You can do it with revoke access from that user.

USE yourDataBaseName
GO 
REVOKE CONNECT FROM GUEST;
GO

Sharing/Unsharing remote server's folder with remote stored procedure execution in SQL Server by serverku

Recently, when I was working with database backup plans and scheduling it on another server as this shared folder of the server should not be available to anyone after the backup process completion. I have created below script with stored procedure on remote server and run the stored procedure remotely from local server to share and unshare the remote server's folder. Please note xp_cmdshell and Ad Hoc Distributed Queries should be enabled on the server.

USE MASTER
GO

-- Creating shared 
EXEC XP_CMDSHELL 'NET SHARE SharedFolder=E:\DatabaseBackups
     /GRANT:Everyone,Full
     /REMARK:"Database backups perform on another server"'    
GO

-- Removing shared
EXEC XP_CMDSHELL 'NET SHARE SharedFolder  /delete"'
GO

Run query against all the databases without MSFOREACHDB and WHILE/CURSOR by serverku

I have learned one more thing today, for small queries when we need to run it for all the databases of instance, then we are mostly using MSFOREACHDB and WHILE loop or CURSOR to get the data. But with COALESCE I can I do with very small code here, which collect count of the objects for each database.

DECLARE @ObjectSQL NVARCHAR(MAX)
SET @ObjectSQL = ''
SELECT @ObjectSQL = COALESCE(@ObjectSQL,'') + CHAR(13) + CHAR(10) 
    + 'SELECT ' + QUOTENAME([Name],'''') + ' as DbName, 
    COUNT(1) AS CntObject 
    FROM ' + QUOTENAME([Name],'') + '.DBO.SYSOBJECTS;'  
FROM SYS.DATABASES 
PRINT (@ObjectSQL)
EXECUTE (@ObjectSQL)

Hope you like it.

Insert default values all columns of table by serverku

We can insert all the default values in tables without specifying values while insert in table, how? See here we have tables defined all columns by default,

CREATE TABLE defaultval(
    id int default 0, 
    name varchar(10) default 'test', 
    cdate datetime default getdate()
);

INSERT defaultval DEFAULT VALUES;

Did you ever get used to it?

Find active open transactions from a SQL Server Instance by serverku

I would like to share the script to find open active transactions and sessions at database level, here you can find the same which capture for active transactions initiated by users only. This will result for all the databases, but you can make a database filter as well. This result is most relatively as DBCC OPENTRAN check in a particular database.

USE master
GO

SELECT 
       est.session_id as [Session ID],
       est.transaction_id as [Transaction ID],
       tas.name as [Transaction Name],
       tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas 
INNER JOIN sys.dm_tran_database_transactions tds 
       ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions est 
       ON (est.transaction_id=tas.transaction_id)
       WHERE est.is_user_transaction = 1 -- user
       AND tas.transaction_state = 2 -- active
       AND tas.transaction_begin_time IS NOT NULL
GO    

You can see the conditions made in this script and the values for the same are as following,

is_user_transaction

• 1 = The transaction was initiated by a user request.
• 0 = System transaction

transaction_state

• 0 = The transaction has not been completely initialized yet.
• 1 = The transaction has been initialized but has not started.
• 2 = The transaction is active.
• 3 = The transaction has ended. This is used for read-only transactions.
• 4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
• 5 = The transaction is in a prepared state and waiting resolution.
• 6 = The transaction has been committed.
• 7 = The transaction is being rolled back.
• 8 = The transaction has been rolled back.is_user_transaction

Perform database backup using SQLCMD utility in SQL Server by serverku

I just went through the SQLCMD command line utility to perform database backup in SQL server where it does not have SQL agent. Below is the command which I placed in a batch file and schedule with windows task scheduler.

    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    

Above command perform a full database backup, but it does not quit from utility after performing it. So I have changed with slight change and placed -Q in place of -q for cmdline query. Otherwise next schedule will be skipped as of current schedule never come out from sqlcmd utility and keep running

    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -Q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    

This will perform backup and quit from the sqlcmd utility.

Delete files using Ole Automation Procedures in SQL Server by serverku

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

After enabling it, we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token

Find SSIS package details scheduled in jobs - SQL Server by serverku

I would like to share the script to find out the scheduled jobs which call SSIS packages in SQL Server.

USE MSDB
GO
SELECT 
 sj.job_id as JobId,
 sj.name as JobName,
 sjs.step_name as StepName,
 sjs.Command as Command
FROM sysjobs sj 
INNER JOIN sysjobsteps sjs
 ON(sj.job_id = sjs.job_id)
 WHERE sjs.subsystem = 'SSIS'
GO

The script return job name and the SSIS package full path as a command like "/FILE " here SSIS package full path" /CHECKPOINTING OFF /REPORTING E"