How to Export SQL Views into SharePoint

[LEFT][CODE]http://www.brighthub.com/computing/windows-platform/articles/64720.aspx[/CODE]SharePoint can be a central source to implement different data types and can display them in the Web parts. It's a collaboration front-end tool that can bring in data from various back-end sources, like SQL, or Excel, or XML, or operational functions of Visual Studio with C# or Visual Basic.

[B]Back End and Front End Database[/B]

The term back-end database comes from developers taking a small database programming system and providing the end-user operations with the database to make them a single item. The developer then decides how to integrate the data for display. One of the most frequently used tool sets is Microsoft Access.
Using the back-end has advantages in terms of scalability and performance. The back-end database can have hundreds of thousands of data rows, but only a small portion will be for display.
The front-end is the portion that will function to display the data from the back end. One other feature that is noteworthy is that the data from the two different systems does not have to be the same type. Frequently SQL data have different specifications than SharePoint. The conversion tools are built in to the software making the back-end - front-end operations feasible.


[B]What is a SQL View?[/B]

SQL, short for Structured Query Language, provides the means whereby database programmers retrieve data from a database. Normally the results are presented within a temporary table, which are the answers to the query previously posed.
A view is a temporary table, a virtual table because it is not intended to last. Rather the view in a database is created under specific conditions. It is created by a query joining one or more tables using the SQL language. The results are displayed in a table, and this table is used to create the view.
Here is an example of a SQLstatement
SELECT
Product_List.ProductID, Product_List.ProductName
FROM
Products AS Product_List
WHERE (Product_List.Discontinued = 0)
This statement produce two items the ProductID and the ProductName from a table called Products, with the condition that the products have not been discontinued.

[B]Display and Web Parts in SharePoint[/B]

SharePoint is a browser based program. It is capable of displaying information in a variety of contexts. It frequently acts like a front-end to other programs like SQL Server 2005, or C# (sharp) or Visual Basic that will serve as back-end programs. Some of the ways that the data from a SQL view can be displayed is within a Web Part. This is part of the browser display that can contain information from other sources. Web Parts are building blocks in a SharePoint Site. One of the ways of displaying data from a back-end operation is using a List View. This will take the data and show it on the front-end, which then will show up in the Web Part.



[B]Exporting SQL View to SharePoint[/B]

To export SQL data to SharePoint you must use SQL Server Integration Services (SSIS). This is part of the Sql Server 2005 package. Run the import/export wizard to start the export.
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66384"][IMG]http://images.brighthub.com/f0/d/f0dfc8b71ed34b01b1852655642061eecf30f248_small.jpg[/IMG][/URL]
Choose the Data Source
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66385"][IMG]http://images.brighthub.com/2d/9/2d98f6481f8ef265e2f2c825f1b80046888a9a33_small.jpg[/IMG][/URL]
[B]If the source data is a view, the SQL Server Import and Export Wizard automatically converts the view to a table in the destination.[/B]
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66386"][IMG]http://images.brighthub.com/b1/1/b1199cea06e660508b84993a907162f571cf871d_small.jpg[/IMG][/URL]
Enter the Name of the Data Source. SQL SSIS can exporting a variety of different files: Excel, flat files, CSV, Access,or XML.
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66387"][IMG]http://images.brighthub.com/42/0/420b7db1359539f7197fa52a6b931bbd9be12297_small.jpg[/IMG][/URL]
Choose a Destination
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66388"][IMG]http://images.brighthub.com/7d/7/7d7fbfeb767c2ad1ce2eca2145b888d1ec192e8d_small.jpg[/IMG][/URL]

Save and Execute Package
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66389"][IMG]http://images.brighthub.com/80/b/80b873ef62066a75c0d845e4fa91645b0bc4bd85_small.jpg[/IMG][/URL]

Complete the Wizard
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66390"][IMG]http://images.brighthub.com/4a/e/4aefb23e526ba9c215bdc50715e0d8d340b92189_small.jpg[/IMG][/URL]
The Execution was Succesful
[URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx?image=66391"][IMG]http://images.brighthub.com/25/9/2594e593598cc2b5bd95e534ed2840b509d087fc_small.jpg[/IMG][/URL]
Those are the export steps. At the other end SharePoint can import the following kind of files: spreadsheets, calendar, list, Cxcel, list template, flat files. Thus multiple file types are not an import problem for SharePoint.
Source: [URL="http://www.brighthub.com/link/header.aspx?u=http%3a%2f%2fmsdn.microsoft.com%2fen-us%2flibrary%2fms140052.aspx&p=64720&returnUrl=%2fcomputing%2fwindows-platform%2farticles%2f64720.aspx"]How to: Run the SQL Server Import and Export Wizard[/URL]
Image Source:[URL="http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm"] SQL Server 2005 Import / Export Wizard[/URL]

[B]Summary: Exporting SQL View to SharePoint[/B]

Back-end data that derives from SQL can be integrated into a front-end program, like Sharepoint.That is what makes SharePoint a powerful utility for bringing disparate pieces of software together. Data sources can find a place in SharePoint, whether they are Access, Excel, CSV, or SQL.

Source:[URL="http://www.brighthub.com/link/header.aspx?u=http%3a%2f%2foffice.microsoft.com%2fen-us%2fsharepointserver%2fHA102164281033.aspx&p=64720&returnUrl=%2fcomputing%2fwindows-platform%2farticles%2f64720.aspx"]http://office.microsoft.com/en-us/sharepointserver/HA102164281033.aspx[/URL]
SQL Views can be seen [URL="http://odetocode.com/code/299.aspx"]here[/URL].
Click the following to view [URL="http://www.rssbus.com/products/sharepoint/templates/template.aspx?webpart=sql.RecordEditor"]SQL Web Part Editor[/URL]


Read more: [URL="http://www.brighthub.com/computing/windows-platform/articles/64720.aspx#ixzz0gRzoMOXH"]How to Export SQL View to Sharepoint[/URL]
[LEFT][COLOR=#000000]


[/COLOR][/LEFT]

[/LEFT]