Demonstrates how to create a catalog and a full text index in SQL Server.
SQL Server Management Studio
Knowledge of SQL
Ability to Use Windows Services Manager
http://online1.daytonastate.edu/player2.php?id=563ca5e068bc78b807910338bb4d4279
USE SampleFullTextDB GO CREATE TABLE TextTable ( [id] INT PRIMARY KEY IDENTITY(1,1), [name] NVARCHAR(200), [text] NVARCHAR(MAX)) EXEC sp_fulltext_database 'Enable' CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON dbo.TextTable([text]) KEY INDEX PK__TextTable__7D78A4E7 INSERT INTO dbo.TextTable VALUES('Message 1','I feel like my follwing problem had to with my data in my Assignment 7 part 2 to be weird. At first, I didn t use a SP for this, I inputted them as text by selecting the tables manually. In the designer, it resulted in having 5 entries PER song and underneith showed only 1 of my 5 musicians and their related Instrument. This was correct, however, it was very lenghty at 5 entries per song were being listed and I had 8 songs. So I tried to make a SP for this at Dr. Eaglins suggestion that it may be easier. It did clear up the multiple entries, however, I am stuck with the musicians and instruments not showing up. It is most likely due to the fact my SP is wrong, and my Function I made that is pulled from the SP would now allow it (saying it could not be bound) My Function was for pulling ALL Musicians and Instruments. Has anyone had any duplications while intputting a new musician into their database? I also may be inputing the new data into my database wrong, I executed sp_InsertMusician which contains everything (Artist, Album, Musician, Instrument, and Song, each of which have "if, else statements" to prevent duplicates). Should I have them all in one SP like this? When adding the rest of my one albums songs I ran into a problem, the musicians and instruments are the same for the whole album, however the SP required me to have ALL fields inputted. So I put them in, one by one for the whole album. Now, for the most part it did not duplicate such as the Artist and Album stayed put, however the Songs table did not. There were, I believe 8 or more entries, before I manually deleted them. Deleting them however did not let me unless I deleted the multiple entries which were in the Musicians field as well. This might have described my problem where Designer was showing 5 entries per song 1 for each Musician and their Instrument on that Song. Does anyone else have their inputting of a new album all together in 1 SP? Or do you have mutiple SP s. Based on my long question, I think I am lost. However not too lost, It did work half way.') INSERT INTO dbo.TextTable VALUES('Message 2', 'This is a long answer. Using the Query Builder here is the stored procedure that I get when I join all the tables, I also have the output from the query with 2 albums below the query - you will not that for every musician it repeats the artis, album, etc... This is OK - when creating the report you can use grouping to easily tell it to grou according to song, and this will make the report look nice. Under week 7 ( https://cop4709.pbworks.com/w/page/62351004/COP4709%20Course%20Lectures%20By%20Week )- grouping is the 3rd (of s) video s. I added a list control to the blank report, then I added Musician and Instrument (dragged onto list). Right clicked and added Parent Group Song_name, right clicked and added Album_Name, right clicked and added Artist_Name and got report as shown at bottom - grouped correctly with all data. SELECT Albums.id AS [Albums id] ,Albums.artistID ,Albums.name AS [Albums name] ,Albums.[year] ,Artists.id AS [Artists id] ,Artists.name AS [Artists name] ,InstrumentPlayed.id AS [InstrumentPlayed id] ,InstrumentPlayed.SongID ,InstrumentPlayed.MusicianID ,InstrumentPlayed.name AS [InstrumentPlayed name] ,Musician.id AS [Musician id] ,Musician.name AS [Musician name] ,Songs.id AS [Songs id] ,Songs.name AS [Songs name] ,Songs.albumID ,Songs.TrackNumber FROM Albums INNER JOIN Artists ON Albums.artistID = Artists.id INNER JOIN Songs ON Albums.id = Songs.albumID INNER JOIN InstrumentPlayed ON Songs.id = InstrumentPlayed.SongID INNER JOIN Musician ON InstrumentPlayed.MusicianID = Musician.id 1 1 Goodbye Yellow Brick Road NULL 1 Elton John 16 1 1 Bass Guitar 1 Dee Murray 1 Funeral For a Friend/Love Lies Bleeding 1 1 1 1 Goodbye Yellow Brick Road NULL 1 Elton John 17 1 2 Piano 2 Elton John 1 Funeral For a Friend/Love Lies Bleeding 1 1 1 1 Goodbye Yellow Brick Road NULL 1 Elton John 18 1 3 Electric Guitar 3 Davey Johnstone 1 Funeral For a Friend/Love Lies Bleeding 1 1 1 1 Goodbye Yellow Brick Road NULL 1 Elton John 19 1 4 Drums 4 Nigel Olsson 1 Funeral For a Friend/Love Lies Bleeding 1 1 1 1 Goodbye Yellow Brick Road NULL 1 Elton John 20 1 5 ARP Synthesizer 5 David Hentschel 1 Funeral For a Friend/Love Lies Bleeding 1 1 2 2 Tommy NULL 2 The Who 21 6 6 Vocals 6 Roger Daltrey 6 Overture 2 1 2 2 Tommy NULL 2 The Who 22 6 7 Electric Guitar 7 Pete Townshend 6 Overture 2 1 2 2 Tommy NULL 2 The Who 23 6 8 Bass Guitar 8 John Entwistle 6 Overture 2 1 2 2 Tommy NULL 2 The Who 24 6 9 Drums 9 Keith Moon 6 Overture 2 1') SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text],'Goodbye') SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text],'SuggeStioN') SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text], 'Goodbye AND Road') SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text], ' "Go*" ') |
All Materials Copyright 2012 Dr. Ron Eaglin