Help Adding Tables To Remote Sql Datasource
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
ADVERTISEMENT
Mar 8, 2006
I am running Visual Studios 2005 enterprise edition and I am creating a mobile application to keep track of some receipts. The problem I'm currently having is that even though I can connect to the MS Access Database with Server Explorer that I'll be aquiring data from; Everytime I attempt to use the Data Configuration Wizard to add a new data source that namely the database I get this error message:
The new connection uses a data provider that is not supported. (end of message)
There is no username or password necessary to access this database and it is an Access 2003 database.
Any help would be greatly appreciated.
View 14 Replies
View Related
May 16, 2008
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
DT
View 1 Replies
View Related
Apr 18, 2007
We are importing Flat file data from our Mainframe system. We have a lot of money amounts coming in, but the mainframe does not store the decimals in the flat file. So for example a row in the file might look like this:
+0000007894-0000000563
Where the first value is $78.94 and the second value is -$5.63
Is there anyway to have the Flat file connection manager put in the decimal place for me, or do i have to create derived columns for each column and divide it by 100? There like 50-100 columns per file, so i'm looking for a better, quicker way.
Thanks in advance.
John
View 12 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
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
View Related
Jan 15, 2007
Hi
I am Creating Click Once Windows Application with Reporting Services 2005.I have created Report and Published on Report Server.In my windows application I am successfully able to view my published report through report viewer control.
Now in my application I am getting a dataset from my custom webservice. I want this dataset data to be added to my report as datasource at runtime on Client Side ,as my report is on Report Server.
waiting for help!!
Thanks in Advance
Pankaj
View 8 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
Apr 19, 2007
Hi Everyone,
I have a page with a textbox and a dropdown list. The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.
I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.
Here is the codebehind file:Imports System.Data.SqlClient
Imports System.Web.Configuration
Partial Class cms_Management_Company_NewCompany
Inherits System.Web.UI.Page
Dim companyName As String
Dim companyActive As Boolean
Dim companyArchived As Boolean
Dim companyDelegates As Integer
Dim userForeName As String
Dim userSurname As String
Dim userEmail As String
Dim userUsername As String
Dim userPassword As String
Dim userActive As Boolean
Dim userTypeID As Integer
Dim companyID As Integer
Dim i As Integer
Dim NoLoops As Integer
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSave.Click
Dim conString As String = WebConfigurationManager.ConnectionStrings("General").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@CompanyName, @CompanyActive, @CompanyArchived, @CompanyDelegates)", con)
cmd.Parameters.AddWithValue("@CompanyName", companyName)
cmd.Parameters.Item("@CompanyName").Value = txtCompanyName.Text
cmd.Parameters.AddWithValue("@CompanyDelegates", companyDelegates)
cmd.Parameters.Item("@CompanyDelegates").Value = lstDel.SelectedValue
cmd.Parameters.AddWithValue("@CompanyActive", companyActive)
cmd.Parameters.Item("@CompanyActive").Value = True
cmd.Parameters.AddWithValue("@CompanyArchived", companyArchived)
cmd.Parameters.Item("@CompanyArchived").Value = False
Using con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
Dim con2 As New SqlConnection(conString)
Dim cmd2 As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@UserForeName, @UserSurname, @UserEmail, @UserUsername, @UserPassword, @UserActive, @UserTypeID, @CompanyID)", con2)
cmd2.Parameters.AddWithValue("@UserForeName", userForeName)
cmd2.Parameters.Item("@UserForeName").Value = "First Name - Delegate 1"
cmd2.Parameters.AddWithValue("@UserSurname", userSurname)
cmd2.Parameters.Item("@UserSurname").Value = "Surname - Delegate 1"
cmd2.Parameters.AddWithValue("@UserEmail", userEmail)
cmd2.Parameters.Item("@UserEmail").Value = "Email Address - Delegate 1"
cmd2.Parameters.AddWithValue("@UserUsername", userUsername)
cmd2.Parameters.Item("@UserUsername").Value = "Username - Delegate 1"
cmd2.Parameters.AddWithValue("@UserPassword", userPassword)
cmd2.Parameters.Item("@UserPassword").Value = "Password - Delegate 1"
cmd2.Parameters.AddWithValue("@UserActive", userActive)
cmd2.Parameters.Item("@UserActive").Value = True
cmd2.Parameters.AddWithValue("@UserTypeID", userTypeID)
cmd2.Parameters.Item("@UserTypeID").Value = 2
cmd2.Parameters.AddWithValue("@UserTypeID", userTypeID)
cmd2.Parameters.Item("@UserTypeID").Value = 1
Using con2
con2.Open()
For i = 1 To NoLoops
cmd2.ExecuteNonQuery()
Next i
con2.Close()
End Using
Response.Redirect("~/cms/Management/Company/Company.aspx")
End Sub
End ClassThe other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)I hope this makes sense.Thank you very much guys.Scott.
View 2 Replies
View Related
Feb 15, 2007
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following
table name: DownloadTable
fileName | DownloadSize |
file1 | 45
file2 | 50
file3 | 20
---------------------------------------------
second table
table name: VideoTable
fileName | VideoSize |
file1 | 40
file2 | 60
file3 | 20
----------------------------------------
Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:
SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable
However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?
Thanks in advance
Regards,
View 8 Replies
View Related
Mar 6, 2014
I'm currently in an SQL internship, and am running into an issue with a project they've given me. Primarily, he's asking me to try to get a table containing student entry testing information to update with a new column that's supposed to contain the testing dates. This in mind, there is another table that contains testing data, and who's students are grouped by date, the data in each group all coming from the students table. Issue is, I don't know how to get the testing dates from the TestData table into the Students table. There's already a relationship set up between the TestID fields on the TestData table (PK) and the Students table (FK), so I don't think I can set up a new relationship to get it working.
View 1 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.
Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.
I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.
I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.
How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?
Thanks in advance.
View 5 Replies
View Related
Oct 27, 2000
Can I add a description to a table or field? The way access gives you to add a description to a field.
Thanks!
View 1 Replies
View Related
Jul 20, 2005
Hi, I'm trying to add a modified datetime and userid to all 72 tables in mySQL 2000 database. I have the script to do one table, and a cursor, but itwon't run across all tables. Any help would be appreciated. Thanks...DECLARE @tName varchar(40)DECLARE C1 CURSOR FORselect name from sysobjects where type = 'U'OPEN C1FETCH NEXT FROM C1 INTO @tName-- Check @@FETCH_STATUS to see if there are any more rows to fetchWHILE @@FETCH_STATUS = 0BEGIN-- This is executed as long as the previous fetch succeedsBEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLESET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONALTER TABLE @tName ADDModifiedDT datetime NULL,ModifiedUserID int NULLGOCOMMITFETCH NEXT FROM C1ENDCLOSE C1DEALLOCATE C1GO
View 3 Replies
View Related
Jan 5, 2007
I keep product name, id in Table1.
I keep Category name, id in Table2.
I keep relation between product and category (product_id, category_id) in Table3.
I have added some products to the table with proper category.
Work fine
But for some products I did not specified any category
(ie their id is not present in Table3)
But now I want all such products
(ie all products whose category is nothing)
To be associated with category_id 10
Can I do this simply with SQL queries?
Hope u can help me
sujith
View 4 Replies
View Related
Oct 10, 2013
I have two tables a and b, where I want to add columns from b to a with a criteria. the columns will be added by month criteria. I want to keep all the records in a, and join columns from b. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
,a.[all_val_yr]
[Code] ....
View 6 Replies
View Related
Apr 5, 2006
Hi everybody:
I am working on a query that referrences a table in a remote database. It seems in Management Studio 2005 anytime you open a new query window you should connect to a specific database instance that's why when I refer to the remote database table using Fully Qualified Name it tells me the database name is unknown.
Do you have any solutions for this??
Thanks a lot
View 4 Replies
View Related
Jul 12, 2004
Hi
I keep reading it's possible to add, amend ,etc. tables in visual studio but to do so I need access to the features that allow this.
I can connect to the database but I haven't been able to use any of these database features since they are "blanked" out.
Obviously, I can do all this at the server but ideally I would like to do it remotely.
Any suggestions?
Thanks in advance.
View 2 Replies
View Related
Jun 13, 2000
I have used the following script to add the size of chosen tables in all the databases on a given server. What I need to do is to create a GRAND total. Can someone give me a hint?
exec sp_msforeachdb
@Command1 = "print'?'",
@Command2 = "use ?
SELECT CAST (o.name as char(20)) as 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'
FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
And o.name in ('Table1','Table2','Table3')
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))"
Thank you
View 1 Replies
View Related
Oct 26, 1999
after upgrade sql from 6.5 to 7.0 , there are some new system
table in system database , all are begin with sysremote_....
such as sysremote_tables, i can not find any information about
them , does anyone know what these tables for and whether
these tables will affect the new sql7.0 application database ?
thanks in advance .
regards
zg
View 1 Replies
View Related
Oct 31, 2005
I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME
I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?
View 1 Replies
View Related
Jan 4, 2006
Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.
Any ideas on how to keep the data updated on the website?
View 4 Replies
View Related
May 22, 2008
hi,
I have a web hosting account with Network Solutions. I also have my databases hosted on their sql server 2005. (along with a few thousand other database which don't belong to me) . Network Solutions does not allow me to speak to their Technical Support. I must submit a request and they email me their response. Bottom line they are not going to help me with my problem.
I am using SQL Server Management Studio Express to connect remotely to the server.
I have 2 databases. Currently all data is stored in database1
I need to move the tables with the data for my website forum from database1to database2 on the same SQL Server
I have already generated a script which creates the schema for the tables on
database2. The problem is moving the data. The web Host does not allow DTS
Here are the things they won't allow:
You can use all features of SQL 2000 (it is 2005) except for the following:
- DTS Packages
- Database Replication
- Mail Services
- XML Support
- Distributed Transactions
- Database Maintenance Plans
- Web Assistant
- Multiple Instances
I can't for the life of me figure out how to transfer the data.
I have tried copying and pasting the data and run into problems with Identity keys and constraints.
I tried a sql generator script to create insert scripts for each line of each table but it won't work. I'm not sure why.
Bottom-line I can't import and export directly. And I desperately need to.
Thanks in advance!
View 14 Replies
View Related
Nov 21, 2006
Hi all,
I would be very glad if someone can suggest me what techniques I should use in the following scenario:
I have 2 SQL Server databases : DB1 and DB2. DB 1 is on a remote server (hosting server) and DB 2 is on a local server.
Some tables of each db contain tables that are polulated and changed by the appropriate application, i.e.:
DB1.users, DB1.orders,... etc are managed by "webapplication"
DB1.products ... are not managed by "webapplication" : in fact only used to read from
DB2.products, DB2.customers, .... etc are managed by "winapplication"
DB2.orders,....are partially managed by "winapplication"
Since the amount of data can go over 100000 records i'm wondering what would by the best approach to :
- synchronize the data of DB1.products and DB2.products in DB1 on remote server (updated newly added rows and update changed rows,....)
- the products data is only (at the moment) added, edited and deleted on the local server
I think SQLBulkCopy will not do the job. Should it be possible with some query? Or...?
Any suggestions are appreciated!!
O.
View 1 Replies
View Related
Oct 2, 2015
When adding/removing tables in SSMS 2016 CTP2, I keep tripping into:
Index was outside the bounds of the Array
When editing a SQL Server 2014 Database.
If I use the SSMS for SQL 2014, the error is not happening. Possibly a reintroduced bug. Tried to use the Connect site to submit, but it says I am not enabled to use this area.
View 3 Replies
View Related
Jul 23, 2005
We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
View 4 Replies
View Related
May 7, 2008
Hi all,
Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.
I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.
Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.
Thanks,
Peter
View 3 Replies
View Related
Jan 5, 2007
I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?
-Mathminded
View 4 Replies
View Related
Feb 13, 2006
Hi all,
I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .
my web site is ready
I didn't have problem to upload my site to my hoster.
Now I want to upload all my tables and my stored procedure create locally with VWD express
How can i do it ?
NB: I know i can't design DB (create/modify tables and stored proc) with express edition
thank's for your help
View 1 Replies
View Related
Sep 7, 2007
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
View 2 Replies
View Related
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
Jun 2, 2015
- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
View 4 Replies
View Related
