Hello:
I'm relatively new to MSQL - I come from a MySQL background and have a crap load of experience with Access/Jet. I tried executing a query in MSQL last night, and got an error. Here's my query:
update inv_mast inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid SET inv_mast.short_code = 'Entrelec' WHERE inv_loc.primary_supplier_id = '100086'
I got the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.
So I tried changing the syntax to:
UPDATE inv_mast
SET inv_mast.short_code = 'Entrelec'
FROM inv_mast, inv_loc
WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
AND inv_loc.primary_supplier_id = '100086'
And didn't get an error, but I didn't get any results (0 row(s) affected).
What am I doing wrong?
If my queries don't give enough of an explanation of the layout of the data, let me know, I'll gladly explain more.
Thanks all! :)
-jimyour update statment probably should have looked like:
update inv_mast
SET inv_mast.short_code = 'Entrelec'
from inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
WHERE inv_loc.primary_supplier_id = '100086'
and if you like aliases:
update im
SET im.short_code = 'Entrelec'
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
WHERE il.primary_supplier_id = '100086'
as to why you didn't update anything, maybe your keys don't line up or you have nothing that satisfies your where clause. what does the following produce?
select im.inv_mast_uid,il.primary_supplier_id
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
order by 1,2|||Paul,
Thanks a TON for your response! The last query you posted lists all the item master ID's with their vendor IDs. So I guess the keys are good.
I'm going to try your update query now...
Dude - I owe you big time!! It worked like a charm - how can I thank you?
-jim
Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
no data returned when using a stylesheet
Executing http queries works fine. But when I try to use a xls stylesheet in the query, I get headings but no data. It must be something in the stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0 sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms Spezialit?ten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari Carnes" /><Orders OrderID="10251" Employe
eId="3" Shipname="Victuailles en stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship Name</B></TD></TR><xsl:
apply-templates/></TABLE></BODY></HTML></xsl:template><xsl:template match="Orders"><TR><TD><xsl:value-of select="@.OrderID"/></TD><TD><xsl:value-of select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
The only problem I could find is with the stylesheet namespace declaration -
I had to change it to <xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> (i.e. with a
version attribute)
Other than that it worked OK for me from both the local PC and a remote
client.
You don't list SQLXML 3.0 sp2 as being installed - it *should* still work
without it, but I haven't tested it.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"David R" <david.roseneder@.dcs.qld.gov.au> wrote in message
news:0C44F895-7C60-4842-B601-D09197A70727@.microsoft.com...
Executing http queries works fine. But when I try to use a xls stylesheet in
the query, I get headings but no data. It must be something in the
stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL
Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler
shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0
sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools
Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms
Spezialitten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari
Carnes" /><Orders OrderID="10251" EmployeeId="3" Shipname="Victuailles en
stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output
media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE
width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship
Name</B></TD></TR><xsl:apply-templates/></TABLE></BODY></HTML></xsl:template
><xsl:template match="Orders"><TR><TD><xsl:value-of
select="@.OrderID"/></TD><TD><xsl:value-of
select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
|||Graeme
Thanks, adding the namespace corrected the problem. Data is now returned.
Thanks again
David Roseneder
This code is based on examples from P151 in John Griffins book XML and SQL Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0 sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms Spezialit?ten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari Carnes" /><Orders OrderID="10251" Employe
eId="3" Shipname="Victuailles en stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship Name</B></TD></TR><xsl:
apply-templates/></TABLE></BODY></HTML></xsl:template><xsl:template match="Orders"><TR><TD><xsl:value-of select="@.OrderID"/></TD><TD><xsl:value-of select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
The only problem I could find is with the stylesheet namespace declaration -
I had to change it to <xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> (i.e. with a
version attribute)
Other than that it worked OK for me from both the local PC and a remote
client.
You don't list SQLXML 3.0 sp2 as being installed - it *should* still work
without it, but I haven't tested it.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"David R" <david.roseneder@.dcs.qld.gov.au> wrote in message
news:0C44F895-7C60-4842-B601-D09197A70727@.microsoft.com...
Executing http queries works fine. But when I try to use a xls stylesheet in
the query, I get headings but no data. It must be something in the
stylesheet but I cant identify the problem. Any help would be appreciated.
This code is based on examples from P151 in John Griffins book XML and SQL
Server 2000 using the Northwind database.
IIS and sql server are running on the same machine (bne20dbm07). Profiler
shows that the data is being retrieved.
Using Windows server 2000, SQL 2000 sp3a, all hotfixes applied, MSXML 3.0
sp4
Queries are being run for a Windows XP machine via IE 6 sp1 plus hotfixes.
httpQuery
http://bne20dbm07/Nwind?sql=select+T...AUTO&root=ROOT
Results
<?xml version="1.0" encoding="utf-8" ?>
- <ROOT><Orders OrderID="10248" EmployeeId="5" Shipname="Vins et alcools
Chevalier" /><Orders OrderID="10249" EmployeeId="6" Shipname="Toms
Spezialitten" /><Orders OrderID="10250" EmployeeId="4" Shipname="Hanari
Carnes" /><Orders OrderID="10251" EmployeeId="3" Shipname="Victuailles en
stock" /></ROOT>
stylesheet query
order.xsl
<?xml version='1.0'?><xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform/1.0"><xsl:output
media-type="text/html"/><xsl:template match="/"><HTML><BODY><TABLE
width='400' border='1'><TR><TD><B>Order ID</B></TD><TD><B>Ship
Name</B></TD></TR><xsl:apply-templates/></TABLE></BODY></HTML></xsl:template
><xsl:template match="Orders"><TR><TD><xsl:value-of
select="@.OrderID"/></TD><TD><xsl:value-of
select="@.Shipname"/></TD></TR></xsl:template></xsl:stylesheet>
http://bne20dbm07/Nwind?sql=select+T...xsl&root=ROOT
Results (Only the heading text in the table is displayed)
Order ID Ship Name
|||Graeme
Thanks, adding the namespace corrected the problem. Data is now returned.
Thanks again
David Roseneder
Subscribe to:
Posts (Atom)