Jeff Prom's SQL Server Blog

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

SQL Saturday # 802 – San Diego 2018

Posted by jeffprom on September 12, 2018

SQL Saturday San Diego is just over a week away! It will be held September 22nd, 2018 at UCSD Extension – University City Center, 6256 Greenwich Dr., San Diego, California, 92122.

I will be presenting a session on performance tuning jobs. Learn performance tuning strategies on how to cut hours (not minutes) from your jobs.

I hope to see you there!

http://www.sqlsaturday.com/802/EventHome.aspx

Advertisements

Posted in Events | Leave a Comment »

SQL Saturday # 773 – Los Angeles 2018

Posted by jeffprom on May 14, 2018

SQL Saturday L.A. is just around the corner! It will be held on June 9th, 2018 at Loyola Marymount University (“LMU”), 1 LMU Drive, Los Angeles, California, Los Angeles, California, 90045.

I will be presenting a session on performance tuning jobs. Learn performance tuning strategies on how to cut hours (not minutes) from your jobs.

There is still time to register so be sure to signup today!

http://www.sqlsaturday.com/773/EventHome.aspx

 

Posted in Events | Leave a Comment »

SQL Saturday # 740 – Orange County 2018

Posted by jeffprom on March 14, 2018

SQL Saturday Orange County, 2018 is quickly approaching! The event will be held on Saturday, April 14th at Golden West College, 15744 Goldenwest St, Orange County, California, United States, 92647. There is still time to register.

Also, I will be presenting a new session on performance tuning jobs and strategies. Hope to see you there!

http://www.sqlsaturday.com/740/eventhome.aspx

 

Posted in Events | Leave a Comment »

Can’t Start SQL Server Due to Insufficient Memory

Posted by jeffprom on December 14, 2017

SQL Server loves memory and will take as much as you give it. Sometimes you may want to limit the amount of memory it can have. To do this, go into management studio, right click on the server, select properties, and select the memory tab. To limit the amount of memory it can have, set a value for Maximum Server Memory (in MB).

If you aren’t careful, you can set a value that is too low and SQL Server won’t be able to run. I recently did this on my local copy and had a hard time changing the setting again to a higher value as everything expects that SQL Server is running, and that you can simply change the configuration.

In my case, I had set the value to 128 MB and SQL Server kept showing an “insufficient memory” error in the windows event log and wouldn’t start.

If you find yourself in a similar predicament, here is how to get it working again.

Open a command prompt as an administrator. Find the install path of your version of the SQL Server engine. In my case it was “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

Change to the directory:

Cd “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”

Next, we want to start SQL Server with minimal configuration. This will start SQL Server in a minimal state and allow you to connect in management studio. To do this use the following:

Sqlservr.exe -f -s <instancename>

Open the server properties window and bump up the max memory limit.

Close the command prompt window to shut down SQL Server. Finally, go to SQL Server Configuration Manager and start the service like normal.

You should now be able to run the service as usual and connect.

Posted in Administration, Troubleshooting | Leave a Comment »

SQL Saturday # 661 – San Diego 2017

Posted by jeffprom on September 21, 2017

While only a few days away, there is still time to register and attend the SQL Saturday San Diego event! I will be presenting on SQL Injections and how to stop them. Come spend the day learning about SQL Server and listening to a great lineup of speakers.

The event will be held at UCSD Extension – University City Center, 6256 Greenwich Dr., San Diego, California, 92122 on Saturday, September 23. See you there!

Posted in Events, Security | Leave a Comment »

SQL Saturday # 640 – Los Angeles 2017

Posted by jeffprom on May 5, 2017

I’m excited to announce that I’ll be presenting at the very first SQL Saturday event in Los Angeles!

The event is coming up fast but there is still time to register. It will be on June 10th, 2017 at the Microsoft Technology Center aka MTC, 13031 W. Jefferson Blvd Suite 200, Los Angeles, California, 90094.

Reserve your spot today! http://www.sqlsaturday.com/640/EventHome.aspx

@SQLSatLA

        

Posted in Events, MDS | Leave a Comment »

Workaround for Self-Joining Table Limitations on Indexed Views

Posted by jeffprom on March 27, 2017

Indexed views are special views that can offer huge performance improvements over regular views. They perform faster because they are stored in the database the same way a table is stored. They also have a unique clustered index which greatly improves performance. However, these benefits come at a price. Indexed views have a lot of limitations to consider before implementation.

To see the full list of limitations and to learn more about indexed views, click here:
https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

Recently, I was creating several indexed views and came across a limitation of joining to the same dimension table multiple times (role-playing dimension). I received the following error message: Cannot create index on view “x_DW.dbo.vw_SchemaBoundView_Test”. The view contains a self join on “x_DW.dbo.DimUser”.

Google searching resulted in several not-so-great suggestions to overcome this limitation. I believe the best solution was to create a new table and essentially writing duplicate data values to this new table. For example; create a dbo.DimUser2 table. I really do not like this approach because I would have to create another table for one single purpose and maintain the ETL and data for this one purpose.

After some thinking, I came up with another solution which requires less maintenance and seems to have good performance with less overhead. I broke my views up into two views: ViewName and ViewName_Base. The Base view has essentially everything minus the second join to the same table. I identified which of the two self-joining tables had the greatest amount of columns or caused the biggest performance hit. This was the join I included in my Base view because it will get stored like a table and indexed. I created the base view with schemabinding and created the unique clustered index.

Next, I created the other, non-base, view. This was nearly identical to the Base view. However, it’s primary source is the Base view. I then joined the Base view to the other self-joining table reference to get the final desired columns which I needed in my select statement.

By splitting the original view into two views, I was able to work around the self-joining table limitation in indexed views but was still able to have a really nice performance improvement. I did not need to create additional ETL or need to create another table with duplicate data that I would need to maintain.

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

Jet Driver Error When Importing Data from Excel Files

Posted by jeffprom on March 24, 2017

Importing data from files is common. Csv file formats are also common but can cause issues with characters such as commas. Excel files offer a nice solution as they are usually formatted properly and do not have issues with characters. However, there can also be a common problem scheduling a job to load the data due to a jet driver issue.

ImportData:Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.

Here is how to overcome this error. First, install the ‘Microsoft Access Database Engine 2010 Redistributable‘ file which will provide the required jet driver.

Next, open the SSIS package/solution in visual studio and click on Project, <project name> Properties. On the Configuration Properties, Debugging tab, Set Run64BitRuntime = False. The jet driver is only 32 bit.

There is one final step and that is a configuration setting on the SQL Agent job itself. Without setting this, it will fail. Edit the job, click on Steps and edit the step. On the General tab, click on Configuration, Advanced and check the box that says 32-bit runtime.

That’s it! Execute the job and it should now finish successfully.

Posted in Excel, SSIS, Uncategorized | Tagged: , | Leave a Comment »

SQL Saturday #611 – Orange County 2017

Posted by jeffprom on February 22, 2017

sqlsat611_web

SQL Saturday Orange County is coming up fast! The event will be on April 1st, 2017 at Golden West College, 15744 Goldenwest St, Orange County, California, 92647.

Reserve your spot today by going here: http://www.sqlsaturday.com/611/EventHome.aspx

I’ll be presenting two sessions:

  • SQL Injections and How To Stop Them
  • Mastering Master Data Services (Lots of changes in SQL Server 2016!)

Posted in MDS, Security | Leave a Comment »

SQL Saturday #497 – Huntington Beach 2016

Posted by jeffprom on March 14, 2016

sqlsat497_web

April is shaping up to be a fun, yet busy month. I have also been selected to present two topics at SQL Saturday #497 in Huntington Beach on April 2nd! I will be presenting on SQL Injections and Master Data Services. Here are descriptions of my presentations.

SQL Injections and How to Stop Them

Right now, there are hackers all around the world trying to get into your web applications. How safe are you? By using a technique called SQL injections, hackers can wreak havoc with web applications by compromising security, manipulating data, hoarding system resources, retrieving sensitive information, and manipulate data database objects such as dropping databases!

During the demo, we will take on a couple of different roles. As a hacker we will walk through steps a hacker might take to compromise a web application in order to retrieve sensitive data such as credit card information, usernames, passwords, and social security numbers. Assuming the role of a developer, we will then show various prevention techniques and their effectiveness in preventing SQL injections.

Attend this session to learn how SQL injections work, identify if you are being attacked, and how to stop them.

Update: Thank you to everyone who attended my session. We had a full room! 🙂

As requested, you can download the presentation and scripts here.

jeffprom-presenting

Mastering Master Data Services

As your organization grows, one challenge will be the management of data between systems and organizational units. With MDS, Microsoft provides the ability to have one accurate source of non-transactional data. This data can then be used within applications, other databases, and ETL processes. By leveraging data stewards to help manage the data repository, you can ensure that your MDS data is always up-to-date and accurate.

In this presentation 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 presentation, you will have a good understanding of what master data management is, what MDS does, how to use it, and how to get started with your own MDS project.

To register for this event and to find additional information, you can visit the SQL Saturday Huntington Beach page here:
http://www.sqlsaturday.com/497/EventHome.aspx

Posted in Events, MDS, Security | 1 Comment »