What is needed for users to use this solution Basic to Intermediate skills in manipulating T-SQL to create Stored Procedures.Users will not need SSMS to use these reports. SQL Server Management Studio (SSMS) to create the Stored Procedure that serves as the report's data source.The ability to create a SQL Server ODBC connection, which comes standard in Microsoft Office.This solution has been tested in Excel 20. This solution uses SQL Server 2012 and the AdventureWorksDW2012 database, so if you don't currently have it then click here to download and install.Data in SQL Server, although this solution can work with any database with a saved Stored Procedure.What is needed for you to create this solution The link to AdventureWorksDW2012 database is here, and the completed Excel doc used in this article is here. This is also a walk-through of Excel named ranges, data validation, data connections, Excel VBA, and SQL Server Stored Procedures. The finished product, which also is an Excel download at the end of this article, is here: Hey Bob, can I get a report that shows the number of aardvarks in every zoo? Hey Bob, can I get a report that shows the number of flights out of St. Hey Bob, you know that thing you ran for me last week? Run it again for last month. Hey Bob, run me my favorite report for July. Hey Bob, run me my favorite report for June. Hey Bob, run me my favorite report for April. So now, having followed the above guidance, you can start to following through some of the Microsoft examples and try stuff out fro yourself.This article targets this common scenario for anyone that can execute SQL Server T-SQL: The third and fourth lines show that SQL Server is upgrading the database from version 705 (SQL 2012 RC0) to version 706 (SQL 2012 RC1/RTM).įor more information on the versions of the MDF file and SQL Server product version, please see Pranavs blog post “ How to determine version of Local Sql Instance and your database“. The file activation failure is simply saying that the log file could not be found, you can see on the second line that SQL has created the required log file for you. Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706. Converting database 'AdventureWorks2012' from version 705 to the current version 706. New log file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL_2012\MSSQL\DATA\AdventureWorks2012_log.ldf' was created. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect. The following message is displayed (this could vary if you’re not using SQL Server 2012): File activation failure. Next, add the database by running the following SQL (amend the filename to reflect the location of the file on your system): CREATE DATABASE AdventureWorks2012 ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL_2012\MSSQL\DATA\AdventureWorks2012_Data.mdf') With SSMS open, connect to your database server and click New Query from the toolbar. For OWNER RIGHTS check that Full control is ticked, if not, tick it and then hit Apply and OK through the remaining dialog windows. To check and rectify this, browse to the file using Windows Explorer and right-click the datafile, select Properties, Security tab then Edit. Some users have noted that if you’re using Windows 7 or 8 then you need to start SSMS as Administrator, I’m using Windows 7 and have had no problem running it up normally but it’s worth bearing in mind if you do hit an error.Īlso, you may encounter an “Access is denied” message when attempting to access the datafile – this is possibly the level of permissions on the data file itself. Once the download has completed, it is advisable to copy the data file to your DATA folder, in my case, this is located here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL_2012\MSSQL\DATA. To get the datafile, browse to the Microsoft SQL Server Samples page on CodePlex and take the AdventureWorks2012 Data File. After installing SQL Server 2012, it is a good idea to download and add the sample database to your SQL Server instance.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |