Fetch Web Data in Power BI (2020-03-17)

Click here to subscribe to Sqlservercentral.com

4 years ago

Html
Text

Problems displaying this newsletter? View online.

Featured Contents

sayand from SQLServerCentral

Introduction Reporting on web data has been a fascinating part of developing any dashboard or reports in Power BI. Often this data is available either free that can be consumed anonymously or by using some kind of authentication mechanism. In this article, I'm going to demonstrate how to connect to a web application from Power […]

Translating a SQL Server Schema into a Cassandra Table: Part II Integrity Constraints

Additional Articles from SimpleTalk

Complex integrity constraints are more challenging to enforce in Cassandra than they are in a relational database. In this article, Shel Burkow walks through four modelling examples in Cassandra involving constraints.

What’s in that database? Getting information about routines

Phil Factor from SimpleTalk

There is a lot of information held in SQL Server metadata about your SQL Server procedures, triggers, views and functions. Some of it is valuable, other nuggets are useful and a few are rather dull but worthy. It really all needs to be tied together to tell the full story, especially if you are not able to, or do not want to, query the metadata directly.

From the SQL Server Central Blogs - T-SQL Tuesday #124 Query Store and Spin Locks #tsql2sday

david.fowler 42596 from SQL Undercover

This month’s T-SQL Tuesday invitation comes from Tracy Boggiano and she’s asking us to write about our experiences with Query Store. I thought about what I should write, I’m...

From the SQL Server Central Blogs - "What did it take to become a MVP?"

Will Assaf from SQL Tact

@william_a_dba what did it take to become a MVP?— kennyb7322 (@kennyb7322) March 3, 2020

This tweet made me pause. I'll explain.Backstory (skip if you're not interested):On Sunday March 1,...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Get a couple fruits
I have this code: DECLARE @s VARCHAR(500) = 'apple,pear,banana,peach,orange,strawberry,blueberry,lime' SELECT TOP 2 * FROM STRING_SPLIT(@s, ',') AS ss What is returned from this?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Backing Up Some Log

I have this code:

DECLARE @n NVARCHAR(MAX) = 'Northwind_Prod' SELECT @n = @n + '_' + CAST(CAST(SYSDATETIME() AS DATE) AS nvarchar(20)) + '.trn' BACKUP LOG @n TO DISK = 'e:\northwindprod.trn'

What happens when I run this code?

Answer: The log is backed up to a file with the current date in the filename

Explanation: This works fine. The BACKUP LOG command can use a variable. The casting is correct. Ref: BACKUP LOG - https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

Discuss this question and answer on the forums

Featured Script

Graph multiple databases growth using SSMS only

Pablo Echeverria from SQLServerCentral

Easily graph your storage needs based on msdb backup data recorded automatically, no matter what your backup solution is.

USE [msdb]
;WITH [BackupSize] AS (
SELECT [bs].[database_name] [Name], DATEDIFF(DD, DATEADD(DD, -45, GETDATE()), [bs].[backup_start_date]) [Date], SUM([bs].[backup_size])/1024/1024/1024 [Size]
FROM [backupmediafamily] [bmf]
INNER JOIN [backupset] [bs] ON [bmf].[media_set_id] = [bs].[media_set_id]
WHERE [bs].[backup_start_date] >= DATEADD(DD, -45, GETDATE())
AND [bs].[type] = 'D'
GROUP BY [bs].[database_name], DATEDIFF(DD, DATEADD(DD, -45, GETDATE()), [bs].[backup_start_date]))
SELECT [Name], [Date], [Size],
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q1],
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q2],
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q3]
INTO #Info FROM [BackupSize]
UPDATE #Info SET [Size] = [Q2] WHERE [Size] < 2*[Q1]-[Q3] OR [Size] > 2*[Q3]-[Q1]
;WITH [slope] AS (
SELECT [Name], MAX([DateAvg]) [DateAvg], MAX([SizeAvg]) [SizeAvg],
CASE WHEN SUM(([Date]-[DateAvg])*([Date]-[DateAvg]))=0 THEN 0 ELSE SUM(([Date]-[DateAvg])*([Size]-[SizeAvg]))/SUM(([Date]-[DateAvg])*([Date]-[DateAvg])) END [m]
FROM (
SELECT [Name], [Date], AVG([Date]) OVER(PARTITION BY [Name]) [DateAvg], [Size], AVG([Size]) OVER(PARTITION BY [Name]) [SizeAvg]
FROM #Info) x
GROUP BY [Name]),
[lr] AS (SELECT [Name], [m], [SizeAvg]-[DateAvg]*[m] [b] FROM [slope])
SELECT [Name], GEOMETRY::STGeomFromText('LINESTRING(0 '+CAST([b] AS VARCHAR(53))+',90 '+CAST([m]*90+[b] AS VARCHAR(53))+')',0) [Geom]
FROM [lr] GROUP BY [Name], [m], [b]
UNION ALL
SELECT [Name], GEOMETRY::STGeomFromText('LINESTRING('+STRING_AGG(CONCAT([Date],' ',[Size]),',') WITHIN GROUP(ORDER BY [Date])+')',0)
FROM #Info GROUP BY [Name]
DROP TABLE #Info

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

Missing dumps during unexpected server reboot - One of my critical database server had an unexpected reboot followed by server hung issue but sadly we are unable to find any related errors in the event logs or SQL logs . Also there was no dumps generated . I recollect there is a way that the dumps can be generated later as well […]

SQL patching solution and automation - Hi All, Would like to get some suggestion of SQL patching solution and automation for multiple servers. I have 150+ SQL with each version 2012,2014,2016 & 2017. We do not have cost to buy patching tool, is there any easy and good solution to automate this like WSUS, PS etc.

SQL Server 2016 - Administration

Finding databases with no dependencies - Hello experts, This is more of a conceptual question - I'm pretty sure I can do all of the legwork once I get pointed in the right direction. How does one go about finding which databases on a server have no dependencies? I realize this may not be totally possible because some developer might have […]

Weird issue with data transfer to Oracle - Hello all... had an odd issue crop up and was wondering if anyone had ideas on what might have caused it... We have a nightly job that copies data from one of our local databases (SQL Server 2016) to an Oracle database at our corporate headquarters. The setup is pretty basic - we have a […]

SQL Server 2016 - Development and T-SQL

count(*) locking a table? - Hello, Why would select count(*) from a table encounter a lock? The table in question had initial bulk insert (append records) and then update happens on those new records. In the meantime I queried the total rows - count(*) and it locked the whole thing. Eventually I had to kill the query with count(*). Did […]

Administration - SQL Server 2014

SQL Server CPU utilization - Hi there, i am using this script to get the cpu utilization. Unfortanetly I get wrong results. The NonSQLCPUUtilization is always negative. It's a SQL Server 2014 with CU4. We have an other SQL Server 2014 with CU4 where it works correctly. Any hints are appreciated. Regards select top 10 id, SQLServerCPUUtilization, 100 - SystemIdle […]

Configuring SQL Replication using Remote Distributor: Create Publication Error - Dear Team, I am currently on SQL server 2014 R2 and I have been trying to replicate my LIVE database to a report server using a remote Distributor. Currently, i am getting the error as shown below: TITLE: New Publication Wizard ------------------------------ SQL Server could not create publication 'XXDB_PUB'. ------------------------------ ADDITIONAL INFORMATION: An exception occurred […]

Encrypted Log Shipping - I've found a number of articles on encrypting backups and transaction log backups, but all these are based on manually generated code to include the encryption details in the backup command. Is there anything out there covering how Log Shipping can be enabled to include encryption, and if so what version of SQL has this […]

Setting up replication - Hello, I'm am an experienced DBA and developer but am new to replication. I have to set up replication on a ~100 GB database and have some questions. I am replicating from our production database to a reporting database on a different server. Any estimates on how long it will take to do the initial […]

SQL 2012 - General

String extraction - SQL 2012 - Hi, I need to make changes to the below to only extract '2002' as the result. select REPLACE(REPLACE(SUBSTRING('CKnovelty2002_pouch-5AR-OneSize',1,CHARINDEX('-','CKnovelty2002_pouch-5AR-OneSize',1) - 1),'CKnovelty',''),'CKflower','') At the moment, I am getting '2002_pouch' as the result which is incorrect. The expected output is '2002'. Can somebody please help in this regard ? Many thanks.

SQL Server 2019 - Administration

not in vs in - I was looking at a T-SQL report query and saw that not in was being used.. I know its better to use IN than NOT IN.. Any inputs?

SQL Server 2008 Performance Tuning

Optimise/ speedup query - Below query is used for inserting and updating the tables in the SQL Server database. The XQuery is slow while executing in SSMS for first time.I am using SQL Server 2008 R2. The total time taken for 500 queries is 20 to 40 seconds. How can I optimise this query to speed up the execution? […]

SSRS 2016

PowerBI Paginated Reports vs SSRS reports - I have a reportserver where I am currently deploying both PowerBI desktop reports, and SSRS rdl reports. We're setting up standards for the team for our PowerBI reporting to match up to our SSRS standards (logos/header/footer elements/color schemes/etc.). Searching online has led us to the conclusion that adding header/footer is best served by using PowerBI […]

Integration Services

extract all data with week - Good morning all I have a table that contains the history of a table over 1 year old he asked me to extract one file per week so we total I must have 54 files Who can help me with this need please

Importing files from azure blob source into SQL Tables - by date range - I'm trying to pass a date to an azure blob storage source within a ForEach Loop and it doesn't look like it takes a dynamic filename. The ForEach Loop needs to find files after a date specified by a variable and then import it into a SQL Table. For the life of me I can't […]

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Share on

Other newsletters from Sqlservercentral.com

Improving Performance for Some LIKE “%stri ng%” Searches (SQL Spackle) (2024-11-29) Sqlservercentral.com
5 hours ago
Simplify Data Loading with Fivetran (HVR): Data Engineering with Fabric (2024-11-27) Sqlservercentral.com
Last Wednesday at 08:03
A First Look at SSMS 21 (2024-11-25) Sqlservercentral.com
Last Monday at 08:05

More newsletters from Sqlservercentral.com

Fetch Web Data in Power BI (2020-03-17)

Share on

Other newsletters from Sqlservercentral.com

Related newsletters