Habilitando a conexão remota no SQL Server 2008

Fonte: http://sqlfromhell.wordpress.com/2010/04/18/habilitando-o-acesso-remoto-no-sql-server-2008/

Por Paulo R. Pereira

E ai pessoas, como tenho recebido alguns e-mails pedindo para mostrar “como fazer para que outros computadores acessem o meu banco de dados” e “habilitar o acesso remoto do SQL Server 2008”, então vamos à demonstração!

Antes de começar, se o servidor estiver fora de um “domínio” ou você não saiba o que é “domínio”, dê uma olhada neste artigo:
http://sqlfromhell.wordpress.com/2009/05/24/habilitando-sql-authentication-e-o-usuario-sa/

Em seguida, habilitamos o Firewall do Windows (permitindo exceções). Mas para ambientes de teste não há problema em deixá-lo desabilitado.

Dentre as diversas exceções, adicionamos mais uma porta:

Para boa parte dos cenários, a porta TCP 1433 já vai suprir as necessidades:

Em outros cenários mais específicos, pode existir a necessidade de habilitar as portas UPD 1434, TCP 1434 e outras (at. http://msdn.microsoft.com/en-us/library/ms175483.aspx ).

No SQL Server Management Studio, verifique se nas propriedades do servidor esta habilitada a opção “Allow remote connections to this server”. Caso não esteja habilitada, será necessário habilitá-la.

No SQL Server Configuration Manager, mais precisamente nos protocolos da sua instância (ex.: SQLEXPRESS, SQL2005, MSSQLSERVER…), entre nas propriedades do protocolo TCP/IP:

E habilite o protocolo TCP/IP. Em alguns cenários também se faz necessário habilitar o protocolo “Named Pipes”.

E recomendo definir a porta de conexão:

Se você quiser configurar outras portas para o SQL Server, dê uma olhada neste artigo:
http://sqlfromhell.wordpress.com/2009/09/05/portas-sql-server/

Reinicie o serviço do SQL Server da sua instância e o SQL Server Browser, depois em uma máquina cliente, tente se conectar ao servidor com um usuário válido:

Se tudo ocorrer como previsto, teremos a conexão:

Bem, estou usando uma VM “zerada” que até o momento tem o Windows Server 2003 R2 e o SQL Server 2008, assim existe grande possibilidade de não funcionar em outros cenários.

Pontos a levar em consideração para tratar outros cenários:

  • Se você não conseguir ao menos dar um “ping” ou compartilhar uma pasta do servidor à rede ou conectar remoto com o Remote Desktop Connection (mstsc.exe), isso pode ser sinal que tem algo errado com o firewall ou a rede ou até mesmo com o Windows (então não é culpa do SQL Server, ainda…).
  • Programas de Antivírus ou Firewall de terceiros, também são grandes culpados por problemas com o acesso remoto, tanto no servidor como no cliente.
  • O usuário que você esta utilizando para se conectar pode não ter permissão para se conectar ou o servidor não esta no domínio (at. http://sqlfromhell.wordpress.com/2009/05/24/habilitando-sql-authentication-e-o-usuario-sa/ ). Para “testar”, no servidor tente se conectar ao SQL Server com o usuário que você esta utilizando na máquina cliente.
  • Para “testar”, não utilize o SQL Server Management Studio 2005 para se conectar a um SQL Server 2008, pois algumas vezes isso não dá muito certo.

Mais uma coisa, se eu não responder um comentário em 24h é sinal que não estou com acesso à internet, pois possivelmente estarei preso na ilha de LOST (probabilidade 0,001%) ou estou numa “missão” extraordinária pelo Exército Brasileiro (probabilidade 4%) ou estou apagando algum incêndio em alguma consultoria (probabilidade 85%) ou dedicado a algum trabalho acadêmico (probabilidade 10%), então também vale a pena procurar os fóruns do MSDN e/ou o suporte da Microsoft.

Na internet, encontrei outros dois artigos bons sobre o assunto:

Instalando e Configurando o SQL Server 2005 Express – Nilton Pinheiro:
http://www.mcdbabrasil.com.br/downloads/install_sqlexpress.pdf

Como configurar Conexão Remota no SQL Server 2005 – Diego Nogare:
http://www.linhadecodigo.com.br/artigo/1260/como-configurar-conexao-remota-no-sql-server-2005.aspx

Search WebPart for Sharepoint 2010

By tdomf_6998b, on October 19th, 2010
This is a free webpart. Below is the complete code. Enjoy!

In SharePoint 2010, you can use the query object model to build custom
search
Web Parts and search applications. This object model allows you to
query against SharePoint Server search as well as to FAST Search Server 2010 for
SharePoint.

The FullTextSqlQuery class available in the object model allows you to build
complex search queries based on SQL syntax however, the KeywordQuery class can
be used to issue search queries based on simple query syntax. With Fast Search
Server 2010, this class can also be used to run queries based on the Fast Query
Language (FQL), which supports advanced queries in enterprise environments.

The example below demonstrates using the query object model to issue a search
query based on a keyword search with the KeywordQuery class. This example is
built as an application page with a text input field called txtSearch, a button
for submitting the query called btnSearch, and an SPGridView called
searchGrid.

Please Note : Before you can run search queries, you also need to specify the
search server that should be used for indexing in Central Administration, under
Application Management -> Databases -> Manage Content Databases ->
[Your content DB] > Search Server.

using System;
using Microsoft.SharePoint;
using
Microsoft.SharePoint.WebControls;
using System.Data;
using
Microsoft.Office.Server;
using Microsoft.Office.Server.Search;
using
Microsoft.Office.Server.Search.Query;

namespace Apress.SP2010.Layouts.CustomSearch
{
public partial class
ApplicationPage1 : LayoutsPageBase
{
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
SetGridColumns();
}
}

// The event is called when the search button is pressed
protected void
btnSearch_Click(object sender, EventArgs e)
{
DataTable search = new
DataTable();

using (SPSite site = new
SPSite(SPContext.Current.Web.Site.Url))
{
KeywordQuery query = new
KeywordQuery(site);

query.ResultsProvider = Microsoft.Office.Server.Search.Query.

SearchProvider.Default;

query.ResultTypes = ResultType.RelevantResults;

query.KeywordInclusion = KeywordInclusion.AllKeywords;

query.QueryText = txtSearch.Text;

ResultTableCollection results = query.Execute();

if (results.Count > 0)
{

ResultTable relevant = results[ResultType.RelevantResults];

search.Load(relevant);

DataView view = new DataView(search);

// Fill the SPGridView defined in the page markup

searchGrid.DataSource = search;

searchGrid.DataBind();
}}}

private void SetGridColumns()
{
SPBoundField fieldTitle = new
SPBoundField();

fieldTitle.HeaderText = “Title”;

fieldTitle.DataField = “Title”;

SearchGrid.Columns.Add(fieldTitle);

SPBoundField fieldPath = new SPBoundField();

fieldPath.HeaderText = “Path”;

fieldPath.DataField = “Path”;

SearchGrid.Columns.Add(fieldPath);

SPBoundField fieldAuthor = new SPBoundField();

fieldAuthor.HeaderText = “Edited”;

fieldAuthor.DataField = “Write”;

SearchGrid.Columns.Add(fieldAuthor);
}}}

In the above code, the OnClick handler for the button creates a KeywordQuery
object and passes the text from the text field to the QueryText property. After
calling Execute, the results are available through the ResultTable at the index
ResultType.RelevantResults in the returned ResultTableCollection. Using a
DataTable, these results are bound to the SearchGrid for display. The relevant
table columns are bound using the SetGridColumns method during Page_Load.

Saiba mais sobre os recursos do Sharepoint

Installing FullText Search on SQL Server 2008

1. DownloadSQL Express with FullText Search capability for SQL Server 2008
http://www.microsoft.com/express/Database/InstallOptions.aspx

2. Select Installation Type as New Installation

3 During Feature Selection, select Full-Text Search

4. While Instance Configuration, Create new Instance Configuration.
– Select Named Instance and give new name.  Example: I have given name: MyShriGanesh

5. Main steps for installation are done, now simply press next with default choices and complete the installation.

6. After Installation login with new instance name created.
– Example: Earlier I have created named instance MyShriGanesh, so i need to login with same named instance in order to take benefit of FullText Search feature.

7. Now, Create New Database, Run Tables and SPs Scripts and restore database with data.  If you don’t have one already, download sample database and use that database.

After installation of sample database your object explorer will look like as follow:

8. Open Query Window and type following command to test whether FullText Search is installed or not.

Select SERVERPROPERTY(‘IsFullTextInstalled’)
If this query returns a value of ‘1’ then the component is installed.

Now, lets go step by step to run Full Text Search on SQL Server 2008

Step 1: Creating Full Text Catalog in SQL Server 2008
Syntax:
Create FullText Catalog DatabaseNameCatalog

Example:
Create FullText Catalog AdventureWorksCatalog

Step 2: Create FullText Index in SQL Server 2008
Syntax:
Create FullText Index on TableName
(Column1, Column2, …., ColumnN)
Key Index PK_TablesPrimaryKey on DatabaseNameCatalog
with Change_Tracking Auto
Note:
  • TableName is name of table you would like to create fulltext index.
  • ColumnName is column on which you would like to create fulltext index, it is column you would like to perform search on.
  • PK_TablesPrimaryKey is primary key of table you are creating on fulltext search index.
  • DatabaseNameCatalog is fulltext search catalog created earlier.
Example:
Create FullText Index on Production.ProductDescription
([Description])
Key Index PK_ProductDescription_ProductDescriptionID on AdventureWorksCatalog
with Change_Tracking Auto

 

Step 3: Running FullText Search Query
There are many different examples of running fulltext query, I would be giving 2 examples of fulltext search query.
Example 1: Using FreeText

Select [Description]
from Production.ProductDescription
Where
FREETEXT([Description],  ‘Entry Level Bike’)
















Example 2: Using Contains
Select [Description] 
from Production.ProductDescription
Where 
Contains([Description],  ‘”Entry” and “Level” and “Bike”‘)















Example 3: Using Weight keyword
 
Select [Description] 
from Production.ProductDescription
Where 
Contains([Description],  
‘ISABOUT (Entry Weight(.8), Level Weight(.4), Bike Weight (.2))’)

 

 

Example 4: Using Inflectional keyword

Select [Description]
from Production.ProductDescription
Where
Contains([Description],
‘FormsOf (INFLECTIONAL, Entry, Level, Bike)’)

Create WPF Master – Detail UI Using Data Sources Window Object DataSource

This article has a corresponding video that can be viewed here.

Master – Detail layouts are the bread and butter of Line of Business (LOB) applications. This walkthrough will show you how to drag & drop from the Data Sources Window to create the UI that is sourced from an Object DataSource.

This article has a C# and VB.NET download that includes both a completed solution and a starter solution. The starter solution includes the data entity classes and a static (Shared) data source class.

This walkthrough assumes that you’ll be starting with the starter solution.

Table of Contents

  1. Completed Application
  2. Starter Solution
  3. Adding a Title to the Form
  4. Adding the ComboBox Used for Record Selection
  5. Adding the Details Form
  6. Adding the DataGrid
  7. How does it Work?
  8. Comments

Completed Application 

Figure 1 Completed Application

Starter Solution 

Open the accompanying (C# or VB.NET) starter solution.

Figure 2 Starter Solution

The Data class provides two static (Shared) properties that expose an ObserveableCollection that the application will use as its data source. The Data class takes the place of a data layer that would be used to populate CLR classes from a database.

Figure 3 Data Class Diagram

The Customer class can contain one or more Addresses. Each Address has an associated AddressType.

Figure 4 Entity Class Diagram

Adding a Title to the Form 

Step One

Figure 5 Draw Border

  • Build the solution
  • Open ApplicationMainWindow.xaml
  • Select the root Grid by clicking inside the Window
  • Create a new Grid Row that takes up about 25% of the Window by
    • Click the Grid rail on the left to create a new row
  • Draw a Border control inside the new row
    • Select the Border control in the Toolbox then draw the Border as pictured in Figure 5 above

Step Two

  • Draw a TextBlock control inside the Border
  • Multi-select the TextBlock and Border controls using either the Document Outline or by CTRL + clicking each control
    • Right click, select Reset Layout, All
    • This will reset the Width, Height, HorizontalAlignment and VerticalAlignment properties to their default values

 ResetBorderTextBlockLayout

Figure 6 Reseting Values

  • After properties are reset your Window should look like Figure 7 below

Figure 7 Title Step Two

Step Three

  • Using the Document Outline, select the Border control
  • Using the Properties Window set the following properties:
    • CornerRadius to 30
    • Margin to 11
    • Padding to 7
    • BorderThickness to 2
    • Background to formTitleBackgroundBrush
      • Use Resource Picker, see Figure 8 below.  After opening Resource Picker, select formTitleBackgroundBrush.
    • BorderBrush to formTitleBorderBrush
      • Use Resource Picker, see Figure 8 below.  After opening Resource Picker, select formTitleBorderBrush.

    Figure 8 Applying Resource

  • Using the Document Outline, select the TextBlock
  • Using the Properties Window set the following properties
    • Text to Customer
    • VerticalAlignment to Center
    • FontSize to 18
    • FontWeight to Bold
    • Foreground to formTitleForegroundBrush
      • Use Resource Picker
  • Select the Grid
  • Hover cursor over the left Grid rail for the top row. Set top row to Auto sizing as in Figure 9 below
  • The bottom image in Figure 9 shows the completed form title

Figure 9 Auto Sizing Row

Adding the ComboBox Used for Record Selection 

Step One

  • Select the root Grid by clicking inside the Window
  • Create a new Grid Row that takes up about 25% of the Window
    • Click the Grid rail on the left to create a new row
  • Draw a StackPanel inside the new row
  • Add a TextBlock to the StackPanel by double clicking the TextBlock icon in the Toolbox
  • Add a ComboBox to the StackPanel by double clicking the ComboBox icon in the Toolbox
  • Form should now look like Figure 10 below

Figure 10 StackPanel

Step Two

  • Select the StackPanel
  • Right click on the StackPanel, choose Reset Layout, All
  • Using the Properties Window set the following properties:
    • Margin to 11
    • Orientation to Horizontal
  • Multi-select the TextBlock and ComboBox
  • Right click on the TextBlock and choose Reset Layout, All
  • Using the Properties Window set the following property:
    • VerticalAlignment to Center
  • Form should now look like Figure 11 below

    Figure 11 StackPanel

Step Three

  • Select the TextBlock
  • Using the Properties Window set the Text property to Select Customer
  • Select the ComboBox
  • Using the Properties Window set the following properties:
    • Margin to 7, 0
    • Width to 175
  • Form should now look like Figure 12 below

    Figure 12 Record Selector Completed

Step Four

From the Data menu, select Show Data Sources.

Figure 13 Data Menu

Figure 14 Data Sources Window

Important
Before clicking the Add New Data Source link, you must have built your solution.  The Data Sources Window uses reflection to discover types in your solution.  If the type has not been built yet, it will not appear in the Data Sources Window.
  • Add a new Data Source by clicking on the “Add New Data Source…” link pictured in Figure 14 above
  • When the Data Source Configuration Wizard is displayed select the Object icon and click the Next button

Figure 15 Select Data Objects

  • Drill down to the Customer object and select it, then press the Finish button
  • Figure 16 below shows the newly created Object Data Source

Figure 16 New Data Source

Step Five

The ComboBox will provide Customer selection. To wire up the ComboBox drag the Customer object and drop it on the ComboBox as pictured in Figure 17.

Figure 17 Binding ComboBox

  • Select the ComboBox
  • Using the Properties Window set the DisplayMemberPath to Name
  • Run application. You should be able to view data as pictured in Figure 18.

Figure 18 Run Application

Note
As part of the Solution Starter, the ApplicationMainWindow Loaded event has code that populates the CollectionViewSource for the application. This is code that developers would normally have to write as the Object Source drag and drop from the Data Sources Window does not add code to your projects.

Adding the Details Form 

The details form in this application plays the role of the Master in our Master-Details application.

Step One

Customize the Customer object output.

Figure 19 Change Layout Type

  • Change the Customer object layout to Details as pictured in Figure 19 above

Figure 20 Change CustomerID Control

  • Change the CustomerID field to use the Label control when rendered as pictured in Figure 20 above

Step Two

Figure 21 Adding New Row

  • Select the Grid and add a new row as pictured in Figure 21 above.

Figure 22 Details Form Generation

  • Drag and drop the Customer object to the Grid Row below the ComboBox
  • With the Details Grid selected, use the Properties Window and reset the following properties:
    • Height
    • Width
  • Using the Properties Window set the following properties:
    • VerticalAlignment to Top
    • Margin to 11
  • Select the outer Grid and change the Details Form row to use Auto sizing
  • The application should now look like Figure 23 below
  • Run the application and select different Customers

Figure 23 Details Form Completed

Adding the DataGrid 

Step One

Customize the Addresses object output.

  • Expand the Address node
  • Set the CustomerID field control to None

Step Two

Figure 24 Add Grid to Bottom Row

  • Add a Grid control to the bottom row
  • With Grid selected, right click, select Reset Layout, All
  • Using the Properties Window set the following property:
    • Margin to 11

Step Three

Figure 25 Drag and Drop DataGrid

  • Drag and drop the Addresses object from the Data Sources Window to the Grid control added in Step Two above
  • With DataGrid selected, right click, select Reset Layout, All
  • The DataGrid should now look like Figure 26 below

 

DataGridResized

Figure 26 Data Grid Resized

Step Four

Edit DataGrid columns using the XAML Editor.

Figure 27 DataGrid Columns

  • Insert a DataGridComboBoxColumn as pictured in Figure 27 above by copying the below XAML:

<DataGridComboBoxColumn SelectedValueBinding=”{Binding Path=AddressType.AddressTypeId, Mode=TwoWay}” ItemsSource=”{x:Static local:Data.AddressTypes}” DisplayMemberPath=”Name” Header=”Address Type” SelectedValuePath=”AddressTypeId” />

  • Cut and Paste the Street column so that it is above the City column as pictured in Figure 27 above.
  • Run you application
    • You can add, edit and delete rows in the DataGrid
    • Notice that the ComboBox in the DataGrid provides selection for the Address, AddressType.
Note
See How does it Work section below for an explanation of the DataGrid ComboBox.

How does it Work? 

The CollectionViewSource is a powerful class that provides a wrapper around a data source, adding sorting, grouping and filtering functionality without changing the source data.

The CollectionViewSource is a proxy class to the underlying CollectionView that provides navigation of collection items.

Information
MSDN CollectionViewSource documentation can be viewed here: http://msdn.microsoft.com/en-us/library/system.windows.data.collectionviewsource.aspx

Figure 28 Data Object Relationships

  • The Data.Customers ObservableCollection is the source for the CustomerViewSource. This data is wired up in the ApplicationMainWindow.Loaded event.
  • When the ComboBox selection changes it sets the CurrentItem on the underlying CollectionView.
  • The Details Form is bound to the CurrentItem. When selection changes, the Details Form DataContext changes and the selected Customer is displayed.
  • The CustomerAddressesViewSource is bound to the CurrentItem (current Customer) Addresses property.
  • The DataGrid is bound to the CustomerAddressesViewSource and displays all items in this collection.

DataGrid ComboBox

The DataGrid ComboBox displays the AddressType.Name for the Address and provides selection of an AddressType.

Figure 29 DataGrid ComboBox

  • SelectedValueBinding property gets and sets the AddressTypeId for the Address in the row
  • ItemsSource is set to the static (Shared) property AddressTypes exposed by the Data class. AddressTypes is a collection of AddressType.
  • DisplayMemberPath is the Name property of the AddressType
  • Header is the column header displayed in the DataGrid
  • SelectedValuePath is the AddressTypeId of the AddressType
Information
MSDN DataGridComboBoxColumn documentation can be viewed here:http://msdn.microsoft.com/en-us/library/system.windows.controls.datagridcomboboxcolumn(VS.100).aspx

Comments 

Microsoft values your opinion about our products and documentation. In addition to your general feedback it is very helpful to understand:

  • How the above feature enables your workflow
  • What is missing from the above feature that would be helpful to you

Thank you for your feedback and have a great day,

Autor: Karl Shifflett

Fonte: http://blogs.msdn.com/b/wpfsldesigner/archive/2010/01/19/create-wpf-master-detail-ui-using-data-sources-window-object-datasource.aspx
Visual Studio Cider Team

MS SQL Server 2005 full-text indexing

Getting started with full-text search

  • Make sure that you download the version of SQL Server Express with the additional full-text search capabilities. This is the larger one of the two downloads possible.
  • Read up a bit on Full Text Search concepts.
  • Making the Catalog
    Now you need to create a full-text catalog. The catalog seems to be a container to put all your full-text indexes into. So, if you are going to need to search the text of three tables, you make three indexes (one for each table) and put all three indexes in the one catalog.
    WARNING: Some of the information of the Microsoft website relates to the full version of SQL Server, such as the “Storage folder” that Express doesn’t have. Don’t panic, you just need to create the catalog by hand, rather than through the Management Interface.
    For my application, I just made one catalog and put two of my indexes into it. From what I understand, if your indexes are going to be huge, you should make a separate catalog for each index. Here is how to make a catalog:

    1. Start SQL Server Management Studio Express, and connect to your database.
    2. Click on New Query to get a box where you can type raw SQL.
    3. Type:
      use mydatabase
      go
      EXEC sp_fulltext_database 'enable'
      go
      CREATE FULLTEXT CATALOG mycatalog
      go
    4. Click “Execute” to run the SQL. This will enable fulltext searching on the database, and create the catalog, which is in fact a folder on your computer called “mycatalog” somewhere within the Microsoft SQL Server folder. If your catalog is going to be HUGE, it may be worth researching some extra parameters for this command to tell it where to put the catalog, e.g. on a different disk to the database itself. This would be for performance improvement more than anything else.
  • // //

    Next we are going to create a full-text index. But if you want to create a full-text index on a table, the table must have a “unique, single-column, non-nullable index“. In most cases this will be your primary key. What Microsoft is saying here is that you must have a non-null index on the table (all primary keys automatically are this), and that the index must be on just one column. Oh Drat! My table has a primary key that is on two columns, so I have to make an index that is on a single unique column in order to use full-text searching. What to do, what to do? Well, my solution was to add a new column to the table called “text_id”. I made this an int column, and also made it an identity column. This way, the text_id field gets automatically filled with a unique integer for each record that is entered, without me needing to change any of my existing aspx pages.
    Also, if you already have a primary key, but it is on a really long field, I suggest you make an int field and use that instead, because the full-text index that gets created will be huge if the primary key you use is big, such as a GUID.

    Ok, here is how to create the full-text index:

    1.  
      1. If you need to add an identity column, do this first.
      2. Make sure you have an index on the identity column. I called mine “myindex”.
      3. Type in and execute this SQL:
        CREATE FULLTEXT INDEX ON mydatabase.dbo.mytable
        (
        column_to_index
        Language 0X0
        )
        KEY INDEX myindex ON mycatalog
        WITH CHANGE_TRACKING AUTO

        Where mytable is the name of the table, column_to_index is the name of the column that is full of text you want to be able to search on, myindex is the name of the index (could be PK_something if you used your primary key), and mycatalog is the name of the catalog you created earlier. The “Language” bit just tells SQL Server to not treat this as being in any specific language. I don’t know how to specify another language here, but you can change it later by using the SQL Server Manager interface. I didn’t notice any difference between a UK English index and a language-neutral index. I suspect it is only important for languages other than English.
      4. I did discover that sometimes when you try to make the index, it doesn’t get fully made until you do a little bit through the user interface. To verify that things have worked, start the SQL Server Manager interface, right-click on the table name and choose “Modify”. Right-click on the column and choose “Full text index…”. In here you need to check that “Columns” is set to the column name you want to index. Also check whether “Active” is Yes — if not, it means the index isn’t built yet.
      5. You may have to wait a while for the index to be created. If you have a large table, the CPU usage on your PC will probably go up to 100% while this happens. If you get problems, see the troubleshooting section below…
    2. Performing a Find
      The actual SELECT statement to find records is then very easy. Here is an example:
      SELECT description
      FROM mytable
      WHERE CONTAINS(description, '"shark attack"')

      (You need to put the search terms in double-quotes, within the single-quotes needed for the SQL)
    3. Eliminating Noise Words
      If your text fields are in HTML, then you should add things lik “BR” and “P” to the list of noise words.

    What I discovered

    At first, full-text search queries were taking about 30 seconds to return results. This was pretty disappointing, and I started looking for ways to tune the system up. But while I was looking, the whole thing sorted itself out, and suddenly queries became almost instant. It is very impressive, I can search for a phrase like “african drumming” on my 20,000 record database almost instantly. I don’t know what caused this delay then speed-up. Presumably the SQL Server was building the indexes, and it took a while. I’m not sure how come I could do queries while the index was being built though. It’s a puzzle.

    Also, I discovered that searching for “drum* lesson*” returns better results than searching for “drum lesson”, because the first one will also find “drumming lessons”. So, I made it so that queries that people enter automatically have a * put after each word.

    A Bit of Troubleshooting

    If it doesn’t work, here are some of the problems I encountered, and the ways of debugging and solving the problems:

    Application Events

    Right Click on My Computer, choose Manage, then Event Viewer, then Application. This shows you the Application Event Log. In here you will see any error messages from the Full Text service. Usually one of these errors will just redirect you to the server logs themselves.

    SQL Server Logfile & Fulltext Index Log-file

    Have a look in the logs. I found mine in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. The fulltext logs are called SQLFTxxxxxx.LOG. They are hard to understand, but they may help.

    Try ReBooting

    You need to reboot after you install FullText Indexing, and a reboot is always a good place to start when troubleshooting. I had the error message “Error ‘0x80040e09’ occurred during full-text index population” appearing in my logfiles, and no fulltext indexes created at all until I figured out that I needed to reboot.

    Commands & Queries that you can type into SQL Server 2005:

    How to check the status of the full-text indexes in a catalog:

    USE mydatabase;
    GO
    EXEC sp_help_fulltext_tables mycatalog;
    GO

    Enable fulltext search on a table:

    USE mydatabase;
    GO
    ALTER FULLTEXT INDEX ON mytable ENABLE;
    GO

    Check if full text indexing is enabled:

    USE mydatabase;
    GO
    SELECT DATABASEPROPERTY(‘mydatabase’, ‘IsFullTextEnabled’);
    GO

    Update

    I’ve been looking at this a bit more recently, and worked out a few things:

    • You can have more than one fulltext indexed column per table. Old SQL Server versions prevented this, but 2005 allows it. So, you can have a table with a title and a summary column, and both can be full-text indexed.
    • The language setting is important, despite what I wrote above. I now use the code ‘2057’ to set my indexes to be in UK English. This means that searches on “dogs” will also find “dog”.
    • You can find the status of your fulltext indexes like this:
      exec sp_help_fulltext_columns mytable
      This shows you which columns in a table are set up for full-text searching.
      select * from sys.fulltext_indexes
      This shows you the status of the current indexes and when they were last updated. I think the key is to make sure that change_tracking_state_desc is set to “AUTO” and that has_crawl_completed is “1”. You can also see the time of the last crawl, i.e. when the index was last updated, which can help you see why things aren’t working.

    Here is my example .sql statement, that sets up three columns of a table for fulltext indexing, and sorts out the change tracking state, and starts a full crawl:

    use content
    exec sp_fulltext_database 'enable'
    go
    exec sp_fulltext_catalog 'cmscatalog', 'create'
    go
    exec sp_fulltext_table 'document', 'create', 'cmscatalog', 'PK_document'
    go
    exec sp_fulltext_column 'document', 'title', 'add', '2057'
    exec sp_fulltext_column 'document', 'keywords', 'add', '2057'
    exec sp_fulltext_column 'document', 'search_text', 'add', '2057'
    go
    exec sp_fulltext_table 'document', 'activate'
    go
    EXEC sp_fulltext_table 'document', 'Start_background_updateindex';
    go
    exec sp_fulltext_table 'document', 'start_full'
    go

    Conclusion

    Full Text Searching is easy to set up and use in SQL Server Express. When it works, it works fast!

    Creating an ASP.NET report using Visual Studio 2010 – Part 3

    We continue building our report in this three part series.

    Creating an ASP.NET report using Visual Studio 2010 – Part 1

    Creating an ASP.NET report using Visual Studio 2010 – Part 2

    Adding the ReportViewer control and filter drop downs.Open the source code for index.aspx and add a ScriptManager control. This control is required for the ReportViewer control. Add a DropDownList for the categories and suppliers. Add the ReportViewer control. The markup after these steps is shown below.

    <div>

        <asp:ScriptManager ID="smScriptManager" runat="server">

        </asp:ScriptManager>

        <div id="searchFilter">

            Filter by: Category :

            <asp:DropDownList ID="ddlCategories" runat="server" />

            and Supplier :

            <asp:DropDownList ID="ddlSuppliers" runat="server" />

        </div>

        <rsweb:ReportViewer ID="rvProducts" runat="server">

        </rsweb:ReportViewer>

    </div>

     

    The design view for index.aspx is shown below. The dropdowns will display the categories and suppliers in the database. The report will be filtered by the selections in the dropdowns. You will see how to do this in the next steps.
    image 
    Attaching the RDLC to the ReportViewer control by clicking on the top right of the control, going to Report Viewer tasks and selecting Products.rdlc.  
    image
    Resize the ReportViewer control by dragging at the bottom right corner. I set mine to 800px x 500px. You can set this value in source view also.

    image

    Defining the data sources.
    We will now define the Data Source used to populate the report. Go back to the “ReportViewer Tasks” and select “Choose Data Sources”
    image
    Select a “New data source..”
    image

    Select “Object” and name your Data Source ID “odsProducts”

    image 
    In the next screen, choose “ProductRepository” as your business object.
    image
    Choose “GetProductsProjected” in the next screen.

    image 
    The method requires a SupplierID and CategoryID. We will have the data source use the selected values of the drop down lists we defined earlier. Set the parameter source to be of type “Control” and set the ControlIDs to be ddlSuppliers and ddlCategories respectively. Your screen will look like this:
    image
    We are now going to define the data source for our drop downs. Select the ddlCategory drop down and pick “Choose Data Source”.

    image
    Pick “Object” and give it an id “odsCategories”
    image 
    In the next screen, choose “ProductRepository”
    image
    Select the GetCategories() method in the next screen.
    image 
    Select “CategoryName” and “CategoryID” in the next screen. We are done defining the data source for the Category drop down.
    image
    Perform the same steps for the Suppliers drop down.

    image
    image
    image 
    Select each dropdown and set the AppendDataBoundItems to true and AutoPostback to true.
    image   
    The AppendDataBoundItems is needed because we are going to insert an “All“ list item with a value of empty. This will be the first item in each drop down list. Go to each drop down and add this list item markup as shown below.
    image

    Double click on each drop down in the designer and add the following code in the code behind. This along with the “Autopostback= true” attribute refreshes the report anytime the selection in the drop down is changed.

    protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)

    {

        rvProducts.LocalReport.Refresh();

    }

     

    protected void ddlSuppliers_SelectedIndexChanged(object sender, EventArgs e)

    {

        rvProducts.LocalReport.Refresh();

    }

    Compile your report and run the page. You should see the report rendered. Note that the tool bar in the ReportViewer control gives you a couple of options including the ability to export the data to Excel, PDF or word.

     image

    Conclusion

    Through this three part series, we did the following:

    • Created a data layer for use by our RDLC.
    • Created an RDLC using the report wizard and define a dataset for the report.
    • Used the report design surface to design our report including adding a chart.
    • Used the ReportViewer control to attach the RDLC.
    • Connected our ReportWiewer to a data source and take parameter values from the drop downlists.
    • Used AutoPostBack to refresh the reports when the dropdown selection was changed.

    RDLCs allow you to create interactive reports including drill downs and grouping. For even more advanced reports you can use Microsoft® SQL Server™ Reporting Services with RDLs. With RDLs, the report is rendered on the report server instead of the web server. Another nice thing about RDLs is that you can define a parameter list for the report and it gets rendered automatically for you. RDLCs and RDLs both have their advantages and its best to compare them and choose the right one for your requirements.

    Creating an ASP.NET report using Visual Studio 2010 – Part 2

    We continue building our report in this three part series.

    Creating the Client Report Definition file (RDLC)image 
    Right click on the RDLC folder, select “Add new item..” and add an “RDLC” name of “Products”. We will use the “Report Wizard” to walk us through the steps of creating the RDLC.
    image 
    In the next dialog, give the dataset a name called “ProductDataSet”. Change the data source to “NorthwindReports.DAL” and select “ProductRepository(GetProductsProjected)”.

    The “Data Source” may show up empty. To get it populated, make sure your project is compiled and there is an index.aspx file in the root folder. This may be a bug.
     
    The fields that are returned from the method are shown on the right. Click next.
    image 
    Drag and drop the ProductName, CategoryName, UnitPrice and Discontinued into the Values container. Note that you can create much more complex grouping using this UI. Click Next.

     

    image 
    Most of the selections on this screen are grayed out because we did not choose a grouping in the previous screen. Click next.
    image
    Choose a style for your report. Click next.
    image
    The report graphic design surface is now visible. Right click on the report and add a page header and page footer.
    image
    With the report design surface active, drag and drop a TextBox from the tool box to the page header. Drag one more textbox to the page header. We will use the text boxes to add some header text as shown in the next figure.

    image
    You can change the font size and other properties of the textboxes using the formatting tool bar (marked in red). You can also resize the columns by moving your cursor in between columns and dragging.
    image

    Adding Expressions

    Add two more text boxes to the page footer. We will use these to add the time the report was generated and page numbers. Right click on the first textbox in the page footer and select “Expression”.
    image
    Add the following expression for the print date (note the = sign at the left of the expression in the dialog below)
    image

    "© Northwind Traders " & Format(Now(),"MM/dd/yyyy hh:mm tt")

    Right click on the second text box and add the following for the page count.

     
    Globals.PageNumber & " of " & Globals.TotalPages

    Formatting the page footer is complete.
     
    We are now going to format the “Unit Price” column so it displays the number in currency format.  Right click on the [UnitPrice] column (not header) and select “Text Box Properties..”

    image
    Under “Number”, select “Currency”. Hit OK.
    image

    Adding a chart

    With the design surface active, go to the toolbox and drag and drop a chart control. You will need to move the product list table down first to make space for the chart contorl. The document can also be resized by dragging on the corner or at the page header/footer separator.

    image
    In the next dialog, pick the first chart type. This can be changed later if needed. Click OK. The chart gets added to the design surface.
    image 
    Click on the blue bars in the chart (not legend). This will bring up drop locations for dropping the fields. Drag and drop the UnitPrice and CategoryName into the top (y axis) and bottom (x axis) as shown below. This will give us the total unit prices for a given category. That is the best I could come up with as far as what report to render, sorry 🙂 Delete the legend area to get more screen estate.
    image
    Resize the chart to your liking. Change the header, x axis and y axis text by double clicking on those areas.

    image
    We made it this far. Let’s impress the client by adding a gradient to the bar graph 🙂 Right click on the blue bar and select “Series properties”.
    image

    Under “Fill”, add a color and secondary color and select the Gradient style.
    image

    We are done designing our report. In the next section you will see how to add the report to the report viewer control, bind to the data and make it refresh when the filter criteria are changed.

    Creating an ASP.NET report using Visual Studio 2010 – Part 1

    Creating an ASP.NET report using Visual Studio 2010 – Part 2

    Creating an ASP.NET report using Visual Studio 2010 – Part 3

    Add a folder called “RDLC”. This will hold our RDLC report.