Problems getting all the data back with a XML query

I am having a nightmare trying to get to the bottom of this problem.
I have a stored procedure that has a user defined function to create a
select table from a csv string of IDs.
The function and the Stored procedure works fine and returns the XML,
Elements as required except that when i process this sp on the client most
of the data is missing.
If i run the query in the query analyser with the csv parameter and no FOR
XML output i get 72 records (10 unique resources, the rest are due to the
joins in the query)
If i run it with the FOR XML i get 6 lines of XML although it is oddly
truncated. If i cut and past it into xmlspy there are missing elements so i
can't verify exactly what i'm getting.
But when i run this in my web app and use am XMLTextReader to turn the
results into a string I get 2 records, when i was expecting 10. I can verify
this by pasting the xml into xmlspy.
I have tried SQLXML managed classes but can't get them to accept parameters,
i keep getting the message that the sp is expecting parameter despite using
createParameter against the command as per all the documentation, so i gave
up with this approach. I thought that this way i could populate a new
dataset and then bind a table to a datagrid control to see what was
returned. I can get it all to work bar the parameters.
Can anyone shed any light on why the results are significantly less than the
sp should return
and can someone give me an example of working with a parameterised sp and
SQLXML
Many thanks
John
VS2003, SQLXML sp2, XPpro sp2
I am confused about what you are trying to get.
FOR XML results a single XML stream if you use the supported APIs through
ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC and
thus shows the XML result junked into 2033 characters per row. You should
not use QA if you plan on further process the result.
So if you can provide us with some more information about what exactly you
do on the API level, we may be able to help...
Best regards
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>I am having a nightmare trying to get to the bottom of this problem.
> I have a stored procedure that has a user defined function to create a
> select table from a csv string of IDs.
> The function and the Stored procedure works fine and returns the XML,
> Elements as required except that when i process this sp on the client most
> of the data is missing.
> If i run the query in the query analyser with the csv parameter and no FOR
> XML output i get 72 records (10 unique resources, the rest are due to the
> joins in the query)
> If i run it with the FOR XML i get 6 lines of XML although it is oddly
> truncated. If i cut and past it into xmlspy there are missing elements so
> i can't verify exactly what i'm getting.
> But when i run this in my web app and use am XMLTextReader to turn the
> results into a string I get 2 records, when i was expecting 10. I can
> verify this by pasting the xml into xmlspy.
> I have tried SQLXML managed classes but can't get them to accept
> parameters, i keep getting the message that the sp is expecting parameter
> despite using createParameter against the command as per all the
> documentation, so i gave up with this approach. I thought that this way i
> could populate a new dataset and then bind a table to a datagrid control
> to see what was returned. I can get it all to work bar the parameters.
> Can anyone shed any light on why the results are significantly less than
> the sp should return
> and can someone give me an example of working with a parameterised sp and
> SQLXML
>
> Many thanks
> John
> VS2003, SQLXML sp2, XPpro sp2
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.xml:25072
Michael,
thanks I have sorted the problem eventually. Brain fade and not enough time
thinking it throgh. The problem was in the sql statement which took ages to
track down but there we go.
One question that is unanswered is how do i pass parameters to SQLXML with
stored procedures, not raw sql text?
i keep getting the message expecting parameter as per my post
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>I am confused about what you are trying to get.
> FOR XML results a single XML stream if you use the supported APIs through
> ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC
> and thus shows the XML result junked into 2033 characters per row. You
> should not use QA if you plan on further process the result.
> So if you can provide us with some more information about what exactly you
> do on the API level, we may be able to help...
> Best regards
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>
|||To what exactly do you want to pass parameters? SQLXML is a general term and
the name of the mid-tier component.
Do you mean how to pass parameters into the SQL statement that uses FOR XML
via stored procs?
Thanks
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
> Michael,
> thanks I have sorted the problem eventually. Brain fade and not enough
> time thinking it throgh. The problem was in the sql statement which took
> ages to track down but there we go.
> One question that is unanswered is how do i pass parameters to SQLXML with
> stored procedures, not raw sql text?
> i keep getting the message expecting parameter as per my post
> thanks
> john
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>
|||Michael
here is the code that i am using
Dim strConn = "provider=SQLOLEDB;data source='....';initial
catalog=......;user id=sa;password=......"
Dim sxcCmd As New SqlXmlCommand(strConn)
Dim sdaDA As New SqlXmlAdapter(sxcCmd)
Dim sxpParam As SqlXmlParameter
Dim xr As Xml.XmlReader
Dim ds As New DataSet
With sxcCmd
..RootTag = "root"
..CommandType = SqlXmlCommandType.Sql
..CommandText = "Test2"
sxpParam = .CreateParameter
End With
With sxpParam
..Name = "@.IDs"
..Value = 5
End With
sdaDA.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
and here is the sp
ALTER PROCEDURE dbo.test2
(
@.IDs int
)
AS
/* SET NOCOUNT ON */
SELECT '<root>'
SELECT * FROM tblResource WHERE ResourceID=@.Ids
FOR XML AUTO, Elements
SELECT '</root>'
as you can see the sp has this parameter @.IDs but when i run the code the
error meesage says ' expecting parameter @.IDs'' yet i am passing it. If i
change the command text to a sql statement with a ? for the parameter then
it works.
Obviously i am missing something here, I presume the command type might be
wrongly set.
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O45cbdZrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> To what exactly do you want to pass parameters? SQLXML is a general term
> and the name of the mid-tier component.
> Do you mean how to pass parameters into the SQL statement that uses FOR
> XML via stored procs?
> Thanks
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
>

Problems Creating TVF function

I had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:

Code Snippet

CREATE FUNCTION f_GetGroupMembership(@.providerKey nvarchar(200), @.connection nvarchar(1000))

RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25))

AS

EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser

This returns an error:

Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1

The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.

Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:

Code Snippet

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.DirectoryServices;

using System.Collections;

public class GroupMembership

{

[SqlFunctionAttribute(FillRowMethodName = "FillGroupRow")]

public static IEnumerable GetGroupsForUser(String ProviderKey, String Connection)

{

return groups.GetGroupsForUser(ProviderKey, Connection);

}

public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type)

{

//cast the enumerator passed in

group grp = (group)obj;

//create output parameters

guid = new SqlGuid(grp.guid);

name = new SqlString(grp.Name);

type = new SqlString(grp.applicationData);

}

}

Can anyone tell me why this is happening? How can I overcome this problem?

Help will be highly appreciated.

Help Please! there is no reference to this error anywhere|||
In your CREATE FUNCTION statement, you are referencing the wrong class, groups.GetGroupsForUser instead of GroupMembership.GetGroupsForUser.|||

Steven,

Thank you I figured out I did not reply here because I did not want to look like the dumba** that I am. Thanks Anyway. I'll try to review my code a third time before I make a fool of myself in public.

Regards,

MB

problems creating a datatable within a UDF in C#

Hi!

I'm having a lot of difficulty in creating a user defined function in vs2005.

why I'm doing it in c# instead of vb or tsql is that I know C# better than the others (that and tsql won't let you make temp tables in functions

But as far as this goes, I can't seem to get this working.

I' trying to execute a simple select statement and get a datatable variable before I run it through the creative algorithm I have planned.

But I can't seem to get the data into a table.

I get this exception when I execute the function on the server:

System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

This is the code I use to get the data:

using(SqlConnection sqlcnct = new SqlConnection("Context Connection=true"))
{ SqlCommand sqlcmd = new SqlCommand(sqlstrng, sqlcnct);

sqlcnct.Open();
SqlDataReader sqldr = sqlcmd.ExecuteReader();

while (sqldr.Read())
{
//inserts into datatable here (I don't yet have code here because i've been trying to figure out how to get it to read first. )
}
}

But unfortunately SQL server 2005 doesn't like that.

Can anyone point me in the right direction or shoot down my hopes right here?

I'm trying to use a function to avoid having to run a sproc on every employee on the employee table and storing it in an actual field /table every time I need to run this function.Two things:

1. In order to access database data from a CLR UDF you need to set the DataAccess and SystemDataAccess properties of the SqlFunction attribute to DataAccessKind.Read, and SystemDataAccessKind.Read:
[SqlFunction(DataAccess=DataAccessKind.Read, SystemDataAccess.SystenDataAccessKind.Read)]
your method name

2. You can not update/insert/delete data from an UDF

HTH

Niels
|||Thanks! It works perfectly now. I'm new to writing functions for sqlserver in visual studio so I had no idea i needed to do that. Fortunately I didn't need to update/insert/delete any data. =)

The reason I needed to do this was because the data I need to work with needs to be picked out of a table and sorted and then calculated from but sqlserver wasn't letting me make a temporary table in a function.

problem with using String Variable in IN Function

Hellow !!
My problem is that I want to use a string variable in the 'where' clause Like this.I am using VB.6

dim ID as string
ID = "('1','2')"

Select coursecode from student
where studentid in ID

Now tell me how to solve this problem. or is there any other way to solve this problem.
Plz help me out.One solution would be:

dim ID as string
ID = ".1.2."

Select coursecode from student
where ID LIKE '%.' || studentid || '.%';|||Hellow !!

First i will say thanks. Tell me some other solution, this one is not working. Let me tell you i am using SQLSever 2000.
Let me explaing my problem with some more details.

recordSet.open" select coursecode from course where student id='1'" , connectionobject

Now save the recordset data in an array or in a string

let suppose
array(0)= "Eng"
array(1)="Phy"
(OR)

dim str as string
str="('eng','phy')"

now how i will use this array or this string in another select statement like

recordset1.open "select studentid from student where coursecode = ???",connectionObject,adopenstatic

Plz dont tell to use sunquery
I will be waiting for the reply Thanks|||You can use IN:

select studentid from student where coursecode in ('eng','phy')|||Hellow :
Thanks for the reply. Actually the result of the first query is not fixed.like

recordSet.open" select coursecode from course where student id='1'" , connectionobject

'Consider the result of this query will be different ever time when ever it runs.
' Like it may give 4 records,10 records may be 15 in the recordset
'now i have two ways to store the result of the query , i can store it either in a "string variable" or in an "Array".
' if string variable is used like

dim str as string
str = "('phy','chem','math')" or str = ('eng','oop','db','oose')

(forget this how i saved the data from the query in this string like this )
the only problem with using the string in the query is the outer quots " " . like

recordset1.open "select studentid from student where coursecode = "' & str & '" ",connectionObject,adopenstatic

Now if i use an array for storing the result of the first query. Tell me the way how that array will be use in the second query like

recordset1.open "select studentid from student where coursecode = array ",connectionObject,adopenstatic

Problem with using Round() in mdx

I am using Round function in MDX. It is not giving expected result. It always gives nearest even number as result.

I am running following mdx:

with member

[Measures].[Calculated Score25]

as 'Round(2.5, 0)'

member [Measures].[Calculated Score35]

as 'Round(3.5, 0)'

select {

[Measures].[Calculated Score25],

[Measures].[Calculated Score35]}

on columns from Cube1

This mdx gives following result

Calculated Score25 Calculated Score35
2 4

When expected result is 3, 4

I agree it's strange, and when I first looked at your results I thought this was a bug. But then I looked at some descriptions of the round function and I suspect this behaviour is intentional:

http://www.techonthenet.com/access/functions/numeric/round.php

Anyway, you can get the value you want using the Excel round function, assuming you have the Excel function library available on your server:

with member

[Measures].[Calculated Score25]

as 'excel!round(2.5,0)'

member [Measures].[Calculated Score35]

as 'excel!Round(3.5,0)'

select {

[Measures].[Calculated Score25],

[Measures].[Calculated Score35]}

on columns from [Adventure Works]

HTH,

Chris

|||

This MS Support article explains various rounding algorithms - for example, VBA Round() performs "Banker's Rounding", whereas Excel Round() does "symmetric arithmetic rounding":

How To Implement Custom Rounding Procedures

...

SUMMARY

There are a number of different rounding algorithms available in Microsoft products. Rounding algorithms range from Arithmetic Rounding in Excel's Worksheet Round() function to Banker's Rounding in the CInt(), CLng(), and Round() functions in Visual Basic for Applications. This article describes what the various Visual Basic for Applications rounding functions do and provides samples of using the functions. In addition, the article includes sample functions that implement various rounding algorithms.

But if you need asymmetric arithmetic rounding, it looks like inverted VBA Int() would work:

with

member [Measures].[Score25] as 2.5

member [Measures].[Score35] as 3.5

member [Measures].[Calculated Score25]

as '-int(-[Measures].[Score25])'

member [Measures].[Calculated Score35]

as '-int(-[Measures].[Score35])'

select {

[Measures].[Calculated Score25],

[Measures].[Calculated Score35]}

on columns from [Adventure Works]

-

Calculated Score25 Calculated Score35
3 4

Problem with using report parameters in SQL query function, SSRS 2

Using Visual Studio to build reports for Reporting Services 2000: is it
allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
select function(@.variable) from table where ..
I get "syntax error or access violation" when trying.
Regards, JouniI think that question would be more appropriate in the sqlserver.programming
group. With that said...
Have you tried running that query in Query Analyzer?
yeah...I don't think that Sql statement will ever run.
from the Books Online for CREATE FUNCTION (transact SQL):
"@.parameter_name
Is a parameter in the user-defined function. One or more parameters can be
declared.
[...]
Specify a parameter name by using an at sign (@.) as the first character. The
parameter name must comply with the rules for identifiers. Parameters are
local to the function; the same parameter names can be used in other
functions. Parameters can take the place only of constants; they cannot be
used instead of table names, column names, or the names of other database
objects. "
Soooo...assuming your user-defined function is a scalar-value function that
returns field names and is owned by "dbo" schema, try using dynamic sql,
something like this:
DECLARE @.sql varchar(8000)
SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
SET @.sql= @.sql + ' FROM MyTable '
SET @.sql= @.sql + ' WHERE ... '
EXEC @.sql
Give it a shot, and if I am way off...reply and let me know. Hope this help
you.
--
Regards,
Thiago Silva
"Jouni" wrote:
> Using Visual Studio to build reports for Reporting Services 2000: is it
> allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> select function(@.variable) from table where ..
> I get "syntax error or access violation" when trying.
> Regards, Jouni|||Hi Thiago,
haven't had time to test this. Actually I already switched to another
approach: defining the whole query as a db procedure and just calling that:
exec jounis_procedure @.report_parameter
I noticed that Microsoft people have chosen this approach themselves: in
"Report Pack for SPS" all the reports use procedure calls.
Regards,
Jouni
"HC" wrote:
> I think that question would be more appropriate in the sqlserver.programming
> group. With that said...
> Have you tried running that query in Query Analyzer?
> yeah...I don't think that Sql statement will ever run.
> from the Books Online for CREATE FUNCTION (transact SQL):
> "@.parameter_name
> Is a parameter in the user-defined function. One or more parameters can be
> declared.
> [...]
> Specify a parameter name by using an at sign (@.) as the first character. The
> parameter name must comply with the rules for identifiers. Parameters are
> local to the function; the same parameter names can be used in other
> functions. Parameters can take the place only of constants; they cannot be
> used instead of table names, column names, or the names of other database
> objects. "
> Soooo...assuming your user-defined function is a scalar-value function that
> returns field names and is owned by "dbo" schema, try using dynamic sql,
> something like this:
> DECLARE @.sql varchar(8000)
> SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
> SET @.sql= @.sql + ' FROM MyTable '
> SET @.sql= @.sql + ' WHERE ... '
> EXEC @.sql
> Give it a shot, and if I am way off...reply and let me know. Hope this help
> you.
> --
> Regards,
> Thiago Silva
> "Jouni" wrote:
> > Using Visual Studio to build reports for Reporting Services 2000: is it
> > allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> >
> > select function(@.variable) from table where ..
> >
> > I get "syntax error or access violation" when trying.
> >
> > Regards, Jouni

Problem with User Defined Function

I wrote a UDF to get a rolling average based upon a date passed to the UDF. The error I get is:

Server: Msg 102, Level 15, State 1, Procedure fn_RollAverage, Line 26
Incorrect syntax near ')'.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION fn_RollAverage(@.CURVE_DATE AS SMALLDATETIME)
RETURNS @.TBLRESULT TABLE
(US0001M_Index AS FLOAT,
US0003M_INDEX AS FLOAT,
US0006M_INDEX AS FLOAT,
US0012M_INDEX AS FLOAT,
usswap2_index AS FLOAT,
usswap3_index AS FLOAT,
usswap4_index AS FLOAT,
usswap5_index AS FLOAT,
usswap6_index AS FLOAT,
usswap7_index AS FLOAT,
usswap8_index AS FLOAT,
usswap9_index AS FLOAT,
usswap10_index AS FLOAT,
usswap11_index AS FLOAT,
usswap12_index AS FLOAT,
usswap13_index AS FLOAT,
usswap14_index AS FLOAT,
usswap15_index AS FLOAT,
usswap20_index AS FLOAT,
usswap25_index AS FLOAT,
usswap30_index AS FLOAT)

AS BEGIN

DECLARE @.BEG_DATE AS SMALLDATETIME

SET @.BEG_DATE = (@.CURVE_DATE - 30)

INSERT @.TBLRESULT(
US0001M_Index,
US0003M_INDEX,
US0006M_INDEX,
US0012M_INDEX,
usswap2_index,
usswap3_index,
usswap4_index,
usswap5_index,
usswap6_index,
usswap7_index,
usswap8_index,
usswap9_index,
usswap10_index,
usswap11_index,
usswap12_index,
usswap13_index,
usswap14_index,
usswap15_index,
usswap20_index,
usswap25_index,
usswap30_index)

SELECT
AVG(US0001M_Index) AS US0001M_Index,
AVG(US0003M_INDEX) AS US0003M_INDEX,
AVG(US0006M_INDEX) AS US0006M_INDEX,
AVG(US0012M_INDEX) AS US0012M_INDEX,
AVG(usswap2_index) AS usswap2_index,
AVG(usswap3_index) AS usswap3_index,
AVG(usswap4_index) AS usswap4_index,
AVG(usswap5_index) AS usswap5_index,
AVG(usswap6_index) AS usswap6_index,
AVG(usswap7_index) AS usswap7_index,
AVG(usswap8_index) AS usswap8_index,
AVG(usswap9_index) AS usswap9_index,
AVG(usswap10_index) AS usswap10_index,
AVG(usswap11_index) AS usswap11_index,
AVG(usswap12_index) AS usswap12_index,
AVG(usswap13_index) AS usswap13_index,
AVG(usswap14_index) AS usswap14_index,
AVG(usswap15_index) AS usswap15_index,
AVG(usswap20_index) AS usswap20_index,
AVG(usswap25_index) AS usswap25_index,
AVG(usswap30_index) AS usswap30_index

FROM dbo.LiborSwap
WHERE CURVE_DATE BETWEEN @.BEG_DATE AND @.CURVE_DATE

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @.TBLRESULT TABLE
(US0001M_Index FLOAT,
US0003M_INDEX FLOAT,
US0006M_INDEX FLOAT

...)

|||

The " AS " keyword is not valid in the @.TBLRESULT table declaration.

|||Thanks thanks a thousand thanks