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

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

Mini Post: Adding a Column to an Existing Table With a Default Value

Hey again everyone and welcome to SQL Something.

Just thought I'd throw this in as well because I needed to do this earlier this week: Adding a column with a default value.

Initial query was found here (click this, it's a very cool blog).

See my slight variant below and explaination:

ALTER TABLE YourTable
ADD YourNewColumn INT NOT NULL DEFAULT(42)
GO

So as far as an explanation goes, what the above does is alter your existing table (named 'YourTable') by adding a new column (named 'YourNewColumn') of type INT with a default value of 42. Each row in the table will then have a new column filled with a value of 42.

You can change the column type and the default value in the query to suit your needs.

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

Mini Post: The Operating System Returned Error 21

Hey everybody and welcome back to SQL Something!

Today we're gonna take a quick look at a very generic error: Error 21.

You will usually get a message along the lines of "The operating system returned error 21" along with something like "The device is not ready". The second part of the error is the important bit as it specifies that, for whatever reason, the 'device' (usually your drive or the actual files on it) is not ready.

This could be due to a number of reasons (hence the generic nature of the error):

• Drive disk space issue
• Hard drive failure
• SAN failure
• Corrupt database files (MDF/LDF etc)

I would say after you check out what might be causing the issue as well as rectify it, you run a DBCC CHECKDB to ensure that you do not have any consistency issues with you database(s).


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

Mini Post: Reseed Identity Column

Hey all, when in doubt: Mini Post!

In this post we look at reseeding the identity column via T-SQL.

Please note that the rules change depending on if there are already values in your table when you attempt the reseeding.

1) With Values in Table

Suppose you want the next value in your table (Table1) to be 12. We will use the following:

DBCC CHECKIDENT (Table1, reseed, 11)

Note that your reseed value will be one less than the value you want to appear (e.g. 11 is one less than 12).

2) Without Values in Table (Empty Table)

Suppose you have an empty table and you want the next value to be 12 (for whatever reason). We will use the following:

DBCC CHECKIDENT (Table1, reseed, 12)

Note that your reseed value will be exactly the value you want to appear (e.g. we use 12).

3) Reseed via TRUNCATE

If you are going to delete all the values in a table anyway before you reseed, you can use the TRUNCATE statement instead of DELETE, and the table will automatically reseed from whatever it's initial starting value was.

TRUNCATE TABLE Table1

(Please read up on TRUNCATE before you decide that it's a viable solution for your needs)


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

Mini Post: Counting Duplicate Records

Hey all, just trying to sneak in a couple posts before the month ends (can't have a month without posts; just feels wrong).

Alright this was a useful something that I used way back whenever and recently had to reuse:

SELECT Col1, COUNT(*) AS Total
FROM Table1
--WHERE SomeCondition
GROUP BY Col1
HAVING COUNT(*) > 1
ORDER BY COUNT(*) ASC

This query will list the number of times a value in Col1 is duplicated (i.e. if appears more than one time).

Handy for finding copies of a value that is only supposed to appear once in a table.
You can, of course, adjust the "HAVING" section to suit your needs (e.g. HAVING COUNT > 3, HAVING COUNT = 1, etc) thereby creating a more generalized search query.


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

Mini Post: Change Column Collation

Hey guys, this is how you go about changing the collation of a column.

Run the following, putting in your database, column and collation names where appropriate:


ALTER TABLE dbo.YourTable ALTER COLUMN [YourColumn]
            varchar(20)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;
GO

Double check that your column collation was change by running the following on your database:

SELECT name, collation_name 
FROM sys.columns 
WHERE name = N'YourColumn';

Mini Post: Change Database Collation

So just to get this one out of the way (was bugging me that I didn't write it as yet), here's how to change the collation of a database. (See bottom of post for links to other related collation posts)

Use only one of the following solutions: EITHER Management Studio OR TSQL.

SQL Server 2008: Change Instance Collation Via Rebuilding The System Databases

Hey everyone, welcome back to the first proper length SQL Something in a while.

In an earlier post I outlined how to get queries to ignore collation. In a future mini post I will show how to change a database's collation.

Today's focus is the instance's collation.

Changing the collation of a SQL Instance is not something to be taken lightly (which is why it's good practtice to choose the correct collation the first time you set up the instance). In order to change it you have (as far as I know) two options:

• Reinstall the instance.
• Rebuild The System Databases.

So without further ado, let's look at the second option.

Mini Post: Get Sizes Of Tables In A Database

Hey all, here's something I've been meaning to put up for a while.

Its a very common question that pops up from time to time so I thought I'd share the answer in one more place on the internet.

Well two answers actually: The Report Solution and The Query Solution.

Mini Post: View Queries Currently Running On Your Instance

Hey all! Below is a quick and easy way you can see what queries are currently running on your instance.


SELECT QueryText.TEXT AS Query,
DB_Name(Requests.database_id) AS DBName,
Requests.session_id AS Session_ID,
Requests.status AS Status,
Requests.command AS Command_Type,
Requests.cpu_time AS CPU_Time,
Requests.total_elapsed_time/1000 AS Time_In_Seconds,
Requests.total_elapsed_time AS Time_In_MilliSeconds
FROM sys.dm_exec_requests Requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS QueryText

The query makes use of the sys.dm_exec_requests and sys.dm_exec_sql_text Dynamic Management Objects (DMOs). Check the links for additional columns that may be useful to you.

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

Mini Post: Adjust Queries to Ignore Collation

Heya people, gonna quickly run through how to adjust queries to ignore collation. I will return with another Mini Post to show how to actually change the collation of an instance and a DB.

You may have run into an scenario where your database has a different collation from that of your instance
(for example, the database could have been taken from an instance with a different collation than that of your current instance).

When you attempt to compare two columns, one with collation A and one with collation B, you will get the following error:
"Cannot resolve the collation conflict"
along with the names of collation A and B as well as the comparison operation that was attempted (whether it was '=', BETWEEN', 'IN' etc)

To resolve this, you can set the collation of A (the previously foreign DB) to be the collation of the current instance. In your WHERE clause where you are doing your comparisons place "COLLATE DATABASE_DEFAULT" immediately after the name of the column used for comparison like so:

...WHERE A.Col1 COLLATE DATABASE_DEFAULT in (SELECT B.Col1 FROM B)

No more error. :-)


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

Recovering A Database With Only The MDF File: These Are Your Options

Hello out there and welcome back to SQL Something!

This is one of a few blog posts I had time to complete and post (busy times). Without further ado, let's get started.

This post was inspired by a recent event where we lost the drive that our log files were on (not gonna go into the gory details about that).
Thankfully, prior to this we knew hardware weirdness was going on with the server so we moved all our production DBs to our secondary server. Whew.
Thus, losing the drive on what was formerly the production server wasn't that bad. And it then gave me some MDF files to attempt to restore at my leisure, just for trying sake.

Mini Post: Using sys.dm_exec_connections to Get Info About Current Connections to SQL Server

Hey guys! Another bite sized mini post here!

Today we take a quick look at sys.dm_exec_connections (2012 equivelent here) to get a little info on what/how sessions are connected to our instance.

As stated in the links sys.dm_exec_connections provides server level info on SQL Server connections. Using the below for example:

SELECT top 50 session_id, auth_scheme, connect_time, client_net_address
FROM sys.dm_exec_connections
order by session_id

Would result in the below:

Fig. 1: Results of querying sys.dm_exec_connections

The client_net_address, I find particularly useful as it shows the host address of the client that is connected to instance. Nice. Auth_scheme is also nice as it shows the 'how' of the connection (is it a SQL login etc).


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

Mini Post: Taking a DB out of 'RESTORING' State

Hey guys, just a quick post here on how to take your database out 'RESTORING' state.

So maybe you stopped Mirroring, maybe you were restoring transaction logs, maybe your database has an issue and curled up into itself or maybe you accidently restored a DB with the "WITH NORECOVERY" option (or heck, maybe you did it on purpose).

The bottom line is your DB is currently in restoring.

To remove it from this state, please run the following:
RESTORE DATABASE YourDBName WITH RECOVERY

Ta-da! Its back online. :-)


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

SQL Server 2012: OFFSET-FETCH

Hello all and welcome back (finally!) to SQL Something. :-)

Been a while I know, but sometimes life steps in your way a little bit. But hey even in those times, life teaches you something new.

Recently I’ve studying for the Microsoft 70-461 Exam (Querying Microsoft SQL Server 2012) so if you find posts in the future seem to be query related then you know why. :-p

A lot of the stuff I’m reading so far seems interchangeable with stuff you can use in SQL Server 2008 queries but I did run into something early that seemed kind of interesting. That thing is OFFSET-FETCH.

Mini Post: Setting Line Numbers In the Query Window

1st Mini Post!!!

I think I’m gonna do these every now and again, in addition to the weekly posts. Stuff that’s a little too small to be a normal post, but stuff that’s still useful.

Alrighty, let’s set line numbers shall we? Honestly I don’t know why a) it’s not on as default or b) it’s so roundabout to set up a simple thing but hey, that’s just me.

Remove a Database from Single User Mode

Hey everybody! Welcome back to SQL Something.

It’s been a long week but still trying to stick to my self-imposed rule of having one post per week. Here’s a simple one on how to remove Single User Mode from a DB.

Fig. 1: What a DB looks like in Single User Mode in Management Studio.

 

From my readings and my experience it seems a DB could go into single user mode in order to prevent corruption after some form of incorrect action was taken, whether purposefully or not. This recently happened to me when I tried to purposefully break replication. I tried to delete a DB while it was publishing, it then threw an error and curled up in a fetal position in single user mode. Poor little guy.

So how do we take a DB out of Single User Mode? We can try the following:

SQL Server Error: Cannot drop database because it is being used for replication.

Hey everyone! Welcome (or also hopefully "Welcome back") to SQL Something. :-)

This week’s instalment is going to be a little short and focused on fixing the above error that I ran into. 

The general consensus is that this error occurs after Replication was removed from a database and then you attempt to delete the DB. Apparently there might be some replication metadata that was left behind that would cause SQL Server to believe that replication is still taking place. If you attempt to delete the DB, you’ll get the error and it may go into "single user mode" (Access to the DB is restricted to one user; I may make a post on how to get out of it in the future).

As usual I’m gonna post everything I read on how to fix it as well as what I actually did.

Using SQL Server Table Hints: NOLOCK

Hey everybody, Geon here again with another exciting instalment of “SQL Something”.

Today we are going to be looking at querying using NOLOCK.

When I first started working where I am, I used to run my SELECT queries ‘normally’, that is, without using the NOLOCK feature. I remember one day I put a large query to run (one that normally took about 30 minutes) with the mind-set that I’ll “check back on it in a bit”. Ten minutes later one of our sys admins pointing out that one of our applications started throwing a bunch of errors, all database related. This app almost continuously wrote data to its database. Suddenly, it could not insert anything into one of its database tables and was kicking up a massive fuss because of it.