DocumentDB Data Migration Tool Error: The remote server returned an error: (400) Bad Request

 Hey everyone, welcome back to SQL SOMETHING!!!

Today, we are taking a look at migrating Azure Cosmos DB data to a JSON file that will be stored in Azure Blob Storage. This will be using the Microsoft DocumentDB Data Migration Tool. Our focus is also more on the following error that  may occur when trying to write to an Azure Blob Storage Account:

“The remote server returned an error: (400) Bad Request”

 

Let's take a look at the solution.

Mini Post: Listing ALL Databases On A Server

Heyyyy everyone, Geon still here. I hope everyone has been well during these times and keeping safe. Remember to wash your hands and wear your mask!

Now, I realized I made a few listing queries over the years: how to list all stored procedures, how to list all tables with a given name, how to list all connections... but I just realized I never did the simplest of them all:

How to list all databases on a server.

To do this, we can query the sys.databases system table. This table can provide us with a wealth of database knowledge such as:

• Database Name
• Database ID
• Creation Date
• State
• Collation

You can filter on any of these fields as you would a regular query.

Please see below for a simple example query I've had to use recently:

SELECT 
s.name as DatabaseName,
s.database_id as DatabaseID,
s.create_date as CreationDate,
s.collation_name as Collation,
s.recovery_model as RecoveryModel,
s.state_desc
FROM 
sys.databases s
WHERE
create_date >= '01/01/2020'
ORDER BY name;

I hope this was useful to someone. :-)


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. :-)

Power Apps And Staying At Home

*knock knock*

Hey everyone, I am still here and today we have another SQL Something!

First though, I would like to take the opportunity to just use this space to acknowledge the happenings in the world around us, so please, bear with me. 

2020 has been a rough year so far especially with regard to the coranavirus pandemic and the escalating tensions that are currently happening in the United States of America. I do believe we can, and will, come through this but we all must do what we can to push the process along.

That being said, this post is related in part to how we are currently living and how we can use the tools available to help ourselves and others.

So here's a video! Of your's truly! With a shaky cam! And not looking my best!

And here's a blog post in a blog post about my thought process: 

• https://www.teleioscodejam.com/news/2020/5/4/april-action-strong-medicine-while-in-stayathome-mode

While NOT entirely (or mostly) SQL related, it does touch on application creation and the backend involved, so I guess it counts...?

Regardless, maybe it will inspire someone out there TO use their hard won SQL knowledge for the betterment of their team, community or country. Or world!

Be safe out there. Be good. And be good to others.

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. :-)

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...