How can i get XML using a Dynamic Query ?

i have a function like this, but i alwasys says wrong:

alter function GROUP_CONCAT(@.tableName varchar(100),@.groupByColumn varchar(100),@.targetColumn varchar(100),@.targetValue varchar(100))

returns varchar(1000)

as

begin

declare @.back varchar(1000)

declare @.sql varchar(1000)

set @.sql = 'select '+@.targetColumn+' from ' + @.tableName + ' where '+@.groupByColumn + ' = '''+@.targetValue + ''' for xml auto, root(''root'')'

declare @.x xml

set @.x = exec(@.sql)

return @.sql

end

I want to generate XML using a exec(...) , that is a Dynamic Query, but SQL Server 2005 always say something wrong with exec(....)

How can i fix it? Thanks

The reason is that you cannot use exec() as an expression. It is a statement that cannot be assigned.

If you just want to return it over TDS, do not assign it to a variable, otherwise assign it to a temp table inside the constructed node and then retrieve it from there.

Also, if you assign the FOR XML to a column or variable of type XML, it is better to add the type directive to the FOR XML as in FOR XML PATH, TYPE, ROOT('root'). That avoids unnecessary parsing.

Best regards

Michael

How can I get the result of a SQL PRINT Statement

I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.

I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.

I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.If you have the SQL Client tools then you can use Profiler to catch the statement that is sent as D-SQL. This is the best way to extract the D-SQLs that are getting executed rather than the Print statement ...

You can on the contrary use a Select and pass this D-SQL statement and catch it in your recordset returned ...

These are some of the options I can think of ...|||Where would I get the SQL Client tools?|||The simplest answer might be to create a DebugLog table and insert your dynamic SQL statement into it.

First, create the table to log your SQL statement:

CREATE TABLE
DebugLog
(
SQLStatement varchar(8000),
AddDate datetime DEFAULT GETDATE()
)

Next, add code to your stored procedure to insert your statement into the log file:

DECLARE @.SQLStatement Varchar(8000)
SET @.SQLStatement = 'SELECT * FROM test'
INSERT INTO DebugLog (SQLStatement) VALUES (@.SQLStatement)
EXEC(@.SQLStatement)

Next, take a look at the SQL statements that were executed (the most recent statement will be on top):

SELECT * FROM DebugLog ORDER BY AddDate DESC

Alternately, you could create an OUTPUT parameter in your sproc and pass the value of @.SQLStatement back to it and display it on your ASP.NET page.

Terri|||Thanks for the help. I'll try that. In point of fact the dynamic sql I'm trying to debug is using sp_executesql with an output parameter. I am trying to retrieve the recordset count based on the criteria parameters I'm passing to it from my asp.net application. In the application, the value returned is zero, which I know cannot be correct. Once I verify the proper assembly of the query, I can concentrate on the syntax of sp_executesql.|||Hi Terri,

When I first saw this post, I was going to respond that PRINT only writes to Query Analyzer. But upon researching it, I found that BOL has this to say about it in the Using PRINT topic:

"The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement."

This suggests that one could capture the error and do something with the PRINT text. But there can be multiple PRINT statements in a sproc, so I assume this would be an SqlException with nested exceptions.

Anyone ever done anything with this? Could be an interesting thing to explore.

Don|||yes, in my data access layer the error handling is like so.

try
{
oCmdExecute.ExecuteNonQuery(); // or ExecuteReader etc...
}
catch(SqlException ex)
{
foreach(SqlError err in ex.Errors)
{
strErrorString = strErrorString + "SqlError: #" + err.Number.ToString () + "\n" + err.Message;
}
strErrorString = strErrorString + "\n\nStored proc: " + sSql + "\n";
}

the SqlError collection will contain all the print statements, of course this only when the stored procedure fails, so you would have to call RAISERROR(' test error ', 16, 1) to force it into the exception block.

Note: that is RAISERROR not RAISEERROR go figure?|||Cool.

Note: that is RAISERROR not RAISEERROR go figure?

Yeah, that's been something odd since the statement was first added to T-SQL.

Don

how can I get the object_name

Hi,
how can I get the object_name from a given dbid?
I think the only solution is to use dynamic sql with cursors because I can
not run dynamic sql inside a UDF...
can you help me, please?
create table dbo.objects (
dbid smallint
, objectid int
)
go
insert into dbo.objects select 1, 1
insert into dbo.objects select 1, 2
insert into dbo.objects select 1, 6
insert into dbo.objects select 1, 8
insert into dbo.objects select 1, 9
insert into dbo.objects select 4, 1
insert into dbo.objects select 4, 2
insert into dbo.objects select 4, 3
go
select
dbid, objectid,
db_name(dbid) as dbname
-- , object_name (dbid, objectid) as object_name
from dbo.objects
Thank you!
ejrejr
What does that mean OBJECT_NAME of dbid? Have you looked in BOL?
"ejr" <namrek1@.hotmail.com> wrote in message
news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> how can I get the object_name from a given dbid?
> I think the only solution is to use dynamic sql with cursors because I can
> not run dynamic sql inside a UDF...
> can you help me, please?
> create table dbo.objects (
> dbid smallint
> , objectid int
> )
> go
> insert into dbo.objects select 1, 1
> insert into dbo.objects select 1, 2
> insert into dbo.objects select 1, 6
> insert into dbo.objects select 1, 8
> insert into dbo.objects select 1, 9
> insert into dbo.objects select 4, 1
> insert into dbo.objects select 4, 2
> insert into dbo.objects select 4, 3
> go
> select
> dbid, objectid,
> db_name(dbid) as dbname
> -- , object_name (dbid, objectid) as object_name
> from dbo.objects
> Thank you!
> --
> ejr
>|||object_name has only one parameter.
So you should use
object_name(objectid)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ejr" <namrek1@.hotmail.com> wrote in message
news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> how can I get the object_name from a given dbid?
> I think the only solution is to use dynamic sql with cursors because I can
> not run dynamic sql inside a UDF...
> can you help me, please?
> create table dbo.objects (
> dbid smallint
> , objectid int
> )
> go
> insert into dbo.objects select 1, 1
> insert into dbo.objects select 1, 2
> insert into dbo.objects select 1, 6
> insert into dbo.objects select 1, 8
> insert into dbo.objects select 1, 9
> insert into dbo.objects select 4, 1
> insert into dbo.objects select 4, 2
> insert into dbo.objects select 4, 3
> go
> select
> dbid, objectid,
> db_name(dbid) as dbname
> -- , object_name (dbid, objectid) as object_name
> from dbo.objects
> Thank you!
> --
> ejr
>|||Use the Information Schema to retrieve metadata wherever possible
rather than create your own catalogue.
If you need to store metadata yourself for any reason then use the
object name. Never store the object ID. The ID is fragile and may
change if the schema changes.
David Portas
SQL Server MVP
--|||yes,
if object_name accept dbid there is no problem at all; I commented the line
object_name (dbid, objectid) to have an idea what I wanted to do
I wanted to know if is possible to get an object name of any database from
an user table with a query like that...
Thank you,
--
ejr
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:Om2v#kmGFHA.3088@.tk2msftngp13.phx.gbl...
> object_name has only one parameter.
> So you should use
> object_name(objectid)
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "ejr" <namrek1@.hotmail.com> wrote in message
> news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
can
>|||ejr
I thint it is something like this
Declare @.Sql varchar(100)
while @.@.rowcount=0
begin
select @.sql='Use '+object_name(objectid) from objects
exec @.sql
select
dbid, objectid,
db_name(dbid) as dbname
, object_name (objectid) as object_name
from dbo.objects
end
Madhivanan|||Sorry It should be
Declare @.Sql varchar(100)
while @.@.rowcount=0
begin
select @.sql='Use '+db_name(dbid) from objects
exec @.sql
end
select
dbid, objectid,
db_name(dbid) as dbname
, object_name (objectid) as object_name
from dbo.objects
end
Madhivanan|||Madhavan,
Have you tested that?
Use <DbName> has no effect without Go.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109255172.695465.89630@.g14g2000cwa.googlegroups.com...
> Sorry It should be
>
> Declare @.Sql varchar(100)
> while @.@.rowcount=0
> begin
> select @.sql='Use '+db_name(dbid) from objects
> exec @.sql
> end
> select
> dbid, objectid,
> db_name(dbid) as dbname
> , object_name (objectid) as object_name
> from dbo.objects
> end
> Madhivanan
>