SQL Database Integration

Re: Converting an Access Databse to a SQL Database

Greg Collins — Thu, 03 Jan 2008 17:19:12 GMT

One table is always so much easier to work with!

Unfortuntately, I don't have a deep knowledge of SQL syntax. I'm still learning. The first question I would have is whether your SQL statement works in a Query window inside SQL Server Management Studio? If so, then it is possible that InfoPath just can't handle that complexity--or maybe it can but needs some additional help. Maybe someone else here, with more SQL knowledge than myself, can help out. If it doesn't work in Management Studio, then I would highly recommend bringing up your question to a SQL-specific newsgroup/forum to get a SQL expert to help out.

Good luck!

Re: Converting an Access Databse to a SQL Database

m181354 — Thu, 03 Jan 2008 13:38:33 GMT

I created two smaller tables but I have another problem now...

I added a Where a clause and an Order By clause at the end of the SQL statement created by Infopath the query and it worked fine using one table as the Main Data Source. But now that I am uisng 2 smaller tables, the query is using Shape Select with an Append Select and a Relate clause at the end of the SQL statement. When I use the Where a clause and an Order By clause I get the following error...

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
[0x80040E14][MSDataShape] Data shape command text contains a syntax error at or near position 2237 in the command. The text near the error is: " [LaborSummaryID] WHERE (((LaborSummary.[ECAM])".

File:script.js
Line:73

I think the Where a clause and an Order By clause needs to be after the Relate statement but I do not know how to do that since the SQL statement is generated by Infopath. I just put it at the end when I was using one table, which did not create the Relate statement, and it worked fine.

Can you help?

Re: Converting an Access Databse to a SQL Database

Greg Collins — Fri, 21 Dec 2007 18:32:42 GMT

Cool! Glad you got it figured out. What was your solution?

Converting an Access Databse to a SQL Database

m181354 — Wed, 19 Dec 2007 20:37:05 GMT

NEVER MIND..FIGURED IT OUT!

I am converting an an Access database that I am using as a prototype to a SQL Database. But I have a problem with the row size...it exceeds the maximum bytes allowed per role(8060). My row size is (11733). I use the table as an Main datasource wjich allows to me to read and write back to it.

I need to break my one large table into a few smaller ones which will also give me better performance too. But how do I make multiple tables or a sql query that would bring all the data together as one Main Datasource?