Feeds:
Posts
Comments

Quick one today:

Table variables are ubiquitous. There are several benefits to using them in your T-SQL code. There are some nuances between table variable and temporary table that we discussed previously; Today, we’ll look at dropping table variable.

How do we DROP a table variable? A table variable is not fully a table, so we cannot use DROP statement or TRUNCATE statement.

--
--   Throw error
--
DECLARE @Table_Variable TABLE (ID2 INT)
INSERT INTO @Table_Variable (ID2) VALUES(1), (2)
SELECT * FROM @Table_Variable

TRUNCATE TABLE @Table_Variable  -- This code throws error
GO

--
-- Error message
--
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@Table_Variable'.

We get the same error message when we use TRUNCATE TABLE or DROP TABLE with table variable.

Solution:

--
--  Correct way to empty table variable
--
DECLARE @Table_Variable TABLE (ID2 INT)
INSERT INTO @Table_Variable (ID2) VALUES(1), (2)
SELECT * FROM @Table_Variable

DELETE @Table_Variable
GO

After DELETE, only the contents are removed; The variable and its structure are still available for INSERTing again, within the scope.

Hope this helps,
_Sqltimes

Quick one today:

Recently, ran into an interesting error message that looks very complicated, but is fairly straight one.

Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

The code looked something like this:

--
-- Error code
--
SELECT    ID = IDENTITY(INT, 1,1)
		, Name
		, type_desc
FROM sys.objects
GO

This is an interesting usage of IDENTITY() function – with INT datatype declaration within the function itself, with SEED value.

Looks like for such usage, we need to use INTO clause redirecting the return dataset into a table. Like this:

--
-- Correct code
--
SELECT    ID = IDENTITY(INT, 1,1)
		, Name
		, type_desc
INTO #Test_Table

FROM sys.objects
GO

Hope this helps,
_Sqltimes

Quick one today:

As alwways, there is value in formatting SQL code in a proper way. There are several ‘proper’ ways to format code; And all are great. To each, their own.

But one formatting tip that everyone appreciates is keeping Sql Server identifiers or keywords in UPPER CASE.

There is a keyboard shortcut, that allows this operation : Ctrl + Shift + U

--
--  Some random un-formatted code.
--
begin
set nocount on
declare @starttime datetime
declare @hierarchyid int
declare @nodeid bigint
declare @statuscode int

Highlight the word that you want to change to UPPER CASE and use the keyboard short cut: Ctrl + Shift + U. Similarly, changing to lower case could be accomplished with Ctrl + Shift + L

--
--  After formatting
--
BEGIN
SET NOCOUNT ON

DECLARE @starttime		DATETIME
DECLARE @hierarchyid	INT
DECLARE @nodeid			BIGINT
DECLARE @statuscode		INT

 

Hope this helps,
_Sqltimes

Quick one today:

A few weeks ago, I ran into an interesting error message:

Error Message:

DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 

After looking into it a bit further, these are the conclusions.

  • SHRINKDATABASE runs on the entire database, unlike SHRINKFILE. So, when I issued the command, the command internally tried to SHRINK both data and log file. The shrink percentage parameter indicates the amount of “shrink” that it is attempting on the database (data & log file). If there is not enough free space inside the data or log file, then Sql Server throws this error out saying it could not proceed. If the expected free space is not available on both the files, the command errors out.
  • SHRINKDATABASE cannot reduce the data and log file size beyond the initially configured number. This could also be a factor.

So, first we need to understand how much free space is available, so I could shrink based on that. For that use the following command:

--
--  How much free space is available
--
DBCC SHRINKDATABASE (SampleDB, TABULAR)
GO

 

This give output in the following format:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
2 1 163392 1024 552 496
2 2 27408 64 27408 64

Once you know how much free space is available, then you could re-run the SHRINKDATABASE command with pertinent parameter values.

 

Note:

  • Running SHRINKDATABASE command on production systems is not advisable. Careful analysis needs to happen before any steps are taken.
  • Also, this (TABULAR) option is an undocumented feature, so it could change in future

Hope this helps,

Quick one today:

Recently, I ran into this interesting error message. The actions that resulted in this error are silly, but the message is interesting. The language used in the error message dignifies the actions to the same level as other valid error messages. But in my opinion these actions are silly and it is nice of Microsoft to be kind and gentle on us.

Code that resulted in this error:

--
--  Incorrect
--
PRINT 'Purged Ended on : ' + CONVERT(VARCHAR, DATETIME)
GO

Interesting error:

Msg 128, Level 15, State 1, Line 11
 The name "DATETIME" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Corrected code sample that works:

--
--  Correct
--
PRINT 'Purged Started on : ' + CONVERT(VARCHAR, GETDATE())
GO
Hope this helps,
_Sqltimes

Quick one today:

Earlier in the lab, during set of a new ESXi host, we ran into this cryptic error.


"HostStorageSystem.ComputeDiskPartitionInfo" for object "storageSystem" on ESXi "<ip address>" failed

Looks like ESXi host, was not able to correctly wipe the disk. So, we need to manually delete the partition to make it available for ESXi host. The following steps worked for us:

Step 1: Make sure SSH access is enabled on the ESXi host.’

Step 2: Connect to ESXi host using putty.

Putty to connect to ESXi host through SSH

Putty to connect to ESXi host through SSH

 

Step 3: Enter root and password to log in.

Login using Putty

Login using Putty

Step 4: Enter the command esxcfg-scsidevs -l to list the name of the disk that you want to wipe.

~ # esxcfg-scsidevs -l
mpx.vmhba1:C0:T0:L0
Device Type: Direct-Access
Size: 237824 MB
Display Name: Local DELL Disk (naa.6842b2b0189ae90019c1b00d08f81f9f)
Multipath Plugin: NMP
Console Device: /vmfs/devices/disks/naa.6842b2b0189ae90019c1b00d08f81f9f
Devfs Path: /vmfs/devices/disks/naa.6842b2b0189ae90019c1b00d08f81f9f
Vendor: DELL Model: PERC H700 Revis: 2.30
SCSI Level: 5 Is Pseudo: false Status: on
Is RDM Capable: false Is Removable: false
Is Local: true Is SSD: false
Other Names:
vml.02000000006842b2b0189ae90019c1b00d08f81f9f504552432048
VAAI Status: unsupported
naa.6842b2b0189ae9001c7e063a1131312b

Step 5: Run “partedUtil get” command to get the details of the disk

partedUtil get /dev/disks/naa.6842b2b0189ae90019c1b00d08f81f9f

66987 255 63 9876546456
1 20 9876546456 165 128

Step 6: Now lets delete this partition using “partedUtil delete” command.

partedUtil delete /dev/disks/naa.6842b2b0189ae90019c1b00d08f81f9f 1

After running this command successfully, you could not go back to vSphere Client and recreate data store.

Create New Disk/LUN ESXi Host

Create New Disk/LUN ESXi Host

Hope this helps,
_Sqltimes

Quick one today:

For one of our lab servers, we needed reconfigure an ESXi host from dynamic (default) to static IP address. Following are the steps, that worked for us. Not sure how, screenshots could be take from a ESXi console, so only text instructions for this post.

Go to the physical ESXi machine and perform the following operations.

  • At the ESXi console screen press F2 to go to Customize System/View Logs
  • Login with your credentials (root/password)
  • Using up/down arrows, select “Configure Management Network” and hit enter
  • Select “Network Adapters” and hit enter. Make sure the status says “Connected“.
  • Go to IP configuration and change from dynamic to static IP and hit enter
  • Using up/down arrows and enter key:
    • Enter a valid static IP address in your network.
    • Subnet mask
    • Default Gateway
    • Hit enter for <OK>
  • Select DNS configuration and hit enter to change its settings.
    • Enter Primary DNS Server
    • Alternate DNS Server
    • Enter valid HostName in your network
    • Hit Enter for <OK> (accepting the settings)
  • Press Esc to main screen.
  • Hit “Y” to accept/apply the changes and restart the host for changes to take effect.

Helpful hints: For Subnet mask, DNS, Default Gateway, etc, I ran ipconfig /all on a different physical machine next to this ESXi host (in the same network) and used the same IP addresses.

After restart, the ESXi host shows the new static IP address with Hostname.

Thanks for this post for teaching me.

Hope this helps,
_Sqltimes
Follow

Get every new post delivered to your Inbox.