Press "Enter" to skip to content

Curated SQL Posts

Finding Uses of xp_cmdshell

Published 2024-08-02 by Kevin Feasel

Steve Jones goes looking:

I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.

That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.

Read on for Steve’s solution. There are also other places where you might find these calls: SSISCatalog if you use Integration Services, specific company metadata tables if you use those to build T-SQL statements, etc. But this does give you a good start.

Comments closed

Execution Plan Analysis in Postgres with StatsViaExplainAnalyze

Published 2024-08-02 by Kevin Feasel

Deepak Mahto shares a tool with us:

Tuning SQL is an integral part of most developer’s or DBA’s primary responsibilities. Understanding and dissecting problematic execution plans are critical steps in the overall tuning process.

One key ingredient of SQL performance tuning, along with the runtime plan, is object statistics. Looking only at the execution plan is often insufficient for making solid recommendations; we need a fair understanding of current object statistics.As the saying goes,

Behind every optimized SQL query lies a deep understanding of the underlying object stats.

Click through to see examples of the StatsViaExplainAnalyze tool and how you can get a copy of it from GitHub.

Comments closed

Power BI Report Server “Couldn’t Connect to the Analysis Services Server”

Published 2024-08-02 by Kevin Feasel

Deepthi Goguri grabs a patch:

When you check the Change log for Power BI Report Server, Version: 1.20.8910.25479 (build 15.0.1115.165), Released: May 28, 2024 release notes mentioned about the security change made during this update to add the environment variable and system variable on the Power BI Report Server machine.

Read on to learn more about this and what it takes to correct the issue.

Comments closed

Losing .NET Code with SSIS 2016 Package Deployment

Published 2024-08-02 by Kevin Feasel

Andy Leonard goes on a quest:

…when one of our earliest SSIS framework customers reached out to share – in the very nicest way possible – that Fail Application on Package Failure in our SSIS framework was “not working.” My response was, “Great Scott!”

The customer is running SQL Server 2016. That’s fine for EDNA’s SSIS frameworks because we have framework versions that work with several versions of SSIS and SQL Server from 2012 forward. I maintain a collection of virtual servers built using the tools available for each version, including the operating systems and versions of Visual Studio, SSIS, and SQL Server.

Read on for the context, the story, and what Andy was able to do about it.

Comments closed

Archiving Files with SSIS

Published 2024-08-02 by Kevin Feasel

Andy Brownsword shuffles things around:

Integration workflows will typically involve handling files. As part of that we’ll need to move them around, for example moving into an archive directory.

The File System Task component can be used for a variety of operations such as creating directories or copying files. Here we’re going to look at two specific operations to help shuffle files around: Move and Rename.

Read on to see how it works and how you can turn this into an archival process for files.

Comments closed

Chat with Azure OpenAI in Streamlit

Published 2024-08-01 by Kevin Feasel

I have a new video:

In this video, I show how we can integrate an Azure OpenAI GPT-4 model into our Streamlit dashboard. Along the way, I also show off how easy it is to create multiple pages and talk a bit about session state and secrets management as well.

The fun part about this is, there’s not even that much code involved. Streamlit handles most of the conversational aspects and you’re primarily responsible for saving history.

Comments closed

Building a Series of T-SQL Statements via Regular Expression

Published 2024-08-01 by Kevin Feasel

Andy Levy now has two problems:

Earlier this year, I embarked on a bit of a project to tidy up the indexes in a sizeable database. This database has over 900 tables, and there are quite a few indexes which I have long suspected don’t need to exist for a variety of reasons. Some indexes are redundant, others don’t get used, still others could be combined into a single index. By reducing the number of indexes, we can improve write performance in the database, and the size of the database itself. But we have to do it carefully.

Read on for an example of how you can use a regular expression to take text in one form (DatabaseName.SchemaName.IndexName, for example) and convert it into a T-SQL script.

Comments closed

Ad Hoc Data Exploration with Azure Data Explorer

Published 2024-08-01 by Kevin Feasel

Michal Bar introduces a new feature:

We are excited to introduce the new Data Exploration feature, designed to enhance your ability to delve deeper into the data presented on any Dashboard.

If the information you’re seeking isn’t readily available on the dashboard, this feature allows you to extend your exploration beyond the data displayed in the tiles, potentially uncovering new insights.

Directly from a dashboard, you can refine your exploration using a user-friendly, form-like interface. This intuitive and dynamic experience is tailored for insights explorers seeking insights based on high volumes of data in near real time.

Click through to see the new feature in action.

Comments closed

The Performance Impact of Local Variables

Published 2024-08-01 by Kevin Feasel

Jared Westover talks performance:

Often, developers use local variables when writing ad hoc queries or stored procedures for many reasons. You might hear “never repeat code” or “avoid using magic numbers.” While writing a lengthy stored procedure, I might include a few. However, did you know that local variables can hurt the performance of your queries? How can you keep local variables from negatively affecting performance? Keep reading to find those answers and more.

This is the kind of performance issue that you can easily forget about. Jared includes two methods for resolving the issue if you run into performance problems on a specific query or stored procedure.

Comments closed

Tracking Task History in Snowflake

Published 2024-08-01 by Kevin Feasel

Kevin Wilkie is interested in just one thing:

By the table function name, you’re probably wondering “Sherpa, why on Earth do I really need to look at query history based on the session? I have Query_History_By_User which gives me a broader look at my data. Who really cares?”

Great question, my friend! You’re right. This new Query_History_By_Session table function isn’t going to give you a lot of data that you can’t get easier – and more helpfully – with Query_History_By_User. Why did Snowflake provide me this “useless” function?

Read on for the value of this function and an example of querying task history.

Comments closed