Forcing Function recompilation in SQL 2000

A stored procedure in the cache is automatically recompiled when a table it refers to has a table structure change. User defined functions are not. Here's a simplified code sample:

set nocount on
go

create table tmpTest (a int, b int, c int)

insert into tmpTest (a, b, c) values (1, 2, 3)
insert into tmpTest (a, b, c) values (2, 3, 4)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fTest]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fTest]
GO

CREATE FUNCTION dbo.fTest (@.a int)
RETURNS TABLE
AS
RETURN (SELECT * from tmpTest where a = @.a)
GO

select * from fTest(1)

CREATE TABLE dbo.Tmp_tmpTest
(
a int NULL,
b int NULL,
d int NULL,
c int NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.tmpTest)
EXEC('INSERT INTO dbo.Tmp_tmpTest (a, b, c)
SELECT a, b, c FROM dbo.tmpTest TABLOCKX')
DROP TABLE dbo.tmpTest
EXECUTE sp_rename N'dbo.Tmp_tmpTest', N'tmpTest', 'OBJECT'

select * from fTest(1)

drop table tmpTest

Running it, the output is:

a b c
-- -- --
1 2 3

Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'tmpTest'.
a b c
-- -- --
1 2 NULL

(I know that "select *" is bad, but it's a lot of legacy code that I'm working with here, and that's how it's written.)

The function doesn't detect that the table has changed in structure, or even that there is no longer a dependency on tmpTest. (Appending a column rather than inserting has the same effect, in that only the first 3 columns are returned.)

DBCC FREEPROCCACHE has no effect, not that I really expected it to, but you never know...

Is there any way, other than dropping and recreating, to force a recompilation of a particular function in memory, or perhaps all functions?

Thanks in anticipation.

Tom

try this example from the Books Online...

USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE titles_by_author GO CREATE PROCEDURE titles_by_author @.@.LNAME_PATTERN varchar(30) = '%' WITH RECOMPILE AS SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name', title AS Title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE au_lname LIKE @.@.LNAME_PATTERN GO |||

Thanks for the suggestion. Just one small point:

It's a function, not a procedure. And WITH RECOMPILE isn't a valid option for a function.

Tom

|||It is because of the SELECT *. Inline table-valued function is similar to view in that the metadata for the columns are persisted at the time of creation of the function. So in your example, the * in the select list will get resolved to table/columns at creation time. You will have to run ALTER FUNCTION or drop/recreate the function to recreate the correct metadata in current versions of SQL Server. SQL Server 2005 SP2 will have a new system stored procedure that can be used to refresh such metadata for SPs, UDFs. This will be similar to sp_refreshview SP. Of course, you wouldn't get into these problems if you specified column names explicitly.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

sql

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

Force a round() when it's below a 5?

I have a sales tax function in my antiquated system. Someone decided to
play it safe and over collect on sales tax.
If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
system shows 10.42
How do I force the round up for reporting to Auditors when they want to
query random samples of data?
TIA
__StephenTry:
select ceiling (10.4115 * 100) / 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>I have a sales tax function in my antiquated system. Someone decided to
>play it safe and over collect on sales tax.
> If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
> system shows 10.42
> How do I force the round up for reporting to Auditors when they want to
> query random samples of data?
> TIA
> __Stephen
>|||Lookup CEILING in Books Online.
ML
http://milambda.blogspot.com/|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Try:
> select ceiling (10.4115 * 100) / 100
Thanks. With your credentials I'll give it a whirl. :)
Will this work within a SUM() when I'm grouping by State, Client, City.
Some clients have different contracts with us and we compute tax on where
the HQ of company is that signed the contract, and not where the recipient
is located.
I'm processing 250,000 + detail rows a month creating a final result of 110
rows summarized today.|||Sure. I assume that this round-up has to happen on each line item before it
is summed. You can make it conditional, too. For example:
select
sum (case when State in ('CA', 'TX') then ceiling (Tax * 100) / 100 else
Tax end)
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:OaaHuYn%23FHA.208@.tk2msftngp13.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Thanks. With your credentials I'll give it a whirl. :)
> Will this work within a SUM() when I'm grouping by State, Client, City.
> Some clients have different contracts with us and we compute tax on where
> the HQ of company is that signed the contract, and not where the recipient
> is located.
> I'm processing 250,000 + detail rows a month creating a final result of
> 110 rows summarized today.
>
>|||Back in the dark ages, we rounded by adding and truncating. For
example, to always round up with 2 decimal places,
SELECT cast((9.53 * 1.0925 + .009999) * 100 as integer) / 100.
Not sure its any simpler.
Good luck.
Payson
Tom Moreau wrote:
> Try:
> select ceiling (10.4115 * 100) / 100
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...

For XML Problem with IIS6 and W2k3

I have this function that worked like a charm under IIS5 and W2K. You pass a
sql string that has for xml auto or a stored produre that has for xml auto in
it. Under IIS6 and W2K3 it stops working after a couple of days with no
rhyme or reason. No error log either. We applied all the service packs
including sqlxml sp3. What is wrong? Thanks.
Here is the code:
function getSQLXML(byval sqlString)
dim adoConn
dim adoCmd
dim adoStreamQuery
set adoConn = vbsqlconnection 'located in sharedfunctions.asp
adoConn.CommandTimeout = 300
set adoStreamQuery = Server.CreateObject("ADODB.Stream")
set adoCmd = Server.CreateObject("ADODB.Command")'
adoCmd.ActiveConnection = adoConn
adoCmd.CommandTimeout = 300
adoConn.CursorLocation = adUseClient
dim sQuery
sQuery = "<recordset xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
sQuery = sQuery + "<sql:query>"+sqlString+"</sql:query>"
sQuery = sQuery + "</recordset>"
adoStreamQuery.Open 'Open the command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar 'Set the input command
stream's text with the query string
adoStreamQuery.Position = 0 'Reset the position in the stream, otherwise
it will be at EOS
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" 'Set the
dialect for the command stream to be a SQL query.
adoCmd.CommandStream = adoStreamQuery 'Set the command object's command
to the input stream set above
dim outStrm
set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
stream
outStrm.Open
adoCmd.Properties("Output Stream").Value = outStrm 'Set command's output
stream to the output stream just opened
adoCmd.Execute , , adExecuteStream
'Response.Write(outStrm.ReadText)
adoCmd.ActiveConnection = nothing
adoConn.Close
set adoConn = nothing
getSQLXML = outStrm.ReadText
end function
P.S. Goorbeeman in the group microsoft.public.sqlserver.server has the same
problem
Can you run the FOR XML query directly on the database?
Have you tried a different template/query to see if the connection works?
Best regards
Michael
"ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
news:C73A1C66-4B94-489C-BA5F-6821CEB095A5@.microsoft.com...
>I have this function that worked like a charm under IIS5 and W2K. You pass
>a
> sql string that has for xml auto or a stored produre that has for xml auto
> in
> it. Under IIS6 and W2K3 it stops working after a couple of days with no
> rhyme or reason. No error log either. We applied all the service packs
> including sqlxml sp3. What is wrong? Thanks.
> Here is the code:
> function getSQLXML(byval sqlString)
> dim adoConn
> dim adoCmd
> dim adoStreamQuery
> set adoConn = vbsqlconnection 'located in sharedfunctions.asp
> adoConn.CommandTimeout = 300
> set adoStreamQuery = Server.CreateObject("ADODB.Stream")
> set adoCmd = Server.CreateObject("ADODB.Command")'
> adoCmd.ActiveConnection = adoConn
> adoCmd.CommandTimeout = 300
> adoConn.CursorLocation = adUseClient
> dim sQuery
> sQuery = "<recordset xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
> sQuery = sQuery + "<sql:query>"+sqlString+"</sql:query>"
> sQuery = sQuery + "</recordset>"
> adoStreamQuery.Open 'Open the command stream so it may be written to
> adoStreamQuery.WriteText sQuery, adWriteChar 'Set the input command
> stream's text with the query string
> adoStreamQuery.Position = 0 'Reset the position in the stream, otherwise
> it will be at EOS
> adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" 'Set the
> dialect for the command stream to be a SQL query.
> adoCmd.CommandStream = adoStreamQuery 'Set the command object's
> command
> to the input stream set above
> dim outStrm
> set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
> stream
> outStrm.Open
> adoCmd.Properties("Output Stream").Value = outStrm 'Set command's output
> stream to the output stream just opened
> adoCmd.Execute , , adExecuteStream
> 'Response.Write(outStrm.ReadText)
> adoCmd.ActiveConnection = nothing
> adoConn.Close
> set adoConn = nothing
> getSQLXML = outStrm.ReadText
> end function
> P.S. Goorbeeman in the group microsoft.public.sqlserver.server has the
> same
> problem
|||To specify the problem. This chunk of code has been working for about 3
years on IIS5 and W2K. The sql that gets executed is passed in as a string
variable. To answer your question the for xml queries always runs correctly
in sql server. In the IIS6 and W2k3 combo this code runs fine for days and
then all of a sudden it bombs without an error message. I have isolated the
place in the asp code snippet below on this line:
adoCmd.Execute , , adExecuteStream
My next step is to recycle the application pool and for the most part that
gets things going again. Sometimes I have to restart IIS and still sometimes
I have to reboot the server all together. If something where wrong with the
code then it should never work. If something were wrong with the sql being
executed then I should get a sql server error.
Thanks for the reply.
"Michael Rys [MSFT]" wrote:

> Can you run the FOR XML query directly on the database?
> Have you tried a different template/query to see if the connection works?
> Best regards
> Michael
> "ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
> news:C73A1C66-4B94-489C-BA5F-6821CEB095A5@.microsoft.com...
>
>
|||Can you send me your private contact info (delete the online part in my
email alias)? I will get somebody from the SQLXML team to get in contact
with you to figure out where the problem lays.
Thanks
Michael
"ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...[vbcol=seagreen]
> To specify the problem. This chunk of code has been working for about 3
> years on IIS5 and W2K. The sql that gets executed is passed in as a
> string
> variable. To answer your question the for xml queries always runs
> correctly
> in sql server. In the IIS6 and W2k3 combo this code runs fine for days
> and
> then all of a sudden it bombs without an error message. I have isolated
> the
> place in the asp code snippet below on this line:
> adoCmd.Execute , , adExecuteStream
> My next step is to recycle the application pool and for the most part that
> gets things going again. Sometimes I have to restart IIS and still
> sometimes
> I have to reboot the server all together. If something where wrong with
> the
> code then it should never work. If something were wrong with the sql
> being
> executed then I should get a sql server error.
> Thanks for the reply.
> "Michael Rys [MSFT]" wrote:
|||You can send your case directly to me: bertan at gmail dot com. Please,
iclude your vb script and query, your schema/template if there is any.
In the mean time, I don't see that you are using IIS anywhere here. You are
simply using ADO. IIS shouldn't be the issue here.
I am afraid your problem lies somewhere in your machine/system
configurations. The only issue I know for SqlXml3 on Win2003 is that you
have to install Soap toolkit seperately.
Thanks.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...
> To specify the problem. This chunk of code has been working for about 3
> years on IIS5 and W2K. The sql that gets executed is passed in as a
string
> variable. To answer your question the for xml queries always runs
correctly
> in sql server. In the IIS6 and W2k3 combo this code runs fine for days
and
> then all of a sudden it bombs without an error message. I have isolated
the
> place in the asp code snippet below on this line:
> adoCmd.Execute , , adExecuteStream
> My next step is to recycle the application pool and for the most part that
> gets things going again. Sometimes I have to restart IIS and still
sometimes
> I have to reboot the server all together. If something where wrong with
the
> code then it should never work. If something were wrong with the sql
being[vbcol=seagreen]
> executed then I should get a sql server error.
> Thanks for the reply.
> "Michael Rys [MSFT]" wrote:
works?[vbcol=seagreen]
pass[vbcol=seagreen]
auto[vbcol=seagreen]
no[vbcol=seagreen]
packs[vbcol=seagreen]
to[vbcol=seagreen]
otherwise[vbcol=seagreen]
output[vbcol=seagreen]
output[vbcol=seagreen]
|||I ran iisstate against w3wp.exe. When the web app hangs here is the
consistant error
ModLoad: 74540000 745d2000 C:\WINDOWS\system32\mlang.dll
(e88.5d0): Access violation - code c0000005 (first chance)
(e88.5d0): C++ EH exception - code e06d7363 (first chance)
"Bertan ARI [MSFT]" wrote:

> You can send your case directly to me: bertan at gmail dot com. Please,
> iclude your vb script and query, your schema/template if there is any.
> In the mean time, I don't see that you are using IIS anywhere here. You are
> simply using ADO. IIS shouldn't be the issue here.
> I am afraid your problem lies somewhere in your machine/system
> configurations. The only issue I know for SqlXml3 on Win2003 is that you
> have to install Soap toolkit seperately.
> Thanks.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
> news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...
> string
> correctly
> and
> the
> sometimes
> the
> being
> works?
> pass
> auto
> no
> packs
> to
> otherwise
> output
> output
>
>
|||I ran iisstate against w3wp.exe and it consistantly hangs at this point
ModLoad: 74540000 745d2000 C:\WINDOWS\system32\mlang.dll
(e88.5d0): Access violation - code c0000005 (first chance)
(e88.5d0): C++ EH exception - code e06d7363 (first chance)
"Michael Rys [MSFT]" wrote:

> Can you send me your private contact info (delete the online part in my
> email alias)? I will get somebody from the SQLXML team to get in contact
> with you to figure out where the problem lays.
> Thanks
> Michael
> "ajsmith02" <ajsmith02@.discussions.microsoft.com> wrote in message
> news:318557E2-0C62-4CDC-A964-F3EDE4A87CD4@.microsoft.com...
>
>
|||ajsmith02, did you ever find a solution to this problem? I have been struggling with the same problem for a number of months now? Would appreciate any help.
Thanks,
TuPups|||Did anyone ever resolve this? I am having the exact same issue...returning
results from a FOR XML procedure to an ado stream object stops working every
several days.
"ajsmith02" wrote:

> I have this function that worked like a charm under IIS5 and W2K. You pass a
> sql string that has for xml auto or a stored produre that has for xml auto in
> it. Under IIS6 and W2K3 it stops working after a couple of days with no
> rhyme or reason. No error log either. We applied all the service packs
> including sqlxml sp3. What is wrong? Thanks.
> Here is the code:
> function getSQLXML(byval sqlString)
> dim adoConn
> dim adoCmd
> dim adoStreamQuery
> set adoConn = vbsqlconnection 'located in sharedfunctions.asp
> adoConn.CommandTimeout = 300
> set adoStreamQuery = Server.CreateObject("ADODB.Stream")
> set adoCmd = Server.CreateObject("ADODB.Command")'
> adoCmd.ActiveConnection = adoConn
> adoCmd.CommandTimeout = 300
> adoConn.CursorLocation = adUseClient
> dim sQuery
> sQuery = "<recordset xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
> sQuery = sQuery + "<sql:query>"+sqlString+"</sql:query>"
> sQuery = sQuery + "</recordset>"
> adoStreamQuery.Open 'Open the command stream so it may be written to
> adoStreamQuery.WriteText sQuery, adWriteChar 'Set the input command
> stream's text with the query string
> adoStreamQuery.Position = 0 'Reset the position in the stream, otherwise
> it will be at EOS
> adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" 'Set the
> dialect for the command stream to be a SQL query.
> adoCmd.CommandStream = adoStreamQuery 'Set the command object's command
> to the input stream set above
> dim outStrm
> set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
> stream
> outStrm.Open
> adoCmd.Properties("Output Stream").Value = outStrm 'Set command's output
> stream to the output stream just opened
> adoCmd.Execute , , adExecuteStream
> 'Response.Write(outStrm.ReadText)
> adoCmd.ActiveConnection = nothing
> adoConn.Close
> set adoConn = nothing
> getSQLXML = outStrm.ReadText
> end function
> P.S. Goorbeeman in the group microsoft.public.sqlserver.server has the same
> problem
|||Nobody helped me. It turned out to be a blessing in disguise. I wound up
creating a component using .Net to pass through the "For XML" sql statements
and return the xml in for of a text stream. After I created the component I
Com Wrapper (using .NET) so that my old asp page to use it.
"ashort" wrote:
[vbcol=seagreen]
> Did anyone ever resolve this? I am having the exact same issue...returning
> results from a FOR XML procedure to an ado stream object stops working every
> several days.
> "ajsmith02" wrote:

FOR XML PATH Question - Nesting Elements

Hi,
I was wondering if anyone can please help me?...I am trying to produce an
XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
nested as childs of a 'bullets' element. Each bullet (to a maximum of 10)
is represented by a field in the database that is named as follows;
field_b1, field_b2, field_b3, ....etc to field_b10
I am using the below statement to produce the XML which currently only works
when I only specify 1 attribute value eg. <bullets><bullet
id="1">Parking</bullet><bullets>;
select top 1
field_id as '@.id',
field_name as 'address/name',
field_street as 'address/street',
field_town as 'address/town',
field_county as 'address/county',
field_pc as 'address/postcode',
field_price as 'price/@.value',
field_stat as 'price/status',
field_pq as 'price/qualifier',
1 as 'bullets/bullet/@.id',
field_b1 as 'bullets/bullet'
from data
where field_id = 9999999
for xml path('property'), root('info')
Which produces;
<info>
<property id="9999999">
<address>
<.... />
<.... />
etc
</address>
<price value="999999">
<... />
<... />
</price>
<bullets>
<bullet id="1">Converted Flat</bullet>
</bullets>
</property>
</info>
If I try to add ;
2 as 'bullets/bullet/@.id',
field_b2 as 'bullets/bullet'
to my statement to create the nested node with a different ID and value it
does not work. Does anyone know of a work around / solution?
Many thanks,
Pete
If I understood your problem correctly something like below should work for
you:
SELECT
1 as "bulets/bulet",
NULL as "bulets/dummy_elt",
2 as "bulets/bulet",
NULL as "bulets/dummy_elt",
3 as "bulets/bulet"
FOR XML PATH('many_bullets')
The NULL columns break the FOR XML PATH groupping logic.
This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete Roberts" <peter.roberts@.vebra.com> wrote in message
news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if anyone can please help me?...I am trying to produce an
> XML file using the new PATH function in SQL 2005 that has 'bullet' nodes
> nested as childs of a 'bullets' element. Each bullet (to a maximum of
> 10) is represented by a field in the database that is named as follows;
> field_b1, field_b2, field_b3, ....etc to field_b10
> I am using the below statement to produce the XML which currently only
> works when I only specify 1 attribute value eg. <bullets><bullet
> id="1">Parking</bullet><bullets>;
> select top 1
> field_id as '@.id',
> field_name as 'address/name',
> field_street as 'address/street',
> field_town as 'address/town',
> field_county as 'address/county',
> field_pc as 'address/postcode',
> field_price as 'price/@.value',
> field_stat as 'price/status',
> field_pq as 'price/qualifier',
> 1 as 'bullets/bullet/@.id',
> field_b1 as 'bullets/bullet'
> from data
> where field_id = 9999999
> for xml path('property'), root('info')
> Which produces;
> <info>
> <property id="9999999">
> <address>
> <.... />
> <.... />
> etc
> </address>
> <price value="999999">
> <... />
> <... />
> </price>
> <bullets>
> <bullet id="1">Converted Flat</bullet>
> </bullets>
> </property>
> </info>
> If I try to add ;
> 2 as 'bullets/bullet/@.id',
> field_b2 as 'bullets/bullet'
> to my statement to create the nested node with a different ID and value it
> does not work. Does anyone know of a work around / solution?
> Many thanks,
> Pete
>
>
|||Another solution is to make the bullet generation a subquery of its own (if
you do not know a priori how many you may have).
Best regards
Michael
"Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
> If I understood your problem correctly something like below should work
> for you:
> SELECT
> 1 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 2 as "bulets/bulet",
> NULL as "bulets/dummy_elt",
> 3 as "bulets/bulet"
> FOR XML PATH('many_bullets')
> The NULL columns break the FOR XML PATH groupping logic.
> This only works if you don't have "ELEMENTS XSINIL" FOR XML directive.
> Regards,
> Eugene
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Pete Roberts" <peter.roberts@.vebra.com> wrote in message
> news:e7%23ZgLycFHA.2688@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks for your help, the solutions offered are exactly what I was after!
Pete
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Od9QvEgdFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Another solution is to make the bullet generation a subquery of its own
> (if you do not know a priori how many you may have).
> Best regards
> Michael
> "Eugene Kogan [MSFT]" <ekogan@.online.microsoft.com> wrote in message
> news:OYVV8d6cFHA.2520@.TK2MSFTNGP09.phx.gbl...
>