Bug? No WSDL information fo a WEBMETHOD that points to a table-value function

Hello all,

I'm trying to create a web service that merely exposes a table value function. I have a dummy function that looks like this (in a DB called ReportingDB):

USE [ReportingDB];
DROP function [dbo].[TestFN];
GO
CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

My web service definition looks like this:

DROP ENDPOINT sql;
go

CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

Everything succeeds, however, the problem is that the TestFN function is not visible in the generated WSDL (http://mypc:90/sql?wsdl). If I do the same with a stored procedure instead of a function, then it works as expected, so it seems that I'm missing something or that this is a bug of SQL Server 2005.

Could somebody please shed some light on this issue?

Regards,

Ianier

Hi imj,

I think this comes from the type of function that you used. Your TestFN is a Table-valued function. I tried it with a Scalar-valued function, and it worked, the method is noted in the wsdl.

Here's my sample code:

DROP function [dbo].[TestFN];

GO

CREATE FUNCTION TestFN (@.DATE datetime)

RETURNS int

AS

BEGIN

DECLARE @.retVal int

SET @.retVal=1

RETURN(@.retVal)

END

GO

DROP ENDPOINT sql;

go

CREATE ENDPOINT sql

STATE = STARTED

AS HTTP(

PATH = '/sql/test',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 90,

SITE = 'mypc.domain.com'

)

FOR SOAP (

WEBMETHOD

'http://tempuri.org/'.'TestFN'

(NAME = 'ReportingDB.dbo.TestFN'),

BATCHES = ENABLED,

WSDL = DEFAULT

)

go

/*GRANT CONNECT ON ENDPOINT::sql TO [xxx\yyy]*/

Best regards,
Marcus

|||Thanks Marcus,

Then it may be a SQL Server bug, since the CREATE ENDPOINT command succeeds. Should I get no answer here, I will open a bug report about this.
The reason why I would like to use table-value functions instead of stored procedures is that, since the metadata for table-value functions results is known in advance, I was expecting to see a typed dataset (or array) in the WSDL. That would let me use my function from, say, InfoPath.
Regards,

Ianier|||Hi again,

FYI, I sent a bug report: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=9e1e9b79-079d-40eb-b3f8-441432a26cbc
Nevertheless, it's hard for me to believe that such a serious issue escaped Microsoft's testing, so I'm still hoping that I'm missing something obvious.
Regards,

Ianier|||SQL Server's SOAP does not support table valued functions unfortunately. This is why it's not working. I found this out by talking to one of the testers. I cannot find where this is mentioned in the docs yet.|||Hello,

Thanks for the info.
That would be a major disapointment. This limitation is not documented and CREATE ENDPOINT reports no error, so this must be either a bug or an "undocumented odd behavior", which is the same.
IMO, not allowing table-value functions in a SOAP endpoint makes the WS feature extremely limited, if useful at all.
I'll come back to this group as soon as I get some feedback on my bug report.
Regards,

Ianier|||

The SQL 2005 Books Online documentation does have a statement stating that Table Value Functions are not supported in Native XML Web Services. Under the "Guidelines and Limitations in Native XML Web Services" topic (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8907005f-7f9b-4f53-80b8-f42729caf86b.htm), there's a bullet point saying:

Table valued user-defined functions are not supported.|||Thank you Jimmy,

In that case CREATE ENDPOINT should not succeed. This is definitely a bug.
Regards,

Ianier|||

Hi Ianier,

Thanks for the feedback.
SQL 2005 actually allows the "CREATE ENDPOINT" query to be executed before any of the webmethods are created.
ie.
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

In this scenario is would not be possible for the CREATE ENDPOINT query to verify what the webmethod is. You can argue that this should be a bug as well, but the current behavior provides a more flexible system. Suppose we were strict about requiring the webmethods to be created before the CREATE ENDPOINT query, what would happen if after the stored procedure or function was created, someone went and ALTER it or even DELETE it? Should the different endpoints to magically re-verify that the new version of the webmethod is still valid or even there?

The current mechanism allows SQL to verify at execution/run time that the methods are valid and the user has the proper permissions.

Hope that addresses some of your concerns.
Jimmy Wu [MSFT]

|||

Hi Jimmy,

Thanks for the information.

Common sense tells me that if the function exists and is not of a supported type (i.e. a table-value one) then CREATE ENDPOINT should fail. After all, CREATE ENDPOINT fails, for example, when trying to bind a WEBMETHOD to a view because views are not supported.

If someone ALTERs or DELETEs the function afterwards in a way that is not compatible with the endpoint, either of the following should happen:
Option 1: give an error explaining that the WEBMETHOD should be modified first (i.e. there's a dependency).
Option 2: delete the WEBMETHOD automatically and give a warning to the user.

Regards,

Ianier

sql

Bug? No WSDL information fo a WEBMETHOD that points to a table-value function

Hello all,

I'm trying to create a web service that merely exposes a table value function. I have a dummy function that looks like this (in a DB called ReportingDB):

USE [ReportingDB];
DROP function [dbo].[TestFN];
GO
CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

My web service definition looks like this:

DROP ENDPOINT sql;
go

CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

Everything succeeds, however, the problem is that the TestFN function is not visible in the generated WSDL (http://mypc:90/sql?wsdl). If I do the same with a stored procedure instead of a function, then it works as expected, so it seems that I'm missing something or that this is a bug of SQL Server 2005.

Could somebody please shed some light on this issue?

Regards,

Ianier

Hi imj,

I think this comes from the type of function that you used. Your TestFN is a Table-valued function. I tried it with a Scalar-valued function, and it worked, the method is noted in the wsdl.

Here's my sample code:

DROP function [dbo].[TestFN];

GO

CREATE FUNCTION TestFN (@.DATE datetime)

RETURNS int

AS

BEGIN

DECLARE @.retVal int

SET @.retVal=1

RETURN(@.retVal)

END

GO

DROP ENDPOINT sql;

go

CREATE ENDPOINT sql

STATE = STARTED

AS HTTP(

PATH = '/sql/test',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 90,

SITE = 'mypc.domain.com'

)

FOR SOAP (

WEBMETHOD

'http://tempuri.org/'.'TestFN'

(NAME = 'ReportingDB.dbo.TestFN'),

BATCHES = ENABLED,

WSDL = DEFAULT

)

go

/*GRANT CONNECT ON ENDPOINT::sql TO [xxx\yyy]*/

Best regards,
Marcus

|||Thanks Marcus,

Then it may be a SQL Server bug, since the CREATE ENDPOINT command succeeds. Should I get no answer here, I will open a bug report about this.
The reason why I would like to use table-value functions instead of stored procedures is that, since the metadata for table-value functions results is known in advance, I was expecting to see a typed dataset (or array) in the WSDL. That would let me use my function from, say, InfoPath.
Regards,

Ianier
|||Hi again,

FYI, I sent a bug report: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=9e1e9b79-079d-40eb-b3f8-441432a26cbc
Nevertheless, it's hard for me to believe that such a serious issue escaped Microsoft's testing, so I'm still hoping that I'm missing something obvious.
Regards,

Ianier
|||SQL Server's SOAP does not support table valued functions unfortunately. This is why it's not working. I found this out by talking to one of the testers. I cannot find where this is mentioned in the docs yet.|||Hello,

Thanks for the info.
That would be a major disapointment. This limitation is not documented and CREATE ENDPOINT reports no error, so this must be either a bug or an "undocumented odd behavior", which is the same.
IMO, not allowing table-value functions in a SOAP endpoint makes the WS feature extremely limited, if useful at all.
I'll come back to this group as soon as I get some feedback on my bug report.
Regards,

Ianier
|||

The SQL 2005 Books Online documentation does have a statement stating that Table Value Functions are not supported in Native XML Web Services. Under the "Guidelines and Limitations in Native XML Web Services" topic (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8907005f-7f9b-4f53-80b8-f42729caf86b.htm), there's a bullet point saying:

Table valued user-defined functions are not supported.|||Thank you Jimmy,

In that case CREATE ENDPOINT should not succeed. This is definitely a bug.
Regards,

Ianier
|||

Hi Ianier,

Thanks for the feedback.
SQL 2005 actually allows the "CREATE ENDPOINT" query to be executed before any of the webmethods are created.
ie.
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go

CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO

In this scenario is would not be possible for the CREATE ENDPOINT query to verify what the webmethod is. You can argue that this should be a bug as well, but the current behavior provides a more flexible system. Suppose we were strict about requiring the webmethods to be created before the CREATE ENDPOINT query, what would happen if after the stored procedure or function was created, someone went and ALTER it or even DELETE it? Should the different endpoints to magically re-verify that the new version of the webmethod is still valid or even there?

The current mechanism allows SQL to verify at execution/run time that the methods are valid and the user has the proper permissions.

Hope that addresses some of your concerns.
Jimmy Wu [MSFT]

|||

Hi Jimmy,

Thanks for the information.

Common sense tells me that if the function exists and is not of a supported type (i.e. a table-value one) then CREATE ENDPOINT should fail. After all, CREATE ENDPOINT fails, for example, when trying to bind a WEBMETHOD to a view because views are not supported.

If someone ALTERs or DELETEs the function afterwards in a way that is not compatible with the endpoint, either of the following should happen:
Option 1: give an error explaining that the WEBMETHOD should be modified first (i.e. there's a dependency).
Option 2: delete the WEBMETHOD automatically and give a warning to the user.

Regards,

Ianier

BUG: 3rd Request: Series style function BUG in SP1

I'll try posting this again, since it garnered no reply either of the
times that I posted it. Is my post being ignored for some reason?
Greetings.
I found an odd issue when working with a series style in a line chart
in SP1. I was using my own function to determine the color of the
series. So I entered =Code.PickColor(Fields!ResultSort.Value) for the
formula. My code is this:
Public Function PickColor(iResultSort As Integer) As String
Dim sColor As String
Select Case iResultSort
Case 0: sColor = "#000000" ' Black, Full
Case 3: sColor = "#800000" ' Brown, Avg
End Select
PickColor = sColor
End Function
The ResultSort field is defined in SQL as an integer. What happened
on the chart is that the data line was drawn in the color expected,
but from the end of the line to the x axis, a black line was drawn.
If I change the code to use a String instead:
Public Function PickColor(sResultSort As String) As String
Dim sColor As String
Select Case sResultSort
Case "0": sColor = "#000000" ' Black, Full
Case "3": sColor = "#800000" ' Brown, Avg
End Select
PickColor = sColor
End Function
then it draws as expected.
I would sort of like to know if there is something that I am missing
here that causes it to behave like this or if there is an issue with
the functionality.
I have the rdl and sample data as xml attached here, and can email
screenshots or whatever if desired.
Thanks.
Dale.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!DPC.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderColor>
<Default>=Code.PickColor( Fields!ResultSort.Value
)</Default>
</BorderColor>
</Style>
<Marker>
<Type>Auto</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Line</Type>
<Title />
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!DaysAfter.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!DaysAfter.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ResultSort.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ResultSort.Value</Label>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5.125in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="List_History ReadOnly">
<rd:DataSourceID>f2bd5a97-e7bc-444d-a3f9-b6f33e8fb5a7</rd:DataSourceID>
<DataSourceReference>List_History ReadOnly</DataSourceReference>
</DataSource>
</DataSources>
<Code>Public Function PickColor(iResultSort As Integer) As String
Dim sColor As String
Select Case iResultSort
Case 0: sColor = "#000000" ' Black, Full
Case 3: sColor = "#800000" ' Brown, Avg
End Select
PickColor = sColor
End Function
Public Function PickLineWidth(sResultSort As String) As String
Dim sWidth As String
Select Case sResultSort
Case 0: sWidth = "1.5pt" ' Thicker
Case Else: sWidth = "1pt" ' Normal
End Select
End Function
</Code>
<Width>6.625in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="DaysAfter">
<DataField>DaysAfter</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ResultSort">
<DataField>ResultSort</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="DPC">
<DataField>DPC</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>List_History ReadOnly</DataSourceName>
<CommandText>SELECT * FROM dbo.Dale</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>1f236bff-9e92-4d02-a9b0-b41e2d7f6950</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
<SampleData>
<DataRow DaysAfter="34" ResultSort="0" DPC="4.07"/><DataRow
DaysAfter="65" ResultSort="0" DPC="4.07"/><DataRow DaysAfter="95"
ResultSort="0" DPC="4.07"/><DataRow DaysAfter="126" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="156" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="187" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="218" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="248" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="279" ResultSort="0"
DPC="4.07"/><DataRow ResultSort="0" DPC="4.07"/><DataRow
DaysAfter="34" ResultSort="1" DPC="3.06"/><DataRow DaysAfter="65"
ResultSort="1" DPC="3.06"/><DataRow DaysAfter="95" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="126" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="156" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="187" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="218" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="248" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="279" ResultSort="1"
DPC="3.06"/><DataRow ResultSort="1" DPC="3.06"/><DataRow
DaysAfter="34" ResultSort="3" DPC="8.59"/><DataRow DaysAfter="65"
ResultSort="3" DPC="11.32"/><DataRow DaysAfter="95" ResultSort="3"
DPC="14.78"/><DataRow DaysAfter="126" ResultSort="3"
DPC="15.42"/><DataRow DaysAfter="156" ResultSort="3"
DPC="16.04"/><DataRow DaysAfter="187" ResultSort="3"
DPC="16.33"/></SampleData>I think there might be several issues here:
* you use DaysAfter as category grouping. Your data has NULLs for DaysAfter
in some of the rows - is this intended?
* the category grouping does not use a sort expression (to sort DaysAfter
ascending) - add a sort expression identical on the category group
expression: =Fields!DaysAfter.Value
* A series grouping is present in the chart - therefore instead of using
=Code.PickColor( Fields!ResultSort.Value), you should use this expression:
=Code.PickColor(First(Fields!ResultSort.Value, "chart1_SeriesGroup1")
The important part is the aggregate scope which has to be identical to the
chart series grouping name. Just using the First aggregate without the scope
will give you incorrect results, because the aggregate will be just scoped
for every chart datapoint (and therefore null if you don't have any
datapoints for a particular series group / category group combination).
Finally, I'm not sure but you might run into an issue in the chart control
we have identified recently and for which we have provided a fix. The fix is
related to NULL datapoints in line charts. Details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;883675. Please feel
free to contact PSS and refer them to KB 883675.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shrumishi" <shrumishi@.yahoo.com> wrote in message
news:4380bfd3.0409170628.7daebbd3@.posting.google.com...
> I'll try posting this again, since it garnered no reply either of the
> times that I posted it. Is my post being ignored for some reason?
> Greetings.
> I found an odd issue when working with a series style in a line chart
> in SP1. I was using my own function to determine the color of the
> series. So I entered =Code.PickColor(Fields!ResultSort.Value) for the
> formula. My code is this:
> Public Function PickColor(iResultSort As Integer) As String
> Dim sColor As String
> Select Case iResultSort
> Case 0: sColor = "#000000" ' Black, Full
> Case 3: sColor = "#800000" ' Brown, Avg
> End Select
> PickColor = sColor
> End Function
> The ResultSort field is defined in SQL as an integer. What happened
> on the chart is that the data line was drawn in the color expected,
> but from the end of the line to the x axis, a black line was drawn.
> If I change the code to use a String instead:
> Public Function PickColor(sResultSort As String) As String
> Dim sColor As String
> Select Case sResultSort
> Case "0": sColor = "#000000" ' Black, Full
> Case "3": sColor = "#800000" ' Brown, Avg
> End Select
> PickColor = sColor
> End Function
> then it draws as expected.
> I would sort of like to know if there is something that I am missing
> here that causes it to behave like this or if there is an issue with
> the functionality.
> I have the rdl and sample data as xml attached here, and can email
> screenshots or whatever if desired.
> Thanks.
> Dale.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Fields!DPC.Value</Value>
> </DataValue>
> </DataValues>
> <DataLabel />
> <Style>
> <BorderColor>
> <Default>=Code.PickColor( Fields!ResultSort.Value
> )</Default>
> </BorderColor>
> </Style>
> <Marker>
> <Type>Auto</Type>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>DataSet1</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Line</Type>
> <Title />
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="chart1_CategoryGroup1">
> <GroupExpressions>
>
<GroupExpression>=Fields!DaysAfter.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!DaysAfter.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <SeriesGroupings>
> <SeriesGrouping>
> <DynamicSeries>
> <Grouping Name="chart1_SeriesGroup1">
> <GroupExpressions>
>
<GroupExpression>=Fields!ResultSort.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ResultSort.Value</Label>
> </DynamicSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <ValueAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Min>0</Min>
> <Margin>true</Margin>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>5.125in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="List_History ReadOnly">
>
<rd:DataSourceID>f2bd5a97-e7bc-444d-a3f9-b6f33e8fb5a7</rd:DataSourceID>
> <DataSourceReference>List_History ReadOnly</DataSourceReference>
> </DataSource>
> </DataSources>
> <Code>Public Function PickColor(iResultSort As Integer) As String
> Dim sColor As String
> Select Case iResultSort
> Case 0: sColor = "#000000" ' Black, Full
> Case 3: sColor = "#800000" ' Brown, Avg
> End Select
> PickColor = sColor
> End Function
> Public Function PickLineWidth(sResultSort As String) As String
> Dim sWidth As String
> Select Case sResultSort
> Case 0: sWidth = "1.5pt" ' Thicker
> Case Else: sWidth = "1pt" ' Normal
> End Select
> End Function
> </Code>
> <Width>6.625in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="DaysAfter">
> <DataField>DaysAfter</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="ResultSort">
> <DataField>ResultSort</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="DPC">
> <DataField>DPC</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>List_History ReadOnly</DataSourceName>
> <CommandText>SELECT * FROM dbo.Dale</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <Description />
> <rd:ReportID>1f236bff-9e92-4d02-a9b0-b41e2d7f6950</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
> <SampleData>
> <DataRow DaysAfter="34" ResultSort="0" DPC="4.07"/><DataRow
> DaysAfter="65" ResultSort="0" DPC="4.07"/><DataRow DaysAfter="95"
> ResultSort="0" DPC="4.07"/><DataRow DaysAfter="126" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="156" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="187" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="218" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="248" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="279" ResultSort="0"
> DPC="4.07"/><DataRow ResultSort="0" DPC="4.07"/><DataRow
> DaysAfter="34" ResultSort="1" DPC="3.06"/><DataRow DaysAfter="65"
> ResultSort="1" DPC="3.06"/><DataRow DaysAfter="95" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="126" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="156" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="187" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="218" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="248" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="279" ResultSort="1"
> DPC="3.06"/><DataRow ResultSort="1" DPC="3.06"/><DataRow
> DaysAfter="34" ResultSort="3" DPC="8.59"/><DataRow DaysAfter="65"
> ResultSort="3" DPC="11.32"/><DataRow DaysAfter="95" ResultSort="3"
> DPC="14.78"/><DataRow DaysAfter="126" ResultSort="3"
> DPC="15.42"/><DataRow DaysAfter="156" ResultSort="3"
> DPC="16.04"/><DataRow DaysAfter="187" ResultSort="3"
> DPC="16.33"/></SampleData>sql

Bug with SET options for table-valued functions.

Hi,

I think I've come across a bug in SQL Server 2000 (SP3):

If I create a table-valued function with the QUOTED_IDENTIFIER and ANSI_NULLS options both set to ON e.g.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.tfn_Test()
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
Then when Scripting this function out again (or using external tools to analyse it - SQL Compare for example), it is scripted as

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.tfn_Test()
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO

It appears to run OK with the original settings, but obviously this script is incorrect.

And if I run this script against my database :
SELECT Name,
Type,
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') IsQuotedIdentOn,
OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') IsAnsiNullsOn
FROM sysobjects
WHERE type IN ('FN','IF','TF')

Then I get NULLs back for the ObjectProperty statements when the type is TF. The other function types return 1 or 0 as expected.

This would appear to be a bug in the way that SQL Server reports the properties out via DMO and the system functions for Table-Valued functions. It is breaking our syncronisation process (using SQL compare).

How do I submit this as a bug to Microsoft? And is it likely to be fixed in an update or Service Pack soon?

Thanks

Alex Weatherall

Further to this :

I can't actually modify table valued functions on SQL Server 2000 in the new SQL 2005 Management Studio due to this error:

TITLE: Microsoft SQL Server Management Studio

Property QuotedIdentifierStatus is not available for UserDefinedFunction '[dbo].[fn_nums]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&
EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=QuotedIdentifierStatus&LinkId=20476

This is due to the same problem. This bug needs fixing asap, it means that I can't use the new management tools to manage SQL Server 2000, I need to use Query Analyzer to access the code for all table valued functions.

Please can someone get back to me asap.

Thanks

Alex Weatherall
TeleWare.com
|||

Solution:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=0750596e-9915-42ea-9295-62d1fb31d0a4

|||

You're right - the properties don't apply to table-valued functions in SQL Server 2000. In SQL Server 2000, the behavior is as if QUOTED_IDENTIFIER and ANSI_NULLS were always on.

The defect where Management Studio tries to get these properties for tabled-value functions in SQL Server 2000 servers has been fixed for SP2.

Thanks,
Steve

Bug with InScope() and some custom code

Hi guys,

i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).

I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:

=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)

Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:

	Month / Year		Sales	Capacity	% Capacity	Avg $/h
	February	2006	3842	7706	49.86%	2.86
		2007	0	0	0.00%	0
	March	2006	4949	8692	56.94%	3.33
		2007	0	0	0.00%	0
	April	2006	5160	8154	63.28%	3.58
		2007	0	0	0.00%	0
	May	2006	3309	8348	39.64%	2.22
		2007	0	0	0.00%	0
	Total		17259	32900	52.46%	0-8*28,28,31,31,30,30,31,31,28

If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end, with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).

Here is the custom code used for all this:

Dim numDays()

Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
Dim thisMonth As String

Dim upper As Integer
upper = 0
On Error Resume Next
upper = UBound(numDays) + 1
ReDim Preserve numDays(upper)

thisMonth = CStr(year) & "-" & month & "-01"
numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
AddDays = numDays(upper)
End Function

Public Function TotalDays() As Integer
Dim lower As Integer
Dim upper As Integer

lower = 0
upper = 0
On Error Resume Next
lower = LBound(numDays)
upper = UBound(numDays)

TotalDays = 0
Dim ii As Integer
For ii = lower To upper
TotalDays = TotalDays + CInt(numDays(ii))
Next
End Function

public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function

sluggy

This has nothing to do with InScope().

The AddDays function is used within a IIF function call. IIF (like any other VB function call) evaluates all arguments before the function is invoked. Hence, the AddDays function is invoked in all cases.

Suggestion:
1. change the expression in the matrix cell to:
=Code.MyCalculation(InScope("matrix2_Calendar_Year"))

2. add a custom code function MyCalculation which uses IF - ELSE blocks to call the other custom code functions. Only in the case of using the conditional IF statement (instead of the IIF function) you will achieve the desired effect.

-- Robert

|||

Doh, thanks Robert, i should have known that I last did VB a few years ago, i've obviously forgotten a bit :)

sluggy

|||

No problem. I'm glad I could help resolving your issue and there is no bug in InScope :)

-- Robert

bug with ident_current

Hello,
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.

It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...

I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.

Thank

Felix Pageau
fpageau@.str.ca

You can test the functionnality with the following code:

create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @.count as int
set @.count = (select count(IDnumber) from identCurrentBugExeample)

--Print the number of record in the table
print @.count

--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')

--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')

--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeampleI'm sorry...is there a question here?|||Yes,

is there an option I can use with the fonction to know if there has been a record inserted. I mean, I would like to know if I can get "0" instead of 1 when there hasn't been any record inserted.

It is not because the count of the table is 0 that hasn't been any record inserted and deleted (and the next identity won't be 1...)|||Are you looking for SCOPE_IDENTITY?|||i'm not looking for SCOPE_IDENTITY. I need the to know what was the last value inserted into the IDENTITY column.

If there wasn't any record ever added, I want something like NULL or -1 to show that the table is still intact.

When there was at least one record added, I want the value of IDENTITY coloumn of the last record|||Ok...I admit it...I have NO idea what you're getting at...

anything like...

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 char(1))
GO

SELECT MAX(Col1) FROM myTable99
INSERT INTO myTable99(Col2) SELECT 'A'
SELECT MAX(Col1) FROM myTable99

DROP TABLE myTable99
GO|||Hello,
the code you have provided works great if the record hasn't been deleted.

When you delete a record (or all the records), the next time you insert one, the value of the IDENTITY column will be x+1

Like:
ID Name GUID
1 Felix AAAAAjsdhksdkjk
2 Bob AAAASDFfsdjkdsj
... ...
7 Alycia ASDADnenefnefn
8 Roger AAAAAdfshdsfhjsdf

If I delete the record #8, and I insert a new one it will look like :
ID Name GUID
1 Felix AAAAAjsdhksdkjk
...
7 Alycia ASDADnenefnefn
9 NewName AAAACCCCddddd

Then I delete all the records
ID Name GUID

Now I must know what is the ID to add a new one (I need to know that before adding it because I'm saving some parts of the record on a Exchnage 2000 linked server (where I must put the ID), get the Global Unique ID of the record in Exchange 2k after saving and putting the guID back in the SQL server in the right record.)

If I do ident_current('tablename') and the value returned is 1. How can I know if the new record I will put in the table will have the ID 1 or 2 if when I do ident_current and there hasn't been anyrecord in the table I got 1 and when there is (or there was) only one record, I also got 1 ?

Do you have a clue on how to do this ?|||By the way, I can't get the GUID (global unique Identifier) directly from Exchange but only while I'm using the linked server to exchange from SQL Server. But I can get the ID from the exchange server while using the linked server and I can set it directly in Exchange...

I need a cross reference between the record (object because it is a OO databse) and the record in SQL server.

That is why I must get the next ID.
Create a dummy record with no informations
Send the ID back to exchange
Save the object in exchange with the ID
Send the GUID of exchange to SQL
Put the GUID in the right "dummy" record with the ID
Get the needed informations from the linked server

And that should work (that is working when I already have a record in the table in sql prior to adding the dummy record. But when I haven't put any, I got a bad ID for the first record that I had. Every other records added later will work fine).

It is why I need to know what is the last value of the IDENTITY column and get 0 when there wasn't any ever added to the table. But the function is giving 1 when there wasn't any record added and is also giving 1 when only one record has been added.

Thank a lot|||I think you will have to set Identity_SID starts from 0 then only..you will get '0' for Select IDENT_CURRENT('table1') when no reords inserted...

try playing with Identity_SID ...you will get the correct value...

see below For SQL help ---
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.

Syntax
IDENT_CURRENT('table_name')

Arguments
table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

Return Types
sql_variant

Remarks
IDENT_CURRENT is similar to the Microsoft SQL Server 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Bug Report: The sorting function that Report Builder have some problem.

Dear all,

Did you use the Report Builder to build report before?

When I create a report using Report Builder, it has a sorting function on the columns. (An arrow icon)

When I hit the sorting icon, the sorting function can only apply to the first column; when i hit the remaining columns, the table have no response to the sorting function.

I've tried a lots of times.

Did someone encounter the same problem?

ThankU.

Yes, am also having same problem.
but inerestingly it works sometimes and i dont see any pattern
BTW am using sql server 2005 standard edition
|||The sorting in a table is per group. It sounds like you have a table that consists of individual groups on each field, instead of a single group containing all the fields you want to display. You can tell which you have by looking at the gray group tabs when the table is selected in design view.|||Hi Bob
in my report, all fields are in separate groups . when i run report (using report builder) sort works only on first column in the report. other columns dont respond to my clicks on sort icon !!

Bug Report: The sorting function that Report Builder have some problem.

Dear all,

Did you use the Report Builder to build report before?

When I create a report using Report Builder, it has a sorting function on the columns. (An arrow icon)

When I hit the sorting icon, the sorting function can only apply to the first column; when i hit the remaining columns, the table have no response to the sorting function.

I've tried a lots of times.

Did someone encounter the same problem?

ThankU.

Yes, am also having same problem.
but inerestingly it works sometimes and i dont see any pattern
BTW am using sql server 2005 standard edition

|||The sorting in a table is per group. It sounds like you have a table that consists of individual groups on each field, instead of a single group containing all the fields you want to display. You can tell which you have by looking at the gray group tabs when the table is selected in design view.|||Hi Bob
in my report, all fields are in separate groups . when i run report (using report builder) sort works only on first column in the report. other columns dont respond to my clicks on sort icon !!

Bug Creating UDF in MSSQL2000

Whe i try to create the next udf:
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@.CubeLength decimal(4,1), @.CubeWidth decimal(4,1),
@.CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @.CubeLength * @.CubeWidth * @.CubeHeight )
END
I get the next error:
Error 170: Line 1: Incorrect syntax near 'FUNCTION'.
Must declare the variable '@.vCubeLength'.
Please Helpme, thanks.This executed perfectly from Query Analyzer in tempdb on My SQL Server 2000 sp3. Are you certain you
don't have other stuff in your script file which was included in the execution?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jpastorf" <jpastorf@.costarricense.cr> wrote in message
news:010d01c4a0be$5cdb1ab0$a301280a@.phx.gbl...
> Whe i try to create the next udf:
> CREATE FUNCTION CubicVolume
> -- Input dimensions in centimeters
> (@.CubeLength decimal(4,1), @.CubeWidth decimal(4,1),
> @.CubeHeight decimal(4,1) )
> RETURNS decimal(12,3) -- Cubic Centimeters.
> AS
> BEGIN
> RETURN ( @.CubeLength * @.CubeWidth * @.CubeHeight )
> END
> I get the next error:
> Error 170: Line 1: Incorrect syntax near 'FUNCTION'.
> Must declare the variable '@.vCubeLength'.
> Please Helpme, thanks.|||My top guesse:
This is a SQL Server version prior to the 2000 version, or this is SQL
Servr 2000, but you have set the compatibility mode to below 80 for this
database.
Steve Kass
Drew University
jpastorf wrote:
>Whe i try to create the next udf:
>CREATE FUNCTION CubicVolume
>-- Input dimensions in centimeters
> (@.CubeLength decimal(4,1), @.CubeWidth decimal(4,1),
> @.CubeHeight decimal(4,1) )
>RETURNS decimal(12,3) -- Cubic Centimeters.
>AS
>BEGIN
> RETURN ( @.CubeLength * @.CubeWidth * @.CubeHeight )
>END
>I get the next error:
>Error 170: Line 1: Incorrect syntax near 'FUNCTION'.
>Must declare the variable '@.vCubeLength'.
>Please Helpme, thanks.
>