SPViews is a tool that will let you easily create relational database views into your Microsoft SharePoint lists and libraries. SPViews is designed to make Microsoft SharePoint lists and libraries easily accessible for reporting tools like Microsoft SQL Server Reporting Services and data integration tools like Microsoft SQL Server Integration Services. It helps you develop and integrate your custom applications with Microsoft SharePoint data quickly with standard SQL connections.
1. When you start SPViews, you first have to connect to the Microsoft SQL Server hosting the SharePoint content database. This works in the same way your SQL management tools do. You can either connect to a remote server by typing the server name in the “Server Name (Instance)” dropdown text box, connect to a locally installed MS SQL server by selecting “.” from the dropdown options or search for it on local network by selecting the "<Browse for more…>" in the dropdown box.
2. If you select "<Browse for more…>", you’ll be able to search for MS SQL databases on your local workstation or on the network. Be aware that if you search for Network SQL Instances, it can only find servers on the local subnet. You can always type in the name of the server in the Connect dialog box if your search fails to find the server you are looking for.
3. You can either authenticate using your current Windows credential by selecting “Windows Authentication” or you can authenticate with your MS SQL user ID and password by selecting the “SQL Server Authentication” option.
4. You can check the “Use TCP to connect” check box to force a TCP connection rather than the default Named Pipes connection.
Next you need to select the SharePoint Content Database you would like to create the views for in the “Content Database” dropdown box. SPViews connects to your Content Database and populates the tree view panel on the right with the sites, sub-sites and the available lists. You can expand the tree to see individual libraries and lists. If you'd like to create your views in a database other than SharePoint Content database, you may pick a different database in the Target Database drop down. The Options pane also becomes available.
Use the Options area to set a naming convention for your column and view names. Here, you will select how spaces in the SharePoint lists and column names will be handled, and how the view name will be formed. The default option for "What to NAME my Views?" will create view names that are unique in the content database.
If you change the default options, you can save them and reload them next time from the File menu.
Next you need to select the lists that should have views created for them. Refer to the Understanding the Views section below to see how columns with types not available in MS SQL Server are handled.
SPViews does not change or update any SQL databases for you. It simply generates the Transact-SQL script. The Transact-SQL script only creates views on the SharePoint content database. If the views already exist, you can optionaly have the script drop and recreate them. You should make sure that the “Prefix” you select in the Options doesn’t interfere with any object in your target content database.
Make sure to select SQL Command Mode when running the Transact-SQL script. It will help you verify that you are running the script on the correct target server against the correct target database.