Why use SQLCMD

What’s wrong with using SQL Server Management Studio (SSMS)? Nothing! What’s wrong with you! Well, I’m in a hurry.

As much as I love GNR Patience, I don’t have any… well not so much of it. Thanks Smart Phone era.

I always use SQLCMD when SSIS is not available– not all of my customers install SSIS along with SQL. I once had a project where I had to scan through terabytes of files to find certain image files. Once the files were found, it would immediately write the path to the database. Since this had to be performed and completed over the weekend, I ran hundreds instances of my C# application to seek out the files.

So here’s how it works

In the command prompt (cmd.exe), run sqlcmd /? and you’ll see the following

There’s a lot of options and the ones I frequently use are
-S Server Name
-E Trusted Connection
-Q Query

In the example below, I’m querying a table, however you can always call a stored procedure to INSERT/DELETE/UPDATE/SELECT your data instead of writing out the query– SP is safer

I hope this gives you some insight as to why/when you would use SQLCMD

Table Size

There are times when you need to migrate data or scale out your database. Here is a script to get you how much space you’re actually using per Table.

Here are 3 methods but not limited.

1. Get a specific table size

EXEC sp_spaceused N’dbo.MyTable’

2. Get all table size

CREATE TABLE #TableSize(Name VARCHAR(100), Rows VARCHAR(50), Reserved VARCHAR(50), Data VARCHAR(50), IndexSize VARCHAR(50), Unused VARCHAR(50))
INSERT INTO #TableSize
EXEC sp_msforeachtable ‘EXEC sp_spaceused [?]’

SELECT * FROM #TableSize ORDER BY Name ASC

3. Custom Code

SELECT
TableName = t.Name
,SchemaName = s.Name
,[RowCount] = p.Rows
,TotalSpaceKB = SUM(a.Total_Pages) * 8
,UsedSpaceKB = SUM(a.Used_Pages) * 8
,UnusedSpaceKB = (SUM(a.Total_Pages) – SUM(a.Used_Pages)) * 8
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.OBJECT_ID > 255
GROUP BY t.Name
,s.Name
,p.[Rows]
ORDER BY t.Name

Isolation Level and NOLOCK

In short,

• SET TRANSACTION ISOLATION LEVEL XXXXX is for the entire script
• WITH (NOLOCK) is for that specific table

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM MyTable WITH (NOLOCK)

READ COMMITTED

Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting innonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Show all current running Stored Procedures

There are times when you need to figure out what Stored Procedures are currently running and if they are blocking any if all other processes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.start_time,
req.blocking_session_id,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
req.row_count
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = ‘MyDatabaseName‘

Concatenate rows into CSV

If you have the need to take multiple rows into 1 comma separated row then this should do the trick. One of the biggest usages for this is when you’re using SSRS with multiple parameters.

DECLARE @COLUMNS VARCHAR(MAX)

SELECT @COLUMNS =
COALESCE (@COLUMNS + ‘,’ + CAST(FirstName AS NVARCHAR) + ”,” + CAST(FirstName AS NVARCHAR) + ”)
FROM MyCustomers

SELECT @COLUMNS

SELECT Linked Server

If you already have a Linked Server setup, here are a way to access the Linked Server tables.

SELECT * FROM LinkedServer..Schema.Table

In the example below

Linked Server name: CACHE

Schema: SQLUser

Table: P

SELECT * FROM CACHE..SQLUser.P

OpenRowSet

Using OpenRowSet to read all data from an Excel worksheet into a global temp table.

INSERT INTO ##ABC
SELECT *
FROM
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘,
‘Excel 8.0;Database=D:\EDI\SAMPLE_051311_1.xls‘, [Sheet1$])

CDO Email

For those who are interested in using SQL cdosysmail to email embedded HTML follow this code as a model. Keep in mind that you are required to have the Stored Procedure sp_send_cdosysmail.

SET NOCOUNT ON
— SETUP VARIABLES
 DECLARE @body1 VARCHAR(MAX)
 DECLARE @subjecttitle VARCHAR(100)
 DECLARE @email VARCHAR(200)
DECLARE @today DATETIME

SET @today = (SELECT CONVERT(VARCHAR,GETDATE()-1,101))
SET @subjecttitle = ‘Daily Sales Summary Report for ‘ + CONVERT(VARCHAR,@today)

— PROCESS EMAIL DATA
 SET @body1=’

‘
SELECTTOP 20 @body1 = @body1 +’
‘
FROM dbo.TransactionHeader
WHERE DIMENSION = 1001 AND RECEIPTDATE = @today
GROUP BY DIMENSION— SEND THE EMAIL
exec master.dbo.sp_send_cdosysmail
@From=’cbuisr@gmail.com’,
@To =’cbuisr@gmail.com’,
@Subject = @subjecttitle,
@Body =  @body1

Act	Plan	Var $	Var %	LY	Comp
‘ + CONVERT(VARCHAR,DIMENSION) + ‘	‘+ CONVERT(VARCHAR,SUM(CONVERT(DECIMAL(12,2),TOTALBEFORETAX))) + ‘	‘ + ‘VAR $’ + ‘	‘+ ‘Var %’ + ‘	‘ + ‘LY’ + ‘	‘ + ‘Comp’ + ‘

Maximum Connection Limit

The Maximum Connection Limit Has Been Reached

This article refers to any weird cases and in this case it’s making a Linked Server Connection to DB2.

To make nice with SQL run these steps.

1. sp_configure 'user connections', 0 GO

2. reconfigure with override

3. Go to services and restart SQL Server

Now when you need to make or test the connection, it should work.

Find Columns in Table

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EncTotChgAmt%’
ORDER BY schema_name, table_name;