Convert Access SQL to ANSII compliant SQL

I don't want to sit here and re-engineer Access SQL to SQL Server ANSII all day. Is there a cool that will convert a sql statement if you past it in to ANSII? Or what other techniques can make my life easier in the process if you have any up your sleeve?

Also, is there anything in .NET or even SQL Server 2005 to help convert SQL statemetns from Access to SQL Server?

AFAIK, there are no tools to do this. If you however write your query using the ODBC canonical syntax then the ODBC/OLEDB driver can convert the syntax to the native data source automatically. Of course, this only applies to applications that you code. For example, Visual FoxPro allows you to create queries like this on the client-side that can be run against different data sources.sqlsql

convert access IIF to sql in order by statement

ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?I;m not sure I got the IIF true/False right...I went with IIF(expr, true, false)...if it's backwards just flip it in the code...

ORDER BY Name
-- , IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
, CASE WHEN Risk.Calc LIKE 'H%' THEN 1
WHEN Riks.Calc LIKE 'M%' THEN 2
ELSE 3
END
, Risk.Tile|||i didnt think you could use IIF in sql|||I think brett was just reiterating your iif and submitting a case example as the replacement.|||yea thats what i thought after i looked at it, thanks for the help brett|||The "--" indicate that it's a comment line...good luck..

did it work?

Convert a time field in the select statement of the query

Hi,

I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.

Can anyone help me please?

Thx

I'm not entirely sure what you're trying to get at, but SQL Server has the following function that will get you the current UTC time:

GETUTCDATE()

If you are trying to convert the time from the timestamp to the corresponding local timestamp value, you would need to know the offset and add this time to the existing field.

Hope this helps some.

Simone

|||

Yeah!! That's the solution...... 2 weeks of deep frustrations and just such a simple solution..... wow!

Thx a lot!!!

|||

No problem, glad it worked for you. If you mark this as the answer it will better help others. Thanks.

Simone

sqlsql

Convert 1-column result set to 3 columns

I have a one-column result from a SELECT statement that I wish to
spread out into three columns, i.e.
Col
--
A
B
C
D
E
Turns into:
C1 C2 C3
-- -- --
A B C
D E
There is no identity column so I couldn't try using MOD() to come up
with a solution -- any ideas how this might be possible, preferably not
using a temp table?What are the criteria for this denormalisation - i.e. how do you determine
which values belong in which column?
What are you trying to achieve (on a more global scale)? It would help if
you could give us more information.
Also google for "cross-tab query".
ML
http://milambda.blogspot.com/|||ML wrote:
> What are the criteria for this denormalisation - i.e. how do you determine
> which values belong in which column?
There is no extra logic, I simply need to put a set of 1-column data
into three columns from left to right, top to bottom.

> What are you trying to achieve (on a more global scale)? It would help if
> you could give us more information.
The reason is that I am designing a report using SQL Reporting Services
where I need to put data in three columns across the page. Reporting
Services supports 'table' element which displays results in rows.
However each of my result is quite narrow so I need to fit 3 of them in
one row to save paper. And because this is not possible using report
designer, I decided to do it at SQL side.|||I've come up with a solution which uses temp table -- it works, but
since my team thinks temp table is absolutely evil it'd be great if
this can be converted into a more streamlined version!
-- Create data table --
drop table t
create table t
(
C char NOT NULL
)
insert into t values('A')
insert into t values('B')
insert into t values('C')
insert into t values('D')
insert into t values('E')
insert into t values('F')
insert into t values('G')
insert into t values('H')
select * from t
-- using temp table to reorganize result
drop table #tt1
create table #tt1
(
id int identity,
c char not null
)
-- Data
insert into #tt1 select c from t
-- Helper data used for incomplete last row
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
-- convert one-column table into a three-column one
SELECT t1.c, t2.c, t3.c
from #tt1 t1
join #tt1 t2 on t2.id=t1.id+1
join #tt1 t3 on t3.id=t2.id+1 or (t2.id = ' ' and t3.id=' ') or (t3.id
= ' ')
where
t1.id % 3 = 1
and t2.id % 3 = 2
and t3.id % 3 = 0|||"Anything shall be evil in hands of the evil."
-- less known Apostle
If your team thinks using temporary tables is evil, then what do they say
about the fact that you're denormalizing data purely for formatting purposes
?
As long as the temporary tables are dropped after use and global (##)
temporary tables are used only when several processes use the same data, and
if there is a sufficient contextual isolation of these processes, then there
is nothing evil about temporary tables. Is an AK-47 evil by itself?
ML
http://milambda.blogspot.com/|||Can there be duplicates in the values?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try,
create table dbo.t (
C char NOT NULL
)
insert into dbo.t values('A')
insert into dbo.t values('B')
insert into dbo.t values('C')
insert into dbo.t values('D')
insert into dbo.t values('E')
insert into dbo.t values('F')
insert into dbo.t values('G')
insert into dbo.t values('H')
go
select
max(
case when rank % 3 = 1 then c end
) as c1,
max(
case when rank % 3 = 2 then c end
) as c2,
max(
case when rank % 3 = 0 then c end
) as c3
from
(
select
count(*) - ((count(*) - 1) % 3) as pk,
count(*) as rank,
t1.c
from
dbo.t as t1
inner join
dbo.t as t2
on t2.c <= t1.c
group by
t1.c
) as a
group by
pk
order by
pk
go
drop table dbo.t
go
AMB
"ak199" wrote:

> I have a one-column result from a SELECT statement that I wish to
> spread out into three columns, i.e.
> Col
> --
> A
> B
> C
> D
> E
> Turns into:
> C1 C2 C3
> -- -- --
> A B C
> D E
> There is no identity column so I couldn't try using MOD() to come up
> with a solution -- any ideas how this might be possible, preferably not
> using a temp table?
>

conversion sql from oracle to SQL Server

Hi Guys, I have this statement that I am converting from Oracle to SQL. Help pls:-) PP_PRICEPOINT_ID is a decimal. What is the appropriate usage..

Oracle
----
update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;

Here is its SQL
------
UPDATE pricepoint
SET pp_type =
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
WHEN 7 THEN 0
ELSE 2
END,
pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
------
I am getting the error
The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
-- What's with this?
-- , pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null|||Try converting your pp_pricepoint variable to varchar before using the substring function

Change :

CASE SUBSTRING(pp_pricepoint_id, 1, 1)

For :

CASE SUBSTRING(Cast(pp_pricepoint_id As VarChar) , 1, 1)

Pls Note i didnt check ur substring use for the sintax.

Hope it can help|||Thanks Brett, this worked..
UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
, pp_qtybreakindex = SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null

I am updating 2 values here..sqlsql

conversion in select statement

Hey y'all,

Can someone make this right? i have an int column and need text:

SELECT (SELECT CASE score WHEN 0 THEN 'qqqqqqqq' ELSE 1 END) AS Expr1, COUNT(Score) AS Expr1

Thanks in advance

SELECT CASE score when 0 then 'qqqqqqqq' ELSE '1' END AS Expr1, COUNT(Score) AS Expr2

Like that?

|||

score is always 0,1 or 2 but i need written labels (text) for my charting...

i can do it with a selection in the build of my chart but i was wondering of i could do it in sql...

|||

SELECT CAST(score as varchar(1)) As score,count(*)

?

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anything

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anythingsqlsql

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), '')
,
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are dat
e
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anything

Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

Controlling errors in Stored Procedure

Hi everyone:

I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like theTRY-CATCH-FINALLY statement (inSQL-92 forSQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error israised.

Can it be done?

Thanks for any help.

No, I'm afraid in SQL2000 we can not do the error handling like usingTRY-CATCH-FINALLY block. If you only want to limit the rows returned by SELECT statements, you can use TOP key word instead. For example:

select top 1 * from sysobjects

|||

Ok, thanks Lori_jay.

control flow of execution of statement

is there a way to check to see if the previous sql statement has completely executed before executing the next statement?

I have a stored procedure that basically has several insert statements. At the end of the insert statements I call bcp to write the table to a text file. The first insert will write a header record into the table. Then it will insert a bunch of records that are selected from other tables and then lastly will write the footer record. My dilemna is that for some reason the first insert of the header record isn't actually happening until the middle of the second set of inserts where it inserts several records from another table. so basically my file ends up looking like this

payment record
payment record
payment record
Header Record
payment record
payment record
payment record
payment record
Footer Record

Can I tell it to wait for the first insert to complete before starting the other insert?

Can you post your sp, table structure and a actual sample of the data? SP's by their nature do not execute the next statement until the previous one has completed. I wonder if you have an index on your table that is causing the data to sort in the format that you have shown even though the insert is happening in the correct order...|||

Here is the stored procedure

The table I am inserting stuff into literally is one field. It is just a way to grab and format data from another table and then call bcp to write the data to a text file.

ALTER PROCEDURE [dbo].[PREPAREFILE]
@.DATE_PAID as char(8), @.HEADER as varchar(MAX), @.FOOTER as varchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
END

BEGIN
DELETE FROM Temp_Formatted
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.HEADER)
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.FOOTER)
END

Then I call bcp to write the Temp_formatted data to a text file.

What happens though in both the table and the file I get this:
Stub
Stub
Stub
Header
Stub
Stub
Payment
Payment
Payment
Payment
Payment
Footer

What I need is:
Header
stub
stub
stub
stub
stub
payment
payment
payment
payment
Footer

Of course my example output is scaled down. I have over 40,000 stub and payment records.

|||

Does your table have an index on the formattedRecord column?

Looks the first byte for a stub is always "6", first byte for a payment is "7". What is does the Header record look like, especially the first byte (you are passing as an arguement), what does the Footer record look like, especially the first byte (you are passing as an arguement)?

If a table does not have an index, it will store the data in the format that it receives it. The insert statements in your proc run sequentially (meaning each insert has to complete successfully before the next insert statement executes).

|||well, for testing purposes i've been just passing 'header' for @.header and 'footer' for @.footer. But it will always be different. No, there are no indexes on the the formatted table. No keys no indexes...nothing. I even ran a test with only writing header/payments/footer without stubs and it does basically the same thing. I will get a bunch of payments then the header and then the rest of the payments and then the footer. It is really really weird.|||

I'm at a loss. Do you know what the header and footer rows will look like (really what the first byte will be)? Will it always be the same?

You could get around this by using a query with an order by clause to load your bcp.

As an example, let's say your header will always start with 'h' and your footer will always start with 'f'

Select formattedRecord
From Temp_Formatted
Order by
Case left(formattedRecord, 1)
When 'h' then 1
When '6' then 6
When '7' then 7
When 'f' then 9
Else 8 -- This forces everything else to sort before the footer
End

Try the above query and see if that gives you the order you want.

|||yeah they are going to be different. Gee, you would think this would be pretty simple. I don't get why it is doing it this way. its really odd that it inserts between the stubs. You don't know of anyway to do the check to see if the header is there first?|||If you know what the header will look like (and it is formatted differently than the footer), you can probably modify the order by clause I posted. Only other thing I can suggest is to drop the table and recreate it. I've never seen this happen before.|||

SQL is a set-based language and tables are unordered set of rows. So even if you insert some rows in a particular order you will not be able to read it in the same order without specifying an ORDER BY clause in your SELECT statement. Any other assumption to the order of the rows based on index or query plan is incorrect. The easiest way to solve this problem is to add an identity column to the table and then modify your BCP to use queryout option & issue a SELECT on the table with the ORDER BY clause specifying the identity column. This will ensure that the you can retrieve rows in the order in which you inserted and this assumes that there is only one instance of SP inserting data into the table at any point in time.

Alternatively, you can do this without any table at all like below:

-- PrepareFile SP

SELECT t.Data

FROM (

SELECT 0, @.HEADER

UNION ALL
SELECT 1, '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 1, '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 2, @.FOOTER

) as t(SortCol, Data)
ORDER BY t.SortCol

Now, change your BCP to just call this SP using queryout option.

Contitional Split, LTRIM

I am trying to follow an SSIS tutorial. It is doing a transformation on mainframe data.

It has the statement LTRIM(State) == '' in the Condititonal split editor.

The data type is DT_STR.

Nothing works (dbl quote,brackets, ect)

I have also tried to change the type to DT_WSTR. According to docs ltrim only works with Unicode.

Can someone please tell me how to detect an empty string.

Thanks for any help

walter

Walter_Of_Maggie wrote:

I am trying to follow an SSIS tutorial. It is doing a transformation on mainframe data.

It has the statement LTRIM(State) == '' in the Condititonal split editor.

The data type is DT_STR.

Nothing works (dbl quote,brackets, ect)

I have also tried to change the type to DT_WSTR. According to docs ltrim only works with Unicode.

Can someone please tell me how to detect an empty string.

Thanks for any help

walter

In the conditional split, it'd best be done this way:

TRIM(State) == "" || ISNULL(State)|||

Hello Phil,

That did the trick. The book with the tutorial is Professional SQL Server2005 Integration Services (Wrox)

Is the syntax in the book just flat wrong? LTRIM(State) = '' One article I read said that LTRIM only works with Unicode.

Anyway, thanks for the help

Regards,

Walter

|||They do work with unicode only, but non-unicode gets implicity converted first.

LTRIM(State) = "" should work fine as well, unless the data is really NULL, not blank.|||

LTRIM(State) == "" Works now.

It didn't work last night.

I don't know how that.

Maybe it just doesn't work on Tuesday

Anyway, My package is working now. I appreciate your help.

Thanks again

Walter

|||Please mark the appropriate response as the answer to your question.

Thanks,
Phil|||

Walter_Of_Maggie wrote:

LTRIM(State) == ""

This is different from what you said on your post at the top of this thread. In there you said (and I've copied and pasted):

LTRIM(State) == ''

Walter_Of_Maggie wrote:

Works now.

It didn't work last night.

I don't know how that.

Maybe it just doesn't work on Tuesday

Anyway, My package is working now. I appreciate your help.

Thanks again

Walter

-Jamie

|||

Looks like I made a mistake. I was sure that the book had single quotes (which didn't work) But when I looked with a magnifying glass, they were double quotes.

Nevertheless, I am sure that last night, before I posted, I tried every conceivable syntax, single quotes,double quotes, brackets, even a regex. I got errors on everything.

I'm sorry for the confusion, it works now, that's it.

Thanks for the help

Walter

Continue on INSERT error.

Hi!

Imagine this SQL statement:

Code Snippet

INSERT INTO B SELECT * FROM A

If one of the insert fails ... don't continue, the statement fail. For example if any field in A violate a constraint in B, the statement fails.

I want that the statement continue if errors occurs, if i lost a number of rows don't matter ... but if i can save or log this row will be great too !!

Is posible? Any way to do it?

Regards.

Make two statements, by adding a WHERE clause, you can verify the CONSTRAINT and add rows ONLY if the CONSTRAINT passes. Then in the second statement, in the WHERE clause, get the rows that do not pass.

FOR illustration:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Name varchar(20) PRIMARY KEY
)

INSERT INTO @.MyTable VALUES ( 'Bill' )

DECLARE @.MyOtherTable table
( RowID int IDENTITY,
Name varchar(20)
)

DECLARE @.Failures table
( RowID int,
Name varchar(20)
)

INSERT INTO @.MyOtherTable VALUES ( 'Bill' )
INSERT INTO @.MyOtherTable VALUES ( 'Mary' )
INSERT INTO @.MyOtherTable VALUES ( 'Omar' )

-- First, isolate the CONSTRAINT Failures
INSERT INTO @.Failures
SELECT
t.RowID,
t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name = t.Name

-- Insert the rows that pass the CONSTRAINT test
INSERT INTO @.MyTable ( Name )
SELECT t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name <> t.Name

SELECT *
FROM @.MyTable

RowID Name
-- --
1 Bill
2 Mary
3 Omar

SELECT *
FROM @.Failures

RowID Name
-- --
1 Bill

|||

Thanks for your reply.

I will write my question in another way. What I want is if I can change the SQL/Server constraint behaviour when a error is thrown. I know that I can do the insert with a "WHERE" clause. But it some cases is useful to perform your own behaviour when the table has a lot of fields and a lot of rows and you are using a INSERT ... SELECT ... clause. There is some utility (NOTIFICATION, TRIGGERS) that help to do this in a speedy way?

Regards.

|||

A CONSTRAINT failure occurs BEFORE the data is inserted into the table -so a AFTER INSERT TRIGGER would not work.

You could create a BEFORE INSERT TRIGGER, but then you would STILL have to use the two step process I demonstrated in my earlier post. And there may be increased locking and blocking behavior as a result of using a TRIGGER.

Bottom line is that the CONSTRAINT prevents the data from getting into the table. Without the data getting to the table, there is little to offer in the form of Notifications, etc., and you are also, pardon the ironic pun, constrained in the ability to use a TRIGGER.

|||

OK! Thanks.

Regards.

Continuation of long SQL statement syntax

Hi All -

I am updating four values. What is the proper syntax to have the
following 4 update statements as one statement?

set objRec = objDB.Execute("Update orientform set session = '" &
strSession & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set fname = '" & strfname
& "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set gender = '" &
strgender & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set lname = '" & strlname
& "' where id = '" & strid & "'")

Thanks,

Joey"update orientform set sessions = '" & strSession & "', fname = '" &
strfname & '", gender = etc etc
where id = '" & strid & "'"

Notes I see you called your command objRec... maybe just habit but you
aren't creating a recordset earlier in the piece are you? Not needed for
updates/inserts/deletes. Also if your id (in the table) has an int dataype
then forget the single quotes around your strid

Jay

<joseph.jasinski@.quinnipiac.edu> wrote in message
news:1102635650.764000.267730@.z14g2000cwz.googlegr oups.com...
> Hi All -
> I am updating four values. What is the proper syntax to have the
> following 4 update statements as one statement?
> set objRec = objDB.Execute("Update orientform set session = '" &
> strSession & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set fname = '" & strfname
> & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set gender = '" &
> strgender & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set lname = '" & strlname
> & "' where id = '" & strid & "'")
> Thanks,
> Joey

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
Jens
Its been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
JensIts been a while since I played with it, but that is for ranking. Since Fuzz
y
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's jus
t
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
JensIts been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:
> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINS full-text XML problem

I've noticed that the CONTAINS function in the SQL SELECT statement has a
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:

> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:

> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:

> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>