Getting "extremely complex query" error when using xml data type parameters

Good morning, I am trying to figure out, and understand, why I am receiving the error that I am when I use xml data type parameters. If you look at the proc below, I have a proc with 8 xml data type parameters. When i call this proc adn pass in the values for the parameters, I recieve is the following:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

In debugging, I found that I DON'T get this error if i have only 3 or 4 xml data type parameters, but if I add more than 4, I get the above error.

The proc looks like the following:

CREATE PROCEDURE dbo.dosomething

@.BegDate DateTime,

@.EndDate DateTime,

@.InClass xml,

@.InDept xml,

@.InCat xml,

@.InPayPer xml,

@.InEmp xml,

@.InLoc xml,

@.InLeave xml,

@.InSalClass xml

AS

SELECT blah, blah

FROM blah

WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above

I call the stored proc by the following...and notice that i am not passing anything very large for each value...

exec dosomething

'20060101','20060101',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

,N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

Does anyone have any suggestions as to what might be causing this? Am i doign my compare in my proc incorrectly? Any insight would be greatly appreciated...

Scott

Hi Scott

This error message normally means that you should rewrite your query. Could you please post a complete repro to either my work address (mrys at the microsoft.com address) or post it here and we will take a look.

Thanks

Michael

PS: Sorry I thought I posted this reply yesterday but my travel connectivity at the moment seems to be shakey.

|||

I guess the problem is not you have lots xml data type parameters. It's caused by the WHERE clause in your query :

"WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above"

You can try to define some temp tables and shred the ID values in the xml into those tables. Jojn those temp tables in your query.

GetReportParameters - How can I get the default value of the parameter?

I'm using the GetReportParameters method to get the parameters of my report.
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>

geting money type from sql

hi

i am retriving value from sql server database like

select cast(round(12345674.8658,2,0) as decimal(20,2))

output is 12345674.87
but i want to get like 12,345,674.87
any function is there?do that in your code. Or cast to Money not decimal.

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

get time in SQL server

Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgs
max
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs
|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:

>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1-- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

get time in SQL server

Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgsmax
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1 -- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
>.
>

Get the smallest timespan between two entries

Hi all,
we have a table with a column of type "datetime"
We want to get the smallest timespan between two entries.
Now we get this timespan with the following query (it works but it's to
slow, it runs 5 secs with 250000 entries):
select min(Datediff(minute,a.rectime,b.rectime))
from dbo.value a, dbo.value b
where b.rectime = ( select min(rectime) from dbo.value
where rectime > a.rectime )
Any idea? Thanks in advance,
Mike
On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:

>Hi all,
>we have a table with a column of type "datetime"
>We want to get the smallest timespan between two entries.
>Now we get this timespan with the following query (it works but it's to
>slow, it runs 5 secs with 250000 entries):
>select min(Datediff(minute,a.rectime,b.rectime))
>from dbo.value a, dbo.value b
>where b.rectime = ( select min(rectime) from dbo.value
> where rectime > a.rectime )
>Any idea? Thanks in advance,
>Mike
Hi Mike,
Try changing the query to
SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
FROM dbo.value AS a, dbo.value b
WHERE b.rectime > a.rectime
You might also add something like
AND b.rectime < DATEADD(minute, a.rectime, 200)
where you change the 200 to a value that you know to be higher that the
timespan you are looking for, but low enough to greatly reduce the number
of matches between the a and b version of the value table.
If that doesn't work, look at your indexes. This query would greatly
benefit from an index on rectime (or rectime plus extra columns). If the
rate of change of this table is not too high and a small performance hit
on inserts, updates and deletes is acceptable, create a nonclustered index
on only rectime - that should yield the best possible performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This might be more efficient:
select top 1
-- add WITH TIES if you select additional columns and want duplicates
datediff (minute, T2.rectime, min(T1.rectime)) as timeDiffMinutes
from yourTable T1 join yourTable T2
on T1.rectime > T2.rectime
group by T2.rectime
order by min(T1.rectime) - T2.rectime
Steve Kass
Drew University
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||Oops - the suggestion I gave doesn't give a good query plan. This is
probably much better:
select top 1
datediff(minute,rectime, Nextrectime) as TimeDiff
from (
select
T1.rectime,
(select top 1 T2.rectime
from yourTable T2
where T2.rectime> T1.rectime
order by T2.rectime) as Nextrectime
from yourTable T1
) T
where Nextrectime is not null
order by Nextrectime - rectime
[and I shouldn't have replied to your post specifically - sorry]
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||On Thu, 28 Oct 2004 20:31:00 -0400, Steve Kass wrote:

>and I shouldn't have replied to your post specifically - sorry
Hi Steve,
De nada. As long as the original poster sees it, all's well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get Text From Image Data Type Column By using FTS

Hi,
I m using Sql Server 2000 Full Text Search. I hv a table with a
column of image data type .
Full Text Search is working fine on this column. But it is only
returning Binary Data.
I want to show Search results in text format. I want to get my
Searched pharase/word in simple text format. Is this possible by using
FTS?
Regards
Sohaib
No, its not possible. You would have to extract the text value from the
image column and store it in a separate varchar(max) or text datatype column
and then iterate its contents for the hit.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Sohaib" <hafizsohaib@.gmail.com> wrote in message
news:1159791947.351151.274960@.k70g2000cwa.googlegr oups.com...
> Hi,
> I m using Sql Server 2000 Full Text Search. I hv a table with a
> column of image data type .
> Full Text Search is working fine on this column. But it is only
> returning Binary Data.
> I want to show Search results in text format. I want to get my
> Searched pharase/word in simple text format. Is this possible by using
> FTS?
> Regards
> Sohaib
>

Get rid of column name as tag when query for xml

here is my query (Email is of type XML):
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Ohh yes my man! It did work just as expected thou performance was a bit slow
.
I'll test it on another server to see it that's query issue or stress testin
g
they might be doing on the server. thanks!
"Martin Honnen" wrote:

> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>|||I read your post with similiar suggestions you gave someone else but couldn'
t
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:

> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
>|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:

> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.

Get rid of column name as tag when query for xml

here is my query (Email is of type XML):
Select Email
From EmailStorage
Where Status=1
For XML Auto
This returns:
<EmailStorage>
<Email><value of Email></Email>
<Email><value of Email></Email>
</EmailStorage>
Since <value of Email> is valid XML I would like to have this:
<EmailStorage>
<value of Email>
<value of Email>
</EmailStorage>
How? TIA!
Art wrote:
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
Does
SELECT (
SELECT [Email].query('.')
FROM EmailStorage
WHERE Status = 1
FOR XML PATH, TYPE
).query('<EmailStorage>{row/node()}</EmailStorage>')
do what you want?
It is a bit convoluted but currently I can't think of an easier way.
Maybe someone else will come up with an easier query.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Ohh yes my man! It did work just as expected thou performance was a bit slow.
I'll test it on another server to see it that's query issue or stress testing
they might be doing on the server. thanks!
"Martin Honnen" wrote:

> Art wrote:
> Does
> SELECT (
> SELECT [Email].query('.')
> FROM EmailStorage
> WHERE Status = 1
> FOR XML PATH, TYPE
> ).query('<EmailStorage>{row/node()}</EmailStorage>')
> do what you want?
> It is a bit convoluted but currently I can't think of an easier way.
> Maybe someone else will come up with an easier query.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
>
|||Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
> here is my query (Email is of type XML):
> Select Email
> From EmailStorage
> Where Status=1
> For XML Auto
> This returns:
> <EmailStorage>
> <Email><value of Email></Email>
> <Email><value of Email></Email>
> </EmailStorage>
> Since <value of Email> is valid XML I would like to have this:
> <EmailStorage>
> <value of Email>
> <value of Email>
> </EmailStorage>
> How? TIA!
>
|||I read your post with similiar suggestions you gave someone else but couldn't
make it work. This one worked great. Thanks!
Now, I need to make this query work with SQL Adapter for biztalk. First time
the wizard is run (to generate the schema for this document) I need to
specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot of
problems with that.
To recap; I'd need something like this (pseudo code)
<Your Query> For XML Auto, XMLData
How can I do this?
"Pawel Potasinski" wrote:

> Select Email '*'
> From EmailStorage
> Where Status=1
> For XML path(''), root('EmailStorage')
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:AFA678AB-3CFF-46C4-B3D7-C762BC0A4BE3@.microsoft.com...
>
>
|||What is the goal exactly? Is you goal just adding XML Schema inline to the
query result? If so, let me know. If you really have to use FOR XML AUTO,
there will be a problem to get the result just as you received from my
prevous query.
Oh, and BTW, the next question is: should XML Schema include elements and
attributes of Email column (of xml data type) or should this column be put
in XSD just as xml data type element (but no internal structure of Email
data will be shown)?
Regards
Pawel Potasinski
Uytkownik "Art" <Art@.discussions.microsoft.com> napisa w wiadomoci
news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...[vbcol=seagreen]
>I read your post with similiar suggestions you gave someone else but
>couldn't
> make it work. This one worked great. Thanks!
> Now, I need to make this query work with SQL Adapter for biztalk. First
> time
> the wizard is run (to generate the schema for this document) I need to
> specify For XML Auto, XMLData (or XMLSchema) directives. I'm having a lot
> of
> problems with that.
> To recap; I'd need something like this (pseudo code)
> <Your Query> For XML Auto, XMLData
> How can I do this?
> "Pawel Potasinski" wrote:
|||The goal here is to make your query work with SQL Adapter (in BizTalk).
Surprisingly, even though this is MS technology and works with XML, SQL
Adapter is rather primitive. One of the first steps in configuration (of the
SQL adapter) is formulating the query so that the adapter configuration
wizard can create a schema based on the output of a query. Two directives
need to be specified in order for the configuration wizard to work, namely
FOR XML AUTO, XMLDATA.
Now, when you look at your query
Select Email '*'
From EmailStorage
Where Status=1
For XML path(''), root('EmailStorage')
How do I incorporate FOR XML AUTO, XMLDATA so that it works with the SQL
Adapter.
"Pawel Potasinski" wrote:

> What is the goal exactly? Is you goal just adding XML Schema inline to the
> query result? If so, let me know. If you really have to use FOR XML AUTO,
> there will be a problem to get the result just as you received from my
> prevous query.
> Oh, and BTW, the next question is: should XML Schema include elements and
> attributes of Email column (of xml data type) or should this column be put
> in XSD just as xml data type element (but no internal structure of Email
> data will be shown)?
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "Art" <Art@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:B3295220-522C-483A-889C-4F602F5FF7D0@.microsoft.com...
>
>
|||Pawel,
BTW, can your query be modified to accept a parameter?
From:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root('EmailStorage')
To:
Select Email '*'
From EmailStorage
Where Status=@.SomeStatus
For XML path(''), root(@.SomeStatus)
I tried it but it doesn't work.

Get Primary Key Column

Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards

Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||

Well no, I am running this script against the IssueTrackerStarterKit database. I am getting that table for sure, listing al columns, but I would like to know if I can know the primary key programmatically !!

regards

|||Run a search for sp_helpindex in SQL Server BOL(books online). And your select statement is using SQL- DMO(data management object) which is Microsoft property and all service packs makes changes which will make you code out dated because the tables have moved. Go to Query Analyzer open the object browser and right click on your table and you will have options of select statements without Syscolumns and Sysobjects. Hope this helps.|||Hi,
You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below

declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid

I hope this helps
Eralper
http://www.kodyaz.com

|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||

The Systems tables have a poster but since SQL Server 2005 is almost here Microsoft have removed it and have made others to remove it. But they are all in the SQL Server Master database with the System title next to them. A good book for starting SQL Server for a developer is SQL Server a beginner's guide by Dusan Petkovic but don't let the title fool you it is not really a beginner's book it was given that title because English is not the writer's first language, he is German. The book covers everything usefull to a developer including complex configurations and the language, he also covered XML and Full text that most other books did not cover. I have a lot of them I call crappy but his book is worth the money for a C# developer, while SQL Server Developer's guide is good for VB developer. Try this link for T-SQL tutorial but I have sent you an ANSI SQL tutorial in the mail. Dowload the file because the site is now part of a consolidator so that file may be removed soon. Hope this helps.

http://www.mssqlserver.com/tsql/

|||

The Systems tables have a poster but since SQL Server 2005 is almosthere Microsoft have removed it and have made others to remove it. Butthey are all in the SQL Server Master database with the System titlenext to them.

Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.

Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.

Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.

Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.