Optimizing SELECT query

Hi

I have one table (tableDemo) with following structure:

TSID bigint (Primary Key)
TskID bigint
Sequence bigint
Version bigint
Frequency varchar(500)
WOID bigint
DateSchedule datetime
TimeStandard real
MeterEstimated real
MeterLast real
Description ntext
CreatedBy bigint
CreatedDate datetime
ModifiedBy bigint
ModifiedDate datetime
Id uniqueidentifier

I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds.

Is there any techniques where I can access all these records within 2-3 seconds ?

Regards,

ND

Why do you need every field of every record? Surely you are not going to display them all on one page? If you want to use them for paging, and are using Sql Server 2005, have a look at ROW_NUMBER:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

|||

Are there any large files stored in any of the rows being returned? i.e. files or larges amounts of text? Returning only 8000 rows shouldnt take very long esp not 120 seconds. Is the instance of sql on your local machine or located someone else?

Tim

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizations on System Tables

Hi

I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.

I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages

Here is an example of our sysxlogins table's report :

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%

As you can see, its got 162 pages, which probably counts as 'several' dozen.

I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.

Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%

DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%

DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%

DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%

DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%

DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%

DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%

DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%

DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%

DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.

Optimise Select Statement

Hi
I have this select statement that I need to optimise:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
The tblCustomer.Code is a string like 'STAR00000001' the function removes
the padded zeros.
The tblEvents table contains information in a string including the
contracted Customer.Code in this format "Type STAR1 ........"
Any help would be much appreciated.
Thanks
BDon=B4t know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
> Hi
> I have this select statement that I need to optimise:
> SELECT Created, Code, tblEvents.Ref
> FROM tblCustomers, tblEvents
> WHERE tblEvents.description LIKE 'Type ' +
> dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
> The tblCustomer.Code is a string like 'STAR00000001' the function removes
> the padded zeros.
No offense, but I think you need to optimise the design, not the query. Why
are you storing padded zeros if they're irrelevant or different from the
data you're actually modeling? Why are these tables quasi-related via a
string that changes?|||Hi Aaron
I would love to change the design but it is a third party product, although
i have requested the change in a future release I need a temporary solution.
Thanks
B
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#d7CV1evFHA.2292@.TK2MSFTNGP12.phx.gbl...
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
removes
> No offense, but I think you need to optimise the design, not the query.
Why
> are you storing padded zeros if they're irrelevant or different from the
> data you're actually modeling? Why are these tables quasi-related via a
> string that changes?
>|||On Tue, 20 Sep 2005 14:51:05 +0100, Ben wrote:

>I have this select statement that I need to optimise:
>SELECT Created, Code, tblEvents.Ref
>FROM tblCustomers, tblEvents
>WHERE tblEvents.description LIKE 'Type ' +
>dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
>The tblCustomer.Code is a string like 'STAR00000001' the function removes
>the padded zeros.
>The tblEvents table contains information in a string including the
>contracted Customer.Code in this format "Type STAR1 ........"
>Any help would be much appreciated.
Hi Ben,
User-defined functions can be slow. If possible, use builtin functions
that achieve the same effect.
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE
'Type ' + REPLACE(tblCustomer.Code, '0', '') + '%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jens
Would this work for codes such as:
STAR00000102?
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127225495.861651.299360@.g47g2000cwa.googlegroups.com...
Dont know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||DECLARE @.String varchar(2000)
SEt @.String = 'STAR00000102'
SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
results in "STAR%"|||Hi Jens
Thanks for your post.
The problem is that we have codes stored in tblEvents.description as STAR1,
STAR2, STAR3......STAR102, STAR103
and for example we need to distict "Type STAR103 ...." from "Type STAR1
...."
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127294257.162562.55160@.z14g2000cwz.googlegroups.com...
> DECLARE @.String varchar(2000)
> SEt @.String = 'STAR00000102'
> SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
> results in "STAR%"
>|||That=B4s not easy, there has to be a delimiter or something where you
can tell that the trailing zeros start, how do you want to differ
perhaps
STARS100 and STARS1 ?|||On Wed, 21 Sep 2005 09:36:47 +0100, Ben wrote:

>Hi Jens
>Would this work for codes such as:
>STAR00000102?
Hi Ben,
I assume that this has to be "shortened" to STAR102?
DECLARE @.a varchar(20)
SET @.a = 'STAR00000102'
SELECT STUFF(@.a, CHARINDEX('0', @.a),
PATINDEX('%[1-9]%', @.a) - CHARINDEX('0', @.a), '')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Optimal solution for getting timestamp of last entry

Hi

I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.

Is this the best method, or can you think of any others i could use?

many thanksWhat's the scope? Database wide or table specific?|||single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution

but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily|||but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easilyHow does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.|||oh dear, i'm in over my head here

what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?

and what's a page split?

sheesh, i should stick to stuff i know|||SELECT MAX(upd_dt) FROM (
SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX

???????????????

You gotta give us more to go on|||Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.

I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?

...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!|||more work? for you or for the server?

i'll bet if you timed the cpu cycles, it'd be pretty close|||more work? for you or for the server?

i'll bet if you timed the cpu cycles, it'd be pretty close

lol - i don't mind doing the work! - i just want this to be as optimal as possible...|||How many rows in the table?|||oh dear, i'm in over my head here
...
sheesh, i should stick to stuff i knowI don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question :)|||In the main table, there will be approximately 1000 new rows per day.

I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?|||Sorry I meant in total|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.

Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.

BTW - one table - is it normalised?|||ok - sounds good to me - can i just write this out how i see it working from what you've said above:

1) Index time/date column Ascending
2) When getting the last update, use something similiar to:

SELECT MAX(timeDateStampColumn) as "LastEntry"
FROM table;

BTW - one table - is it normalised?
My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. I'm at the designing stages of the project so will certainly be more tables added later on.|||My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. That table structure sounds fine although I would disagree and say that the very early stages are absolutely the right time to normalise. I would also strongly recommend you design your complete database before worrying about requirements like "how do I find the time of the last SMS?".|||point taken ;)

thanks everyone for your excellent help (as usual) :D

Optimal installation on Win 2003

Hi

I'm fairly new to this, so bare with me...

I have to make a new installation of an MS SQL 2000 EE on a Windows 2003 Std. Edt.

HW:
-------
Dual Xeon 2,4 + 1 GB Ecc
1 x 32 MB Adaptec 2100S RAID Controller
2 x 18 GB 10K HD
4 x 18 GB 15K HD
-------

So far I have made following configuration...
-------
2 x 18 GB 10K HD / RAID 1
- C:\OS
- D:\MSSQL program files + System DB's (Master, pubs ect.)

4 x 18 GB 15K HD / RAID 5
- E:\TempDB
- F:\Data + Logs
-------
But I'm not sure that this is the optimal configuration, and I'm willing to start all over :)

So my q's are......
-------
Which RAID configuration would you suggest?
Which partitions on the raids would you suggest?
Which usage would you assign the various partitions?
How do I move the system and temp db's?
-------

Thanx!

Regards,

Taras Bredel dkHi Taras,

I just had our systems guys create a SQL 2000 box on Win 2k3. I have a raid 5 (with 2 HD) on a dual p3 with 1 gig of ram.

Let me just say that I can't remember having so many problems with a SQL box. Granted anyone using Win 2k3 in a production environment should have expected some early adopter problems, I didn't think my problem would be so frustrating.

My problem is that I can't get the SQL box to listen on any ports. By default, SQL server should listen on port 1433. I can plainly see that Network Utility has TCP/IP enabled and the default port is 1433.

I'm this close to rebuilding that box to a win 2k server.
The only thing really stopping me now is debating what is less work - moving the data - or trying to fix the SQL server.|||Hi Lee

Well it could install it on a W2K, but benchmarks shows that the performance gain on W2K3 should be considerably large.

So my advice to you is trouble shoot the W2K3 installation.

//Taras|||Taras,

Yes. That was the main reason that I wanted it on a 2k3 machine. The data is to be served internally on a quasi realtime basis. This is not a real-time system - but I would like as much performance as possible.

Once you get the machine up and running tell me if you had any problems.

Thanks.

OPENXML with Multiple Namespace

Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
Thanks
Saihi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCd ResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1 data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:

> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***
>|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
Thanks
Sai|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time :)
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:

> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change you
r
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> ----
--
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> ----
--
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> ----
--
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> ----
--
> Thanks
> Sai
>
>

OPENXML with Multiple Namespace

Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
hi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***
|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCdResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:

> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:

> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change your
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> Thanks
> Sai
>
>

OPENXML question

Hi!

I am trying to import an xml file into a SQL 2005 table using sp_xml_prepareDocument ...OPENXML.

I always get 0 rows affected even though there is data in the file. The table structure is identical to the XML output. The OpenXML qry uses the following syntax:

FROM OPENXML(@.xmlHndAdd, '/NewDataSet/Table1', 1)
WITH MyTbl

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Any suggestions are greatly appreciated!!!

Thank you!

Found the right syntax if anyone has similar questions:

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE mytbl

INSERT mytbl

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)

WITH mytbl

OpenXML multiple namespaces

Hi

I've been banging my head against this one for a while. There are examples around but I can't seem to find anything that works (or find an explanation).

The problem: The xml document uses an imported common schema to reference common tags/objects (xmlns:cmn) but I can't seem to get openXML working with multiple namespaces. If I remove the primary namespace (emboldened in red) the SELECT returns as expected. Editing the xml document is not an option.

Here's (a simplified version) of my code...

---------------------

DECLARE @.idoc INT
DECLARE @.doc varchar (8000)

SET @.doc ='
<?xml version="1.0" encoding="UTF-8"?>
<LHA_DATA xmlns="http://www.trs.co.uk/schema/DataShare.xsd" xmlns:cmn="http://www.trs.co.uk/schema/Common_ed.xsd">
<Manifest>
<cmn:senderRecipient>
<cmn:LACode>A0000</cmn:LACode>
<cmn:OfficeCode>0</cmn:OfficeCode>
</cmn:senderRecipient>
</Manifest>
</LHA_DATA>
'

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<LHA_DATA xmlns:cmn="xmlns="http://www.trs.co.uk/schema/DataShare.xsd" http://www.trs.co.uk/schema/Common_ed.xsd" />'

SELECT *
FROM OPENXML (@.idoc, 'LHA_DATA', 2)
WITH (LACode varchar(10) '//cmn:LACode',
OfficeCode varchar(10) '//cmn:OfficeCode')

EXEC sp_xml_removedocument @.idoc

----------------------

Any help greatly received!!!!Hi

My continued searching eventually proved fruitful :D . Anyone who stumbles on this with the same problem may find the following link useful.

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic45014.aspx

Cheers
JW