On some servers when you try to load an Excel file using OpenRowset, you will get this delightful error:
Msg 15281, Level 16, State 1, Line 1
Continue reading OpenRowset Msg 15281, Level 16
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator
can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
In many reports, you need to have a column that shows the balance and it needs to show the running total for all of the data that shows up.
Continue reading SSRS: Create a running total using RunningValue
Occasionally after building a new server and installing SQL Server you try to login to it and you receive this error:
Continue reading Login timeout expired (Access Denied)…One Solution.
A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
When a SSRS subscription fails to run successfully, it will automatically retry 3 times with 15 minutes between runs. This is a good default, but sometimes you want to change those settings. Below I will show you how.
Continue reading Modify SSRS Subscription Retries
Here is problem you may encounter using a Linked Server connecting to an Oracle Database. I was trying to create a table in SQL Server with data from an Oracle database using a Linked Server and I received this error: Unable to enlist in the transaction Msg 7391, Level 16, State 2
Continue reading Unable to enlist in the transaction using a Linked Server. Msg 7391, Level 16, State 2
I was trying to truncate a table on a remote server using a Linked Server and received the following error:
Msg 4701, Level 16, State 1, Line 6
Cannot find the object "bill" because it does not exist or you do not have permissions.
Read on to see how I solved this issue.
Continue reading Msg 4701, Level 16 trying to truncate table through Linked Server
I setup a linked server on a new database server and was trying to run a query that runs against an Oracle database and I got the following error:
Msg 7399, Level 16, State 1, Line 1
Continue reading Msg 7399, Level 16, State 1 Linked Server. Access Denied
The OLE DB provider “OraOLEDB.Oracle” for linked server “ccbtst” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “OraOLEDB.Oracle” for linked server “ccbtst”.
I have been using Oracle databases since version 7.2 back in 1995, but starting in Oracle 10G released in 2003, they added a feature called the recycle bin. This feature does exactly what you think it would do. If you drop a table by accident you can recover the table and all of the data just like the Windows recycle bin is used to recover files.
Continue reading Another feature I wish SQL Server had.
In Sql Server, if you try to count more than 2.1 billion rows or sum up values more than that, you will get this message: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.
Today I will show you the solution to this problem.
Continue reading Msg 8115, Level 16 Arithmetic overflow
If you have a data warehouse and you frequently run queries using aggregate functions like SUM, AVG, COUNT, etc, you need to investigate columnstore indexes. I am going to show you some performance benefits that come from using this new (8 years old now) index type.
Continue reading SQL Server Columnstore Index Performance Benefits