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 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 user defined funtions

Hi,
I defined a publisher and a pushsubscriber with some articles. The articles
were Tables, Views and user defined functions. The pushsubscriber was created
with the sync_type=automatic. When I synchronisize the publisher with the
subscriber, the merge-agent fails on the follwing view creation command:
CREATE VIEW [dbo].[TGS_CATALOG]
AS
SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
+
dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
Latin1_General_CS_AS AS PFAD,
dbo.TGS_CATALOG_MASTER.ENTITYNUM,
dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
dbo.TGS_CATALOG_MASTER.KEYS,
dbo.TGS_CATALOG_MASTER.REVIERID
FROM dbo.TGS_BAUMTHEMEN INNER JOIN
dbo.TGS_CATALOG_MASTER ON dbo.TGS_BAUMTHEMEN.REVIERID
= dbo.TGS_CATALOG_MASTER.REVIERID AND
dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
dbo.TGS_REVIERE.REVIERID
I looked in the logfile and saw that there was a drop command of the user
defined function 'NullStringFilter', but no recreationof it before the
create command
was proccessed. Therfore the creation of the view fails.
What can I do to change the order of the schema creation process ?
I want to recreate the user defined functions before the recreation of the
views
starts !!!
Best regards
Axel Lanser
replication uses sysdepends to figure out the ordering of articles.
Sometimes this table can be out of sync (read erroneous).
I tend to deploy my problematic schema using a pre-snapshot command where
all constraints are disabled. Then I set all articles up so that they use
the delete data in existing table in the name conflicts section. I then use
a post-snapshot command to enable the constraints.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> Hi,
> I defined a publisher and a pushsubscriber with some articles. The
articles
> were Tables, Views and user defined functions. The pushsubscriber was
created
> with the sync_type=automatic. When I synchronisize the publisher with the
> subscriber, the merge-agent fails on the follwing view creation command:
> CREATE VIEW [dbo].[TGS_CATALOG]
> AS
> SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
> dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
> dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
> dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
> +
> dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
> Latin1_General_CS_AS AS PFAD,
> dbo.TGS_CATALOG_MASTER.ENTITYNUM,
> dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
> dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
> dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
> dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
> dbo.TGS_CATALOG_MASTER.KEYS,
> dbo.TGS_CATALOG_MASTER.REVIERID
> FROM dbo.TGS_BAUMTHEMEN INNER JOIN
> dbo.TGS_CATALOG_MASTER ON
dbo.TGS_BAUMTHEMEN.REVIERID
> = dbo.TGS_CATALOG_MASTER.REVIERID AND
> dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
> dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
> dbo.TGS_REVIERE.REVIERID
> I looked in the logfile and saw that there was a drop command of the user
> defined function 'NullStringFilter', but no recreationof it before the
> create command
> was proccessed. Therfore the creation of the view fails.
> What can I do to change the order of the schema creation process ?
> I want to recreate the user defined functions before the recreation of the
> views
> starts !!!
> Best regards
> Axel Lanser
|||Hi,
how can I create pre- and post-snapshots ?
Best regards
Axel Lanser
"Hilary Cotter" wrote:

> replication uses sysdepends to figure out the ordering of articles.
> Sometimes this table can be out of sync (read erroneous).
> I tend to deploy my problematic schema using a pre-snapshot command where
> all constraints are disabled. Then I set all articles up so that they use
> the delete data in existing table in the name conflicts section. I then use
> a post-snapshot command to enable the constraints.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> articles
> created
> dbo.TGS_BAUMTHEMEN.REVIERID
>
>
|||ALN:
You need to write you own SQL File where you set your custom ALTER TABLE
commands for disabling constraints. While setting up publication you can
specify which SQL file needs to be run before snapshot is applied. Check BOL
for @.pre_snapshot_script parameter in sp_addpublication procedure.
"ALN" wrote:
[vbcol=seagreen]
> Hi,
> how can I create pre- and post-snapshots ?
> Best regards
> Axel Lanser
> "Hilary Cotter" wrote:
|||Hi,
thank you very much for your fast response. It helps me.
Best regards
Axel Lanser
"Mark" wrote:
[vbcol=seagreen]
> ALN:
> You need to write you own SQL File where you set your custom ALTER TABLE
> commands for disabling constraints. While setting up publication you can
> specify which SQL file needs to be run before snapshot is applied. Check BOL
> for @.pre_snapshot_script parameter in sp_addpublication procedure.
>
> "ALN" wrote:

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