Showing posts with label built. Show all posts
Showing posts with label built. Show all posts

Friday, March 23, 2012

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 Smile

-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 Smile

-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.

fuzzy search

Is there a built in capability in Sql server 2005 to do a search which can handle spelling errors. for eg.

We are doing a search for "hanovr" and our database contains "hanover" . In cases when there is a spelling error searching using LIKE,CONTAINS,FREETEXT are not giving me the results. Is there an out of the box solution for this problem.

Please Advice.

Oracle has a function, Soundex if I remember right, that worked off the auditory equivalent value.

I don't remember if sql server has something like that or not.

|||

Duh. Just looked it up and there is one. Check it out!

|||

there is also a version of soundex in sql server. check it here:http://msdn2.microsoft.com/en-us/library/aa259235(sql.80).aspx

|||

There are actually 2 functions in SQL using which you can check whether 2 values are matching or not. SOUNDEX and DIFFERENCE. These functions actually ignore the vowels ( a,e,i,o,u ).

The SOUNDEX function returns a four-character code to evaluate the similarity of two strings.

The DIFFERENCE function returns the difference ( ranging from 0 to 4, 0 is the highest possible difference and 4 is the least possible difference ) between the SOUNDEX values of two character expressions. Run below queries.

select soundex ('hanovr' ) , soundex ('hanover' ) , difference ('hanovr' ,'hanover' )select soundex ('hanovr' ) , soundex ('hanovr' ) , difference ('hanovr' ,'hanovr' )

I've used them previously with not much luck. You can if possible go with some .net libraries to check spelling mistakes.

Hope this will help.

|||

I was looking for something similar - a search mechanism that deals with misspellings. Came acrosshttp://shuffletext.com/highlight, which is a little misspelling search component in beta.

Figured I'd jump on the thread, as it works pretty good. No problems integrating, but there's a couple of annoying things: it doesn't hand you back any info on how good the match was, and sometimes the first result isn't always the best result.

sql

Wednesday, March 21, 2012

Future with SSIS

Hi,

We have a NT service built via Visual Basic 6 which fires dts processes on demand according some criteria. Well, on the whole and basically only for the following rule:

-dts process is executed when our service recognize a concrete plain file leaved in a folder. It’s generally generated for a mainframe JCL along with a activator file (.nul file, empty). Each application own a folder and each folder have lots of files as the aforementioned ones. Scheduler for that is an ASP application that lives within a intranet. It also is a monitor and logger

In that schema, keeping on mind that eight out of ten are ETL processes, critical ones and on daily-basis, our question is, how to handle on with this stuff using by sql25k and its powerful SSIS? Migrating such dts packages to SSIS packages is a piece of cake (now no before; thanks Jamie, Michael and other guys for your awesome help) because of its tipology. However our main concern is what the hell to use. Centralized dtsx package running all day long as a service, listening and checking for files? .Net windows application either vb or c#? aspx solution? Set of .vbs interlinked among them?

This is a old post/goal. In spite of we have not defined any deadline for that, what the heck as time goes by is more urgent for us. A restriction: we can’t to programme nothing from our sql25k production cluster physically. Any stuff will be done on workstations.

In terms of cost, no problem at all. We are interested in hear thoughts and point of view about which is the most effective way

Thanks in advance,

I don't like packages running all day, listening for events. The architecture was not designed for that, and we have not tested packages as long running listeners.

I would suggest using SQL Server Agent, with alerts, listening for WMI messages about files arriving. See: http://msdn2.microsoft.com/en-us/library/ms191508.aspx

The alerts can execute SSIS packages using the SQL Agent SSIS subsystem.

Donald

|||Thanks a lot for your answer Donald.sql

Monday, March 19, 2012

function within view

I have a view built like this
CREATE VIEW XXX
AS
select * from XXX_Calculate()
the XXX_calculate() function is built like this
CREATE FUNCTION XXX_Calculate ()
RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
....
it contains cursors which insert values to the @.Result table.
The problem is whenever a user calls this view the function is beiing
executed again so the retrieval is slow...
Is there a hint to have it behave like normal view?
Thanx in advance.
Sorry about my poor English...P Platan:
At first i'm trying to to use cursors at all.
Because as you see it works very slow, i would offer you to think how to be
avoid usin cursor.
If your example is as well as your view', I dont see whay do you need view
at all. On many programs that use sql you can use SELECT * fron function().
I would offer you to use store procedure instead of view. because on store
procedure you can set the function result on one temporary table and use it
as you can in the store procedure. Also all other software who work with sql
server can use store procedure as well as view.
"P Platan" <pplat@.exnds.com> wrote in message
news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>I have a view built like this
> CREATE VIEW XXX
> AS
> select * from XXX_Calculate()
> the XXX_calculate() function is built like this
> CREATE FUNCTION XXX_Calculate ()
> RETURNS @.Result TABLE (XXXID bigint NOT NULL, XXX2ID bigint NOT NULL)
> ....
> it contains cursors which insert values to the @.Result table.
> The problem is whenever a user calls this view the function is beiing
> executed again so the retrieval is slow...
> Is there a hint to have it behave like normal view?
> Thanx in advance.
> Sorry about my poor English...
>|||I use view because it resides in another database from the one that the
function calls.
To be more specific
In the old datbase schema we had a basic table with 150 categories as fields
I the new implementation we want to normalize it and have them 'vertical'.
In order not to transfer lots of data to the other db and not load triggers
in the basic table which is accessed very heavily we created the view to the
new db which 'verticals' the categories-fields of the basic table.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uNX6wjeQGHA.5248@.TK2MSFTNGP09.phx.gbl...
>P Platan:
> At first i'm trying to to use cursors at all.
> Because as you see it works very slow, i would offer you to think how to
> be avoid usin cursor.
> If your example is as well as your view', I dont see whay do you need view
> at all. On many programs that use sql you can use SELECT * fron
> function().
> I would offer you to use store procedure instead of view. because on store
> procedure you can set the function result on one temporary table and use
> it as you can in the store procedure. Also all other software who work
> with sql server can use store procedure as well as view.
> "P Platan" <pplat@.exnds.com> wrote in message
> news:ulItixdQGHA.4536@.TK2MSFTNGP10.phx.gbl...
>