Celtic Coding Solutions Blog

Celtic Coding Solutions Blog - for everything computer related

Error when importing Excel 2007 spreadsheet into SQL Server 2012

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 abortion hotline installed?

Fix: you need to install generic abortion pill 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

SQL Server shrink log or data files and how to check what is happening

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 abortion pill dallas but it mainly just tells you cost for abortion the process is running.  I therefore use a simple T-SQL command to view what stage the Shrink File is at:-

SELECT

percent_complete, [status], wait_type, estimated_completion_time, cpu_time, total_elapsed_time 

FROM 

sys.dm_exec_requests

WHERE 

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.

SQL Server - Unable to create database - Exclusive Lock on Database 'model'

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'. for abortion facts Retry the operation later.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

 

Fix:- 

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 abortion pill reviews there maybe a geniue reason for the usage) and re-run your create database command.

SQL70001 error on a Visual Studio Database Project

I had a Visual Studio 2012 Database Project along with my Visual Studio Asp.Net Project abortion trimester 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. on line abortion pill  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:-

Unable to open Database Project in Visual Studio 2012

Installed SQL Server 2012 Management Studio on my local PC and found out in Visual Studio 2012 medical abortion pill 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 risk of abortion pill up and solutions (infact they just 404'd).

 

The fix was to install the SQL Server Data Tools - quite simple really?

http://msdn.microsoft.com/en-us/jj650015

SQL Server Hard Drive Performance with SQLIOSIM.exe

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 abortion centers 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:-

Click OK

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 abortion facts codeplex) as this converts the results file into a simple to read csv file (just open it in Excel).

SQL Server Reporting Services Subscription Error

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 why abortion 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 abortion options most likely due to the report taking too long to be created.  Therefore check your timeout seconds in the Report Manager:-
Http://YourReportServer/Reports
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 Performance Dashboard for SQL Server 2008 - Issue 3

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.  unwanted pregnancy Either amend the following code in setup.sql or look for free abortions 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,

to

dateadd(ms, -1 * (@ts_now - [timestamp])/1000, GetDate()) as EventTime,

Microsoft Performance Dashboard for SQL Server 2008 - Issue 2

Microsoft's SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of medication abortion 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 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 anti abortion facts 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,

to

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,