Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

SQL Saturday #332 – Minnesota 2014

Posted by jeffprom on September 5, 2014

sqlsat332_web-white

This year I have been selected to present at SQL Saturday #332 Minnesota on what I consider to be a very fascinating topic; SQL Injections. If left unprotected, your web applications can be severely compromised by SQL Injection attacks. To learn more about SQL Injections, I invite you to attend my session on October 25th, 2014.

SQL Injections and How To Stop Them

SQL injections are a technique where malicious users can inject SQL commands into a SQL statement, through web page input. If left unprotected, SQL injections can cause havoc with web applications by compromising security, manipulating database data, hoarding system resources, retrieving sensitive data, and even removing database objects like the database itself! This session will show how SQL injections work, identify if you are being attacked, and cover various prevention techniques and their effectiveness.

There is a fantastic list of presenters at this event! Secure your spot today by signing up on the SQL Saturday website.

http://sqlsaturday.com/332/eventhome.aspx

 

 

 

Posted in Events, Security | Leave a Comment »

SQL Saturday #291 – Chicago 2014

Posted by jeffprom on March 19, 2014

I am happy to announce that I was selected to speak at SQLSaturday Chicago! This event will be held April, 26 2014 at 1221 N Swift Road, Addison, IL 60101. I will be presenting on Master Data Services.

Master Data Services
Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project.

Additional information can be found here:
http://sqlsaturday.com/291/eventhome.aspx

Be sure to register for this event today!

Posted in Events, MDS | 1 Comment »

Generating Dynamic CRUD Queries Within Microsoft Excel

Posted by jeffprom on February 21, 2014

Last year I learned a neat trick from Ross McNeely. We kept getting ad-hoc Excel documents sent to us and we needed to update the databases with these values. Traditionally you might go through the import/export data wizard, or create an SSIS package to import the data. This usually leads to data conversion issues, possible temp tables, writing extra queries, and somtimes all around headaches.

This method shows how to write CRUD sql statements within Excel that can then update the database. This is a really quick way to take a handful of fields and update data with little effort. If you have a lot of fields you may want to consider going the more traditional route because it could take some time to write long query statments in Excel. That said, I have used this technique probably 100 times so I decided to post about it. Let’s take a look at how it works.

I have two examples here that will update two tables in the Adventure Works DW database. Let’s pretend that someone has just sent us an Excel file and needs product information updated in the DimProduct table. This is what the data looks like.

First, find an open cell off to the right (F) and begin writing the sql query as shown here. Begin with =” and place single quotes where the sql statement will require them. In order to reference other data from the row, place double quotes, an ampersand and then the cell location. To continue the rest of the statement simply place another ampersand and double quote.

Continue until the rest of the statement is complete. Here is what it should look like:
=”update DimProduct set EnglishProductName=’”&B2&”‘, StandardCost=’”&C2&”‘, FinishedGoodsFlag=”&D2&”, Color=’”&E2&”‘ where ProductAlternateKey=’”&A3&”‘”

Once finished, click off of the query statement. If it worked correctly you should now see the query populated with values. If you received some errors, go back and double check the statement. Syntax issues are the most common mistakes.

Now that we have one statement that looks correct, let’s quickly fill in all the other rows. Single click the cell with the query (F2), and then double click the green square on the lower right. This will populate the rest of the rows with the same query template.

After the rest of the rows are populated, you should now have a complete set of queries.

One last trick to note is when using dates. You will need to use the text() function as shown here and specify a date format.
=”update FactInternetSales set ShipDate=’”&TEXT(C2,”yyyy-mm-dd”)&”‘ where SalesOrderNumber=’”&A2&”‘ and SalesOrderLineNumber=”&B2

The final step is to copy the query statements from Excel and paste them into SQL Server Management Studio where you can simply execute them to update the database. In the previous screenshot you would click on column D, copy, and then paste into Management Studio, and execute.

And there you have it, writing queries directly within Excel provides a quick way to take data from an Excel spreadsheet and manage data within your database with very little effort.

Posted in Excel, T-SQL | Tagged: , | Leave a Comment »

Using Microsoft Access to Import and Directly Manage Data within SQL Server

Posted by jeffprom on November 7, 2013

In this blog post I’m going to cover a couple of taboo subjects in the SQL Server realm.
1. Using Microsoft Access.
2. Giving end users the ability to import data directly into SQL Server tables and allow them to directly manage data within these tables.

Ok calm down, sit back down and let me explain how these are useful from a SQL perspective. Picture this; one source of an ETL process is to take an external file and import it into the data warehouse. Sound familiar? Absolutely. Now this file may fluctuate a bit. The data often changes and the formatting can cause issues during the import process. Also, the data values are often updated. Wouldn’t it be handy if we could instead just give the end user the ability to directly import data into a staging table? Why not give the end user the ability to update these staged values until they are ready to be imported into the data warehouse?

Now that we understand the use case, let’s take a look at a table based solution so our end users can quickly import and manage their data. Unfortunately, Excel only offers a one-way option for working with data in tables. You can import data into Excel from SQL Server, but you cannot make updates and send it back. Let’s instead turn to Microsoft Access where we can create linked tables and import data from flat files directly into SQL Server. We also get a nice ‘spreadsheet’ view of the data within the table and can update values directly on the server. Scary huh. Let’s see how we can do this.

First, we need to create an ODBC connection to the SQL Server database. On your workstation, go to the Control Panel, Administrative Tools, and select Data Sources (ODBC).

Select the ‘User DSN’ tab, and click on the ‘Add’ button.

Walk through the wizard and give your connection a relevant name and point it to the database you wish to connect to.

Next, navigate to Access. In my examples I will be using Microsoft Access 2013. From the main screen select ‘Blank desktop database’. Select a file name, loction, and click ‘Create’.

You will now be presented with an empty looking table. In order to import data to an existing SQL Server table, we need to first create a linked table connection to SQL Server. From within Access, go to the ‘External Data’ tab and click the ODBC Database icon.

Choose the second option which is ‘Link to the data source by creating a linked table’.

Next, click on the ‘Machine Data Source’ tab and choose the ODBC connection that you previously created.

Finally, select the Table you want to connect to, click ‘Next’, and then ‘OK’.

You will now have a linked table icon on the left side.

It is worth noting here that from within Access you cannot create a new table on SQL Server, or modify fields. There is a design view. However, it will prompt that you cannot save your changes for linked tables. An admin or developer will need to create the tables before hand for the end users.

Now that we have a destination table defined, let’s import some data. click on the ‘External Data’ tab and click the Excel button.

From this screen, select the data source file location and select one of the import options. We already have a table so we will select the ‘Append’ option. While this screen looks like it has options to create a new table on SQL Server, it will not. The first option will create a new local table, and the 3rd option will create a link to the source Excel file. Click ‘Ok’, and the next screen will show a preview of the data. If all looks correct, click ‘Next’ and then ‘Finish’.

The last step is to look at the data, and update where neccessary. To do this simply double click the linked table from the left hand side. This will now show data in a table view where you can alter values. Note: Data updated here will directly update data within the sql server database.

Normally SQL Server Admin’s and Developers do all of the data preparation, ETL work, and data management. By leveraging Microsoft Access, we can give end users the ability to import data directly into SQL Server staging tables and also allow them to manage data within a spreadsheet view. With proper security in place, this can help expedite ETL processing. It can also provide a cost effective data quality solution by giving end users the ability to review and cleanse data before it is imported into a data warehouse.

Posted in Access | Tagged: , | 2 Comments »

Installing Data Quality Services on SQL Server 2012

Posted by jeffprom on November 1, 2013

Data Quality Services is the new data cleansing tool provided in SQL Server 2012 Enterprise and BI Editions. DQS is a knowledge-driven solution that enables you to do data cleansing and matching through the DQS client, or automated cleansing through the DQS Cleansing transform in SSIS.

In this blog post I will show how to install DQS. When you first install SQL Server, there are two DQS options to select if you would like to use DQS. As shown here, the top option (Instance FeaturesData Quality Services) will install the DQS Server files. The second option (Shared FeaturesData Quality Client) will install the DQS client.

DQS Install File Options

Once you have the DQS files available from the SQL Server install, the next step is to run the DQS Installer. This can be found under the Microsoft SQL Server 2012 menu under Data Quality Services. Click on the Data Quality Server Installer.

DQS Menu Installer

This will launch a command prompt window and start the install. As seen here, you will need to enter in a Database Master Key password.

DQS Install command prompt

The install will continue and take on average about 7 minutes to finish. Once it finishes, you will be presented with a final message stating that DQS Installer finished sucessfully, press any key to continue. Simply press any key and the window will close.

DQS Installer command prompt finished

Congratulations, you now have the DQS server installed!

After doing a refresh on the database list in management studio, we can see there are now three new DQS databases that have been installed.

DQS_MAIN – Includes DQS stored procedures, the DQS engine, and published knowledge bases.
DQS_PROJECTS – Includes data that is required for knowledge base management and DQS project activities.
DQS_STAGING_DATA – Similar to tempdb.

DQS Installed Databases

While knowing about these databases is helpful, all of the DQS work will primarlily be done using the DQS client tool. This tool can be found in the same menu location where the DQS server install was located. When you launch the Data Quality Client tool you will be presented with a prompt to connect to the DQS server.

DQS Connect to server

Enter the instance location where DQS was installed and click Connect. You are now logged into the DQS client and will see the main screen.

DQS Client

On the left side is where you work with Knowledge Bases. The middle section is for creating projects to do data cleansing and data matching. The right side is for Administration tasks. These will each be discussed further in future posts, but you should now at least have DQS installed and ready to go.

Posted in DQS | Tagged: , , | Leave a Comment »

SQL Saturday #238 – Minneapolis, MN

Posted by jeffprom on September 19, 2013

SQL Saturday #238

I’m happy to announce that I was selected to speak at the SQL Saturday #238, Minnesota event! This year I thought I would try something different. I recently changed jobs and am working with Ross McNeely. Ross mentioned that he once presented on F# which got me thinking… I know a bit about using PHP with SQL Server. So that is my session this year: ‘When scripting worlds collide: PHP + SQL Server’. This should be a fun presentation. I’ve actually used PHP longer than I have SQL Server. Somewhere in the neighborhood of 12 years. It wasn’t until just a few years ago that PHP was able to connect with SQL Server, and it makes for a very powerful combination!

Abstract:
PHP is a popular, and free, scripting language. It can be used to create websites, or simply perform a variety of tasks when run from a command prompt. Combine PHP with SQL Server and you have a powerful way to create data driven websites. In this session learn how to install, configure, and use PHP with SQL Server. Learn the basics of connecting to databases, writing queries, and using data to create dynamic, data driven websites.

I look forward to seeing everyone at the event.
http://sqlsaturday.com/238/eventhome.aspx

Posted in Events | Leave a Comment »

Encrypting SQL Server Connections

Posted by jeffprom on September 5, 2013

If you use SQL Server in the cloud you may not always have a secure connection. Thankfully there is support in several areas within SQL Server to help secure connections. Let’s take a look at a couple.

SQL Server Management Studio
1. In SSMS, go to Object Explorer
2. Click the Connect drop-down, and select Database Engine
3. Enter the Server name and login credentials (don’t click Connect yet!)
4. Click on the ‘Options >>’ button
5. On the ‘Connection Properties’ tabe, check the box for ‘Encrypt Connection’

6. If you have an untrusted certificate on your server (see below), click on the ‘Additional Connection Parameters’ tab. Enter ‘TrustServerCertificate=True’.

7. Click the ‘Connect’ button.

PowerPivot
1. Open Excel
2. Click on the PowerPivot tab
3. Click on the Manage button
4. Select the drop down under ‘From Database’
5. Select ‘From SQL Server’
6. Enter the Server name, login credentials, and Database name
7. Click the ‘Advanced’ button

8. Scroll down to the bottom and set the following:
Trust Server Certificate = True (see certificates below)
Use Encryption for Data = True

9. Click Ok
10. Select the data sources (tables)

Certificates
Digital certificates can come from various sources. There are several ‘trusted’ 3rd party sources such as Thawte and VeriSign that are called Certificate Authorities (CA). To get a certificate from a trusted source you will most likely need to pay an annual fee. If you have a trusted certificate, you shouldn’t have to tell SQL Server that you want to trust the certificate.

On the other hand, if you don’t have a trusted certificate, you should already have a self signed certificate that Windows created.

To see a list of certificates installed on your computer, do the following:
1. click on Start, Run, MMC
2. File, Add/Remove Snap-in…
3. Certificates, Add
4. Select Computer Account, Next
5. Local Computer, Finish
6. Ok
7. Expand Certificates, Personal, and click on Certificates

This shows a list of certificates currently installed. From here you can import and export certificates by right clicking and going under the ‘All Tasks’ menu.

Securing connections in SQL Server is a great way to keep your data private over unsecure lines. SQL Server uses certificates to establish secure connections. Be sure to use these techniques to create secure connections from SSMS or PowerPivot when venturing out into the open cloud.

Posted in Security | Leave a Comment »

MN BI User Group – September 10th, 2013

Posted by jeffprom on August 21, 2013

MN BI User Group

On Tuesday, September 10th I will be speaking at the Minnesota BI User Group. The topic will be ‘Master Data Management and Microsoft Master Data Services.’ There will also be a 2nd presentation on ‘Recognizing The Parallel Data Warehouse (PDW) Workload’ presented by Jason Strate and Sanjay Kaul.

The event will be at the Microsoft office:
3601 76th Street West
Suite 600
Edina, MN 55435

For more info, visit the website at http://www.msbiusergroupmn.org

Register today!

Posted in Events, MDS | Leave a Comment »

SQL Saturday #175 Fargo – Recap

Posted by jeffprom on April 29, 2013

Last Saturday was the very first SQL Saturday event in Fargo, ND. It was a great success with about 125 attendees. Thanks to everyone who helped organize the event, the speakers, and to everyone who attended. Also a big thanks to everyone who attended my session. The board room style was a first for me but was kind of cool! I felt like an executive running a meeting. :)

This was also a SQL Saturday first because we had a special guest appearance by the man himself, Mr. Bill Gates!!

Bill Gates

Well in all honesty, he was actually in the same building to talk to the Microsoft employees. However, they were kind enough to let us hear him speak. How cool is that?!

The event was held at the Microsoft Executive Briefing Center which was extremely nice. The Microsoft employees onsite, such as Kelly Obach, who helped with the event were also super awesome.

Microsoft Executive Briefing Center Fargo, ND

All in all, a fantastic time. There was talk of having it again next year so hopefully I will be back again soon.

Posted in Events | Leave a Comment »

SQL Saturday #189 Costa Rica – Recap

Posted by jeffprom on April 20, 2013

Last Saturday was the SQL Saturday #189 Costa Rica event and what an awesome event! Thanks to everyone who helped make this such a great event; the organizers, the sponsors, the attendees and the speakers. Being well into April, it was quite a shock to come back to Minnesota and receive another 9 inches of snow. I should have stayed in Costa Rica a bit longer. :D

Friday night the group went out to a restaurant called Mirador Tiquicia in Escazu. It was a bit of a drive and up a very steep hill, but it was worth it! At the top it had an amazing view of the entire city of San Jose. Here is a glimpse of the view with my wife and I.

city lights of san jose

For dinner we had a delicious buffet of traditaional Costa Rican food. Then, traditional Costa Rican dancers performed in the middle of the restaurant. What a great night!

Costa Rican dancers

On Saturday, it was time for the big event. There were 320 registered, and 257 who attended. The conference took place at the Aurola Holiday Inn in San Jose. Here you can see Eduardo Castro making the welcome announcements. In this picture he’s introducing the speakers for the day. I’m on the bottom of this slide.

sql saturday costa rica welcome

My 2 sessions were in the afternoon so I mingled and attended the morning sessions. Then it was off to lunch which was on an upper floor of the hotel. Again, we were treated to a nice view of San Jose and a great lunch. Here are some pictures of San Jose from the dinning area.

San Jose

San Jose

After a short break, it was then time to get to work. Here is me getting ready for my first session.

Getting ready for my sessions

I first presented on Master Data Services, and then on Data Quality Services. Thank you to everyone who attended my sessions and for being such a great crowd. The questions were good and hopefully everyone left knowing a bit more about MDS and DQS.

presenting on master data services

I had such a great time and met so many fun people; Eduardo Castro, Kenneth Ureña (Thanks for the coffee it was awesome!), Joaquin Zuñiga, Kevin Boles, Luis Carlos Diaz, Alex Vargas, Jesus Gil, Russell Fustino, and many others! Latin America has a great SQL Server community. Thank you for letting me be a part of this event. Hopefully I’ll be able to attend again next year!

Posted in Events | Tagged: , | 1 Comment »