Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advanceHello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id> )
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL b
y
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id> )
> For more detailed information about how to query the XML date using XQuery
,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net...005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">https://www.microsoft.com.nsatc.net...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Lines: 33
X-Tomcat-ID: 34576541
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: Microsoft
X-Tomcat-NG: microsoft.public.sqlserver.xml
NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
Xref: leafnode.mcse.ms microsoft.public.sqlserver.xml:1829
Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)sql

Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advance
Hello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id>)
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL by
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id>)
> For more detailed information about how to query the XML date using XQuery,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
> px
> Is this article you want?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Query value in between two columns

Hi All,

I am new to Sql and I want to know if a given value lets say its 225
is within ranges defined in the table.

I have a table TblControl. Data type of both field is int.

StartRange End Range
0 100
200 300
500 600
900 950

(Sql server 2000 on window 2000)

Thanks
-MaxOn 26 Aug 2004 12:13:48 -0700, Max wrote:

>Hi All,
>I am new to Sql and I want to know if a given value lets say its 225
>is within ranges defined in the table.
>I have a table TblControl. Data type of both field is int.
>StartRange End Range
>0 100
>200 300
>500 600
>900 950
>(Sql server 2000 on window 2000)
>Thanks
>-Max

Hi Max,

You can test this with BETWEEN. Example:

SELECT StartRange, EndRange
FROM Control
WHERE 225 BETWEEN StartRange AND EndRange

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,

It solved my problem.

-Maxsql

Query to see if an int field starts with a certain number

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!Convert it to string, perform a substring, and then do your comparison.

Perhaps: substring(cast([thisfield] as varchar(50)),1,1)|||I don't know how the performance of this will compare, but if thisfield is non-negative, this should work as well:

[thisfield] / power(10, cast(log10([thisfield] as int))

Cheers,
-Isaac
|||

I hate to ask this, but the giant pink elephant in the room is "how do you have an int that doesn't start with a number?" What it sounds like you have is a column of string values that may or may not be an integer, and you want to see if the first character of the string is a number, right? For this it is:

thisColumn like '[1234567890]%'

But if the column is supposed to only contain integers, the best way to make sure that they are integers is to create the column using an integer datatype.

Query to return latest record, multiple join fields

Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql

Query to return certain result if meet requirement

Hi,
Is there anyway to query 7 table from MSSQL , If any of the table consist of 0 then the value 0 is return. If all 7 table dont consist 0 value in the table, value 1 is return

Regards
Mandrakeooi

Quote:

Originally Posted by Mandrakeooi

Hi,
Is there anyway to query 7 table from MSSQL , If any of the table consist of 0 then the value 0 is return. If all 7 table dont consist 0 value in the table, value 1 is return

Regards
Mandrakeooi

Try

select case count(*) when 0 then 1 else 0 end
from (
select myColumn from table1 where myColumn=0
union all
select myColumn from table2 where myColumn=0
union all
...
select myColumn from table7 where myColumn=0)

query to parse out values from one column into different columns

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.Look up CROSSTAB queries in Books Online.select userid,
max(case recordtag when 1 then record end) as firstname,
max(case recordtag when 2 then record end) as middleinitial,
max(case recordtag when 3 then record end) as suffix
from [YourTable]
group by userid|||Thanks for the info. I'll let you know how I do.|||I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

Query to get column name with specific value

Here is tested schema

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into testtable values ('123','pass',null,'fail','skip')
insert into testtable values ('456','fail',null,'pass','skip')
insert into testtable values ('789',null,'fail','skip','pass')
insert into testtable values ('345','pass','pass','pass','fail')

I would like to fetch the COLUMNNAME where the value is fail.
Basically I need to know which test failed, test1, test2, test3 or
test4?

Is this possible?One method:

SELECT CASE
WHEN test1 = 'fail' THEN 'test1'
WHEN test2 = 'fail' THEN 'test2'
WHEN test3 = 'fail' THEN 'test3'
WHEN test4 = 'fail' THEN 'test4'
END AS Test
FROM testtable

This could also be accomplished with a rather ugly dynamic SQL script but
I'd rather not go there. You might consider revising your schema to
eliminate the repeating data. It's a lot easier to query data when your
data is in 1NF. Suggested alternative:

CREATE TABLE TestTable
(
SerialNumber char (12) NOT NULL,
TestNumber int NOT NULL,
TestResult varchar(10),
CONSTRAINT PK_TestTable PRIMARY KEY(SerialNumber, TestNumber)
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kj" <kjaggi@.hotmail.com> wrote in message
news:665416be.0409071642.3710dac0@.posting.google.c om...
> Here is tested schema
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[TestTable]
> GO
> CREATE TABLE [dbo].[TestTable] (
> [SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testtable values ('123','pass',null,'fail','skip')
> insert into testtable values ('456','fail',null,'pass','skip')
> insert into testtable values ('789',null,'fail','skip','pass')
> insert into testtable values ('345','pass','pass','pass','fail')
>
> I would like to fetch the COLUMNNAME where the value is fail.
> Basically I need to know which test failed, test1, test2, test3 or
> test4?
> Is this possible?|||Thanks. I have already changed the schema. This is legacy stuff I am
trying to clean up but I needed to migrate the data to the new schema.

Query to Get and Set Image data type Value in SQL Query

Hi

I'm using Sharepoint Services in my application.My database is SQl server 2005 My Sharepoint site using one database there is one table called docs. In this table one column is called MetaInfo and its datatype is Image.
My Question is How to write a Query to Get and Set Image data type Value.I want to execute the Query in SQL Itself. If anyone knows the answer Please let me to know.
Thanks

Regards,
Vinoth

It is not clear what you want to do exactly. But you can just use regular insert/update statements to modify image data. You need to specify the image data as a binary string. Ex:

update t
set imagecol = 0xfff3030939393910

If you have the data in another table, then you can use UPDATE with the T-SQL syntax to copy value from one table to another. Ex:

update t
set imagecol = t1.imagecol
from t1
where t1.i = t.i

Also, you can have SPs with text/ntext/image data type as parameters so you can pass these values directly from the client side and use them in statements like above. Note that you cannot manipulate the variable directly in T-SQL.

For more control on the modifications, you can use UPDATETEXT. See also PATINDEX and SUBSTRING topics in Books Online.

Query to find default value for a column

Where are the default values for a column stored in SQL Server. I thought
they would be in the syscolumns table, but I cannot find them there, nor
anywhere else for that matter.
Thanks,
Jasonget the cdefault from syscolumns and
select from syscomments for the id
syscomments.id = syscolumns.cdefault.
Let me know if this helps
"JasonDWilson" wrote:

> Where are the default values for a column stored in SQL Server. I thought
> they would be in the syscolumns table, but I cannot find them there, nor
> anywhere else for that matter.
> Thanks,
> --
> Jason|||Try this:
select distinct substring(object_name(c.id), 1, 50) 'Table Name'
, substring(c.name, 1, 40) 'Column Name'
, object_name(c.cdefault)'Default Name'
from syscolumns c,
syscomments m
where m.id = c.cdefault
Perayu
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:E45274CE-E559-40E0-805E-7DD5037D90BD@.microsoft.com...
> Where are the default values for a column stored in SQL Server. I thought
> they would be in the syscolumns table, but I cannot find them there, nor
> anywhere else for that matter.
> Thanks,
> --
> Jason

Query to create a countable list using a single value from table

Hi,
from a single numeric value, say 'n', in a field in a table, i'd like to create a SELECT query that produces a list from 1 to n, e.g
if the value was 6, I would like selected back:
1
2
3
4
5
6
Any pointers?
GregWhy?|||I'm developing a system using an Enterprise product, Metastorm e-work, that sits above SQL server. Given a Quantity value, I want to make a drop down list of the values from 1 to Quantity using the form designer that comes with the product, to allow a user to change this value.

I'd like to do something more sophisticated than my posted request, but if there is SQL code available to do this, it will get me on the way.

g.|||CREATE a numbers TABLE...

SELECT Col1 from myTable99
WHERE Col1 <= @.value

You'll use it for more than just this...|||Cheers.|||Originally posted by Brett Kaiser
You'll use it for more than just this...

so true

similar example: generating sequential dates
see The integers table (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid569539_tax285649,00.html)
(site registration may be required, but it's free)

Query to ADD/SELECT values from an SQL

Hello.

I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.

Either way, I need the row returned at the end of the query.

I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.

For example:

User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.

Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.

Does anyone know how I may accomplish this?something like this:

create proc MyProc
(
 @.countryid int out,
 @.countryname nvarchar(50)
)
set @.countryid = -1
select @.countryid = CountryId
from MyTable
where CountryName = @.countryname
if (@.countryid = -1)
begin
 insert into MyTable (CountryName)
 values (@.countryname)
 select @.countryid = scope_identity()
end

|||or

IF NOT EXISTS ( Select countryId from from MyTable where CountryName = @.countryname)
 INSERT INTO MyTable (CountryName) values (@.countryname)

hth

Query Timeout

Hi,
I want to increase query timeout value but I don't know how to do this.
Please
advise me where to change query timeout value. Is it possible to set this
for a
particular database?
Thanks,
Soura.
Hi Sounder,
I think we can increase the Timeout for a whole server(all DB's in that
Server)
Right click on the server name => Select properties => Choose the
Connections tab => Increase the query timeout value. (The value is in
seconds. Set it to 0 for unlimited time) => Click OK.
Regards,
Herbert
"SouRa" wrote:

> Hi,
> I want to increase query timeout value but I don't know how to do this.
> Please
> advise me where to change query timeout value. Is it possible to set this
> for a
> particular database?
> Thanks,
> Soura.
>
|||Hi Herbert,
Thanks for your reply. There is an option in replication job to change the
number of attempts to connect subscriber with some specified time interval.
We can modify the information. Similarly is there any option to change query
time out (in any replication jobs,subscription properties or any where else)?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think we can increase the Timeout for a whole server(all DB's in that
> Server)
> Right click on the server name => Select properties => Choose the
> Connections tab => Increase the query timeout value. (The value is in
> seconds. Set it to 0 for unlimited time) => Click OK.
>
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
I think u need to increase the timeout of merge agent....
Open the Replication monitor in that choose the Merge agent => Right-click
appropriate publication and select Agent Profiles... => Click the New Profile
button to create the new profile with the appropriate QueryTimeout value =>
Choose the newly created profile.
Hope this will help..
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. There is an option in replication job to change the
> number of attempts to connect subscriber with some specified time interval.
> We can modify the information. Similarly is there any option to change query
> time out (in any replication jobs,subscription properties or any where else)?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Hi Herbert,
Thanks for your reply. Actually I getting time out error in my application
frequently.
So I decided to increase the query time out value. I found a profile High
volume server to server profile (in merge agent profiles), any idea about
this?
Thanks,
Soura.
"Herbert" wrote:
[vbcol=seagreen]
> Hi Sounder,
> I think u need to increase the timeout of merge agent....
> Open the Replication monitor in that choose the Merge agent => Right-click
> appropriate publication and select Agent Profiles... => Click the New Profile
> button to create the new profile with the appropriate QueryTimeout value =>
> Choose the newly created profile.
> Hope this will help..
> Regards,
> Herbert
>
> "SouRa" wrote:
|||Hi Sounder,
Since ur getting timeout in application, i think u have to increase the
query timeout of Server instead of replication agent.
by my previous post step u can set the query timeout of Server.
Regards,
Herbert
"SouRa" wrote:
[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||SouRa, rather than adjusting the query timeout setting, why dont you try to
figure out why the queries are taking so long?
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:14E41307-124F-40A1-A1ED-97E4E9DC7B81@.microsoft.com...[vbcol=seagreen]
> Hi Herbert,
> Thanks for your reply. Actually I getting time out error in my application
> frequently.
> So I decided to increase the query time out value. I found a profile High
> volume server to server profile (in merge agent profiles), any idea about
> this?
> Thanks,
> Soura.
> "Herbert" wrote:
|||Soura,
the querytimeout as you seem to be requesting - for all access to the
server - is really a client-side setting. Herbert is right that
replicationwise we can set this in the agent profile, but as you seem to be
requesting more generally, it'll be a property of the command object if you
are using ADO / ADO.NET. Usually this doesn't need changing but there are
legitimate cases for it. I also concur with Chris - you might want to start
by looking to see if you have blocking issues that can be remedied by using
NOLOCK or a reporting server or better indexes for optimization etc. Not
always appropriate but still worth verifying.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
We have modified the Merge agent profile to "High end server to server.."
After couple of days of this change, we came across a new error message "the
process is running and waiting for a response from one of the backend
connections", after this message, we noticed the normal error message "
...clean up".
Please provide any other areas we may need to review.
We "push" the subscription to the Subscriber. All our foreign keys are not
enabled for replication.
Thanks,
Soura
"Paul Ibison" wrote:

> Soura,
> the querytimeout as you seem to be requesting - for all access to the
> server - is really a client-side setting. Herbert is right that
> replicationwise we can set this in the agent profile, but as you seem to be
> requesting more generally, it'll be a property of the command object if you
> are using ADO / ADO.NET. Usually this doesn't need changing but there are
> legitimate cases for it. I also concur with Chris - you might want to start
> by looking to see if you have blocking issues that can be remedied by using
> NOLOCK or a reporting server or better indexes for optimization etc. Not
> always appropriate but still worth verifying.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||SouRa,
the first error message is not normally anything to be concerned about. I
often get this when initializing. In my case it is the application of the
indexes that causes this message. Have a loog at the processes and use bdcc
inputbuffer to see exactly what is happening during this time. Please could
you post up the complete text of the second error message.
Cheers,
Paul
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:800312A8-DE48-44DA-9B1C-705323B6EA6E@.microsoft.com...[vbcol=seagreen]
> Hi,
> We have modified the Merge agent profile to "High end server to server.."
> After couple of days of this change, we came across a new error message
> "the
> process is running and waiting for a response from one of the backend
> connections", after this message, we noticed the normal error message "
> ..clean up".
> Please provide any other areas we may need to review.
> We "push" the subscription to the Subscriber. All our foreign keys are not
> enabled for replication.
> Thanks,
> Soura
> "Paul Ibison" wrote:

Query Time Limit?

I know I can use SET QUERY_GOVERNOR_COST_LIMIT to prevent a query from executing if its estimated execution time exceeds the value of the specified time in seconds, but is there some way to have a query stop executing (and raise an error) if the time exceeds a specified time? For example, if the query takes longer than 20 seconds, have it stop executing and raise an error?

Thanks,

-Dave

It prevents query from executing based on the cost rather than controlling resources.

BOL

'If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely.

"Query cost" refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

|||

If you are referring to having the ability to control any one specific query -that is not possible.

QUERY_GOVERNOR_COST_LIMIT effects ALL queries executed on the server. It prevents a query from executing IF the estimated time exceeds the limit. Say the QUERY_GOVERNOR_COST_LIMIT was 120 seconds, the estimated plan was 119 seconds -the query would then execute EVEN if the resulting time required exceeded 120 seconds. As far as I am aware, there is no way to have an executing query abort at some predetemined elapsed time value.

Of course, from an application, it might be possible to spawn a thread that executed a query against SQL Server, and then the parent thread could abort the executing thread after a predetermined time. But that could be messy...