A Fine Slice Of SQL Server
The Attunity connector for Oracle used inside of the SSIS data flow looks a little different than the typical OLE DB connector we most commonly use. Specifically, the Attunity source has two options: “Table Name” and “SQL command.” What the Attunity Oracle Source doesn’t have in this dialog box is “SQL command from variable” (like we see for an OLE DB source).
Expressions are your friend.
Comments closedJens Vestergaard shows us how to filter tables in Management Studio:
As a quick tip, this on is one of these tool tips, that just makes your everyday much easier. Sometimes, more than often, you run into databases that contains a huge number of tables – all listed alphabetically. This can, at times, be cumbersome and annoying to browse. SQL Server Management Studio (SSMS) actually has a feature that will assist you in getting your job done, quicker; It’s called Filtering.
My most common filter criterion is by schema, but there are several filtering options available.
Comments closedGinger Grant discusses creating models in Power BI:
When comparing the features of the Online Client with the Desktop version of Power BI, there is one very obvious difference, there is no way to create a data model in Power BI online. It is not possible to create a data model using the online client. The online client is designed to connect to an existing online source such as Sales Force or Azure DB. If you are using an existing model, there is no need to create one. When using the enterprise gateway, which uses an on-premises database such as a SQL Server, SSAS or Hana, the data model is contained within the database exposed via the enterprise gateway, so again no reason exists to create a data model. Report creation can occur either using the online client or desktop as there is compelling technical reason that I am aware of which would determine where the report is created.
Cf. an earlier post on Power Pivot and Power BI modeling.
Comments closedAnup Warrier had an issue recently with sys.messages queries generating an error:
What you normally expect when you run SELECT * FROM sys.messages? The query will return a row for eachmessage_id or language_id of the error messages in the system, for both system-defined and user-defined messages.
Rather than returning the rows, system generated an error for me:
Msg 18058, Level 17, State 0, Line 1
Failed to load format string for error 362, language id 1033. Operating system error: 317(The system cannot find message text for message number 0x%1 in the message file for %2.). Check that the resource file matches SQL Server executable, and resource file in localized directory matches the file under English directory. Also check memory usage.
Anup follows up with the answer, so if you’re running into this issue, check out his post.
Comments closedPhil Factor has a query he shares to discern table smells in SQL Server:
Table smells in SQL Server are just like code smells. They are just an indication that something may need to be checked or investigated. They are easy to check as well, because you can generally ferret through the object catalog metadata views to flush out the aspects that just don’t smell right. In this blog, I show the sort of query I’ll use. Actually, I generally use rather more strict criteria because I’d be concerned about tables that don’t seem to be making full use of constraints, and tables that don’t have ‘soft’ dependencies (aren’t referenced in code within the metadata, from views, procedures or functions). I like to have a result that displays the names of the tables that look suspect, along with the list of the ‘smells’.
This is a great start. As Phil notes in the comments, it’s not necessarily that these are wrong so much as that if you see them, there ought to be a specific reason for it to be this way.
Comments closedReza Rad shows us how to interrogate Power BI:
When you published your Power BI file into the Power BI desktop, usually you create a dashboard for it. For Power Q&A to work (the version of Power Q&A at the time of writing this post) you should create a dashboard for your report. After creating the dashboard you will see the question bar of Q&A on the top of your dashboard.
My preferred technique is good developer, bad developer.
Comments closedJason Strate shows us that you can use a UNION (or UNION ALL) to insert into a temp table:
What makes this interesting is when I am using UNION to join the results. How do you place a final resultset from a UNION, EXCEPT, or INTERSECT into a temporary table using SELECT INTO? Where does the INTO portion of the query go?
This is actually a pretty simple thing to do. The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.
No subqueries are necessary here.
Comments closedSteve Jones shows us how to use CHARINDEX to find a string:
A quick one today, just looking for strings. I wrote an article on this, so there’s more detail there, but here’s a bit of code you can look through and see what it does.
He didn’t tag his post T-SQL Tuesday, but it certainly is apropos.
Comments closedAstronomers wanted a tool that would be able to quickly answer questions like: “find asteroid candidates” or “find other objects like this one”, which originally gave the motive to build the SQL-based backend. Indeed, right from the beginning Jim Gray asked Alex Szalay to define 20 typical queries astronomers might want to ask and then together they designed the SkyServer database to answer those queries. The anecdote is that the conversation went as follows:
Jim: What are the 20 questions you want to ask?
Alex: Astronomers want to ask anything! Not just 20 queries.
Jim: Ok, start with 5 queries.
[it took Alex 30 minutes to write them all down]
Jim: Ok, add another 5 queries.
[it took Alex 1 hour to write them all down]
Jim: Ok, now add another 5 queries.
[Alex gave up and went home to think about them]
…
Alex (said later): In 1.5 hours, Jim taught me a lot of humility!
…
Alex (said later): It also taught us the importance of long-tail distribution and how to prioritize.
This is my favorite part of the article.
Comments closedKenneth Fisher explains his blog header, which shows different ways to manipulate text in SQL Server:
When I started this blog a friend of mine suggested I write a really complicated query as a header. Now I’m not sure how complicated it really is, but I find it fairly amusing, and the whole point of it is to manipulate some text to generate a different set of text. So this seems like a good time to go through it and explain what’s going on.
There’s a bit to unpack, but it’s a fun experiment.
Comments closed