Access DataSet in Code Window

I would like to access datasets in a report from code in the code window. I
found how to access parameters from the code window but I do not see a way to
iterate through a dataset.
Thanks
Matt AdamsWhat are you trying to accomplish. Maybe there is a better (more RS way) of
accomplishing it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
>I would like to access datasets in a report from code in the code window.
>I
> found how to access parameters from the code window but I do not see a way
> to
> iterate through a dataset.
> Thanks
> Matt Adams|||Hi Bruce,
Thanks for your prompt reply. Here is what I am trying to accomplish.
I have textboxes on the report that list the parameters that were selected
when the report was ran (because our users just love to print for some
reason). The parameters are multi-value parameters and those values are
pulled from lookup tables into a RS dataset. If the "Select All" is
selected, then I display "All" in that parameter's textbox, otherwise I
display exactly what is selected. I currently have this working in the code
window by defining the full string of current values and comparing this
against a Join statment on the Parameter label. This works fine but if the
list of values changes at the database, I need the report to be able to
handle this in a dynamic fashion.
Thanks for your assistance.
Matt Adams
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish. Maybe there is a better (more RS way) of
> accomplishing it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
> >I would like to access datasets in a report from code in the code window.
> >I
> > found how to access parameters from the code window but I do not see a way
> > to
> > iterate through a dataset.
> >
> > Thanks
> >
> > Matt Adams
>
>|||OK, you don't need to be accessing the data directly. You can do all this
with the parameters object. Just remember before writing code, expressions
are your friend. It is very rare indeed that I write code (I do in a few
cases but not often).
Here is what I do in a textbox. I do one additional thing, if they pick more
than a certain number I just say they picked more than X parameters.
Container is the name of the multi-value parameter in this example:
="Containers= " & iif(Parameters!Container.Count > 8," more than 7
Selected",Join(Parameters!Container.Value, ", "))
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
> Hi Bruce,
> Thanks for your prompt reply. Here is what I am trying to accomplish.
> I have textboxes on the report that list the parameters that were selected
> when the report was ran (because our users just love to print for some
> reason). The parameters are multi-value parameters and those values are
> pulled from lookup tables into a RS dataset. If the "Select All" is
> selected, then I display "All" in that parameter's textbox, otherwise I
> display exactly what is selected. I currently have this working in the
> code
> window by defining the full string of current values and comparing this
> against a Join statment on the Parameter label. This works fine but if
> the
> list of values changes at the database, I need the report to be able to
> handle this in a dynamic fashion.
> Thanks for your assistance.
> Matt Adams
> "Bruce L-C [MVP]" wrote:
>> What are you trying to accomplish. Maybe there is a better (more RS way)
>> of
>> accomplishing it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
>> >I would like to access datasets in a report from code in the code
>> >window.
>> >I
>> > found how to access parameters from the code window but I do not see a
>> > way
>> > to
>> > iterate through a dataset.
>> >
>> > Thanks
>> >
>> > Matt Adams
>>|||That is definately a better solution than what I am doing now with a lot less
coding. It will not be dynamic but it will be less maintenance this way.
Thanks alot.
Just for the sake of having knowledge, do you know of a way to access a RS
Dataset from the code window or by calling a referenced component? Say if I
had a need to iterate through the dataset.
Thanks again.
"Bruce L-C [MVP]" wrote:
> OK, you don't need to be accessing the data directly. You can do all this
> with the parameters object. Just remember before writing code, expressions
> are your friend. It is very rare indeed that I write code (I do in a few
> cases but not often).
> Here is what I do in a textbox. I do one additional thing, if they pick more
> than a certain number I just say they picked more than X parameters.
> Container is the name of the multi-value parameter in this example:
> ="Containers= " & iif(Parameters!Container.Count > 8," more than 7
> Selected",Join(Parameters!Container.Value, ", "))
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
> > Hi Bruce,
> >
> > Thanks for your prompt reply. Here is what I am trying to accomplish.
> >
> > I have textboxes on the report that list the parameters that were selected
> > when the report was ran (because our users just love to print for some
> > reason). The parameters are multi-value parameters and those values are
> > pulled from lookup tables into a RS dataset. If the "Select All" is
> > selected, then I display "All" in that parameter's textbox, otherwise I
> > display exactly what is selected. I currently have this working in the
> > code
> > window by defining the full string of current values and comparing this
> > against a Join statment on the Parameter label. This works fine but if
> > the
> > list of values changes at the database, I need the report to be able to
> > handle this in a dynamic fashion.
> >
> > Thanks for your assistance.
> >
> > Matt Adams
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What are you trying to accomplish. Maybe there is a better (more RS way)
> >> of
> >> accomplishing it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
> >> >I would like to access datasets in a report from code in the code
> >> >window.
> >> >I
> >> > found how to access parameters from the code window but I do not see a
> >> > way
> >> > to
> >> > iterate through a dataset.
> >> >
> >> > Thanks
> >> >
> >> > Matt Adams
> >>
> >>
> >>
>
>|||You can have a custom assembly but it is not trivial. The biggest issue is
dealing with security for custom assemblies.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:8A423D93-D7E2-4DF9-AE23-78C2E67756A6@.microsoft.com...
> That is definately a better solution than what I am doing now with a lot
> less
> coding. It will not be dynamic but it will be less maintenance this way.
> Thanks alot.
> Just for the sake of having knowledge, do you know of a way to access a RS
> Dataset from the code window or by calling a referenced component? Say if
> I
> had a need to iterate through the dataset.
> Thanks again.
> "Bruce L-C [MVP]" wrote:
>> OK, you don't need to be accessing the data directly. You can do all this
>> with the parameters object. Just remember before writing code,
>> expressions
>> are your friend. It is very rare indeed that I write code (I do in a few
>> cases but not often).
>> Here is what I do in a textbox. I do one additional thing, if they pick
>> more
>> than a certain number I just say they picked more than X parameters.
>> Container is the name of the multi-value parameter in this example:
>> ="Containers= " & iif(Parameters!Container.Count > 8," more than 7
>> Selected",Join(Parameters!Container.Value, ", "))
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
>> > Hi Bruce,
>> >
>> > Thanks for your prompt reply. Here is what I am trying to accomplish.
>> >
>> > I have textboxes on the report that list the parameters that were
>> > selected
>> > when the report was ran (because our users just love to print for some
>> > reason). The parameters are multi-value parameters and those values
>> > are
>> > pulled from lookup tables into a RS dataset. If the "Select All" is
>> > selected, then I display "All" in that parameter's textbox, otherwise I
>> > display exactly what is selected. I currently have this working in the
>> > code
>> > window by defining the full string of current values and comparing this
>> > against a Join statment on the Parameter label. This works fine but if
>> > the
>> > list of values changes at the database, I need the report to be able to
>> > handle this in a dynamic fashion.
>> >
>> > Thanks for your assistance.
>> >
>> > Matt Adams
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> What are you trying to accomplish. Maybe there is a better (more RS
>> >> way)
>> >> of
>> >> accomplishing it.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> >> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
>> >> >I would like to access datasets in a report from code in the code
>> >> >window.
>> >> >I
>> >> > found how to access parameters from the code window but I do not see
>> >> > a
>> >> > way
>> >> > to
>> >> > iterate through a dataset.
>> >> >
>> >> > Thanks
>> >> >
>> >> > Matt Adams
>> >>
>> >>
>> >>
>>|||Ok. Makes sense. Thanks.
"Bruce L-C [MVP]" wrote:
> You can have a custom assembly but it is not trivial. The biggest issue is
> dealing with security for custom assemblies.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:8A423D93-D7E2-4DF9-AE23-78C2E67756A6@.microsoft.com...
> > That is definately a better solution than what I am doing now with a lot
> > less
> > coding. It will not be dynamic but it will be less maintenance this way.
> > Thanks alot.
> >
> > Just for the sake of having knowledge, do you know of a way to access a RS
> > Dataset from the code window or by calling a referenced component? Say if
> > I
> > had a need to iterate through the dataset.
> >
> > Thanks again.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> OK, you don't need to be accessing the data directly. You can do all this
> >> with the parameters object. Just remember before writing code,
> >> expressions
> >> are your friend. It is very rare indeed that I write code (I do in a few
> >> cases but not often).
> >>
> >> Here is what I do in a textbox. I do one additional thing, if they pick
> >> more
> >> than a certain number I just say they picked more than X parameters.
> >> Container is the name of the multi-value parameter in this example:
> >> ="Containers= " & iif(Parameters!Container.Count > 8," more than 7
> >> Selected",Join(Parameters!Container.Value, ", "))
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
> >> > Hi Bruce,
> >> >
> >> > Thanks for your prompt reply. Here is what I am trying to accomplish.
> >> >
> >> > I have textboxes on the report that list the parameters that were
> >> > selected
> >> > when the report was ran (because our users just love to print for some
> >> > reason). The parameters are multi-value parameters and those values
> >> > are
> >> > pulled from lookup tables into a RS dataset. If the "Select All" is
> >> > selected, then I display "All" in that parameter's textbox, otherwise I
> >> > display exactly what is selected. I currently have this working in the
> >> > code
> >> > window by defining the full string of current values and comparing this
> >> > against a Join statment on the Parameter label. This works fine but if
> >> > the
> >> > list of values changes at the database, I need the report to be able to
> >> > handle this in a dynamic fashion.
> >> >
> >> > Thanks for your assistance.
> >> >
> >> > Matt Adams
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> What are you trying to accomplish. Maybe there is a better (more RS
> >> >> way)
> >> >> of
> >> >> accomplishing it.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> >> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
> >> >> >I would like to access datasets in a report from code in the code
> >> >> >window.
> >> >> >I
> >> >> > found how to access parameters from the code window but I do not see
> >> >> > a
> >> >> > way
> >> >> > to
> >> >> > iterate through a dataset.
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > Matt Adams
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>sql

Access database with php access

Hello all,

forgive me if this isn't the right forum for this isue

I'm using the following php code:

$db = realpath("database/userdatabase.mdb") or die('<b>Connectie met database mislukt</b>');

$conn = new COM("ADODB.Connection");
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db");

$conn->Execute("INSERT INTO TblNews(Title, Nieuws, Date, Author) VALUES ('Testing', 'tekst', '16Oktober2006', 'Ikke')");

$conn->Execute("INSERT INTO TblNews VALUES (20,'Testing', 'tekst', '16 Oktober 2006', 'Ikke')");

The 2nd insert command works perfectly but the first gives an error all the time!
The table has a NewsID with auto_increment on..

The error:
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.' in d:\www\tiedtzat.nl\www\nieuw\testdb.php:19 Stack trace: #0 d:\www\tiedtzat.nl\www\nieuw\testdb.php(19): com->Execute('INSERT INTO Tbl...') #1 {main} thrown in site.com\testdb.php on line 19

Thanks for the help in advance! :)

Hi,

if the driver does not bring back any detailed information you should try to execute the query within access to see a more detailed error information. As from your queries ir could be taht your second queries fills a not nullable field which is not filled within the first query. Which columns are you filling in the second query that you don′t dill in the first one ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hey,

I found the problem!
I was using Date as one of the fields in the table which access doesn't seem to accept :)

Thanks for your help!

|||

Hi TrojanH: (sorry for my English I only speak Spanish) I'm traying too use PHP with Ms Accesss database and ADODB.Connection but have not found a manual about ADO and PHP. and I'not able mannage the recordset , I need know how do a seek, like mysql_data_seek or know the record number etc,

if it's possible can you tellme where to find info?

Thanks.

|||

Hello Gabriel,

As you might have noticed, information about adodb.connection is very hard to find on the Internet so I don't have much information...

What I use in php is this:

$db = realpath("database/db.mdb") or die('<b>Connection failed</b>');

$conn = new COM("ADODB.Connection");
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db");

$rs = $conn->Execute("Select blabla... yadi yadi");

while(!$rs->EOF){

$blabla = $rs->Fields("blabla")->value;

$rs->MoveNext(); // Dont forget this!!!

}

good luck!|||

TrojanH:

that is the only way I know to view-manage the base. but I need more than this,

Thank.

|||

Gabriel wrote:

TrojanH:

that is the only way I know to view-manage the base. but I need more than this,

Thank.

The Execute performs all actions including update, delete etc...

I don't know what you need to know exactly but that probably goes beyond my knowledge about access databases

|||

Hi again:

Actualy I know how to do SELECT, INSERT, UPDATE & DELETE.

But I need functions link : "mysql_data_seek()", "mysql_num_rows()", or " Select * from tabla LIMIT $ini, $reg "

This kind of funtions that allow to manage data.

|||

Gabriel wrote:

"mysql_num_rows()"

You could try the standard count(*) function in sql

Gabriel wrote:

" Select * from tabla LIMIT $ini, $reg "

As far as I know, there is no 'LIMIT' in sql (only mysql) and sql will only accept stuff like "Select TOP 10 * FROM..."

Sorry, cant help you any further with this... I hope this works for you

Access data sources from Rendering Extension code

I am writing a rendering extension for reporting services (the one that
implements IRenderingExtension).
I need to be able to enumerate and access [populated] datasets that were
used in the report. I don't see any way of getting a reference to DataSets
from Report or createAndRegisterStream objects, or other any other way. Can
you recommend something, please?
On the same token, if more than one dataset was defined in the report
designer, but only one was actually referenced from report items (i.e. fields
referenced all belong to one ds), does RS optimize away the other datasets,
or does it still execute queries and populate them?
Thank you!
Denis (DenisL at softartisans dot com)Hi Denis,
you cannot access populated datasets in the RenderingObjectModel. Just
consider the cases where a report is rendered from an execution or a history
snapshot. In both cases no data set queries are executed, but just the
information stored in the snapshot is exposed in the RenderingObjectModel.
Snapshots typically *only* contain the processed report items (in an output
format independent format) but no data.
As processing a report has multiple phases, datasets only get populated if
dataset queries get executed. In any case, populated datasets will never be
accessible through the RenderingObjectModel.
Regarding optimization of dataset executions: if a dataset is not referenced
by any reportitem in the entire report, then the commandtext is not
executed. If however it is referenced by a reportitem that can e.g. be
shown/hidden dynamically based on a condition, the query has to be executed
at processing time.
Right now, only a custom assembly could indirectly access populated datasets
(by passing in field values as parameters) during report processing.
In the next version there will be a way ("CustomReportItem") to "plug in"
your own custom processing controls into the report server which will have
access to the data at processing time. However, the custom processing
control will not be able to generate any arbitrary output format independent
rendering object (at least for the next version) - but it could generate a
custom image object with custom properties during processing which are then
exposed during rendering to your custom rendering extension.
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Denis Levin" <DenisLevin@.discussions.microsoft.com> wrote in message
news:5C708846-5796-4D11-B377-76A0A7B22513@.microsoft.com...
> I am writing a rendering extension for reporting services (the one that
> implements IRenderingExtension).
> I need to be able to enumerate and access [populated] datasets that were
> used in the report. I don't see any way of getting a reference to DataSets
> from Report or createAndRegisterStream objects, or other any other way.
Can
> you recommend something, please?
> On the same token, if more than one dataset was defined in the report
> designer, but only one was actually referenced from report items (i.e.
fields
> referenced all belong to one ds), does RS optimize away the other
datasets,
> or does it still execute queries and populate them?
> Thank you!
> Denis (DenisL at softartisans dot com)|||Ths helps a lot!
Thank you, Robert.
"Robert Bruckner [MSFT]" wrote:
> Hi Denis,
> you cannot access populated datasets in the RenderingObjectModel. Just
> consider the cases where a report is rendered from an execution or a history
> snapshot. In both cases no data set queries are executed, but just the
> information stored in the snapshot is exposed in the RenderingObjectModel.
> Snapshots typically *only* contain the processed report items (in an output
> format independent format) but no data.
> As processing a report has multiple phases, datasets only get populated if
> dataset queries get executed. In any case, populated datasets will never be
> accessible through the RenderingObjectModel.
> Regarding optimization of dataset executions: if a dataset is not referenced
> by any reportitem in the entire report, then the commandtext is not
> executed. If however it is referenced by a reportitem that can e.g. be
> shown/hidden dynamically based on a condition, the query has to be executed
> at processing time.
> Right now, only a custom assembly could indirectly access populated datasets
> (by passing in field values as parameters) during report processing.
> In the next version there will be a way ("CustomReportItem") to "plug in"
> your own custom processing controls into the report server which will have
> access to the data at processing time. However, the custom processing
> control will not be able to generate any arbitrary output format independent
> rendering object (at least for the next version) - but it could generate a
> custom image object with custom properties during processing which are then
> exposed during rendering to your custom rendering extension.
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Denis Levin" <DenisLevin@.discussions.microsoft.com> wrote in message
> news:5C708846-5796-4D11-B377-76A0A7B22513@.microsoft.com...
> > I am writing a rendering extension for reporting services (the one that
> > implements IRenderingExtension).
> > I need to be able to enumerate and access [populated] datasets that were
> > used in the report. I don't see any way of getting a reference to DataSets
> > from Report or createAndRegisterStream objects, or other any other way.
> Can
> > you recommend something, please?
> >
> > On the same token, if more than one dataset was defined in the report
> > designer, but only one was actually referenced from report items (i.e.
> fields
> > referenced all belong to one ds), does RS optimize away the other
> datasets,
> > or does it still execute queries and populate them?
> >
> > Thank you!
> >
> > Denis (DenisL at softartisans dot com)
>
>

Access Combo Box Linked to Stored Proc

Does anyone for code code to link a stored procedures to a combo box in Access?A list of available stored procedures to execute or something else?

Access COM+ component through embedded code

I am trying to access a COM+ component through embedded code of my
report. I added the reference to my COM+ component and a reference to
EnterpriseServices in the report. I also added the following to
rssrvpolicy.config:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="GPATS_BO"
Description="This code group grants permissions to
GPATS_BO.dll full trust. ">
<IMembershipCondition
class="StrongNameMembershipCondition"
version="1"
PublicKeyBlob="<value>"
/>
</CodeGroup>
I also added the following line to my assembly:
<Assembly: System.Security.AllowPartiallyTrustedCallers()>
Here is the function that I am trying to call:
<System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
Unrestricted:=True)> _
Public Function GetInvestmentTypeTest(ByVal partyId As Integer) As
String
Try
Return "IT WORKED"
Catch ex As Exception
Throw ex
End Try
End Function
I get the following error:
System.Security.SecurityException: Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
failed.
at
System.EnterpriseServices.ServicedComponentProxyAttribute.CreateInstance(Type
serverType)
at
System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(Type
serverType, Object[] props, Boolean bNewObj)
at ReportExprHostImpl.CustomCodeProxy.GetInvestmentTypeTest(Int32
partyId)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Security.Permissions.SecurityPermission
The Zone of the assembly that failed was:
MyComputer
I can get the value and it uses the COM+ component when it is run in
the IDE and you preview the report. Once it is deployed and you try to
view it through the RS website, it gives the error above.
I am an admin on the machine and I even unchecked "Enforce access
checks for this application" in COM+ for this component, so permissions
really shouldn't be a problem. I also made the account that reporting
services runs under an admin on the machine and it didn't help (as I
suspected, but I had to try anyway).
I tried the same steps but with a dll not in COM+ (except I also copied
the dll into the folder "C:\Program Files\Microsoft SQL
Server\MSSQL.3\Reporting Services\ReportServer\bin\") and it works fine
both locally and when deployed.
Is it even possible for a report to use a COM+ component? The reason I
ask is that the application and the reports use many common functions.
I really don't want 2 dlls with the same code, so we are trying to make
Reporting Services use the component.Is there anyone that has tried this before? I see a few posts on the
subject, but no resolution. Thanks for any help anyone can give on
this issue.
Devin
Devin wrote:
> I am trying to access a COM+ component through embedded code of my
> report. I added the reference to my COM+ component and a reference to
> EnterpriseServices in the report. I also added the following to
> rssrvpolicy.config:
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="GPATS_BO"
> Description="This code group grants permissions to
> GPATS_BO.dll full trust. ">
> <IMembershipCondition
> class="StrongNameMembershipCondition"
> version="1"
> PublicKeyBlob="<value>"
> />
> </CodeGroup>
>
> I also added the following line to my assembly:
> <Assembly: System.Security.AllowPartiallyTrustedCallers()>
>
> Here is the function that I am trying to call:
> <System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
> Unrestricted:=True)> _
> Public Function GetInvestmentTypeTest(ByVal partyId As Integer) As
> String
> Try
> Return "IT WORKED"
> Catch ex As Exception
> Throw ex
> End Try
> End Function
>
> I get the following error:
> System.Security.SecurityException: Request for the permission of type
> 'System.Security.Permissions.SecurityPermission, mscorlib,
> Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
> failed.
> at
> System.EnterpriseServices.ServicedComponentProxyAttribute.CreateInstance(Type
> serverType)
> at
> System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(Type
> serverType, Object[] props, Boolean bNewObj)
> at ReportExprHostImpl.CustomCodeProxy.GetInvestmentTypeTest(Int32
> partyId)
> The action that failed was:
> Demand
> The type of the first permission that failed was:
> System.Security.Permissions.SecurityPermission
> The Zone of the assembly that failed was:
> MyComputer
>
> I can get the value and it uses the COM+ component when it is run in
> the IDE and you preview the report. Once it is deployed and you try to
> view it through the RS website, it gives the error above.
> I am an admin on the machine and I even unchecked "Enforce access
> checks for this application" in COM+ for this component, so permissions
> really shouldn't be a problem. I also made the account that reporting
> services runs under an admin on the machine and it didn't help (as I
> suspected, but I had to try anyway).
> I tried the same steps but with a dll not in COM+ (except I also copied
> the dll into the folder "C:\Program Files\Microsoft SQL
> Server\MSSQL.3\Reporting Services\ReportServer\bin\") and it works fine
> both locally and when deployed.
> Is it even possible for a report to use a COM+ component? The reason I
> ask is that the application and the reports use many common functions.
> I really don't want 2 dlls with the same code, so we are trying to make
> Reporting Services use the component.

Access chart Palette color set in custom code

Hi, all. I am doing a pie chart, to display the market value for each asset type. I couldn't put the market value and asset type together in the legend, so I create another table behind the pie chart. Since I want to use the table to replace the legend of pie chart, I plan to put a retangle with same color of each asset type in the pie chart. How can I use the Palette color serizes like Excel, Earth Tone, Pastel in the assemble custom code?

I recommend to just define your own color palette and use it in both the chart and the custom table legend. Check this blog article for an example and explanations: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert

|||Thanks, Robert. It helps

Access chart Palette color set in custom code

Hi, all. I am doing a pie chart, to display the market value for each asset type. I couldn't put the market value and asset type together in the legend, so I create another table behind the pie chart. Since I want to use the table to replace the legend of pie chart, I plan to put a retangle with same color of each asset type in the pie chart. How can I use the Palette color serizes like Excel, Earth Tone, Pastel in the assemble custom code?

I recommend to just define your own color palette and use it in both the chart and the custom table legend. Check this blog article for an example and explanations: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert

|||Thanks, Robert. It helps

Access C# code from Integration services

I have an application in C# in which i have few events like download,Import and I have written all the code for that and its working fine but I have to schedule these processes so that I need not to run it manually daily. I am trying to write an Integration services package through which I could handle the required event, without rewriting the whole code.

Please Reply.

Thanks,

Pooja

bajaj.puja@.gmail.com

Hi Pooja B,

You can access a process (an exe file) with Execute Process Task - is this what you mean? If you just want to schedule the process maybe you could use "Scheduled Tasks" in windows.

Another aprouch is to ajust the code to fit into a Custom Task - how to do this is a little bit of work...

Regards
Simon
|||You can also use a script task to reference a .net assembly. Not sure exactly what you are trying to accomplish though, so I'm not sure if this is the right approach.|||

Pooja B wrote:

I have an application in C# in which i have few events like download,Import and I have written all the code for that and its working fine but I have to schedule these processes so that I need not to run it manually daily. I am trying to write an Integration services package through which I could handle the required event, without rewriting the whole code.

Please Reply.

Thanks,

Pooja

bajaj.puja@.gmail.com

Pooja,

See my reply to your previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423350&SiteID=1

-Jamie

Access 2003 with SQL Express 2005

I am a fresher in using SQL with access.

I am using a code in Ms access to link a table that is in Sql express 2005.

I use this linked table name to download records to the local Access table.

The link is then dropped in Access.

Is there any way to write back records into the linked Sql table from Access.

It does not allow me to delete / add / modify records of a linked table.

How can I do this.

Thanks

Sjohn

YOU will need to specify the primary key on the linked server table that exists in SQL Server. This information is automatically created by default.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Absurdal affair in trigger file

Hi!

I found a bug in my trigger and i don't know why it is error.

See small report:

After executing following code's fragment in my trigger:

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL
SET @.orzecz_koszty_sadowe = 0
IF @.orzecz_mks_przyznane IS NULL
SET @.orzecz_mks_przyznane = 0

IF @.orzecz_oplaty_pozostale IS NULL
SET @.orzecz_oplaty_pozostale = 0

IF @.orzecz_zasadzona_zaliczka_na_koresp IS NULL
SET @.orzecz_zasadzona_zaliczka_na_koresp = 0

-- Koniec sprawdzanie czy sa NULL

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

we have on consolle following result:

************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43
************** koszt sadowe ***************** 0
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43

So, variable @.orzecz_koszty_sadowe was set to 0.
Previously it was 145, so why in the if statement it values to NULL?

After small change, code looks like this:

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL
SET @.orzecz_koszty_sadowe = 0
IF @.orzecz_mks_przyznane IS NULL
SET @.orzecz_mks_przyznane = 0

IF @.orzecz_oplaty_pozostale IS NULL
SET @.orzecz_oplaty_pozostale = 0

IF @.orzecz_zasadzona_zaliczka_na_koresp IS NULL
SET @.orzecz_zasadzona_zaliczka_na_koresp = 0

-- Koniec sprawdzanie czy sa NULL

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

and the result is following:

************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43
************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43

So the result is proper. Now I explain the difference between
above listings. Second listing arise from first by following
transformation:

1. Place the cursor after last character of comment preceding
first IF statement (ie. after ....pozycji 'Koszty sadowe')
2. Press Enter key
3. Press Delete key (the line comes back, to initial position)

And that's all :) There's no optical difference between two
listings. However first works bad, and the second works good.

Any idea? Explanation?
I used SQL Server 2000.
Best regards
Walter

Could you please post a script that demonstrates the problem?|||Probably there was a Unix-style newline in your SQL. The query editor displays CHR(13) without CHR(10) as a new line, but the SQL processor considers the text before and after CHR(13) to be part of the same line if there is no CHAR(10).

So the SQL processor interpreted
-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL

to mean the same thing as

-- Potrzebne do wstawienia pozycji 'Koszty sadowe' IF @.orzecz_koszty_sadowe IS NULL

The typing you did changed the CHR(13) to a valid 2-character Windows newline marker.

Steve Kass
Drew University

About TRAN, CURSOR and @@ERROR

Please take a look at my code below:
CREATE PROCEDURE ...
....
@.userId int,
....
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
Henri
Why use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:

> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>
|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...[vbcol=seagreen]
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
...[vbcol=seagreen]
@.confKey[vbcol=seagreen]
an
>
|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>
|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
David Portas
SQL Server MVP
|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:

> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
>
>
|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...[vbcol=seagreen]
when[vbcol=seagreen]
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
that my[vbcol=seagreen]
specified[vbcol=seagreen]
when[vbcol=seagreen]
if[vbcol=seagreen]
updated.[vbcol=seagreen]
no[vbcol=seagreen]
this
>
|||It is certainly better than using a cursor.
"Henri" wrote:

> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> when
> that my
> specified
> when
> if
> updated.
> no
> this
>
>

About TRAN, CURSOR and @@ERROR

Please take a look at my code below:
CREATE PROCEDURE ...
...
@.userId int,
...
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
HenriWhy use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
> > Please take a look at my code below:
> >
> > CREATE PROCEDURE ...
> > ....
> > @.userId int,
> > ....
> >
> > AS
> >
> > BEGIN TRAN
> >
> > DECLARE @.confKey varchar(20), @.confValue varchar(20)
> > DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
...
> > OPEN curs_conf
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> >
> > WHILE @.@.FETCH_STATUS = 0 BEGIN
> >
> > DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key =@.confKey
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> >
> > IF @.confValue <> '' BEGIN
> > INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> > (@.userId, @.confKey, @.confValue)
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> > END
> >
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> > END
> >
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> >
> > COMMIT
> >
> > This code works but is the way it catches errors correct?
> > Do I have to test @.@.ERROR elsewhere?
> > Is it necessary to close and deallocate the cursor before returning when
an
> > error occurs?
> > Is it better to rollback before or after closing and deallocating the
> > cursor?
> >
> > Thanks for answering my questions.
> >
> > Henri
> >
> >
> >
> >
>|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
--
David Portas
SQL Server MVP
--|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > In a transaction you will usually want to test for @.@.ERROR after every
> > statement that manipulates data. As Nigel has said however, there is no
> > obvious reason to use a cursor here at all. I'm not clear just what this
> > code is supposed to do so if you need more help we'll need some more
> > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >
>
>|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> >> I thought it would be clever to delete the keys and insert them again
when
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
> > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > from client
> > and a function converts it in a table variable of key value records,
that my
> > cursor uses.
> >
> > The aim of this procedure is to update an "user configuration" table,
> > removing, updating and inserting configuration keys and values as
specified
> > by the client.
> > I thought it would be clever to delete the keys and insert them again
when
> > needed with the updated values. That prevents from testing for each key,
if
> > it is already present in the base, and if it has to be inserted or
updated.
> >
> > As I said to nigel, the cursor is used so that I don't have to build the
> > key-value table variable twice.
> > Am I missing something?
> >
> > Henri
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > In a transaction you will usually want to test for @.@.ERROR after every
> > > statement that manipulates data. As Nigel has said however, there is
no
> > > obvious reason to use a cursor here at all. I'm not clear just what
this
> > > code is supposed to do so if you need more help we'll need some more
> > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > >
> >
> >
> >
> >
>|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> > >> I thought it would be clever to delete the keys and insert them again
> when
> > needed with the updated values.
> >
> > That's usually not a good idea. Only update data when necessary or you can
> > get into problems with RI, triggers and tr logs.
> >
> > You can use the function to insert into a temp table for the data updates.
> > Then delete any records that are not needed then insert any new ones.
> >
> > "Henri" wrote:
> >
> > > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > > from client
> > > and a function converts it in a table variable of key value records,
> that my
> > > cursor uses.
> > >
> > > The aim of this procedure is to update an "user configuration" table,
> > > removing, updating and inserting configuration keys and values as
> specified
> > > by the client.
> > > I thought it would be clever to delete the keys and insert them again
> when
> > > needed with the updated values. That prevents from testing for each key,
> if
> > > it is already present in the base, and if it has to be inserted or
> updated.
> > >
> > > As I said to nigel, the cursor is used so that I don't have to build the
> > > key-value table variable twice.
> > > Am I missing something?
> > >
> > > Henri
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > > In a transaction you will usually want to test for @.@.ERROR after every
> > > > statement that manipulates data. As Nigel has said however, there is
> no
> > > > obvious reason to use a cursor here at all. I'm not clear just what
> this
> > > > code is supposed to do so if you need more help we'll need some more
> > > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
>|||Performance is always something you should test out for yourself with your
data - it's rarely possible to generalize. For strings of trivial size the
efficiency of client code vs TSQL is probably negligible but your comments
on the performance of your function suggested this wasn't a negligible
problem. My point was that you should start from the assumptions of correct
design - performance optimization comes later.
--
David Portas
SQL Server MVP
--

About stored proc compilation...

Does SQL Server compile stored procs to bytecode or to native x86 code? Is
there documentation on this somewhere?
Thanks,
JayNeither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay|||I sure appreciate the quick response. This is very helpful. A search in Book
s
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:

> Neither. It compiles them to an internal query plan that is executed by t
he
> SQL engine. I do not know of any public documentation on the internal pla
n
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>

About stored proc compilation...

Does SQL Server compile stored procs to bytecode or to native x86 code? Is
there documentation on this somewhere?
Thanks,
Jay
Neither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay
|||I sure appreciate the quick response. This is very helpful. A search in Books
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:

> Neither. It compiles them to an internal query plan that is executed by the
> SQL engine. I do not know of any public documentation on the internal plan
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>

about store procedure

i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you

about SmallDateTime sthg. interesting

Hello,

In the code

SqlParameter dtDate =new SqlParameter("@.date",SqlDbType.SmallDateTime,4);dtDate.Value=Convert.ToDateTime(Session["date"].ToString());cmd.Parameters.Add(dtDate);

where the @.date parameter is defined as a SMALLDATETIME inside a stored procedure,
I cannot use a function such as "Convert.ToSmallDateTime" and it seems
a conversion from DateTime to SmallDateTime takes place anywhere within the code.

Eventually it works but I wonder if everything's ok with this code.
Is there anything wrong about it?

Thanks

Hi, there are some differences between DATETIME and SMALLDATETIME type in SQL: date range and time precision. However normally converting from 'valid' DATETIME(falls in the data range of SMALLDATETIME) data to SMALLDATETIME should not cause problem. Here are some userful links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_27c5.asp?frame=true

|||There is major difference between DateTime and SmallDateTime, the reason is resolution, SmallDateTime will not give you seconds and sometimes you can use it to remove duplicates because SQL Server will just trim the data. One word of caution I would not use it for standard date column. Hope this helps.

about restore

Now, I take over a application, one of the functionality is to restore database, but I find the original code, the original code puzzles me. To all user databases, for example, the restoring procedure of pubs database, is as follows:

1.

RESTORE DATABASE [pubs] FROM DISK = N'C:\backup\model.bak'

WITH FILE = 1, RECOVERY, REPLACE, KEEP_REPLICATION,

MOVE N'modeldev' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_Playback.mdf',

MOVE N'modellog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_Playback.ldf'

2.

RESTORE DATABASE [pubs] FROM DISK = N'C:\backup\pubs.bak'

WITH STATS=10, FILE = 1, RECOVERY, REPLACE, KEEP_REPLICATION,

MOVE N'pubs' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf',

MOVE N'pubs_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf'

I dont understand what role the first SQL statement plays.

Or the original code has problem?If you are running these two statements right after one another with nothing in between, then I would say the first statement is useless. I'm not sure why you would recover a backup of the model database into the pubs database and then restore a backup of the pubs database into pubs. I notice it has the KEEP_REPLICATION setting on, but I don't see how that would alter things. Anyone else?

About receiving an image from SQL server 2000 and displaying it in aspx page..............

hi friends,

i need a code for storing and receiving an image to/fro SQL SERVER 2000 (in C#). i had searched some sites, all are in VB for windows forms not for website. finally i got a code from some site. it is working for storing purpose. not working for receiving purpose. the code for receiving and displaying purpose is

(in a fresh page)

privatevoid Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
 MemoryStream stream =new MemoryStream ();
 SqlConnection connection =new
 SqlConnection (@."server=INDIA\INDIA;database=iSense;uid=sa;pwd=india");
try
 {
 connection.Open ();
 SqlCommand command =new
 SqlCommand ("select Picture from Image", connection);
byte[] image = (byte[]) command.ExecuteScalar ();
 stream.Write (image,0, image.Length);
 Bitmap bitmap =new Bitmap (stream);
 Response.ContentType ="image/gif";
 bitmap.Save (Response.OutputStream, ImageFormat.Gif);
 }
finally
 {
 connection.Close ();
 stream.Close ();
 }
}

what s the problem is.......i'm getting an exception at Bitmap instantiation.

(i.e Bitmap bitmap =new Bitmap (stream);)

exception is "Parameter is not valid"

what is the problem with that coding? was it correct? do u have any code for this in C#? if so, pls provide that.....help me......pls..........

Have a look at

http://www.codeproject.com/aspnet/NetPix.asp This article explains an ASP.NET application to view and share photos online.
|||

i got the answer from

http://www.ftponline.com/vsm/2002_07/online/hottips/esposito/

in that program setoffset =0;(they have coded as 78,it will not work.) this is very very important.......now i'm so happy........after a month i got the answer.....

About Members and Ordinal function

When I use MDX sample application,
I write a code like this:

SELECT

{[Time].[All Time].[2005]} ON COLUMNS,

{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS

FROM francis.

Here Equipment is a huge dimension table,actually I want to do it like

Filter([Equipment].[All types].[mechanism].Members to narrow the range as prevent duplicate.
But it failed.

One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.

Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.

I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?

Chris|||Hi, Chris, give you more details as example. I assume[Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.

|||No, your last example won't do what you want, I think - it will return all the members on the same level as[Equipment].[All types].[mechanism].[machine001].[abc]. Does[Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.

You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911

HTH,

Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.

About Members and Ordinal function

When I use MDX sample application,
I write a code like this:

SELECT

{[Time].[All Time].[2005]} ON COLUMNS,

{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS

FROM francis.

Here Equipment is a huge dimension table,actually I want to do it like

Filter([Equipment].[All types].[mechanism].Members to narrow the range as prevent duplicate.
But it failed.

One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.

Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.

I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?

Chris|||Hi, Chris, give you more details as example. I assume [Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.

|||No, your last example won't do what you want, I think - it will return all the members on the same level as [Equipment].[All types].[mechanism].[machine001].[abc]. Does [Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.

You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911

HTH,

Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.