'I was trying to use OPENROWSET to query a remote database and I experienced the following error "Ad hoc access to OLE DB provider has been denied error. You must access this provider through a linked server". When I ran this same query using a sysadmin account it worked fine. Why doesn't this work for other logins?'...
'There has been a lot of buzz about containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source to Windows in March of 2016. Full disclosure, I am a co-founder at WinDocks.
In this article we’ll take a look at containers, the reasons for their growing popularity, and use by SQL Server DBAs and developers.'...
'In Part 1 of this series, How to FTP a Dynamically Named Flat File, I explained how to create an SSIS Package that output a dynamically named flat file and then sent the output file to a FTP server. In this part, I will explain how to create an ‘Integration Services Catalog’ to deploy the SSIS Project created in the first article. I will then show how to run the SSIS Project on a schedule as SQL Server Job.'...
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/125585/
'With the new SQL server, you can have the best of both worlds. In your data models, you can choose when to use traditional structures and when to introduce NoSQL concepts.
This article details the application of JSON data and the use new SQL 2016 constructs that enable an integration of JSON data to a relational schema. The idea behind this article to the list examples of JSON nested elements, sample data converted to JSON, and JSON relational data.'...
'Today I came across a weird error as my colleague was trying to open SSMS (SQL Server Management Studio), and it was throwing following error:
‘-120’ is not a valid value for property ‘Width’
… in the dialogue box shown below:
… a similar error can also occur for Height also (2nd image).'...
'How the queries passed from SSAS to the DB engine is very interesting. First, however, let's review the basics of DirectQuery mode in SQL Server Analysis Services (SSAS). All information is as of RC1 (release candidate 1) for SQL Server 2016.
Two Modes for SSAS Tabular Models
When developing an SSAS Tabular model, you can choose one of two options for handling the underlying data...'...
http://www.sqlchick.com/entries/2016/3/20/overview-ssas-tabular-directquery-mode-sql-server-2016
'Have you ever been in a similar situation where your PROD database size is in GBs or TBs, and for a code-release validation or some performance-fix you need to restore it on an Dev or Test server? You know that taking backup & restore will take lot of time, but you have no other option.
We also face this many a times as our PROD database size ranges from 500 GB to 1-2 TB, and we end up waiting 4-5 hrs in this activity.
There are even third party tools, but they also take good time and have their own pros & cons.
Now SQL Server brings a new feature with SQL Server 2016 SP1 & 2014 SP2, i.e. DBCC CLONEDATABASE, to create a new database clone of an existing database within seconds. The new cloned database created is ReadOnly, with no data, but with Statistics.'...
'I received a phone call from a customer dealing with a t-sql problem . I asked him to organise some access so I could troubleshoot the problem. When I logged on I wanted to confirm the level of permissions.
An easy way to check for your self is to use the fn_my_permissions function. The fn_my_permissions always returns permissions in the security context of the logged in caller.
There are multiple options , but some quick ones to give a list of server and database level permissions are listed below.'...
http://www.sqlserver-dba.com/2016/05/how-to-check-my-sql-permissions.html
'I recently needed to replicate all of the reports on one report server to a different server. I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file. Needless to say, that’s a slow and painful process. Since a new set of PowerShell command lets (Cmdets) were introduced for Reporting Service just a couple of weeks ago, I decided to give them a try and share the results.'...
'Today I want to talk a little bit about the difference between the Sql Server FileStream introduced in 2008, and the SQL Server FileTable introduced in SQL 2012. Both features share the common capability of integrating file data with a SQL Server database which has been enabled to use FileStreams. A file stream is a pointer to a byte array stored on a file system. SQL Server can be configured to store a FileStream in a native fashion, much like an operating system.'...
https://www.sswug.org/bentaylor/editorials/filestream-vs-filetable/