Tried to import an Excel 2010 spreadsheet (file extension xlsx) into SQL Server 2012 using the import wizard and selecting Microsoft Excel 2007:-
Then I had the following error on my Windows 7 x64 PC:-
SQL Server Import and Export Wizard Error
Additional Information: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
This is strange as I have Office 2010 x64
Fix: you need to install
the x86 (32bit) Office drivers. This is a simple download from Microsoft called "2007 Office System Driver: Data Connectivity Components".
Url - http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
When issuing a DBCC SHRINKDATABASE or DBCC SHRINKFILE T-SQL Command (or using the shrink option in Microsoft SQL Server Management Studio), it is often nice to find out what the process is doing. You can look at sp_who2
but it mainly just tells you the process is running. I therefore use a simple T-SQL command to view what stage the Shrink File is at:-
percent_complete, [status], wait_type, estimated_completion_time, cpu_time, total_elapsed_time
command in ( 'DbccFilesCompact', 'DbccSpaceReclaim', 'DbccLOBCompact')
The first field Percent_Complete is the most useful, but also check field [Status] is not suspended, if so check field wait_type and see why it is not running.
Whilst trying to create a new SQL Server Database I had the following error:-
TITLE: Microsoft SQL Server Management Studio
Create failed for Database 'Test'. (Microsoft.SqlServer.Smo)
Could not obtain exclusive lock on database 'model'.
Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
Some people suggest disconnecting and reconnecting your SQL Server Management Studio Session, but this did not work for me.
More than likely someone or something is accessing the model database (which might have been yourself hence the suggestion of closing SSMS). So this is the first thing to check, either open Activity Monitor and filter by Database model or just use the following command (in a new query window):-
SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'
Check that nothing is using the database, in my case it was because the Standard Full Model Database backups where running and had become suspended on the model database. Shutting this process down or killing the processes (be careful here as
there maybe a geniue reason for the usage) and re-run your create database command.
I had a Visual Studio 2012 Database Project along with my Visual Studio Asp.Net Project
for storing my Stored Procedures in TFS. However whenever I tried to Build my Project I kept getting Error 2 SQL70001: This statement is not recognized in this context. I had made sure that my Database Project wasn't included in the build process so why was I getting these errors. I then took a look at the properties of the Stored Procedures and noticed the following:-
The Build Action was set to Build!
So the fix was fairly easy, just change to None:-
Installed SQL Server 2012 Management Studio on my local PC and found out in Visual Studio 2012
I could no longer open a SQL Server Data Project. I kept getting the following error "Unable to open Database Project" from SQL Server Data Tools and both web links failed to bring up and solutions (infact they just 404'd).
The fix was to install the SQL Server Data Tools - quite simple really?
If you want only the
Date part of the DateTime field there are many ways of doing this however the best method I can find is the following:-
SELECT DATEADD(dd, 0, DATEDIFF(dd,0, GETDATE())) as DateOnly
If you are having some strange performance issues on SQL Server, I usually like to see how the Hard Drive is performing. Microsoft released a great tool called SQLIOStress.exe. However this has now been superseeded with SQLIOSim.exe. You can download either the x86 or x64bit version from Microsoft.
Bascially all it does is attack the Hard Drive in similar scenarios as SQL Server would. So to run:-
Load SQLIOSim.exe and remove all drives apart from
the ones where you will have the SQL Database files save e.g. this is the default when I run on my server:-
So just select the files and click remove to get something like:-
Then click the top right hand icon to start the test:-
There are plenty of documents on the internet to interpret the result and what they mean. But I would also recommend downloading the SQLIOSimParser (just search in
codeplex) as this converts the results file into a simple to read csv file (just open it in Excel).
After creating a Subscription for a report I noticed it was not actually sending the email error reported was:-
Failure sending mail: An error has occurred during report processing.
To Find out what is the actual error take a look at
the SSRS Logs (usually located at):-
C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles
The Error we are looking for is something along the lines of:-
Error will appear in the latest log as:-
ReportingServicesService!processing!e!DD/MM/YYYY-HH:MM:DD:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSetName'. ---> System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.
The cause is
most likely due to the report taking too long to be created. Therefore check your timeout seconds in the Report Manager:-
Click Site Settings (Top Left Corner)
Under "Report Execution Timeout" change to the following:-
"Limit report execution to the following number of seconds: 3600" e.g. 1hr
Microsoft's SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of kit for checking the status of your SQL Server (Microsoft Download page).
However after finding a slight coding issue when trying to install setup.sql (see Previous Blog), and then another issue when running the code (see Previous Blog). Well another issue occured - yippy:-
An Error has occured during report processing (rsProcessingAborted). Cannot read the next data row for the dataset CPU_UTILIZATION_HISTORY. (rsErrorReadingNextDataRow).
Arithmetic overflow error converting expression to data type int.
Again this is another simple fix.
Either amend the following code in setup.sql or look for the SP [MS_PerfDashboard].[usp_Main_GetCPUHistory] in the msdb database.
The line to change is:-
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
dateadd(ms, -1 * (@ts_now - [timestamp])/1000, GetDate()) as EventTime,
Microsoft's SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of Microsoft Download page).
kit for checking the status of your SQL Server (
However after finding a slight coding issue when trying to install setup.sql see Previous Blog I have now found another issue when running the code:-
An Error has occured during report processing. (rsProcessingAborted). Cannot read the next data row for the dataset SESSION_CPU_WAIT_INFO (rsErrorReadingNextDataRow)
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Again this is a simple fix. Either amend the following code in setup.sql or look for the SP [MS_PerfDashboard].[usp_Main_GetSessionInfo] in the msdb database.
The line to change is:-
sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
sum(convert(bigint, CAST( DATEDIFF(minute, login_time, getdate()) as bigint)*60000
+DATEDIFF(millisecond, DATEADD(minute, DATEDIFF(minute, login_time, getdate()), login_time), getdate() )))
-SUM(CONVERT(bigint, s.total_elapsed_time)) as idle_connection_time,