General database quesion

guys i have a programming question i have table called prerequsites
which is like this
prerequisite component component

a b

so inside my before insert trigger i have to check if the prerequisite is a valid entry like for instance
an entry like b is a prerequisite component for component a which is not a valid entry since i already have an entry in the prerequisite table which defines that comonent a is a prerequisite of b. however this is
very basic so i can test it easy some thing like this in my before insert
prerequisite component component

a b
b a ( Not a valid entry)
select count(*) from prerequisite where component = value of prerequisite component
and prerequisite component = value of component
the above select statement will actually tell me if entry is allowed for insert and does not override other prerequsites

but it can get very complex depending on how the user chooses so if someone can tell me a nice algorithm or good logic to approach this problem it would be great like for instance

prerequisite component component

a b
b c
c a ( Not a valid entry. so how do i ensure that this entry is not allowed considering that complexity for prerequsites can increase quite greatly)

please can someone tell me how to approach this problem.

The database i am using is Oracle 9i but it does not really matter since it is a general database question

This is a directed graph. If you find a cycle in it, then it is an invalid entry.

a<-b // a preceeds b
b<-c // b preceeds c
c<-a // c preceeds a, but this forms a cycle: a<-b<-c<-a

You can use a depth first search or a breadth first search to find the cycle. Any standard algorithms book or website will tell you how to do it. You will need a stack or recursion for DFS or a queue for BFS.

See

http://en.wikipedia.org/wiki/Graph_theory

You can also try a time/space tradeoff and solve the problem in O(1) time without a stack or queue by using a technique called "path enumeration". The idea is to precompute all edges - even those with path length > 1. The problem is the #of edges can grow very large depending on the topology of your graph. But in some cases with relatively "flat" and "bushy" graphs, this might be a very acceptable tradeoff. This was explained in a Joe Celko book.

Regards,
Clifford Dibble

GACing components

Hi, help please!!

I'm trying to GAC a component that I've built. I had one of my company's .Net gurus help me out with this and we did a few things without me actually following what was going on. Stuff like creating a public key, creating a .snk file in C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents...I have to confess I didn't really follow what he was doing!

But, we did manage to succssfully get my component into the SSIS toolbox which was the main thing.

Then I looked at the BOL page explaining how to install and GAC stuff (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/efe22939-9186-4eb4-ac33-6b92f12e93db.htm) and it made no mention of the stuff we'd done. So, I tried to back that stuff out which included removing the following line from AssemblyInfo.vb:
<Assembly: AssemblyKeyFile("C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\RegExComponent.snk")>

And now, when ever I try to GAC the component using the same command (i.e. gacutil /iF "c:\program files\Microsoft SQL Server\90\DTS\PipelineComponents\RegExComponent.dll") I get the following error:

Failure to add assembly to the cache: Attempt to install an assembly without a strong name

Can anyone fill me in on what's going on? Why can't I GAC stuff without that reference to the snk file? What is an .snk file? Why did I need it to originally GAC stuff when the BOL page makes ne mention of it?

questions questions...

cheers
Jamie
Hi,

Any assembly that needs to be put into the GAC needs to be Strong Named.

Go through the following links to know what is a Strong Name and to create a key and then to sign your assembly using the key generated:
1.]Strong Named Assemblies
2.]Creating a Key
3.]Example and steps on Strongnaming a DLL

Regards,
Vikram|||You're correct that an assembly needs to be signed with a strong name before it can be installed in the GAC. A .NET Framework tool named sn.exe is used to generate the key file used for signing. I found a topic named "Strong-Named Assemblies" in the Framework SDK that summarizes the verification benefits of the strong name.

In Visual Studio 2003, you linked key file to code by the manual entry in assemblyinfo.vb that you mentioned. In Visual Studio 2005, you can configure Project Properties to sign your assembly with the selected key file each time that you build it. This is great for ease of use, but may allow the developer to forget that it's taking place...which may be why such an important step was overlooked in the BOL topic.

On the subject of ease of use, note that you can also use VS 2005 "post build events" scripts to uninstall the previous version of your component from the GAC, copy the new build to the PipelineComponents folder, and install the new build into the GAC.

I'll make sure that the BOL topic you mentioned is update to mention the strong name requirement.

-Doug
|||Thanks for the advice guys, that makes more sense now. If I have any more problems I'll let you know.

I'm particularly interested in the scripts that Doug mentioned for re-installing a component. I'll take a look at that later and no doubt be replying here with some Qs

-Jamie|||

Set your build events to run gacutil. /if forces an install, other flags perform an uninstall. Using the macros helps, so I always have the same commands regardless of project. My post-build event is-

"$(DevEnvDir)\..\..\SDK\v2.0\Bin\gacutil" /if "$(TargetPath)"

|||Hi again,
I added a post-build-event as Darren suggested. When I built the project I got:

Rebuild All started: Project: RegEx, Configuration: Release Any CPU

Vbc.exe /noconfig /imports:Microsoft.VisualBasic,System,System.Collections,System.Collections.Generic,System.Data,System.Diagnostics /nowarn:"42016,41999,42017,42018,42019,42032,42036,42020,42021,42022" /rootnamespace:Conchango.SqlServer.SSIS.DataFlow.RegExComponent /doc:obj\Release\Conchango.SqlServer.SSIS.DataFlow.xml /define:"CONFIG=\"Release\",TRACE=-1,_MyType=\"Windows\"" /reference:"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll","C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll","C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.PipelineHost.dll",C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\System.Data.dll,C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\System.dll,C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\System.Xml.dll /debug- /keyfile:RegExComponent.snk /optimize+ /out:obj\Release\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.dll /resource:obj\Release\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.Resources.resources /target:library RegEx.vb "My Project\AssemblyInfo.vb" "My Project\Application.Designer.vb" "My Project\Resources.Designer.vb" "My Project\Settings.Designer.vb"

RegEx -> C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.dll

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\Gacutil" /if "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.dll"

Microsoft (R) .NET Global Assembly Cache Utility. Version 2.0.50215.44

Copyright (C) Microsoft Corporation. All rights reserved.

Assembly successfully added to the cache

========== Rebuild All: 1 succeeded, 0 failed, 0 skipped ==========

Which all looks pretty hunky dory to me. Looks as though its got to the GAC OK.

So now I want to use that component. I go into my toolbox, delete the previous incarnation from the toolbox and go into "Choose Items...". I pick the new component from the list and its back in my toolbox. When I use it, its still the old version of my component.

The only way I can get it to definately pick up the new one is to change the assembly version number. Forgive my ignorance but is this expected?
-Jamie

|||No, that is not expected. Changing the assembly version is a ball ache, as it changes the strong name, which is what the package references. Hack the Xml time to keep compatability!

Sounds like you have version mismatches in different locations. You will need to restart BIDS to get it to pick up a new build.

Double check you are -

Copying or building to the \PipelineComponents\ directory
GAC'ing the correct version.

I used to build to the PipelineComponents folder, but there are two issues with this-

1 Sometimes VS looses the plot on the build folder, and all those relative path dot dots, so I ended up with a \Program Files....PipelineComponents\ hierachy under
my project!

2 FxCop bitches if the assembly is not under the project.

So leave the build folder to the default bin\<config>.

Then use Post-Build events such as these-

"$(DevEnvDir)\..\..\SDK\v2.0\Bin\gacutil" /if "$(TargetPath)"
copy "$(TargetPath)" "$(SolutionDir)Build" /Y
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents" /Y
Install to GAC
Copy to my build folder which is what I use for installs and similar
Copy to DTS folder|||

DarrenSQLIS wrote:

No, that is not expected. Changing the assembly version is a ball ache, as it changes the strong name, which is what the package references. Hack the Xml time to keep compatability!

Sounds like you have version mismatches in different locations. You will need to restart BIDS to get it to pick up a new build.

Double check you are -

Copying or building to the \PipelineComponents\ directory
GAC'ing the correct version.

I used to build to the PipelineComponents folder, but there are two issues with this-

1 Sometimes VS looses the plot on the build folder, and all those relative path dot dots, so I ended up with a \Program Files....PipelineComponents\ hierachy under
my project!

2 FxCop bitches if the assembly is not under the project.

So leave the build folder to the default bin\<config>.

Then use Post-Build events such as these-

"$(DevEnvDir)\..\..\SDK\v2.0\Bin\gacutil" /if "$(TargetPath)"
copy "$(TargetPath)" "$(SolutionDir)Build" /Y
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents" /Y
Install to GAC
Copy to my build folder which is what I use for installs and similar
Copy to DTS folder

Cheers Darren. it seems as though stopping and starting BIDS (which is equally as much a ballache) would solve this. Ah well!

I haven't had the same experiences that you have - I build straight to the DTS\PipelineComponents folder and GAC it using post-build-events and had no problems...yet! If and when I do I'll switch back.

Ta
-Jamie|||

I go into my toolbox, delete the previous incarnation from the toolbox and go into "Choose Items...".

Does this mean that everytime i make changes to my assembly i have to go to each script task and 'remove & add' the reference again?

I'm hoping not.

Btw, what is BDIS and how do you restart it?

thanks.

|||

If your assembly string name changes, then yes I think you will need to remove and re-add the reference.

BIDS
(http://wiki.sqlis.com/default.aspx/SQLISWiki/BIDS.html)

|||

So if i don't change the assembly name, i just recompile my assembly and GAC it, and my package script will pick up the new code right?

Also, in a deployment scenario, do i have to copy my assembly into the c:\windows\framework\2.0\ directory before i GAC it ? I had to do this in order 'Add reference' in my script. Do i need to do this when deploying? or just GACing the assembly will do?

As for BIDS, duh! why didn't i think of it! Too many acronyms in my mind.

|||

Enzoe wrote:

So if i don't change the assembly name, i just recompile my assembly and GAC it, and my package script will pick up the new code right?

If you don't change your assembly strong name. This is made up of the assembly name, assembly version and public key token from key used to sign it. So leave the name alone, fix the version (take out the * if present in AssemblyInfo), and don't change the signing keys. The latter is not something peopl do very often.

Enzoe wrote:

Also, in a deployment scenario, do i have to copy my assembly into the c:\windows\framework\2.0\ directory before i GAC it ? I had to do this in order 'Add reference' in my script. Do i need to do this when deploying? or just GACing the assembly will do?

Correct, GAC will suffice. The framework folder is only required for the designer to be able to find it for the Add Reference. At runtime it will look in the GAC for you.

|||

That was my line of thought and i did read about this somewhere. But i just did some changes to my assembly (added a new function), built it and gac-ed it from my assembly project folder.

When i opened my package and script, the new function didn't appear. i had to copy my assembly back to the framework directory and re-GAC it before the new function appeared in my intellisense.

Or can i summarise that whenever design is required, not just at the 'add reference' point, but also for intellisense and coding to work, it has to be copied to framework directory but when the package actually executes, it looks at the GAC only.

Is this understanding accurate?

|||

Enzoe wrote:

that whenever design is required, not just at the 'add reference' point, but also for intellisense and coding to work, it has to be copied to framework directory but when the package actually executes, it looks at the GAC only.

Correct.

GACing components

Hi, help please!!

I'm trying to GAC a component that I've built. I had one of my company's .Net gurus help me out with this and we did a few things without me actually following what was going on. Stuff like creating a public key, creating a .snk file in C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents...I have to confess I didn't really follow what he was doing!

But, we did manage to succssfully get my component into the SSIS toolbox which was the main thing.

Then I looked at the BOL page explaining how to install and GAC stuff (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/efe22939-9186-4eb4-ac33-6b92f12e93db.htm) and it made no mention of the stuff we'd done. So, I tried to back that stuff out which included removing the following line from AssemblyInfo.vb:
<Assembly: AssemblyKeyFile("C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\RegExComponent.snk")>

And now, when ever I try to GAC the component using the same command (i.e. gacutil /iF "c:\program files\Microsoft SQL Server\90\DTS\PipelineComponents\RegExComponent.dll") I get the following error:

Failure to add assembly to the cache: Attempt to install an assembly without a strong name

Can anyone fill me in on what's going on? Why can't I GAC stuff without that reference to the snk file? What is an .snk file? Why did I need it to originally GAC stuff when the BOL page makes ne mention of it?

questions questions...

cheers
Jamie
Hi,

Any assembly that needs to be put into the GAC needs to be Strong Named.

Go through the following links to know what is a Strong Name and to create a key and then to sign your assembly using the key generated:
1.]Strong Named Assemblies
2.]Creating a Key
3.]Example and steps on Strongnaming a DLL

Regards,
Vikram|||You're correct that an assembly needs to be signed with a strong name before it can be installed in the GAC. A .NET Framework tool named sn.exe is used to generate the key file used for signing. I found a topic named "Strong-Named Assemblies" in the Framework SDK that summarizes the verification benefits of the strong name.

In Visual Studio 2003, you linked key file to code by the manual entry in assemblyinfo.vb that you mentioned. In Visual Studio 2005, you can configure Project Properties to sign your assembly with the selected key file each time that you build it. This is great for ease of use, but may allow the developer to forget that it's taking place...which may be why such an important step was overlooked in the BOL topic.

On the subject of ease of use, note that you can also use VS 2005 "post build events" scripts to uninstall the previous version of your component from the GAC, copy the new build to the PipelineComponents folder, and install the new build into the GAC.

I'll make sure that the BOL topic you mentioned is update to mention the strong name requirement.

-Doug|||Thanks for the advice guys, that makes more sense now. If I have any more problems I'll let you know.

I'm particularly interested in the scripts that Doug mentioned for re-installing a component. I'll take a look at that later and no doubt be replying here with some Qs

-Jamie|||

Set your build events to run gacutil. /if forces an install, other flags perform an uninstall. Using the macros helps, so I always have the same commands regardless of project. My post-build event is-

"$(DevEnvDir)\..\..\SDK\v2.0\Bin\gacutil" /if "$(TargetPath)"

|||Hi again,
I added a post-build-event as Darren suggested. When I built the project I got:

Rebuild All started: Project: RegEx, Configuration: Release Any CPU

Vbc.exe /noconfig /imports:Microsoft.VisualBasic,System,System.Collections,System.Collections.Generic,System.Data,System.Diagnostics /nowarn:"42016,41999,42017,42018,42019,42032,42036,42020,42021,42022" /rootnamespace:Conchango.SqlServer.SSIS.DataFlow.RegExComponent /doc:obj\Release\Conchango.SqlServer.SSIS.DataFlow.xml /define:"CONFIG=\"Release\",TRACE=-1,_MyType=\"Windows\"" /reference:"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll","C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll","C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.PipelineHost.dll",C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\System.Data.dll,C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\System.dll,C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\System.Xml.dll /debug- /keyfile:RegExComponent.snk /optimize+ /out:obj\Release\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.dll /resource:obj\Release\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.Resources.resources /target:library RegEx.vb "My Project\AssemblyInfo.vb" "My Project\Application.Designer.vb" "My Project\Resources.Designer.vb" "My Project\Settings.Designer.vb"

RegEx -> C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.dll

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\Gacutil" /if "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Conchango.SqlServer.SSIS.DataFlow.RegExComponent.dll"

Microsoft (R) .NET Global Assembly Cache Utility. Version 2.0.50215.44

Copyright (C) Microsoft Corporation. All rights reserved.

Assembly successfully added to the cache

========== Rebuild All: 1 succeeded, 0 failed, 0 skipped ==========

Which all looks pretty hunky dory to me. Looks as though its got to the GAC OK.

So now I want to use that component. I go into my toolbox, delete the previous incarnation from the toolbox and go into "Choose Items...". I pick the new component from the list and its back in my toolbox. When I use it, its still the old version of my component.

The only way I can get it to definately pick up the new one is to change the assembly version number. Forgive my ignorance but is this expected?
-Jamie

|||No, that is not expected. Changing the assembly version is a ball ache, as it changes the strong name, which is what the package references. Hack the Xml time to keep compatability!

Sounds like you have version mismatches in different locations. You will need to restart BIDS to get it to pick up a new build.

Double check you are -

Copying or building to the \PipelineComponents\ directory
GAC'ing the correct version.

I used to build to the PipelineComponents folder, but there are two issues with this-

1 Sometimes VS looses the plot on the build folder, and all those relative path dot dots, so I ended up with a \Program Files....PipelineComponents\ hierachy under
my project!

2 FxCop bitches if the assembly is not under the project.

So leave the build folder to the default bin\<config>.

Then use Post-Build events such as these-

"$(DevEnvDir)\..\..\SDK\v2.0\Bin\gacutil" /if "$(TargetPath)"
copy "$(TargetPath)" "$(SolutionDir)Build" /Y
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents" /Y
Install to GAC
Copy to my build folder which is what I use for installs and similar
Copy to DTS folder

|||

DarrenSQLIS wrote:

No, that is not expected. Changing the assembly version is a ball ache, as it changes the strong name, which is what the package references. Hack the Xml time to keep compatability!

Sounds like you have version mismatches in different locations. You will need to restart BIDS to get it to pick up a new build.

Double check you are -

Copying or building to the \PipelineComponents\ directory
GAC'ing the correct version.

I used to build to the PipelineComponents folder, but there are two issues with this-

1 Sometimes VS looses the plot on the build folder, and all those relative path dot dots, so I ended up with a \Program Files....PipelineComponents\ hierachy under
my project!

2 FxCop bitches if the assembly is not under the project.

So leave the build folder to the default bin\<config>.

Then use Post-Build events such as these-

"$(DevEnvDir)\..\..\SDK\v2.0\Bin\gacutil" /if "$(TargetPath)"
copy "$(TargetPath)" "$(SolutionDir)Build" /Y
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents" /Y
Install to GAC
Copy to my build folder which is what I use for installs and similar
Copy to DTS folder

Cheers Darren. it seems as though stopping and starting BIDS (which is equally as much a ballache) would solve this. Ah well!

I haven't had the same experiences that you have - I build straight to the DTS\PipelineComponents folder and GAC it using post-build-events and had no problems...yet! If and when I do I'll switch back.

Ta
-Jamie|||

I go into my toolbox, delete the previous incarnation from the toolbox and go into "Choose Items...".

Does this mean that everytime i make changes to my assembly i have to go to each script task and 'remove & add' the reference again?

I'm hoping not.

Btw, what is BDIS and how do you restart it?

thanks.

|||

If your assembly string name changes, then yes I think you will need to remove and re-add the reference.

BIDS
(http://wiki.sqlis.com/default.aspx/SQLISWiki/BIDS.html)

|||

So if i don't change the assembly name, i just recompile my assembly and GAC it, and my package script will pick up the new code right?

Also, in a deployment scenario, do i have to copy my assembly into the c:\windows\framework\2.0\ directory before i GAC it ? I had to do this in order 'Add reference' in my script. Do i need to do this when deploying? or just GACing the assembly will do?

As for BIDS, duh! why didn't i think of it! Too many acronyms in my mind.

|||

Enzoe wrote:

So if i don't change the assembly name, i just recompile my assembly and GAC it, and my package script will pick up the new code right?

If you don't change your assembly strong name. This is made up of the assembly name, assembly version and public key token from key used to sign it. So leave the name alone, fix the version (take out the * if present in AssemblyInfo), and don't change the signing keys. The latter is not something peopl do very often.

Enzoe wrote:

Also, in a deployment scenario, do i have to copy my assembly into the c:\windows\framework\2.0\ directory before i GAC it ? I had to do this in order 'Add reference' in my script. Do i need to do this when deploying? or just GACing the assembly will do?

Correct, GAC will suffice. The framework folder is only required for the designer to be able to find it for the Add Reference. At runtime it will look in the GAC for you.

|||

That was my line of thought and i did read about this somewhere. But i just did some changes to my assembly (added a new function), built it and gac-ed it from my assembly project folder.

When i opened my package and script, the new function didn't appear. i had to copy my assembly back to the framework directory and re-GAC it before the new function appeared in my intellisense.

Or can i summarise that whenever design is required, not just at the 'add reference' point, but also for intellisense and coding to work, it has to be copied to framework directory but when the package actually executes, it looks at the GAC only.

Is this understanding accurate?

|||

Enzoe wrote:

that whenever design is required, not just at the 'add reference' point, but also for intellisense and coding to work, it has to be copied to framework directory but when the package actually executes, it looks at the GAC only.

Correct.

FYI: Using Try's with Finally

Within my script component I was having some difficulty getting nested Exceptions to bubble out of the script component logic. The symptom appears when you get an ex.message of "Object reference not set to an instance of an object." As soon as I removed the Finally section my message (or the actual Exception object) was properly passed to the caller.

I've only done minimal work with Try/Catch/Finally structures. Is this normal?

Thanks,

Jeff Tolman
E&M Electric

Sounds like you are "swallowing" the exception. Catching it and then not doing anything with it. A code sample would be helpful.|||

Hey Jason,

No, I'm always passing on the exception. The problem comes when there is a Finally section after the Catch. The message that I throw up to the caller does not make it and shows up as the "Object reference not set to an instance of an object" message. It works fine without the Finally clause.

Jeff

|||

JazzGeek wrote:

The problem comes when there is a Finally section after the Catch. The message that I throw up to the caller does not make it and shows up as the "Object reference not set to an instance of an object" message.

I suspect the code inside Finally block fails (probably due to uninitialized variable) and throws a new exception. Could you post the code? Check that all the variables that code inside Finally uses has been assigned some objects, even if an exception is thrown before this code.

|||

I only had one line of code within the Finally block:

....
Catch ex as Exception
Throw ex
Finally
sqlReader.Close()
End Try

I ended up doing this:

....
Catch ex as Exception
Throw ex
End Try
sqlReader.Close()

which worked.

Jeff

|||

Is the sqlReader variable initialized? Most likely, when the first exception occurs, sqlReader is not assigned anything. So when you try to close null object, you get the new "Object reference not set to an instance of an object" exception, and this exception is propagated up instead of the original. If sqlReader.Close() is out of the finally scope, it is not called when first exception is thrown.

I don't think there is anything specific to SSIS here. Finally could be used in SSIS script component, but as anywhere, you should be careful to avoid throwing new exceptions inside catch or finally blocks, otherwise this new exception is propagated to next level, instead of original which is lost.

Fuzzy Group Updates?

Hi there,

Quick Background: I have an SSIS package that reads data from a flat file then runs it through a Fuzzy Grouping component. The result of this Fuzzy Group is put into a SQL server 2005 table.

Question: Over time, the flat file will be adding new records (some that should be added to existing groups) and so I'll need to update my Fuzzy Group table to include these new records. Is there anyway to simply add these new records to the existing Fuzzy Group without changing all of the _key_out values? If I completely regenerate the Fuzzy Group table that will potentially give me different _key_out values correct?

Does this make sense?

Any help would be greatly appreciated!

>>" If I completely regenerate the Fuzzy Group table that will potentially give me different _key_out values correct?"

Correct.

wenyang

|||

Thanks for the reply!

Anyway to preserve the _key_out while still adding records to the groups? Sounds like a complete rebuild of the Fuzzy group is out of the question. Anyway to do this incrementally?

|||

Hi,

Yes, each time you run Fuzzy Grouping with a different set of input rows (or with a different threshold), it is possible that different groupings will result.

If you have run FG once and would like to keep the existing groups, one alternative would be to use Fuzzy Lookup for the incremental input rows. You would basically perform a fuzzy lookup against the output of FG and return the _key_out of the best matching row. You have thus effectively found a group for the new input row. If no match is found above the FL match threshold, then just assign a new unique _key_out to the input row to create a new group.

A slight problem with this approach is that over time all the incremental rows may not be grouped as well as they could be, as the clustering algorithm that Fuzzy Grouping uses to globally pick groupings is not being employed. At that point you may want to just rerun FG and switch to the new groupings.

We are considering adding a feature in the next version that will allow you keep all the old groupings intact.

Let us know if you have any more questions.

Regards,

-Kris

|||Thanks Kris, that will probably suffice for now. Yeah put that in the next version, the FG component is great but it doesn't have much use after the initial run because of this limitation.

Full-Text Search is not installed, or a full-text component cannot

I am unable to get Full Text Indexing to work with SQL Server 2000 Developers
Edition. When I execute this command:
EXEC sp_fulltext_database 'enable'
This is what I get:
(1 row(s) affected)
Server: Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.
I attempted to install FTS again from the CD, but it indicates that it is
already installed.
Here is the version I am using:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright 1988-2003 Microsoft Corporation Developer Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
When I run this at the command prompt:
net start mssearch
I get this:
The requested service has already been started.
More help is available by typing NET HELPMSG 2182
I’m not sure where to go from here.
Hello,
The possible causes of this kind of issue may be as followings:
* Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
This is the most common cause. Reapplying the SP fixes FTS.
* Microsoft Search service is not installed or started
* SQL server or Microsoft Search service is not started as a administrative
user
To narrow down the issue, I suggest that you perform the following steps:
1. Make sure SQL server and Microsoft Search service are started as the
same domain\administrative user account
2. Install the latest SQL SP4 or reinstall SP3.
Microsoft SQL Server 2000 Service Pack 4
<http://www.microsoft.com/downloads/d...FC8D-C20E-4446
-99A9-B7F0213F8BC5&displaylang=en#filelist>
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Dale,
Sorry for the late reply on this thread, but this type of question is best
answered in the fulltext newsgroup.
The key bit of info here is the SQL Server edition "SQL Server 2000
Developers Edition" as FTS is not installed by default as it is with Std.
Edition and above. You can "re-install" the FTS components via removing or
renaming the following product tracking registry key (remove :
"<Instance_Name>\" if this is not a named instance):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<Instance_Name>\Tracking\{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}
Then you will be able to re-install the FTS / MSSearch components. You may
also want to review the procedures in KB article 827449 "How to manually
reinstall the Microsoft Search service for an instance of SQL 2000" at
http://support.microsoft.com/default...b;EN-US;827449
Sophie, the advise that you've provided is wrong and will cause known
problems, i.e.. high CPU utilization (there is a KB article on this).
Specifically, the Microsoft Search service MUST be started and running under
the "System account" (LocalSystem), while the MSSQLServer service can be
started and running under either LocalSystem or DOMAIN\Account (but NOT the
DOMAIN\Administrators account!). All this I've learned from years of
experience with SQL FTS since 1998 SQL 7.0 Beta3 while at MSFT...
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:Lg1hyhFqFHA.2152@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> The possible causes of this kind of issue may be as followings:
>
> * Full-Text Search(FTS) is installed AFTER SQL Service Service Pack(SP).
> This is the most common cause. Reapplying the SP fixes FTS.
> * Microsoft Search service is not installed or started
> * SQL server or Microsoft Search service is not started as a
> administrative
> user
>
> To narrow down the issue, I suggest that you perform the following steps:
> 1. Make sure SQL server and Microsoft Search service are started as the
> same domain\administrative user account
> 2. Install the latest SQL SP4 or reinstall SP3.
> Microsoft SQL Server 2000 Service Pack 4
> <http://www.microsoft.com/downloads/d...FC8D-C20E-4446
> -99A9-B7F0213F8BC5&displaylang=en#filelist>
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>

Full-Text Search is not installed, or a full-text component cannot

Active-active cluster. The second node was reinstalled after a hardware
failure.
The MS Search service is started.
But when I try to query full-text the error message shows up:
Server: Msg 7609, Level 17, State 1, Line 1
Full-Text Search is not installed, or a full-text component cannot be loaded.
How can I reinstall full-text on cluster environment without affect the node
is working ?
thank you
Shimon
I believe you need to evict this node from the cluster, reinstall SQL Server
there and then upgrade it to add it back to the cluster. I have forwarded
this to microsoft.public.sqlserver.clustering for their response to this, as
this is really a clustering question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:F1A440E4-2781-408A-B7CE-356968CDCF13@.microsoft.com...
> Active-active cluster. The second node was reinstalled after a hardware
> failure.
> The MS Search service is started.
> But when I try to query full-text the error message shows up:
> Server: Msg 7609, Level 17, State 1, Line 1
> Full-Text Search is not installed, or a full-text component cannot be
> loaded.
> How can I reinstall full-text on cluster environment without affect the
> node
> is working ?
> thank you
> Shimon
|||When you evict a node from a cluster and replace it, you must also clean the
node from the SQL installation. Run the SQL installer and remove the old
node. Then you can re-add it to SQL, which will also re-install full-text.
Don't forget to reapply any service packs and hotfixes to the new node.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23sOoM$sFGHA.3176@.TK2MSFTNGP12.phx.gbl...
>I believe you need to evict this node from the cluster, reinstall SQL
>Server there and then upgrade it to add it back to the cluster. I have
>forwarded this to microsoft.public.sqlserver.clustering for their response
>to this, as this is really a clustering question.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:F1A440E4-2781-408A-B7CE-356968CDCF13@.microsoft.com...
>
|||Any time you need to rebuild a piece of hardware in a cluster, it should be
evicted. You need to run SQL Server setup to evict it from the SQL Server
piece of the cluster and then you need to evict the node from the cluster.
Once you add the node back into the cluster, you then need to run SQL Server
setup again to add the node back in for SQL Server. That operation will
reinstall all of the software components.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23sOoM$sFGHA.3176@.TK2MSFTNGP12.phx.gbl...
>I believe you need to evict this node from the cluster, reinstall SQL
>Server there and then upgrade it to add it back to the cluster. I have
>forwarded this to microsoft.public.sqlserver.clustering for their response
>to this, as this is really a clustering question.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:F1A440E4-2781-408A-B7CE-356968CDCF13@.microsoft.com...
>