Using LAG To Find The Difference In Timestamps Between Rows

Hey everyone, tis another merry entry of SQL Something!

Slipping in this last post for 2019!

HAPPY NEW YEAR AND ALL THE BEST FOR THE UPCOMING 2020!

Today we are going to look at using LAG and specifically how to use it to find the difference between times, where one time value... IS IN THE PREVIOUS ROW.

Turns out it's really simple using LAG (available in SQL 2015 onwards).

The File "...\TestDB.mdf" Is Compressed But Does Not Reside In A Read-Only Database Or Filegroup

Hey everyone, welcome back to SQL Something!

Today we are looking at an old error that I've been meaning to post:

The file "...\TestDB.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.


This error popped up when I attempted to bring a DB online via the following command:

USE [master]
GO
ALTER DATABASE TestDB SET ONLINE
GO

 As the error message indicates, there seems to be some kinda file compression going on somewhere. This compression could either be on the folders where the MDF and LDF files for the database reside or, the compression could be on the files themselves.

Thankfully, checking the folders and files for compression and then turning compression off, is the same.

• Right-click the folder containing the MDF/LDF files or right-click the files themselves.
• Choose 'Properties'. (Fig. 1)
• Choose 'Advanced...'.
• Uncheck "Compress contents to save disk space".
• Click 'Ok'.
• Click 'Apply' and 'Ok'.

Fig. 1: Smash that Advanced button

Fig. 2: Remove the compression

Repeat the above for any folders or files related to the database. Note: If a folder is compressed, the folder name will be in blue font. If a file is compressed, the file icon will be slightly different.

Hopefully that should end your woes. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Dropping Multiple Tables That Include A Specific String In Their Names

Good day everyone!

It is I, Geon Bell, back with another SQL Something! (Still alive!)

Today we are going to look at something questionably dangerous: Dropping Multiple Tables at Once Via One Query!

Yeah, I'm shocked at myself for even suggesting that.

Not gonna lie, I still get very... anxious when performing table drops, much less doing them en masse, but it'll be fiiiine. Maybe.

On a serious note, my scenario was as follows: I had come across a situation where I restored a Azure SQL bacpac on my local machine, in order to fiddle around with it safely. The database, however, was part of an Azure DataSync and came with a bunch of tables I didn't need. Thankfully they all ended with the same suffix.

I didn't want to drop each table individually, so I went looking across the internet for an easier way and found this link: https://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string

Thank you very much to Curt and Filip!

Now the query they gave was as follows:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

When I ran it (with my appropriate suffix), I got the error: "Cannot drop table MyTableName because it does not exist or you do not have permission." for each table.

Checking the online again, I saw that people removed the '[' brackets from their regular drop statements to solve that issue. It still didn't work, so I also added the schema name as well.

I then combined the three solutions: The original query, AND I removed the squared brackets AND added the schema name. The final working solution for me was as follows:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table SchemaName.' + Table_Name --Add schema name, remove brackets
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

And that's that. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

The remote procedure call failed. [0x800706be]

Good day everyone and welcome to (or welcome back to) SQL Something!

Firstly, HAPPY 2019! I wish all my visitors the best for the coming year. May you all find your own special brand of success and happiness. 😊

Alright let’s get into it. I recently redid my PC and I had to re-install my various SQL instances as well as a couple variations of Visual Studio. Sometime after all of that was done, I opened SQL Server 2017 Configuration Manager and saw the following:

The remote procedure call failed. [0x800706be]

Woe is me...