Forcing use of an index

Hi
Can you force a query to use a particular index. IF so please let me
know the sql statement syntax.
thanks
NewishHi
Take a look at "index hints" topic in the BOL
"Newish" <ahussain3@.gmail.com> wrote in message
news:1164198675.592286.139920@.m7g2000cwm.googlegroups.com...
> Hi
> Can you force a query to use a particular index. IF so please let me
> know the sql statement syntax.
> thanks
> Newish
>

Forcing Query Plans

Is it possible to force a query plan on a Stored procedue. I have attempted the following and i receive a Incorrect syntax near the keyword 'OPTION'. Any ideas?

EXEC testdatabases..testprocedure

OPTION (USE PLAN N'
<ShowPlanXML xmlns=
"http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5"
Build="9.00.1187.07">
<BatchSequence>
<Batch>
<Statements>
...
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
')
GO

You could use OPTION and USE PLAN only with SELECT/INSERT/DELETE/STATEMENTS. So move your plan in body of stored procedures.

If you couldn't change your stored procedure use Plan Guide by sp_create_plan_guide http://msdn2.microsoft.com/en-us/library/ms179880.aspx

FOR XML on Server 7

One of my remote servers is still SQL Server 7.
I was trying FOR XML AUTO query on it,
and it giving me Incorrect syntax near 'XML'.
The question is if XML queries can't be done on Server 7 at all,
or there is a way to make it working?
Thanks,
Michael
FOR XML does not work on SQL Server 7 or older.
But if your local server is 2000 or newer, you could use the FOR XML on the
local instance on the result of the remote query.
HTH
Michael
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
> One of my remote servers is still SQL Server 7.
> I was trying FOR XML AUTO query on it,
> and it giving me Incorrect syntax near 'XML'.
> The question is if XML queries can't be done on Server 7 at all,
> or there is a way to make it working?
> Thanks,
> Michael
>
|||I remember MS did offer a beta when 2000 was not out yet,
it was an ISAPI .dll that offered the same functionality
as in 2000 but for 7. But that was late '99. You'd have a
rough time tracking it down.
>--Original Message--
>FOR XML does not work on SQL Server 7 or older.
>But if your local server is 2000 or newer, you could use
the FOR XML on the[vbcol=seagreen]
>local instance on the result of the remote query.
>HTH
>Michael
>"MichaelK" <michaelk@.gomobile.com> wrote in message
>news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
7 at all,
>
>.
>
|||That ISAPI is not supported and cannot be used through linked servers...
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0f8801c4f1e0$9a9daed0$a301280a@.phx.gbl...[vbcol=seagreen]
>I remember MS did offer a beta when 2000 was not out yet,
> it was an ISAPI .dll that offered the same functionality
> as in 2000 but for 7. But that was late '99. You'd have a
> rough time tracking it down.
> the FOR XML on the
> 7 at all,

FOR XML on Server 7

One of my remote servers is still SQL Server 7.
I was trying FOR XML AUTO query on it,
and it giving me Incorrect syntax near 'XML'.
The question is if XML queries can't be done on Server 7 at all,
or there is a way to make it working?
Thanks,
MichaelFOR XML does not work on SQL Server 7 or older.
But if your local server is 2000 or newer, you could use the FOR XML on the
local instance on the result of the remote query.
HTH
Michael
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
> One of my remote servers is still SQL Server 7.
> I was trying FOR XML AUTO query on it,
> and it giving me Incorrect syntax near 'XML'.
> The question is if XML queries can't be done on Server 7 at all,
> or there is a way to make it working?
> Thanks,
> Michael
>|||I remember MS did offer a beta when 2000 was not out yet,
it was an ISAPI .dll that offered the same functionality
as in 2000 but for 7. But that was late '99. You'd have a
rough time tracking it down.
>--Original Message--
>FOR XML does not work on SQL Server 7 or older.
>But if your local server is 2000 or newer, you could use
the FOR XML on the
>local instance on the result of the remote query.
>HTH
>Michael
>"MichaelK" <michaelk@.gomobile.com> wrote in message
>news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
7 at all,
>
>.
>|||That ISAPI is not supported and cannot be used through linked servers...
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0f8801c4f1e0$9a9daed0$a301280a@.phx.gbl...
>I remember MS did offer a beta when 2000 was not out yet,
> it was an ISAPI .dll that offered the same functionality
> as in 2000 but for 7. But that was late '99. You'd have a
> rough time tracking it down.
> the FOR XML on the
> 7 at all,

FOR XML not working in a subquery

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.

select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a

The idea is to return 2 columns:
accountid
xmldata (address as xml)

Assuming the fields are correct, any ideas on what the problem might be?(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.
>
>
select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a
>
The idea is to return 2 columns:
accountid
xmldata (address as xml)
>
Assuming the fields are correct, any ideas on what the problem might be?

The problem is simply that you try to achieve something which is not
possible in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well thanks for such a detailed explanation of the answer.

Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?

Erland Sommarskog wrote:

Quote:

Originally Posted by

(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.

select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a

The idea is to return 2 columns:
accountid
xmldata (address as xml)

Assuming the fields are correct, any ideas on what the problem might be?

>
The problem is simply that you try to achieve something which is not
possible in SQL 2000.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

Well thanks for such a detailed explanation of the answer.
>
Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?

In SQL 2000, FOR XML can only be used in the outermost SELECT, to
produce a one-row, one-column result set. It cannot be used in subqueries,
derived tables. A good reason for this is that in SQL 2000, there is
not really any xml data type. Yet the result set returned by a FOR
XML clause is not really any of the SQL Server data types - it's XML.
It works thanks to some special hooks in the client APIs that can see
that here comes a one-row, one-column result set, which is an XML
document. There is no plumbing to permit FOR XML be composed with other
sorts of data.

This is all different in SQL 2005, where XML is a first-class citizen.

See also Books Online, the topic
XML and Internet Support ->
Retrieving and Writing XML Data ->
Retrieving XML Documents Using FOR XML ->
Guidelines for Using the FOR XML Clause

this topic lists a number of limitations with FOR XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

for xml explicit problem with a self join

What is the syntax for converting a simple sql statement like this:
SELECT
PortalDirectory.PortalDirectoryUserID,
PD1.AttributeValue
FROM
PortalDirectory
INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '12345')
... into a FOR XML EXPLICIT statement?
This is what i've tried but it doesn't work, i always get this error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PortalDirectory' does not match with a table name or
alias name used in the query.
SELECT
1 AS TAG,
NULL AS PARENT,
PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
NULL AS [PD1!2!Value]
UNION ALL SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [PortalDirectory!1!ID],
PD1.AttributeValue AS [PD1!2!Value]
FROM
PortalDirectory
INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
FOR XML EXPLICIT
there must be something pretty basic i'm missing, as i've looked everywhere
and noone mentions what to do when you have 2 tables that are really the
same one (if there is a special name for what i'm doing i can't think of
it!!)
Thanks
Paul
Every select statement in a UNION ALL needs its own from clause. Best is to
first write the query without the FOR XML aspects.
For some more complex (but still small) FOR XML explicit queries, see the
FOR XML in SQLServer 2005 whitepaper at
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.
Best regards
Michael
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:2p16plFfhntkU1@.uni-berlin.de...
> What is the syntax for converting a simple sql statement like this:
> SELECT
> PortalDirectory.PortalDirectoryUserID,
> PD1.AttributeValue
> FROM
> PortalDirectory
> INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID
> =
> PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '12345')
> ... into a FOR XML EXPLICIT statement?
> This is what i've tried but it doesn't work, i always get this error:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'PortalDirectory' does not match with a table name or
> alias name used in the query.
> SELECT
> 1 AS TAG,
> NULL AS PARENT,
> PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
> NULL AS [PD1!2!Value]
> UNION ALL SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL AS [PortalDirectory!1!ID],
> PD1.AttributeValue AS [PD1!2!Value]
> FROM
> PortalDirectory
> INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
> PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '11351')
> FOR XML EXPLICIT
>
> there must be something pretty basic i'm missing, as i've looked
> everywhere
> and noone mentions what to do when you have 2 tables that are really the
> same one (if there is a special name for what i'm doing i can't think of
> it!!)
>
> Thanks
> Paul
>
|||Doh, i can't believe i missed that about the FROM clause. Thank you.
Nevertheless, it still doesnt work properly.
So i now have this
SELECT
1 AS TAG,
NULL AS PARENT,
PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
NULL AS [PD1!2!Value]
FROM
PortalDirectory INNER JOIN PortalDirectory PD1
ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
UNION ALL SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [PortalDirectory!1!ID],
PD1.AttributeValue AS [PD1!2!Value]
FROM
PortalDirectory INNER JOIN PortalDirectory PD1
ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
--FOR XML EXPLICT
Looking at the universal table that this produces, i get duplicate rows for
the first table one for each actual result row (which meansi i get lots of
<PortalDirectory ID="14"/><PortalDirectory ID="14"/>... )
Now i could change the first SELECT to be SELECT DISTINCT (and it does work
fine) but this can't be the way to do it surely - it just feels like a
workaround bad code. Adding in ordering doesnt make a difference - there
are simply too many rows being put into the universal table.
Michael Rys [MSFT] wrote:
> Every select statement in a UNION ALL needs its own from clause. Best
> is to first write the query without the FOR XML aspects.
> For some more complex (but still small) FOR XML explicit queries, see
> the FOR XML in SQLServer 2005 whitepaper at
>
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.[vbcol=seagreen]
> Best regards
> Michael
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:2p16plFfhntkU1@.uni-berlin.de...
|||A quick addition to my previous post - i could also do SELECT TOP 1 in my
first SELECT statement which would also work and be better than SELECT
DISTINCT however i still have the feeling that i'm doing something
fundamentally wrong or there is a better way.
Paul wrote:
> Doh, i can't believe i missed that about the FROM clause. Thank you.
> Nevertheless, it still doesnt work properly.
> So i now have this
> SELECT
> 1 AS TAG,
> NULL AS PARENT,
> PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
> NULL AS [PD1!2!Value]
> FROM
> PortalDirectory INNER JOIN PortalDirectory PD1
> ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '11351')
> UNION ALL SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL AS [PortalDirectory!1!ID],
> PD1.AttributeValue AS [PD1!2!Value]
> FROM
> PortalDirectory INNER JOIN PortalDirectory PD1
> ON PortalDirectory.PortalDirectoryUserID =
> PD1.PortalDirectoryUserID WHERE (PortalDirectory.AttributeValue =
> '11351') --FOR XML EXPLICT
> Looking at the universal table that this produces, i get duplicate
> rows for the first table one for each actual result row (which meansi
> i get lots of <PortalDirectory ID="14"/><PortalDirectory ID="14"/>...
> )
> Now i could change the first SELECT to be SELECT DISTINCT (and it
> does work fine) but this can't be the way to do it surely - it just
> feels like a workaround bad code. Adding in ordering doesnt make a
> difference - there are simply too many rows being put into the
> universal table.
>
> Michael Rys [MSFT] wrote:
>
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.[vbcol=seagreen]

For Update of Cursor in a UDF

Hi,
I am writing a UDF that returns a table variable. In the UDF, I have a
cursor that I want to update. I am getting a syntax error on the UPDATE.
Is there a reason I cannot do this is a user defined function?
Thanks
SteveYou cannot update data inside of a UDF. That restriction is in place,
AFAIK, to avoid some logic problems that might occur, e.g., if a scalar UDF
is being called row-by-row and updates rows that have already been
processed.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:3FE89A39-AB3F-4464-BBB3-ED652EB80D0A@.microsoft.com...
> Hi,
> I am writing a UDF that returns a table variable. In the UDF, I have a
> cursor that I want to update. I am getting a syntax error on the UPDATE.
> Is there a reason I cannot do this is a user defined function?
> Thanks
> Steve|||>> I am writing a UDF that returns a table variable. In the UDF, I have a
You cannot do any updates which changes the persisted data or the database
state from within a UDF. This is by design and documented in SQL Server
Books Online.
Perhaps if you post your overall requirements with relevant information,
others might suggest an alternative. Using a cursor inside a table-valued
UDF for updating certain data seems a very convoluted route.
Anith|||Why are you calling a UDF from a cursor? And are you sure you need to
use a cursor at all?
Please post DDL, sample data and explain your required end result if
you need more help.
David Portas
SQL Server MVP
--|||Thanks for all the responses, obviously, my approach was not too popular.
I like baseing ACCESS reports off of UDF table variables. For this
particular report, I need to process a lot of data and it required me to use
a cursor, and I wanted to update a column so the next pass through would kno
w
I had been there. If my UDF, I gather all this information and Insert it
into the table variable, then that is returned to ACCESS.
It is nice doing it with a UDF cause of the table variable. If I use a
Stored Proc, I would have to CREATE a temp table in the Proc and populate it
,
then base the report on the temp table, if it is still around.
Steve
"Anith Sen" wrote:

> You cannot do any updates which changes the persisted data or the database
> state from within a UDF. This is by design and documented in SQL Server
> Books Online.
> Perhaps if you post your overall requirements with relevant information,
> others might suggest an alternative. Using a cursor inside a table-valued
> UDF for updating certain data seems a very convoluted route.
> --
> Anith
>
>|||I think he's calling a cursor from a UDF :)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124296660.434609.202840@.g14g2000cwa.googlegroups.com...
> Why are you calling a UDF from a cursor? And are you sure you need to
> use a cursor at all?
> Please post DDL, sample data and explain your required end result if
> you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||>> For this particular report, I need to process a lot of data and it
Cursors are seldom required for data updates. In most cases, you'd write a
single UPDATE statement, preferably within a stored procedure to do any
updates, but it depends on what you exactly meant by "process"
If you are interested in getting some additional assistance, please go
through www.aspfaq.com/5006 and post relevant information for others to
better understand your problem scenario.
Anith|||Thanks for the information. I don't update via a Cursor that often, but in
this case, it was sitting on the row I wanted update, and just thought it
would be convienient. Either way though, if I can't do any updates in a UDF
,
I am taking the wrong approach.
Thanks
"Anith Sen" wrote:

> Cursors are seldom required for data updates. In most cases, you'd write a
> single UPDATE statement, preferably within a stored procedure to do any
> updates, but it depends on what you exactly meant by "process"
> If you are interested in getting some additional assistance, please go
> through www.aspfaq.com/5006 and post relevant information for others to
> better understand your problem scenario.
> --
> Anith
>
>