Mickey's T-SQL Ponderings

Sharing my tidbits of knowledge on T-SQL, SSRS, and whatever catches my fancy.

August 20, 2014
by Mickey Stuewe
0 comments

SQL Server Data Transferred to a SQLite Database Using SSIS

The Sixth Mission

In this installment of SQLCoOp, we are writing about SSIS. For me, this happens to tie closely with my the June SQLCoOp post I wrote, called Data Models, SQL Server, SQLite, and PowerShell. In that post I walked through how to create a data model in ER/Studio Data Architect and have it end up in a SQLite database. In this post I’ll show how to use SSIS to get master data from a SQL Server database and insert it into a newly created SQLite database that can then be deployed with client applications.

MCJJ_ALL

The Project

I needed to create a consistent way of pre-populating an empty SQLite database to be used in new builds of our application, so I turned my development SSIS package that moved test data from SQL Server into the SQLite database into a production SSIS package that pre-populated master data into a SQLite database.

Before you get started trying this example out, make sure that you have an ODBC Driver for the flavor of SQLite that you are using installed on your computer. Here is a great resource for ODBC Drivers. After the installation is finished, setup an ODBC connection to the SQLite database that you’ll be populating.

Step One

The first step I did, was run a DELETE statement for each of the tables that I’ll be populating. This allowed me to not worry about the destination database being populated from last time I ran the package. I used an Execute SQL task for each table. I set the Connection property to my ODBC connection I created and I set the SQLStatement property to the corresponding DELETE Statement.

SQLCoop6_Image1

 

SQLCoOp6_Image2

Step Two

For the next step I created a Data Flow task for each table. Inside each Data Flow task I created an ADO.Net Source and ADO.Net Destination tasks.

For each of the tables I wanted to populate, I created a unique stored procedure in a utility database called SSISMigration. This allowed me to customize the needs of each of the stored procedures without worrying about the needs of the applications using similar stored procedures in the source database.

I set the  ADO.Net Source connection to the SSISMigration database. I set the Data Access Mode to “SQL Command” and the SQL Command text to an execute statement such as “EXEC MSSL. GetAddressType”.

Side Note: If you want to pass a parameter to the stored procedure in a generic form, then check out the Expressions property of the parent Data Flow task.

 

SQLCoOp6_Image3

For the ADO.Net Destination task, set the Connection Manager property and select the table that you would like to have populated. Then click on the Mappings menu item to verify that the columns are mapped correctly between the Source and Destination tables.

SQLCoOp6_Image4

Step Three

Now I can connect each of my Execute SQL Tasks with their corresponding Data Flow tasks. This will ensure that the delete process occurs before the insert process.

SQLCoOp6_Image5

Step Four

The first time I built this process, I had all the statements connected in one big line. This made the processing an asynchronous process. I then learned that I could have multiple processes happening at the same time. But what if I have one last process that needs to occur after all other processes are finished? This is where the awesome Sequence Container comes into play. By placing all my Delete/Insert processes into the Sequence container, I can have them run synchronously, and still have a final process occur after they have all completed.

Step Five

Finally, I need to clean up after myself. Since I did some deleting and inserting, I want to make sure that my brand new SQLite database is as small as possible. SQLite has a command called VACUUM. This command rebuilds the database to remove any fragmentation caused by CUD operations. To run the VACUUM command, I use another Execute SQL Task. I set it up like I did in step one, with the SQL Statement set to “VACUUM;”.

Now I have three processes that occur synchronously and one process that happens after the first three are completed.

SQLCoOp6_Image6

Step 6

Run the package. If I’m only making this “new” master copy of the database for development, then I might just run the package from Visual Studio on an as needed basis. If I want this to be an automated process, then I can set the package up on SQL Server to run on a schedule.

But Wait, There’s More

My example here was for populating a new SQLite database with master data from SQL Server. I could use this same process for creating a subset of data for testing needs or for creating new SQL Server databases that need to be deployed in new environments.

Don’t Stop Yet

Don’t forget to check out these blog posts by the rest of the SQL CoOp team on the subject of SSIS:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next time!!

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest

August 12, 2014
by Mickey Stuewe
1 Comment

T-SQL Tuesday #57 – SQL Family to the Rescue of a Local Community

My good friend Jeffrey Verheul(b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. This month’s invitation is on the topic of SQL Family and Community.

I was very happy to see this topic this month since I just had an amazing SQL Family kind of day a week ago and this is the perfect venue to share it.

The Community

The beginning of this year I took a leap of faith and started a local PASS chapter focusing on BI in Irvine, CA. It’s called BIG PASS Community. I’ve been slowly growing the group steadily each month. I have a hand full of people who come every month and we usually have 1 or 2 new people as well. A couple of months ago, I was approached by a SQL Family member, Rob Hatton, who was a recent transplant from Florida. He wanted to help with my new community, so now we are co-leaders.

Before each meeting, we eat dinner together in the kitchen at our venue so that we can get to know each other better and network. Not only is our venue really nice, but Rosalyn, who is a Sales Rep there and stays late for us, is a wonderful hostess. She helps promote our meetings and helps take care of little details. We’re really blessed to have her.

The phone rang on a Friday afternoon

Rosalyn often calls me before our meetings to find out who our latest speaker is and if they will be presenting locally or not, so I wasn’t surprised to receive a phone call from her. This time she had some bad news for us. She had decided to leave the company to pursue a new opportunity, but she couldn’t find another person to host us in the evenings at their facility. We were now without a venue.

I was really worried about loosing our venue since I’ve seen other user groups unable to meet for months until a new location was found. Our group also doesn’t have any financial resources to pay for a venue either. What was a girl to do?

Twenty-four hours

I spoke with my co-leader about the situation and we developed a plan. We would change the next meeting to a networking event at a restaurant. That would allow us to still have a meeting and give us a month to find a new venue. I sent out emails late Sunday night to our community members letting them know what had happened and the new schedule for our next meeting.

Monday morning I received an email from David, who is one of our community members, “We have a classroom at work. Do you want me to see if we can use it?” I replied, “YES!” He kept updating me throughout the day with his progress on getting approval. Then I received an email from another community member named Ted. “We can use our classroom at work. We also have a nice break room for our dinners together.” We now had a venueand a potential backup venue.

This is a perfect example of the heart of SQL Family. They step in when someone needs help and lend a hand.

Building relationships

I want to share with you how I met each of the people I mentioned above.

In the spring of 2013 I was at the after party at the Orange County SQL Saturday. One of my friends wanted to introduce me to someone who recently moved near me. His name was Rob Hatton. I had the wonderful opportunity to get to know him and his lovely wife Barb better that night. We then crossed paths at two other SQL Saturdays over the last year.

Last year, at PASS Summit I helped host the Southern California User Group tables at lunch. I wanted to connect with more people in my area. We had several new people join us for lunch who weren’t aware of the local user groups. Two of those people were David and his co-worker James. We had a great time getting to know each other at lunch and was delighted to see them at many of the evening events where I had the opportunity to speak with them further. When I started the BIG PASS Community user group, they started attending it as well.

This past April was the local SQL Saturday event in Orange County. That was where I met Ted. He attended both of my morning presentation. We crossed paths again at lunch, where we had time to network further. I was able to tell him about the local user groups and encourage him to attend them. I was really happy to see him at the June meeting.

Why am I sharing this with you? Wellto show you how integrated our community is. To show you how the roots travel far. SQL Family connections don’t all start in a classroom or on twitter. They start at lunch tables, Karaoke bars, and walking between presentations.

Thanks for all the fish

Thanks go out to Jeffrey Verheul for hosting this month’s T-SQL Tuesday blog party. He is one my favorite SQL Family members who I met through my T-SQL Tuesday participation. While seven time zones and a large ocean separate us, technology has allowed us to be friends, co-bloggers, and SQL Family members.

July 15, 2014
by Mickey Stuewe
0 comments

Becoming a SQL Prompt Power User

MCJJ_ALLFour SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. This month we are cooperatively blogging about SQL Prompt by Red Gate.

The Next Mission

What makes up a SQL Prompt power user? Well, they use the SQL prompt commands like they were extensions of SSMS. They know the shortcuts, and they’ve memorized the key words to call up their favorite scripts. Let’s take a look what these short cuts are and how to integrate them seamlessly into your every day SQL Life.

Playing The Ebony Keys

A power user of any software is one who uses the hot keys in lieu of the menus. SQL prompt has a plethora of hot key combination.

Before I use <ctrl> + e to execute my code, I always use <ctrl> + k, <ctrl> + y. This hot key combo will structure your code based on the styles you have set in SQL prompt. This is my most heavily used hot key combo right after <ctrl> + e and <ctrl> + r (hiding the results window).

There is a new hot key combination that is coming out soon in version 6.4, and I’m very excited about it. It adds a semicolon at the end of each statement! I’ve been trying to add the habit of using semicolons for 2 years now, and it never sticks. Now I can use <ctrl> + b, <ctrl> + c and away I go.

Here is a table of the hot key combinations I use the most, plus the shortcut way I remember them.

Hot Key Combination Shortcut Hot Key Combo What It Does
<ctrl> + b,
<ctrl> + w
<ctrl> + bw Expands ALL of the wild cards.
<ctrl> + b,
<ctrl> + q
+ bq Qualifies all the SQL Objects and fixes the capitalization as well.
in beta <ctrl> + b,
<ctrl> + c
<ctrl> + bc Adds semicolons to the end of each statement. (Available soon in 6.4)
<ctrl> + k,
<ctrl> + y
<ctrl> + ky Standardizes the code based on the formatting styles that were set in SQL Prompt.

As a power user, my key strokes will look like this when I use them all at once:

<ctrl> + bw, bq, bc, ky and finally <ctrl> + e to execute my code. Here is an example of how an ugly duckling turns into a beautiful swan by applying all of these hot keys at one time.

Before


alter procedure [dbo].[salesteamassignment_rptugly](@group as varchar(1000), @startdate as date, @enddate as date)as
set nocount on
declare @uselessvar as varchar(max)
select
p.businessentityid ,firstname,lastname,s.name as store,[group] as TerritoryGroup,cr.name as CountryRegion,st.name as Territory,customercount,s.*
from
adventureworks2008r2.sales.salesperson as sp join adventureworks2008r2.person.person as p on sp.businessentityid = p.businessentityid
join adventureworks2008r2.sales.salesterritory as st on sp.territoryid = st.territoryid join [adventureworks2008r2].[person].[countryregion] as cr on st.countryregioncode = cr.countryregioncode
join adventureworks2008r2.sales.store as s on s.salespersonid = sp.businessentityid join dbo.fn_split(',', @group) as grps on st.[group] = convert(nvarchar(50), grps.separatedvalue)
outer apply (select count(*) as customercount from adventureworks2008r2.sales.customer as c where s.businessentityid = c.storeid ) as cc

After

ALTER PROCEDURE dbo.SalesTeamAssignment_rptUGLY
(
@group AS varchar(1000)
,@startdate AS date
,@enddate AS date
)
AS
SET NOCOUNT ON;
DECLARE @uselessvar AS varchar(MAX);
SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName
,s.Name AS store
,st.[Group] AS TerritoryGroup
,cr.Name AS CountryRegion
,st.Name AS Territory
,cc.customercount
,s.BusinessEntityID
,s.Name
,s.SalesPersonID
,s.Demographics
,s.rowguid
,s.ModifiedDate
FROM
AdventureWorks2008R2.Sales.SalesPerson AS sp
JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN AdventureWorks2008R2.Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
JOIN AdventureWorks2008R2.Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode
JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID
JOIN dbo.fn_Split(',', @group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
OUTER APPLY (
SELECT
COUNT(*) AS customercount
FROM
AdventureWorks2008R2.Sales.Customer AS c
WHERE
s.BusinessEntityID = c.StoreID
) AS cc;

But Wait, There’s More

Formatting your code is only half of the features in SQL Prompt. There are three other features that every power user should use. (Ok, the truth be told, there are more than 3, but my article shouldn’t be the length of a book.)

Feature 1:

SQL Prompt offers the ability to save snippets of code. This is really useful for repetitive tasks. Here is a list of articles that I’ve written talking about specific snippets that I find handy.

Placeholders are one of the most helpful features that SQL Prompt provides for you to work with snippets. The placeholders are reserved words that are variables for specific pieces of information or represent actions that can be applied to your snippet. They are always completely capitalized and are surrounded by dollar signs ($). You can find the current list for SQL Prompt 6 here.

One of the new placeholders is prefect for the power user. It allows you to insert a snippet and have part of it highlighted. Here is an example of how I like to use it. This particular snippen, which I named “st1″, will add SELECT TOP 100 * FROM in front of the table name that I”ve highlighted and copied to the copy buffer.

$SELECTIONSTART$SELECT TOP 100 *
FROM $PASTE$$SELECTIONEND$

The $PASTE$ placeholder will be replaced with whatever is in my copy buffer. The $SELECTIONSTART$ and $SELECTIONEND$ placeholders will cause the code within the placeholders to be highlighted.

To use it, I follow these steps.

1. I highlight a table I want to use and hit <ctrl> + c to get the table  into my copy buffer.

SQLCoop5_image1

 

2. I type st1 + to get the snippet. I now have a highlighted SELECT statement ready to execute, so I hit + e to execute it.

SQLCoop5_Image2

 

3. Finally, I type <ctrl> + z twice to undo the changes I’ve made.

SQLCoop5_image1

 

This allows me to quickly see some sample data from the table without ever leaving my current query window.

Feature 2:

This is a hidden gem. This feature finds all the invalid objects in your database. This is a great feature for those of you who need to perform code reviews and want to make sure that any DDL changes have not broken any existing SQL Objects.

In this example I have a table with a trigger that uses the field ModifiedDTS. I deleted the field and ran the command. It listed all the SQL Objects that are now broken due to the deleted field.

SQLCoop5_Image3

This feature does not have a Hot Key, but it is definitely worthy of a power user.

Feature 3:

This is a great “clean up” feature, especially when you are working with stored procedures with 200 + lines that you have inherited. You know the ones, they are filled with old code and have never been cleaned up. Well, this feature is perfect for it, because it finds all the unused variables and parameters. Simply use the hot key combination + b, + f. SQL Prompt, will then underline in green all the unused variables and parameters for you to evaluate for usefulness.

SQLCoop5_Image4

 

I encourage you to spend 10 minutes going through the SQL Prompt menu to find out what other goodies exist for you to leverage. There are also new features coming soon in version 6.4. Here is a great reference to all the release notes for each version, plus the release notes for the current beta version.

Don’t Stop Yet

If you want to read more about how to work with SQL Prompt, then check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next month!!

Original Post: On a SQL Collaboration Quest

July 8, 2014
by Mickey Stuewe
0 comments

T-SQL Tuesday #56 – Starting Off With Assumptions

Dev Nambi (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. This month’s invitation is on the topic of Assumptions. Dev gave some great examples for possible T-SQL Tuesday posts with Assumptions as the focus. Here is the invitation:

For this T-SQL Tuesday, the topic is assumptions. For example:

  • The sun will come up tomorrow.
  • Firewalls and anti-virus are enough to protect my computer.
  • My backups work even if I don’t restore them.
  • I don’t need to check for that error, it’ll never happen.

Your assignment for this month is to write about a big assumption you encounter at work, one that people are uncomfortable talking about. Every team has an elephant in the room.

This is a great topic, but I want to take this in another direction. I want to talk about the assumptions that should be stated at the beginning of a project.

In the Beginning

When we start a new project, we are given requirements. You might be laughing at me and saying, “Mickey, I’m never given proper requirements. I just get an email with a couple of sentences or I’m told verbally what to go do.” Well, while I agree that an email or a verbal assignment is a crappy way to be given requirements, if that is what you have, then that is what you have.

The good news is, you don’t have to leave it at that. No matter how bad of a writer you think you are, if you can expand on what little information you are given, then you will be better off in the end. And this is where assumptions come in.

Assumption
Dictionary.com
1. something taken for granted; a supposition: a correct assumption. Synonyms: presupposition; hypothesis, conjecture, guess, postulate, theory.
4. the act of taking possession of something: the assumption of power. Synonyms: seizure, appropriation, usurpation, arrogation.
Covering your behind with assumptions

Whenever I’m given an assignment, whether it is in a full blown requirements document (angels get their wings when this happens), or when I’m given some vague guidelines, I always create additional documentation. Sometimes this documentation is for me. Sometimes it is for my teammates. Sometimes it is used only as a discussion point in a meeting. No matter who the documentation is for, I always include a section called Assumptions. This section is at the beginning of the document and it lists all the elephants in the room that no one wants to look at. Sometimes these elephants are actually really drunk. (Those also get listed under a different section called Risks.)

You should list all the various pieces of information that you think are important, but are missing from what you were given. This information could be facts that you need, such as all dates will be based on UTC time. Or it could be tasks such as a new web service needs to be built by another team.

Some of your assumptions might be wrong. That is not a bad thing to have happen. If you hadn’t listed XYZ as an assumption and you coded to XYZ, then more time will need to be taken to fix your work, or worse, XYZ ends up in production. By having your incorrect assumption listed and corrected, you now know more information about your project.

Some of your assumptions will take the project owner by surprise. This is also not a bad thing to have happen. It means that you have information they were not aware of. It could also mean that they didn’t think the project all the way through. Either way they now have information they didn’t have before.

By listing out all the missing pieces of information and tasks that need to be accomplished to make your project successful, you are shedding light on the entire scope of the project. You are taking control by assuming ownership of your situation and helping the end result of the project to be a successful one.

NOTE: There is one type of document that I don’t include an Assumptions section in and that is for my SSRS Reports. Those requirements are gathered using a template so that all the answers I need are not missed. For me, this is my only exception.
Thanks for all the fish

Thanks go out to Dev Nambi for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://devnambi.com.

July 1, 2014
by Mickey Stuewe
2 Comments

I’m Speaking at PASS Summit This November

MickeyFedora2014I’m very excited to share that my abstract was one of the 144 abstracts selected for PASS Summit 2014. This will be my first time speaking at PASS Summit and I just can’t take the grin off my face.

My presentation is called Techniques for Dynamic SSRS Reports and can be found in the BI track. In my presentation we’ll go over ways to add navigation to your reports, as well as how to make a single report satisfy different users needs.

I hope to see you all at Summit in Seattle this year!

June 23, 2014
by Mickey Stuewe
0 comments

Foreign Key Constraints: Friend or Frenemy?

This month has been wild and crazy for me. I’m still in Louisville, Kentucky after a very successful SQL Saturday yesterday (#286). While I’ve been in Louisville, SQL Server Pro magazine published my first article with them. I talk about all the problems that can be caused if you don’t have Foreign Key Constraints in your database. So grab some coffee and your favorite e-reader and enjoy my article: Foreign Key Constraints: Friend or Frenemy?

June 17, 2014
by Mickey Stuewe
0 comments

Catching Up With Mickey

IMG_0555I can’t believe the year is almost half way through. I keep trying to slow the days down, but it just isn’t working. This year I’ve already accomplished so much, and I still have a long list before the year ends. Here is a recap and some events to look forward too!

January

I started the year off with a bang by starting a brand new Business Intelligence chapter in Irvine called Business Intelligence Group, A PASS Community (AKA BIG PASS Community). We consistently have 15 people every month and I’m really happy to announce that I have speakers lined up for the rest of the year! (Yippee!)

I also had the opportunity to participate in Pragmatic Work’s Training on the T’s. This is a free webinar series they have every Tuesday and Thursday.  I was able to present my Scalable SSRS Reports Achieved Through the Powerful Tablix presentation. You can still go to their website and view it.

I also had the honor of presenting remotely to the LA SQL UG for their 10th anniversary!

February

This month was spent writing abstracts for the year…and still understanding my new user group. I was also being courted for what became my new job. You can read about it here.

March

March was extra special. I had the opportunity to present at the Silicon Valley SQL Saturday. It was extra special, because it marked my 1 year anniversary for speaking in the SQL community. I also had my largest class to date! 97 people! Here was my favorite tweet of the day too. (Thanks Glenn!)

April

This month was full of meetings for our local Huntington Beach SQL Saturday that I helped host at the end of April. It was great having SQL Family come out to my neck of the woods beach.

May

I didn’t speak anywhere this month, but I did spend time every weekend writing. (Actually, I write every month.) I really enjoy participating in the T-SQL Tuesday Blog Parties, writing for myself, and participating in #SQLCoOp with my friends Julie, Chris, and Jeffrey.

June

And here we are in June, where I decided I would do EVERYTHING. I’m writing, speaking, leading, writing, and participating in #SQLHangout. Oh, and I’m getting my first dog. (More on her in a moment.)

My friend Boris Hristov (b|t|f), from Bulgaria, invited me to participate in an “episode” of SQL Hangout. We hung out in our two countries with 10 time zones between us and chatted about data types. You might not think this is an exciting topic, but it is a cornerstone to all databases. We came up with some great reasons why all database professionals should care about the data types of every field in their tables. So grab some popcorn or a glass of whiskey and hang out with us for half an hour.

 

You can find out about up and coming SQL Hangouts by following #SQLHangout on twitter, and you can find the full list of recorded SQL Hangouts here.

This month, I’ve also been blessed with a co-leader for my (now our) BI user group. His name is Rob Hatton, and I’m really happy he asked to lead the group with me.

I also had the opportunity this month to drive out to Riverside to speak with the Inland Empire User Group. This is the third time they’ve had me present, but the first time I’ve actually presented in person. Riverside is not a quick drive from where I live, but my boss, Steven was happy to be a carpool buddy for me. It ended up being a perfect presentation for him to hear, since it was on source controlling your SQL scripts with Red Gates’ SQL Source Control.

Now we get to look into the future…

2014-06-15 22.26.14Well, not to far into the future. Tomorrow (Wednesday) I’m heading out to Kentucky for a week. One of the events on my vacation will be speaking at SQL Saturday #286, Louisville. I’m really looking forward to the event since I enjoyed it so much last year. My husband and I are also going whiskey tasting with friends, we’ll hopefully be visiting the Corvette factory, and we’ll be picking up this adorable Labradoodle puppy who we’ve named Lucy. She will be 10 weeks old, and I can’t wait to hold her.

Here is a list of other events that I’ll be speaking at this year. You can also go to my 2014 Speaking Engagements page for an updated list through out the rest of the year.

I’ve applied to a few other events, but the accepted speaker lists have not been sent out for those events yet.

I’ll also be attending PASS Summit 2014 in Seattle in Nov this year. I hope to see all of you there.

June 9, 2014
by Mickey Stuewe
5 Comments

Data Models, SQL Server, SQLite, and PowerShell

The Fourth Mission

In this installment of SQLCoOp, we are sharing our views on something we’ve learned this month. While I learned quite a bit this month in order to create a new SQLite Database with test data that mapped back to my SQL Server database, I will limit this post to how my first PowerShell script aided me in creating a new SQLite Database from Embarcadero’s ER/Studio Data Architect.

MCJJ_ALL

The Project

I’ve been working on a project recently where we have a SQL Server backend on our server and SQLite databases on our client machines. The two databases have quite a few tables in common, but the two database platforms don’t share the same toolsets. Here are the hurdles I had to overcome.

  1. The SQL language that SQLite uses is similar to T-SQL, but not the same. This means scripting tables, constraints, and other SQL objects are slightly different.
  2. I use Embarcadero’s ER/Studio Data Architect (DA) to do all of my data modeling, but there isn’t a driver that will work with DA to compare my database model to a SQLite database so that I can keep them in sync.
  3. I needed to create test data in both databases, but I don’t have an environment where the databases can coexist so that I can write the proper insert scripts.

In this post, I’ll focus on the first two hurdles. The third hurdle was solved using Red Gate’s SQL DataGenerator, Python, and SSIS. I will save this story for another time since it is worthy of a blog post all on its own.

Hurdle 1: Understanding SQLite

The first thing I had to learn was the ins and outs of SQLite. I acquired a great book, Using SQLite and started reading. I also searched the depths of the internet. What I found was that SQLite likes to store almost everything as integer, numeric, and text, BUT there are data types that we are familiar with like varchar, datetime, etc. They just get “interpreted” as integers, numeric, and text depending on what the data types are. This is a good reference for understanding how the more familiar data types are actually stored in the SQLite database.

When I was first learning about SQLite, I mistook the storage classes as actual data types. I thought SQLite was going to use a type of implicit conversion, but SQLite is not the same as SQL Server. There is also a big benefit to using the more familiar data types instead of sticking to the storage classes that SQLite uses. The benefit has to do with the ADO.NET class and making life easier for your application developer who has to take data from SQL Server and get it into SQLite.

NOTE: You REALLY need to understand how dates work in SQLite. Depending on how the date and time are stored in the underlying database (string, integer, numeric) will determine what “zero” is equivalent to. Read this to find out more.

Hurdle 2: Creating SQL Scripts for Schema Changes From ONE Environment

There are several software packages out in the world that will allow you to get visual data models of SQLite tables, but I already use Embarcadero’s ER/Studio Data Architect (DA) and I didn’t want to learn another one. I also didn’t want to have my SQL Server data models in one software package and my SQLite data models in a different one. So I had to figure out how to get around this hurdle…and I used PowerShell to do it.

The first thing I did, was to create my data models. My first SQLite data model was based on my initial research which implied I should store all my data in integer and text data types since that is what the engine will be using to store the data. This was a poor assumption on my part. Since I had both my SQL Server and my SQLite database models in DA, I was able to easily update my data types in my SQLite database with their SQL Server counter parts. I did have to inspect each field manually since not all attributes were the same between the two databases. For instance, I have to use CURRENT_TIMESTAMP instead of getdate() and I had to model auto incrementing columns differently.

Note: This is a sample database

SQLCoOp_Image3

SQL Server database

SQLCoOp4_Image2

First iteration of my SQLite database

NOTE: I was able to use the Compare and Merge Utility by selecting the option to compare against another model.

SQLCoOp4_Image1

 

SQLCoOp_Image4

Second iteration of my SQLite database

Making a SQLite Script from ER/Studio Data Architect

Before we go down this rabbit hole, there is another piece of information that you need to know about SQLite. You can’t add foreign key constraints after the table is created. This is very frustrating for me since I know of some existing tables that need foreign key constraints and it will be a bigger task than it would be in SQL Server to get foreign key constraints applied. For this project, it means I need to script the foreign keys as well as the primary keys in the table creation script.

There are two operations that need to be done in order to generate a SQLite script from DA. The first is to use the Generate Database Wizard to generate a whole database instead of using the Compare and Merge Utility to compare the database model with the actual database. This operation is outlined below in steps 1 though 6. The second operation is to use a simple PowerShell script to make the necessary changes to the script so that it will no longer be a T-SQL script made to run in SQL Server, but a SQL script ready to run in SQLite. This is outlined in step 7 below.

Here are the steps you’ll need to do:

Step 1

Make sure that all auto incrementing fields are not set up as primary keys in the database model. The reason for this, is all auto incrementing fields are primary keys, so the syntax for creating an auto incrementing field is very specific.

Take a look at the EmployeeAward table in DM SQLite above. It is setup as an identity column, but not as a primary key. In order for me to have a visual cue that the field is a primary key, I used an object called a Domain. Domains are templates that can be used throughout the database model so that data types, defaults, and even definitions remain consistent and can easily be changed throughout an entire database model or across all database models if the Repository feature of DA is being used.

Step 2

Right click the physical model that you will be working with, and select Generate Database. You will be walked through a wizard. At the end you are going to want to save your settings so that you don’t have to select them again.

SQLCoOp_Image6

Step 3

On Page 1 of the wizard, you can create individual scripts or a single long script. I’ve been doing both so that I can select a single table later on if I need to regenerate it. You will also need to select the folder that you would like the scripts to be created in. I created 3 folders for my project.

  • MultiScripts (This will hold scripts for each object separately)
  • SingleScript (This will hold a single script that contains all the objects.)
  • Standardize (This holds the PowerShell script called StandardizeSQL.ps1 which will be explained in step 7.)
Step 3

I don’t do anything on page 2 of the wizard since this script will not run in SQL Server.

Step 4

Page 3 of the wizard is where the fun starts. Set the following options for the tables you want to include in the script. These options are found on the General Options tab. All the other tabs don’t need to have their options modified…unless you used features like sparse columns that can’t be used in SQLite. Then you might need to fiddle with some of the other settings.

 

SQLCoOp_Image5

Step 5

Save your settings on page 4 of the wizard so that you can quickly run through the wizard next time.

Step 6

If you are asked to open your newly created script, then say no. We’re not done yet.

Step 7

Run the following PowerShell script. You can do this by right clicking the file containing the PowerShell script and selecting Run with PowerShell. Note: The ability to run PowerShell scripts needs to be enabled first.

The script will go through all the *.sql files in the indicated folder and change the T-SQL specific syntax to the SQLite syntax. Here are the syntactical changes.

  • Int will be changed to integer.
  • Identity columns will be changed to primary key autoincrement columns.
  • The words nonclustered and clustered will be removed from primary and foreign key constraints since all indexes are nonclustered and those keywords are not supported in SQLite.

Before you run the script below, you’ll need to change the path to your scripts on line 8.

Note: there is a space before the asterisk on line 8 and there are spaces on either side of “int” on line 12.

# To Run this in the Powershell it would be written as ./StandardizeSQL.ps1
# Note: nonclustered needs to come before clustered since clustered is in the word nonclustered.

$wshell = New-Object -ComObject Wscript.Shell -ErrorAction Stop

Try
{
$configFiles=get-childitem -Recurse C:\SQLiteScripts *.sql

foreach ($file in $configFiles)
{
(Get-Content $file.PSPath) |
Foreach-Object {$_ -replace " int ", " integer "} |
Foreach-Object {$_ -replace "IDENTITY\(1,1\)", "PRIMARY KEY AUTOINCREMENT"} |
Foreach-Object {$_ -replace "nonclustered", ""} |
Foreach-Object {$_ -replace "clustered", ""} |
Set-Content $file.PSPath

}

$wshell.Popup("Success! And there was much rejoicing!", 0,"Done.",64)
}
Catch
{

$wshell.Popup($_.Exception.Message,0, "Oops! An error occurred.",48)
Break
}

After you are done running the PowerShell script, you’ll have a SQLite script that you can run in your favorite SQLite querying tool.

Here is the before and after scripts for two of the tables from my example.

CREATE TABLE Employee(
EmployeeID int NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
isActive bit DEFAULT 0 NOT NULL,
InsertedDTS datetime CONSTRAINT [DF_Employee_InsertedDTS] DEFAULT CURRENT_TIMESTAMP NOT NULL,
ModifiedDTS datetime NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID)
)
;

CREATE TABLE EmployeeAward(
EmployeeAwardID int IDENTITY(1,1),
EmployeeID int NOT NULL,
AwardName varchar(100) NOT NULL,
AwardDate datetime NULL,
CONSTRAINT FK_EmployeeAward_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
)
;

Before

CREATE TABLE Employee(
EmployeeID integer NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
isActive bit DEFAULT 0 NOT NULL,
InsertedDTS datetime CONSTRAINT [DF_Employee_InsertedDTS] DEFAULT CURRENT_TIMESTAMP NOT NULL,
ModifiedDTS datetime NULL,
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
)
;

CREATE TABLE EmployeeAward(
EmployeeAwardID integer PRIMARY KEY AUTOINCREMENT,
EmployeeID integer NOT NULL,
AwardName varchar(100) NOT NULL,
AwardDate datetime NULL,
CONSTRAINT FK_EmployeeAward_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
)
;

After

Don’t Stop Yet

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next time!!

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest

May 13, 2014
by Mickey Stuewe
2 Comments

T-SQL Tuesday #54 – How LinkedIn and Red Gate Landed Me My Job

My friend Boris Hristov (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. This month’s invitation is on the topic of interviewing and hiring. This happens to be an apropos topic for me, since I have a new job.

It was a dark and stormy night…

OK, it was 8:30 am on a Monday morning and it was a beautiful January morning. It is Southern California after all…

I was checking my email like I always do after I get my morning coffee. There was yet another LinkedIn request. I’ve gotten to the point in my career where I’m picky about who I connect with. Basically, I always keep my LinkedIn account up to date, I’ve stopped connecting with people whom I couldn’t possibly help because our professions are so different, and I don’t connect with recruiters unless they are on my good side. This particular LinkedIn request was from a CEO named Richard at a company called DeskSite. Little did I know it would change my professional life.

A String of Events

Let’s step back a few days. I was already at a relatively new job. I had been there for six months, but I had also been frustrated. It was not the position I was expecting. I decided to pray about it the whole weekend and figure out if I wanted to start interviewing again or if I wanted to stick it out for another six months. By Monday I had decided to start looking.

So, we’re back to the LinkedIn request sitting in my email box on a Monday morning asking if I want to connect. Normally I would have said no. “He’s a CEO. He’s not a SQL person. We have no common connection.”, I would tell myself. But I was in a good mood. I thought, “Sure. Why not.”

Within an hour, I had an email from Richard, and it said:
Hey, we’re looking for a Database Architect. I was wondering if you could spend 10 minutes on the phone with me to see if you could help us find one.

I happen to be a chapter leader of a local BI user group called BIG PASS Community. While our group is BI focused, I know we have database professionals that cover the board, so I agreed to speak with him to see if I could find a match.

Side Story: I went down to my car to speak with Richard so that my colleagues wouldn’t know. I didn’t want them to think I was looking for a new job. Unfortunately, I have a blue tooth speaker at my desk and it was close enough to my car that my phone started transmitting through it. (Face Palm) So, I had to move my car. Problem solved.

Richard painted a picture for me of his startup company, DeskSite. He then told me where it currently stood technically and where he wanted to take it over the next three years. He then asked if I knew anyone who might be interested in joining his team or if he could possibly lure me away from my current company.

I kind of stumbled over my words, “I…I’m available. I just decided…literally, this weekend to start looking for a new position.” I cannot tell you how happy I made him. By time we got off the phone, DSC_3187I had an in-person interview scheduled for the next night at their office.

Side Story: When Richard called to verify that I could still make it, he told me that he had seen my SQL earrings that I wore in one of my Avatars. He loved them! So I wore them to the interview.

When I arrived at their office, I was greeted with a lot of enthusiasm. They were so happy to meet me. You see, they had Googled me. They knew that I’m heavily involved in the PASS Community. They had even seen the YouTube video that Red Gate published of me speaking at one of their SQL in the City events in 2013. They absolutely loved my enthusiasm and my obsession with SQL. They had already decided they wanted me on their team. They just had to convince me that I wanted to be on their team.  (You see, I was at a startup when the dot com bubble burst. It makes me leery of startups.)

My half hour interview ended up being three hours. At some point I was offered a job. Richard then wanted to know what it would take to have me on his team. Normally I don’t bring up the fact I like to speak and attend conferences in the first interview, but I’m also not normally sought after. So I asked if they would send me to conferences. Richard didn’t even blink. He gave me an allotment of days AND a budget. Wow. I did tell him I needed it in writing. I learned the hard way by taking something like that on good faith.

I was not prepared to have left with a verbal offer in hand. I was definitely thinking this offer was too good to be true. So like a good data professional, I started researching.

Over the next week or so, I asked many of my SQL friends what they thought. I sent Richard quite a few questions about the position, the company, the stock options, the offer, and even the culture of the company. (If you have never worked at a startup, they are VERY different than a mid-size or larger company.) I also made two more visits to their office.

The first trip was at lunch. You see, Richard’s ideal company is more like a family and families eat together. For those of you who don’t know me, I’m a people person. I despise eating by myself. It depresses me. Now that I have a Kindle, I have gotten more used to it, but I still prefer to eat with people. The bottom line is, I loved the culture of the company and Richard was one step closer to getting me on his team.

The second trip was a technical trip. They wanted me to meet with one of the consultants they use, to make sure I knew what I said I knew, and to talk deeper about the technical environment I would be working in. This one hour interview ended up being three hours. I think the technical part was only an hour, the other two hours was about the company… And my acceptance of their offer. (Aaacckkkk!)

Side note. I don’t do anything without talking to my awesome husband. I did take a bathroom break and talked to him on the phone for a bit and he was completely supportive of me taking the position.

The money dance

This is the part that has always been hard for me. Making sure I get paid fairly. I was asked what I wanted for a salary. I spent several hours researching what my salary should be. I have a wonderful friend who I got to talk real numbers with. I knew that I had been undervalued 2 jobs ago, but that had to do with the growth I experienced at that company. I grew professionally so fast when I first got involved with PASS that my salary soon became disproportionate to my knowledge, but because of red tape, my salary could not be fixed.

Anyhow, I finally came up with a number and sent it in. They made me an offer based on that number. It was made up of cash and options in the company. Unfortunately the cash portion was much too low. I was crest fallen. I know I could make a lot of money when the company goes public, and I really do think it will, but I have a daughter who will be heading to college in two years. I can’t risk her education or her younger sister’s education.

So what did I do? I talked to my friend who was also crest fallen for me. He offered some great advice and helped me devise a counter offer. I thought for sure it wouldn’t happen.  A week went by with no word. I prepared to start interviewing with other companies.

Then the clouds broke

I then received an email apologizing for the delay. They were in the processes of acquiring a larger office space do to the growth of the company and it had taken up much of their time. They really wanted me on their team. They understood my financial needs, but they had to discuss my counter offer.

In the end we came to an agreement and I became a member of an amazing team. I have been at DeskSite for a month and a half as of this writing. I’m very excited about the challenges ahead of me and I’m happy about being part of an amazing team.

So you see, I owe my awesome job to LinkedIn and Red Gate. I suppose Google should be added to the list since it was used to find my SQL in The City video on YouTube. So, YouTube should be added to the list as well……………

Thanks for all the fish

Thanks go out to Boris Hristov for hosting this month’s T-SQL Tuesday blog party. I always love and appreciate Boris’ enthusiasm about participating in T-SQL Tuesday, so please visit his website at http://borishristov.com/.

April 30, 2014
by Mickey Stuewe
6 Comments

New and Improved SQL Search By Red Gate

MCJJ_ALL

The Third Mission

In this installment of SQLCoOp, we are sharing our views on the newly released SQL Search 2.0 product by Red Gate. This amazing product is free to the public and can be downloaded here.

 


 

SQL Search is one of those amazing products that you just can’t believe they give away. While I always find it useful, I found it the most useful this year when I was getting to know a new (to me) database. Why? Because I could quickly and easily search all the databases for particular keywords. Below I’ve included some of my favorite Use Cases for using SQL Search.

Use Case 1: Where did I leave my TODO list?

One of the things I love to do is leave notes for myself in my SQL objects, but I’m not always good at remembering where those notes are. With SQL Search, I can find them easily by searching for the keyword TODO. Now I can go a step further and look for all the TODO’s that ALSO have my name.

 

I do this by using the AND operator to search for the two keywords todo and mstuewe. There are some special rules to note.

1. AND and it’s counter part OR are both case sensitive.

2. You can only use one at a time. I can have todo AND mtuewe AND 2014, but  I can’t have todo AND (mstuewe OR mickey). (Maybe in a future version – hint, hint.)

 

SNAGHTML341ae0

Use Case 2: How many databases do I need to look through?

George is new to the company and he needs to get to know how orders are stored in the database. He decides to use SQL Search to look at all the tables that have Order in their name. He is a bit shocked when he find out that there are hundreds of databases on the server and he is not sure which ones he should start with. George, luckily got some help from one of his co-workers Sarah. She told him he should start with three specific databases.

 

In previous versions of SQL Search, you could look through one database or all the databases. This could lead to a very long list of values if you have quite a few databases on your server. This is now a problem of the past. (And there was much rejoicing.)  Now you have the option of selecting only the database you are interested. You can even select the system tables which are all listed at the bottom  of the pull down window.

 

image

 

Use Case 3: Rolling the dice for a job.

imageLiz needs to find all the SQL objects that use the SalesOrder  table, but she isn’t interested in searching through tables and views. She does, however want to search through all the jobs first, and then she’ll take a look at triggers, stored procedures and functions.

 

I found this next new feature a huge bonus. Jobs are not something I’m looking through often, but now I have a way to search through them without any trouble. Simply, select Jobs.

 

I’m also very happy to be able to select ONLY the database objects I want to search through. For me this means I can ignore tables and views. (Yippee!)

Use Case 4: Getting to know your neighbors.

There have been some implicit conversion problems with some of the date fields in the database. Sarah has been tasked to verify that all fields that end in “DTS” are actually datetime data types and not date data types.

 

Red Gate has added the ability to see table definitions in the results pane. It’s a simple view of the table structure, but it beats having to go back to the Object Explorer to see the entire table structure. Now when you’re researching field data types, you can do it quickly and efficiently.

 

image

Bonus Tips

Here are a few bonus tips that make SQL Search even more useful:

1. You can highlight all the rows in the grid, copy it, and paste it directly into Excel for further reference. This can be very helpful if you are changing all the objects in the list and you want to keep track of the original list. (This isn’t a new feature, but it is an awesome one to know about.)

2. They have improved the indexing of the database objects. This is GREAT  news. In the past, if you were expecting a modified object to appear on the list and it didn’t, you would have to close SSMS and restart it in order for the SQL objects to be properly re-indexed. You no longer need to do that. Just go to the Index menu item, and select Reindex.

3. Each of the columns in the grid can be sorted. This can be very handy if you want to deal with all the tables at one time, or a particular schema first.

 

image

Don’t Stop Yet

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

 

See you next time!!

 

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

 

Original Post: On a SQL Collaboration Quest