Multiple Tables In XML Datasource
Jan 9, 2007
Hi,
I have an XML datasource that has multiple nodes at the same level. I need to retrieve contents of two nodes in a dataset. If I have an hierarchy, then I am able to get the information from two nodes. In the current data source, both the nodes are at the same level, without any relationship. I tried the following Query but it returns just the first node.
Please help me.
TIA.
Ashish
Query
<Query>
<ElementPath> root {}/ ReportDoc/ ResourceHeader/ ResourceUsage </ElementPath>
</Query>
XML Datasource
<?xml version="1.0" standalone="yes"?>
<root>
<xs:schema id="ReportDoc" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="ReportDoc" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="ResourceHeader">
<xs:complexType>
<xs:sequence>
<xs:element name="CompanyName" minOccurs="0" msdata:Ordinal="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="StrtDt1" type="xs:dateTime" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="StrtDt2" type="xs:dateTime" minOccurs="0" msdata:Ordinal="2" />
<xs:element name="StrtDt3" type="xs:dateTime" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="StrtDt4" type="xs:dateTime" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="StrtDt5" type="xs:dateTime" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="StrtDt6" type="xs:dateTime" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="StrtDt7" type="xs:dateTime" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="StrtDt8" type="xs:dateTime" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="StrtDt9" type="xs:dateTime" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="StrtDt10" type="xs:dateTime" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="StrtDt11" type="xs:dateTime" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="StrtDt12" type="xs:dateTime" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="WkHrs1" type="xs:decimal" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="WkHrs2" type="xs:decimal" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="WkHrs3" type="xs:decimal" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="WkHrs4" type="xs:decimal" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="WkHrs5" type="xs:decimal" minOccurs="0" msdata:Ordinal="17" />
<xs:element name="WkHrs6" type="xs:decimal" minOccurs="0" msdata:Ordinal="18" />
<xs:element name="WkHrs7" type="xs:decimal" minOccurs="0" msdata:Ordinal="19" />
<xs:element name="WkHrs8" type="xs:decimal" minOccurs="0" msdata:Ordinal="20" />
<xs:element name="WkHrs9" type="xs:decimal" minOccurs="0" msdata:Ordinal="21" />
<xs:element name="WkHrs10" type="xs:decimal" minOccurs="0" msdata:Ordinal="22" />
<xs:element name="WkHrs11" type="xs:decimal" minOccurs="0" msdata:Ordinal="23" />
<xs:element name="WkHrs12" type="xs:decimal" minOccurs="0" msdata:Ordinal="24" />
<xs:element name="TtlWkHrs1" type="xs:decimal" minOccurs="0" msdata:Ordinal="25" />
<xs:element name="TtlWkHrs2" type="xs:decimal" minOccurs="0" msdata:Ordinal="26" />
<xs:element name="TtlWkHrs3" type="xs:decimal" minOccurs="0" msdata:Ordinal="27" />
<xs:element name="TtlWkHrs4" type="xs:decimal" minOccurs="0" msdata:Ordinal="28" />
<xs:element name="TtlWkHrs5" type="xs:decimal" minOccurs="0" msdata:Ordinal="29" />
<xs:element name="TtlWkHrs6" type="xs:decimal" minOccurs="0" msdata:Ordinal="30" />
<xs:element name="TtlWkHrs7" type="xs:decimal" minOccurs="0" msdata:Ordinal="31" />
<xs:element name="TtlWkHrs8" type="xs:decimal" minOccurs="0" msdata:Ordinal="32" />
<xs:element name="TtlWkHrs9" type="xs:decimal" minOccurs="0" msdata:Ordinal="33" />
<xs:element name="TtlWkHrs10" type="xs:decimal" minOccurs="0" msdata:Ordinal="34" />
<xs:element name="TtlWkHrs11" type="xs:decimal" minOccurs="0" msdata:Ordinal="35" />
<xs:element name="TtlWkHrs12" type="xs:decimal" minOccurs="0" msdata:Ordinal="36" />
<xs:element name="UtilHours1" type="xs:decimal" minOccurs="0" msdata:Ordinal="37" />
<xs:element name="UtilHours2" type="xs:decimal" minOccurs="0" msdata:Ordinal="38" />
<xs:element name="UtilHours3" type="xs:decimal" minOccurs="0" msdata:Ordinal="39" />
<xs:element name="UtilHours4" type="xs:decimal" minOccurs="0" msdata:Ordinal="40" />
<xs:element name="UtilHours5" type="xs:decimal" minOccurs="0" msdata:Ordinal="41" />
<xs:element name="UtilHours6" type="xs:decimal" minOccurs="0" msdata:Ordinal="42" />
<xs:element name="UtilHours7" type="xs:decimal" minOccurs="0" msdata:Ordinal="43" />
<xs:element name="UtilHours8" type="xs:decimal" minOccurs="0" msdata:Ordinal="44" />
<xs:element name="UtilHours9" type="xs:decimal" minOccurs="0" msdata:Ordinal="45" />
<xs:element name="UtilHours10" type="xs:decimal" minOccurs="0" msdata:Ordinal="46" />
<xs:element name="UtilHours11" type="xs:decimal" minOccurs="0" msdata:Ordinal="47" />
<xs:element name="UtilHours12" type="xs:decimal" minOccurs="0" msdata:Ordinal="48" />
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="ResourceUsage">
<xs:complexType>
<xs:sequence>
<xs:element name="CompanyName" minOccurs="0" msdata:Ordinal="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="SiteName" minOccurs="0" msdata:Ordinal="1">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OrganizationID" minOccurs="0" msdata:Ordinal="2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="OrganizationName" minOccurs="0" msdata:Ordinal="3">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ResourceCode" minOccurs="0" msdata:Ordinal="4">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ResourceDesc" minOccurs="0" msdata:Ordinal="5">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Prd1" type="xs:decimal" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="Prd2" type="xs:decimal" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="Prd3" type="xs:decimal" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="Prd4" type="xs:decimal" minOccurs="0" msdata:Ordinal="9" />
<xs:element name="Prd5" type="xs:decimal" minOccurs="0" msdata:Ordinal="10" />
<xs:element name="Prd6" type="xs:decimal" minOccurs="0" msdata:Ordinal="11" />
<xs:element name="Prd7" type="xs:decimal" minOccurs="0" msdata:Ordinal="12" />
<xs:element name="Prd8" type="xs:decimal" minOccurs="0" msdata:Ordinal="13" />
<xs:element name="Prd9" type="xs:decimal" minOccurs="0" msdata:Ordinal="14" />
<xs:element name="Prd10" type="xs:decimal" minOccurs="0" msdata:Ordinal="15" />
<xs:element name="Prd11" type="xs:decimal" minOccurs="0" msdata:Ordinal="16" />
<xs:element name="Prd12" type="xs:decimal" minOccurs="0" msdata:Ordinal="17" />
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
<xs:element name="ReportSummary">
<xs:complexType>
<xs:sequence>
<xs:element name="PrdId" type="xs:int" minOccurs="0" msdata:Ordinal="0" />
<xs:element name="StrtDt" type="xs:dateTime" minOccurs="0" msdata:Ordinal="1" />
<xs:element name="EndDate" type="xs:dateTime" minOccurs="0" msdata:Ordinal="2" />
<xs:element name="WkHrs" type="xs:decimal" minOccurs="0" msdata:Ordinal="3" />
<xs:element name="TtlWkHrs" type="xs:decimal" minOccurs="0" msdata:Ordinal="4" />
<xs:element name="UtilHours" type="xs:decimal" minOccurs="0" msdata:Ordinal="5" />
<xs:element name="GraphUtilized" type="xs:decimal" minOccurs="0" msdata:Ordinal="6" />
<xs:element name="GraphAvailable" type="xs:decimal" minOccurs="0" msdata:Ordinal="7" />
<xs:element name="GraphOverUtilized" type="xs:decimal" minOccurs="0" msdata:Ordinal="8" />
<xs:element name="SiteURN" minOccurs="0" msdata:Ordinal="9">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="128" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
<xs:attribute name="action" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<ReportDoc>
<ResourceHeader>
<CompanyName>EPIS Software - US</CompanyName>
<StrtDt1>2004-11-07T00:00:00.0000000-06:00</StrtDt1>
<StrtDt2>2004-11-14T00:00:00.0000000-06:00</StrtDt2>
<StrtDt3>2004-11-21T00:00:00.0000000-06:00</StrtDt3>
<StrtDt4>2004-11-28T00:00:00.0000000-06:00</StrtDt4>
<StrtDt5>2004-12-05T00:00:00.0000000-06:00</StrtDt5>
<StrtDt6>2004-12-12T00:00:00.0000000-06:00</StrtDt6>
<StrtDt7>2004-12-19T00:00:00.0000000-06:00</StrtDt7>
<StrtDt8>2004-12-26T00:00:00.0000000-06:00</StrtDt8>
<StrtDt9>2005-01-02T00:00:00.0000000-06:00</StrtDt9>
<StrtDt10>2005-01-09T00:00:00.0000000-06:00</StrtDt10>
<StrtDt11>2005-01-16T00:00:00.0000000-06:00</StrtDt11>
<StrtDt12>2005-01-23T00:00:00.0000000-06:00</StrtDt12>
<WkHrs1>0</WkHrs1>
<WkHrs2>0</WkHrs2>
<WkHrs3>0</WkHrs3>
<WkHrs4>0</WkHrs4>
<WkHrs5>0</WkHrs5>
<WkHrs6>0</WkHrs6>
<WkHrs7>0</WkHrs7>
<WkHrs8>0</WkHrs8>
<WkHrs9>32</WkHrs9>
<WkHrs10>40</WkHrs10>
<WkHrs11>40</WkHrs11>
<WkHrs12>40</WkHrs12>
<TtlWkHrs1>0</TtlWkHrs1>
<TtlWkHrs2>0</TtlWkHrs2>
<TtlWkHrs3>0</TtlWkHrs3>
<TtlWkHrs4>0</TtlWkHrs4>
<TtlWkHrs5>0</TtlWkHrs5>
<TtlWkHrs6>0</TtlWkHrs6>
<TtlWkHrs7>0</TtlWkHrs7>
<TtlWkHrs8>0</TtlWkHrs8>
<TtlWkHrs9>32</TtlWkHrs9>
<TtlWkHrs10>40</TtlWkHrs10>
<TtlWkHrs11>40</TtlWkHrs11>
<TtlWkHrs12>40</TtlWkHrs12>
<UtilHours1>0</UtilHours1>
<UtilHours2>0</UtilHours2>
<UtilHours3>0</UtilHours3>
<UtilHours4>0</UtilHours4>
<UtilHours5>0</UtilHours5>
<UtilHours6>0</UtilHours6>
<UtilHours7>0</UtilHours7>
<UtilHours8>0</UtilHours8>
<UtilHours9>0</UtilHours9>
<UtilHours10>0</UtilHours10>
<UtilHours11>0</UtilHours11>
<UtilHours12>0</UtilHours12>
</ResourceHeader>
<ResourceUsage>
<CompanyName>EPIS Software - US</CompanyName>
<SiteName>US</SiteName>
<OrganizationID>10010001104</OrganizationID>
<OrganizationName>Executives - Irvine</OrganizationName>
<ResourceCode>EHILL</ResourceCode>
<ResourceDesc>Eu Hll</ResourceDesc>
<Prd1>0</Prd1>
<Prd2>0</Prd2>
<Prd3>0</Prd3>
<Prd4>0</Prd4>
<Prd5>0</Prd5>
<Prd6>0</Prd6>
<Prd7>0</Prd7>
<Prd8>0</Prd8>
<Prd9>0</Prd9>
<Prd10>0</Prd10>
<Prd11>0</Prd11>
<Prd12>0</Prd12>
</ResourceUsage>
<ReportSummary>
<CompanyName>EPIS Software - US</CompanyName>
<PrdId>1</PrdId>
<StrtDt>2004-11-07T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-14T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>2</PrdId>
<StrtDt>2004-11-14T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-21T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>3</PrdId>
<StrtDt>2004-11-21T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-11-28T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>4</PrdId>
<StrtDt>2004-11-28T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-05T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>5</PrdId>
<StrtDt>2004-12-05T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-12T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>6</PrdId>
<StrtDt>2004-12-12T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-19T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>7</PrdId>
<StrtDt>2004-12-19T00:00:00.0000000-06:00</StrtDt>
<EndDate>2004-12-26T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>8</PrdId>
<StrtDt>2004-12-26T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-02T00:00:00.0000000-06:00</EndDate>
<WkHrs>0</WkHrs>
<TtlWkHrs>0</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>9</PrdId>
<StrtDt>2005-01-02T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-09T00:00:00.0000000-06:00</EndDate>
<WkHrs>32</WkHrs>
<TtlWkHrs>32</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>10</PrdId>
<StrtDt>2005-01-09T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-16T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>11</PrdId>
<StrtDt>2005-01-16T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-23T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
<ReportSummary>
<PrdId>12</PrdId>
<StrtDt>2005-01-23T00:00:00.0000000-06:00</StrtDt>
<EndDate>2005-01-30T00:00:00.0000000-06:00</EndDate>
<WkHrs>40</WkHrs>
<TtlWkHrs>40</TtlWkHrs>
<UtilHours>0</UtilHours>
<GraphUtilized>0</GraphUtilized>
<GraphAvailable>0</GraphAvailable>
<GraphOverUtilized>0</GraphOverUtilized>
<SiteURN>US</SiteURN>
</ReportSummary>
</ReportDoc>
</root>
View 1 Replies
ADVERTISEMENT
May 10, 2007
Hi All,
New to the forums, so, hello.
I want to import data from multiple XML files into SQL 2005 using SSIS. I know how to set up a flat data source etc... but am unsure how I would go about importing from multiple files.
Anyone got any ideas?
Thanks,
Drammy
View 6 Replies
View Related
Oct 10, 2005
I added two tables to my access database. I tried to add them to my dataset through the configuration wizard.
View 1 Replies
View Related
Mar 21, 2008
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
View 1 Replies
View Related
Jul 5, 2005
I'm using net2.0 and SQL server express locally and have succeseffully placed my connection string in my sebconfig file and made connection and grids and detail etc, etc. Now I want to publish what I've done. So I got a free account at maximumasp beta 2.0. They give some room on a sql 2000 server and a empty database. Now I want to add the Northwind table to that DB but I don't have Enterprise manager and have no idea how to add the tables.Can someone please help?Thanks!Rich
View 3 Replies
View Related
Jul 27, 2015
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B). Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
View 3 Replies
View Related
Oct 12, 2007
Hi,
This is a very detailed question, I hope this is the best forum to address it. It is more related to general ODBC access, and less to SQL Server data access.
Calling all ODBC experts!
I am a developer using an ODBC toolkit to connect to my companies metadata management product, which in turn communicates to SQL Server, Oracle, DB2 and other databases. The ODBC toolkit we are using is very old, and has been very stable in accessing data with clients such as Access, Excel, Visio and others. It is a read-only ODBC driver that does not support a lot of advanced query and data manipulation features, such as catalog.
I'm using Visual Studio 2005 to debug the ODBC calls that Excel is making when attempting to load data through the External Data Wizard. The Excel version I am using is 2003. The method that I am using to attempt to load data is with 'Data | Import External Data | Import Data'. When that dialog appears, I select 'New Source...' then 'ODBC DSN'. I then select my DSN and click Next.
At this point, I get back the error 'unable to obtain a list of tables from the datasource'.
What is frustrating is, if I do 'Data | New Database Query' and use MSQuery to load the data, everything works fine.
As I mentioned I am using VC2005 and debugging, I believe the problem has to do with the capabilities of our driver and the columns that Excel is binding to return data. In this call, Excel is binding two columns - Table Qualifer (
TABLE_QUALIFIER (1)) and Remarks (TABLE_REMARKS(5)). Our driver does not support qualifiers, so we return a NULL for that value:
case TABLE_QUALIFIER:
fSqlTypeIn = SQL_CHAR;
rgbValueIn = NULL;
cbValueIn = SQL_NULL_DATA;
Excel makes the SQLTables() call, which successfully returns the entire list of tables from our server product. It then attempts to SQLBind the two columns I mentioned above. The first call to SQLFetch returns with SQL_SUCCESS but the ODBCGetData call fills the QUALFIER column with a NULL. So obviously, Excel doesn't like this value and then ends up quiting and displaying the message above (I think....) This is confirmed by looking at the ODBC trace calls:
EXCEL 1500-1b18 ENTER SQLTablesW
HSTMT 01F120E0
WCHAR * 0x4DE3B000 [ 1] "%"
SWORD 1
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x00000000
SWORD 0
EXCEL 1500-1b18 EXIT SQLTablesW with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
WCHAR * 0x4DE3B000 [ 1] "%"
SWORD 1
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x00000000
SWORD 0
EXCEL 1500-1b18 ENTER SQLBindCol
HSTMT 01F120E0
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD74C0
SQLLEN 256
SQLLEN * 0x01DD74BC
EXCEL 1500-1b18 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD74C0
SQLLEN 256
SQLLEN * 0x01DD74BC (-1163005939)
EXCEL 1500-1b18 ENTER SQLBindCol
HSTMT 01F120E0
UWORD 5
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD75C8
SQLLEN 510
SQLLEN * 0x01DD75C4
EXCEL 1500-1b18 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
UWORD 5
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD75C8
SQLLEN 510
SQLLEN * 0x01DD75C4 (-1163005939)
EXCEL 1500-1b18 ENTER SQLFetch
HSTMT 01F120E0
EXCEL 1500-1b18 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
EXCEL 1500-1b18 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 01F120E0
SQLSMALLINT 1
SQLWCHAR * 0x0013531C (NYI)
SQLINTEGER * 0x00134F0C
SQLWCHAR * 0x00134F1C (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x00134F04
EXCEL 1500-1b18 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 3
SQLHANDLE 01F120E0
SQLSMALLINT 1
SQLWCHAR * 0x0013531C (NYI)
SQLINTEGER * 0x00134F0C
SQLWCHAR * 0x00134F1C (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x00134F04
So I began to examine the GetInfo calls. There are probably 100 or so that Excel makes. Anything related to qualifiers or cataloging seems to indicate (according to MSDN) that we do not support it. So my thinking was, if Excel properly identifies that we don't support qualifers, why does it attempt to bind the column? Is this a flaw in Excel, or are there some other GetInfo properties that I am not properly setting?
The attributes I am setting (that seem related) are:
SQL_QUALIFIER_LOCATION = 0
SQL_QUALIFIER_USAGE = 0
SQL_QUALIFER_TERM = ""
SQL_CATALOG_LOCATION = 0
SQL_CATALOG_NAME = "N"
SQL_CATALOG_TERM=""
SQL_CATALOG_USAGE=0
Looking at ODBC trace logs, the only GetInfo values that Excel is quering is SQL_QUALIFIER_LOCATION, SQL_QUALIFIER_TERM and SQL_QUALIFIER_USAGE. According to the MSDN documentation, an application is supposed to query SQL_CATALOG_NAME to determine if catalogs are supported. I know it is not, because I don't see it in the ODBC trace log, or my breakpoints in VC2005 are never hit.
I realize this is a HUGE question. I hope that I came across clear and that my question is understandable. I guess what it comes down to is - (maybe some Excel engineers can answer this one) - is this method of loading data into Excel require a Table Qualifier? If not, how can I configure my settings so that it asks for Table Name (as MsQuery does) instead?
Thank you so much for your time and consideration!
View 1 Replies
View Related
Jun 5, 2007
Hai,
I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.
The error message is "Invalid Argument".
I use the following code for creating the linked table
Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table
Dim ADOConnection As New ADODB.Connection
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")
catDB = New ADOX.Catalog
catDB.ActiveConnection = ADOConnection
tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB
' Set the properties to create the link.
Dim adoxPro As ADOX.Property
adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True
adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString
adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl
End With
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")
'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)
'Append the primary index to table.
catDB = Nothing
End Sub
If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.
Thanks in advance
Sudeep T S
View 4 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Jun 27, 2006
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
View 9 Replies
View Related
Mar 1, 2007
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
View 9 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 15, 2008
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
View 1 Replies
View Related
Nov 29, 2007
I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.
Creating additional sources is fairly easy copy of the first source and change to the table name.
I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.
How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?
This seems like a simple / common task, but I've been unable to find a solution.
Thanks, Richard
View 1 Replies
View Related
May 31, 2007
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies
View Related
Dec 21, 2007
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
View 4 Replies
View Related
May 23, 2008
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
View 3 Replies
View Related
Nov 4, 2004
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View 1 Replies
View Related
Jan 20, 2006
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)
delete dbo.tblcase
where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseclient
where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseinformation
where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaselawyer
where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseprosecutor
where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
View 1 Replies
View Related
Jun 1, 2007
Hi,
I have searched but not found quite the best way to look at this so far..
I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.
In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.
Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?
Just wondering if there is a neater way of doing this??
It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.
Hope that explains ok and thanks in advance.
Mike
View 3 Replies
View Related
Aug 12, 2006
Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
View 6 Replies
View Related
May 4, 2001
Hi, Gurus,
I am trying to populate a table with repeating groups in multiple columns by using information from two other tables. The sample tables and records are like:
Table A
CSID
1
2
3
4
Table B
CP_IDCO_CODE
12C1
12C2
12C3
12C4
13C5
13C6
13C7
13C8
14C9
14C10
14C11
14C12
Table C (The empty table I want to populate like the following)
CSID CP_ID_1 CO_CODE_1 CP_ID_2 CO_CODE_2 CP_ID_3 CO_CODE_3
112C1 13 C514 C9
212C2 13 C614 C10
312C3 13 C714 C11
412C4 13 C814 C12
What is the best way to do it? Thanks in advance.
Sam
View 2 Replies
View Related
Aug 4, 2004
I am trying to write (my first, unfortunatly) DTS, and am having some problems.
I need to be able to import multiple flatfiles (all in the same format, just with different schema), each one going into a different table. I have written an application to call my DTS, sending it variables for the tablename and the filename. This works fine when I test it on a single flatfile.
My problem is, the Tranformation object does not reset after each DTS call, so I get "Column does not exist" errors after the first successful import. I can go into the DTS Manager and reset the Transformation options, but that would defeat the purpose of automation. Is there anyway to reset, or another technique, the Transformation object so that it will continuosly work on files that use different schema?
I am very new at DTS, so please consider me "ignorant" when replying.
Thanks in advance.
- Jordan
View 4 Replies
View Related
Mar 11, 2008
I have two tables, Personnel and Emails.
Personnel
*per_personnelID (int)
per_name (varchar)
per_office (varchar)
per_cell (varchar)
Emails
*eml_emailID (int)
eml_personnelID (int)
eml_name (varchar)
I can have a user that has multiple emails but I need to return them all in one row in addition to the name, office and phone. And I need to do this regardless of the number of emails they have. Please, can anyone help the newbie?
View 4 Replies
View Related
Aug 9, 2006
Is there a way to import multiple csv files from a directory into sql2005? The situation I have right now is that I have a folder withmultiple csv files that i need to import into sql 2005. I can do itwith the import wizard but it takes to long. The files will be updatedmonthly. The first row in the files contains all the header informationwhich may change monthy. What I am looking to do is import all of thesecsv into tables. One csv file into for one table. Ideally I would liketo use the name of the csv file as the name of the table. Any bump inthe right direction would be apprecieted
View 1 Replies
View Related
Mar 20, 2004
Hi
I am trying to import an excel spreadsheet into a sql server database. The problem is I need the information in the spreadsheet to be imported into 3 different tables. I thought of transfering all details into a temporary table and then setting 3 triggers for each transfer of information. But this seems very inefficient. I know there should be an easier way to do this in dts.
Any help would be greatly appreciated
Thanks
View 6 Replies
View Related
Apr 8, 2008
Hello,
I have 2 tables I am using as test, and for the life of me I can't figure this out.
Table: stats
Columns: ID, Name, Goals, Assists, Points
Example: 1, George, 1, 1, 2
Table: Players
Columns: ID, Name, Pick1, Pick2, Pick3
Example: 1, Bob, 1, 4, 7
I want to find the SUM value of stat.Points, where Players.Pick1 = stats.ID for all picks.
Then Grouping them by Players.name
Am I creating the database schema wrong for this?
View 2 Replies
View Related
Jun 6, 2007
Hi, I had someone help me refine results to show results that have data that match 2 tables. I then created a column of values from the two tables but how do i take the average of that column?
my original script looks something like this:
select a.day, a.time, a.cname, sum(T_GS), sum(T_CS), sum(T_CS)/sum(T_GS) Cost,
sum(T_OPEX), sum(T_CS)/10/sum(T_OPEX) PERCENT from Fs_b12 a, Fs_b7 b
where a.day=2005 and b.day=2005 and a.time=b.time and a.carrier_name=b.carrier_name group by a.day, a.time, a.cname
order by a.cname, a.time
View 2 Replies
View Related
Nov 12, 2007
hi,
I have a query like below,
select max(batchNumber ) from Adjustments where store_Id = 8637
Union
select max(batchnumber) from batch b
inner join Document d on
d.Document_Id = b.General_Id and b.BatchType <> 'Warehouse' and b.TranTable='Document'
This gives me 2 rows,how ever i want the max on the result of this union.
Assume, the above returns
10
20
i want 20.
how to go about this?
Thanks
View 2 Replies
View Related
Feb 10, 2007
hi,
I have 3 tables: Unix, Windows & Sybase
there are 3 dropdownlists in .asp page,
dropdown1=<displays the lists of table> (Unix, Windows & Sybase) variable as @table
dropdown2=<displays the list of Columns from the dropdown1 selected table> variable as @server
dropdown3=<displays the list of Row Header from the dropdown1 selected table> variable as @user
how shal i define the select statement for this:
it should be something like,
Select * from @table WHERE Server= @server AND Row-Header = @user
I appreciate if you could kindly help me in getting the correct syntax, thanx in advance.
View 4 Replies
View Related
