If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
upsizing to sql 2005
I am using the upsizing tool to slq 2005 from access 2003. I have three
tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#2
|
|||
|
|||
upsizing to sql 2005
As far as the data is concerned, there should be no problem as long as the
table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#3
|
|||
|
|||
upsizing to sql 2005
How can I fix thist kind of problems? I am running into problem where it
stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#4
|
|||
|
|||
upsizing to sql 2005
If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do
it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#5
|
|||
|
|||
upsizing to sql 2005
Michel,
I ran this again and it looks like everything updated, but when I look at the adp file it stoped at the same point every time. Here is an idea of what I am getting: Upsizing Wizard Report 1Database Microsoft Access Database: C:\database\BizChair-2.mdb SQL Server Database: BizChair-2SQL Upsizing Parameters Table Attributes to Export Indexes Table relationships: Validation rules Upsized using DRI Defaults Structure only, no Timestamp fields added: No tables Modifications to Existing Database Attach newly created Save password and SQL Server tables user ID with attached tables Client/Server Modifications Create a new Access Save password and client/server user ID with application. application then this happens: Query Name: Chicago Textile - CT Upsized using SQL: CREATE FUNCTION "Chicago Textile - CT" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CT')) ORDER BY StoreData.id) Query Name: Chicago Textile - FLW Upsized using SQL: CREATE FUNCTION "Chicago Textile - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CT')) ORDER BY FabricsData.id) Query Name: Chicago Textile - Sections Upsized using SQL: CREATE FUNCTION "Chicago Textile - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-CT')) ORDER BY SectionData.id) Query Name: ChildBrite - FLW Upsized using SQL: CREATE FUNCTION "ChildBrite - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CB')) ORDER BY FabricsData.id) Query Name: ChildBrite - CB Upsized using SQL: CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) - 40 - 18-Oct-07 Query Name: Children's Factory - CHF This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings - FLW This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings Inc - CFI This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) It continues and never populates the adp after that, but it seems data went into the sql server because the records for all tables are the same for access and sql. I am using alot of queries and cross queries. Do not know what to fix this problem. Thanks Dee "Michel Walsh" wrote: If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#6
|
|||
|
|||
upsizing to sql 2005
The Cross queries (crosstab queries, you mean) are not upsizeable to MS SQL
Server. While SQL from MS SQL Server 2005 has some PIVOTing capabilities, it cannot match the capabilities of Jet, such as not having an IN clause in the PIVOT statement. You will have to re-write all those. For queries like: Query Name: Children's Factory - CHF This query is not upsizeable Since, it seems, we don't have the JET-SQL statement, it is hard to tell why it cannot be upsized to MS SQL Server - SQL. *IF* the query is NOT a crosstab, the most common kind of problem is that the query uses a VBA function. It may also use SQL specific to JET. Edit each of these queries within JET, then, assuming you have some high-end managing tool at your disposition for MS SQL Server (other than Access), try to re-write the query in MS SQL Server. The tool I personally use is the MS SQL Server Analyzer, which is a kind of "debug immediate window", but for SQL statements. If you are still unable to upsize one of these queries, you can always ask here, query by query, if someone got an idea. Hoping it may help, Vanderghast, Access MVP "Dee" wrote in message ... Michel, I ran this again and it looks like everything updated, but when I look at the adp file it stoped at the same point every time. Here is an idea of what I am getting: Upsizing Wizard Report 1Database Microsoft Access Database: C:\database\BizChair-2.mdb SQL Server Database: BizChair-2SQL Upsizing Parameters Table Attributes to Export Indexes Table relationships: Validation rules Upsized using DRI Defaults Structure only, no Timestamp fields added: No tables Modifications to Existing Database Attach newly created Save password and SQL Server tables user ID with attached tables Client/Server Modifications Create a new Access Save password and client/server user ID with application. application then this happens: Query Name: Chicago Textile - CT Upsized using SQL: CREATE FUNCTION "Chicago Textile - CT" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CT')) ORDER BY StoreData.id) Query Name: Chicago Textile - FLW Upsized using SQL: CREATE FUNCTION "Chicago Textile - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CT')) ORDER BY FabricsData.id) Query Name: Chicago Textile - Sections Upsized using SQL: CREATE FUNCTION "Chicago Textile - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-CT')) ORDER BY SectionData.id) Query Name: ChildBrite - FLW Upsized using SQL: CREATE FUNCTION "ChildBrite - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CB')) ORDER BY FabricsData.id) Query Name: ChildBrite - CB Upsized using SQL: CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) - 40 - 18-Oct-07 Query Name: Children's Factory - CHF This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings - FLW This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings Inc - CFI This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) It continues and never populates the adp after that, but it seems data went into the sql server because the records for all tables are the same for access and sql. I am using alot of queries and cross queries. Do not know what to fix this problem. Thanks Dee "Michel Walsh" wrote: If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#7
|
|||
|
|||
upsizing to sql 2005
What I do not understand then, is why did these work and it stoped the
Here is an example that is in the earlier part of the upsizing: Query Name: Arcadia - ARC Upsized using SQL: CREATE FUNCTION "Arcadia - ARC" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-ARC')) ORDER BY StoreData.id) Query Name: Arcadia - FLW Upsized using SQL: CREATE FUNCTION "Arcadia - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-ARC')) ORDER BY FabricsData.id) Query Name: Arcadia - Sections Upsized using SQL: CREATE FUNCTION "Arcadia - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-ARC')) ORDER BY SectionData.id) What is the difference here? Thanks Dee "Michel Walsh" wrote: The Cross queries (crosstab queries, you mean) are not upsizeable to MS SQL Server. While SQL from MS SQL Server 2005 has some PIVOTing capabilities, it cannot match the capabilities of Jet, such as not having an IN clause in the PIVOT statement. You will have to re-write all those. For queries like: Query Name: Children's Factory - CHF This query is not upsizeable Since, it seems, we don't have the JET-SQL statement, it is hard to tell why it cannot be upsized to MS SQL Server - SQL. *IF* the query is NOT a crosstab, the most common kind of problem is that the query uses a VBA function. It may also use SQL specific to JET. Edit each of these queries within JET, then, assuming you have some high-end managing tool at your disposition for MS SQL Server (other than Access), try to re-write the query in MS SQL Server. The tool I personally use is the MS SQL Server Analyzer, which is a kind of "debug immediate window", but for SQL statements. If you are still unable to upsize one of these queries, you can always ask here, query by query, if someone got an idea. Hoping it may help, Vanderghast, Access MVP "Dee" wrote in message ... Michel, I ran this again and it looks like everything updated, but when I look at the adp file it stoped at the same point every time. Here is an idea of what I am getting: Upsizing Wizard Report 1Database Microsoft Access Database: C:\database\BizChair-2.mdb SQL Server Database: BizChair-2SQL Upsizing Parameters Table Attributes to Export Indexes Table relationships: Validation rules Upsized using DRI Defaults Structure only, no Timestamp fields added: No tables Modifications to Existing Database Attach newly created Save password and SQL Server tables user ID with attached tables Client/Server Modifications Create a new Access Save password and client/server user ID with application. application then this happens: Query Name: Chicago Textile - CT Upsized using SQL: CREATE FUNCTION "Chicago Textile - CT" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CT')) ORDER BY StoreData.id) Query Name: Chicago Textile - FLW Upsized using SQL: CREATE FUNCTION "Chicago Textile - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CT')) ORDER BY FabricsData.id) Query Name: Chicago Textile - Sections Upsized using SQL: CREATE FUNCTION "Chicago Textile - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-CT')) ORDER BY SectionData.id) Query Name: ChildBrite - FLW Upsized using SQL: CREATE FUNCTION "ChildBrite - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CB')) ORDER BY FabricsData.id) Query Name: ChildBrite - CB Upsized using SQL: CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) - 40 - 18-Oct-07 Query Name: Children's Factory - CHF This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings - FLW This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings Inc - CFI This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) It continues and never populates the adp after that, but it seems data went into the sql server because the records for all tables are the same for access and sql. I am using alot of queries and cross queries. Do not know what to fix this problem. Thanks Dee "Michel Walsh" wrote: If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#8
|
|||
|
|||
upsizing to sql 2005
Maybe all what could be done automatically was done.
Vanderghast, Access MVP "Dee" wrote in message ... What I do not understand then, is why did these work and it stoped the Here is an example that is in the earlier part of the upsizing: Query Name: Arcadia - ARC Upsized using SQL: CREATE FUNCTION "Arcadia - ARC" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-ARC')) ORDER BY StoreData.id) Query Name: Arcadia - FLW Upsized using SQL: CREATE FUNCTION "Arcadia - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-ARC')) ORDER BY FabricsData.id) Query Name: Arcadia - Sections Upsized using SQL: CREATE FUNCTION "Arcadia - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-ARC')) ORDER BY SectionData.id) What is the difference here? Thanks Dee "Michel Walsh" wrote: The Cross queries (crosstab queries, you mean) are not upsizeable to MS SQL Server. While SQL from MS SQL Server 2005 has some PIVOTing capabilities, it cannot match the capabilities of Jet, such as not having an IN clause in the PIVOT statement. You will have to re-write all those. For queries like: Query Name: Children's Factory - CHF This query is not upsizeable Since, it seems, we don't have the JET-SQL statement, it is hard to tell why it cannot be upsized to MS SQL Server - SQL. *IF* the query is NOT a crosstab, the most common kind of problem is that the query uses a VBA function. It may also use SQL specific to JET. Edit each of these queries within JET, then, assuming you have some high-end managing tool at your disposition for MS SQL Server (other than Access), try to re-write the query in MS SQL Server. The tool I personally use is the MS SQL Server Analyzer, which is a kind of "debug immediate window", but for SQL statements. If you are still unable to upsize one of these queries, you can always ask here, query by query, if someone got an idea. Hoping it may help, Vanderghast, Access MVP "Dee" wrote in message ... Michel, I ran this again and it looks like everything updated, but when I look at the adp file it stoped at the same point every time. Here is an idea of what I am getting: Upsizing Wizard Report 1Database Microsoft Access Database: C:\database\BizChair-2.mdb SQL Server Database: BizChair-2SQL Upsizing Parameters Table Attributes to Export Indexes Table relationships: Validation rules Upsized using DRI Defaults Structure only, no Timestamp fields added: No tables Modifications to Existing Database Attach newly created Save password and SQL Server tables user ID with attached tables Client/Server Modifications Create a new Access Save password and client/server user ID with application. application then this happens: Query Name: Chicago Textile - CT Upsized using SQL: CREATE FUNCTION "Chicago Textile - CT" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CT')) ORDER BY StoreData.id) Query Name: Chicago Textile - FLW Upsized using SQL: CREATE FUNCTION "Chicago Textile - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CT')) ORDER BY FabricsData.id) Query Name: Chicago Textile - Sections Upsized using SQL: CREATE FUNCTION "Chicago Textile - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-CT')) ORDER BY SectionData.id) Query Name: ChildBrite - FLW Upsized using SQL: CREATE FUNCTION "ChildBrite - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CB')) ORDER BY FabricsData.id) Query Name: ChildBrite - CB Upsized using SQL: CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) - 40 - 18-Oct-07 Query Name: Children's Factory - CHF This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings - FLW This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings Inc - CFI This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) It continues and never populates the adp after that, but it seems data went into the sql server because the records for all tables are the same for access and sql. I am using alot of queries and cross queries. Do not know what to fix this problem. Thanks Dee "Michel Walsh" wrote: If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#9
|
|||
|
|||
upsizing to sql 2005
Michel,
I am not sure how to fix the rest. We are currently using the access database on the yahoo webserver and it gets worked on all day and updated several times a day on the server. If you or some one could explain to me how to fix this I will have the web content people start making their changes, while I make the sql changes. I just do not know what to fix and how to fix it. Thanks Dee "Michel Walsh" wrote: Maybe all what could be done automatically was done. Vanderghast, Access MVP "Dee" wrote in message ... What I do not understand then, is why did these work and it stoped the Here is an example that is in the earlier part of the upsizing: Query Name: Arcadia - ARC Upsized using SQL: CREATE FUNCTION "Arcadia - ARC" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-ARC')) ORDER BY StoreData.id) Query Name: Arcadia - FLW Upsized using SQL: CREATE FUNCTION "Arcadia - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-ARC')) ORDER BY FabricsData.id) Query Name: Arcadia - Sections Upsized using SQL: CREATE FUNCTION "Arcadia - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-ARC')) ORDER BY SectionData.id) What is the difference here? Thanks Dee "Michel Walsh" wrote: The Cross queries (crosstab queries, you mean) are not upsizeable to MS SQL Server. While SQL from MS SQL Server 2005 has some PIVOTing capabilities, it cannot match the capabilities of Jet, such as not having an IN clause in the PIVOT statement. You will have to re-write all those. For queries like: Query Name: Children's Factory - CHF This query is not upsizeable Since, it seems, we don't have the JET-SQL statement, it is hard to tell why it cannot be upsized to MS SQL Server - SQL. *IF* the query is NOT a crosstab, the most common kind of problem is that the query uses a VBA function. It may also use SQL specific to JET. Edit each of these queries within JET, then, assuming you have some high-end managing tool at your disposition for MS SQL Server (other than Access), try to re-write the query in MS SQL Server. The tool I personally use is the MS SQL Server Analyzer, which is a kind of "debug immediate window", but for SQL statements. If you are still unable to upsize one of these queries, you can always ask here, query by query, if someone got an idea. Hoping it may help, Vanderghast, Access MVP "Dee" wrote in message ... Michel, I ran this again and it looks like everything updated, but when I look at the adp file it stoped at the same point every time. Here is an idea of what I am getting: Upsizing Wizard Report 1Database Microsoft Access Database: C:\database\BizChair-2.mdb SQL Server Database: BizChair-2SQL Upsizing Parameters Table Attributes to Export Indexes Table relationships: Validation rules Upsized using DRI Defaults Structure only, no Timestamp fields added: No tables Modifications to Existing Database Attach newly created Save password and SQL Server tables user ID with attached tables Client/Server Modifications Create a new Access Save password and client/server user ID with application. application then this happens: Query Name: Chicago Textile - CT Upsized using SQL: CREATE FUNCTION "Chicago Textile - CT" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CT')) ORDER BY StoreData.id) Query Name: Chicago Textile - FLW Upsized using SQL: CREATE FUNCTION "Chicago Textile - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CT')) ORDER BY FabricsData.id) Query Name: Chicago Textile - Sections Upsized using SQL: CREATE FUNCTION "Chicago Textile - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-CT')) ORDER BY SectionData.id) Query Name: ChildBrite - FLW Upsized using SQL: CREATE FUNCTION "ChildBrite - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CB')) ORDER BY FabricsData.id) Query Name: ChildBrite - CB Upsized using SQL: CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) - 40 - 18-Oct-07 Query Name: Children's Factory - CHF This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings - FLW This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings Inc - CFI This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) It continues and never populates the adp after that, but it seems data went into the sql server because the records for all tables are the same for access and sql. I am using alot of queries and cross queries. Do not know what to fix this problem. Thanks Dee "Michel Walsh" wrote: If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
#10
|
|||
|
|||
upsizing to sql 2005
Update only the data portion. In your Access application, as an-mdb, not as
an adp, then link to the tables in the MS SQL Server (using the same name you were using in your Jet queries and elsewhere in your application) and your application should continue to work (with its Jet-queries, on linked table). You then have time to migrate manually your queries, if required, since your application and the data, in the database, are working fine. Hoping it may help, Vanderghast, Access MVP "Dee" wrote in message ... Michel, I am not sure how to fix the rest. We are currently using the access database on the yahoo webserver and it gets worked on all day and updated several times a day on the server. If you or some one could explain to me how to fix this I will have the web content people start making their changes, while I make the sql changes. I just do not know what to fix and how to fix it. Thanks Dee "Michel Walsh" wrote: Maybe all what could be done automatically was done. Vanderghast, Access MVP "Dee" wrote in message ... What I do not understand then, is why did these work and it stoped the Here is an example that is in the earlier part of the upsizing: Query Name: Arcadia - ARC Upsized using SQL: CREATE FUNCTION "Arcadia - ARC" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-ARC')) ORDER BY StoreData.id) Query Name: Arcadia - FLW Upsized using SQL: CREATE FUNCTION "Arcadia - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-ARC')) ORDER BY FabricsData.id) Query Name: Arcadia - Sections Upsized using SQL: CREATE FUNCTION "Arcadia - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-ARC')) ORDER BY SectionData.id) What is the difference here? Thanks Dee "Michel Walsh" wrote: The Cross queries (crosstab queries, you mean) are not upsizeable to MS SQL Server. While SQL from MS SQL Server 2005 has some PIVOTing capabilities, it cannot match the capabilities of Jet, such as not having an IN clause in the PIVOT statement. You will have to re-write all those. For queries like: Query Name: Children's Factory - CHF This query is not upsizeable Since, it seems, we don't have the JET-SQL statement, it is hard to tell why it cannot be upsized to MS SQL Server - SQL. *IF* the query is NOT a crosstab, the most common kind of problem is that the query uses a VBA function. It may also use SQL specific to JET. Edit each of these queries within JET, then, assuming you have some high-end managing tool at your disposition for MS SQL Server (other than Access), try to re-write the query in MS SQL Server. The tool I personally use is the MS SQL Server Analyzer, which is a kind of "debug immediate window", but for SQL statements. If you are still unable to upsize one of these queries, you can always ask here, query by query, if someone got an idea. Hoping it may help, Vanderghast, Access MVP "Dee" wrote in message ... Michel, I ran this again and it looks like everything updated, but when I look at the adp file it stoped at the same point every time. Here is an idea of what I am getting: Upsizing Wizard Report 1Database Microsoft Access Database: C:\database\BizChair-2.mdb SQL Server Database: BizChair-2SQL Upsizing Parameters Table Attributes to Export Indexes Table relationships: Validation rules Upsized using DRI Defaults Structure only, no Timestamp fields added: No tables Modifications to Existing Database Attach newly created Save password and SQL Server tables user ID with attached tables Client/Server Modifications Create a new Access Save password and client/server user ID with application. application then this happens: Query Name: Chicago Textile - CT Upsized using SQL: CREATE FUNCTION "Chicago Textile - CT" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CT')) ORDER BY StoreData.id) Query Name: Chicago Textile - FLW Upsized using SQL: CREATE FUNCTION "Chicago Textile - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CT')) ORDER BY FabricsData.id) Query Name: Chicago Textile - Sections Upsized using SQL: CREATE FUNCTION "Chicago Textile - Sections" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT SectionData.* FROM SectionData WHERE (((SectionData.id) Like '%-CT')) ORDER BY SectionData.id) Query Name: ChildBrite - FLW Upsized using SQL: CREATE FUNCTION "ChildBrite - FLW" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT FabricsData.* FROM FabricsData WHERE (((FabricsData.id) Like '%-CB')) ORDER BY FabricsData.id) Query Name: ChildBrite - CB Upsized using SQL: CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) - 40 - 18-Oct-07 Query Name: Children's Factory - CHF This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings - FLW This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) Query Name: Choice Furnishings Inc - CFI This query is not upsizeable CREATE FUNCTION "ChildBrite - CB" () RETURNS TABLE AS RETURN (SELECT TOP 100 PERCENT StoreData.* FROM StoreData WHERE (((StoreData.id) Like '%-CB')) ORDER BY StoreData.id) It continues and never populates the adp after that, but it seems data went into the sql server because the records for all tables are the same for access and sql. I am using alot of queries and cross queries. Do not know what to fix this problem. Thanks Dee "Michel Walsh" wrote: If the upsizer cannot convert a Jet-query to an MS SQL-query, you have to do it by hand, in the end of the process. As example, if your query uses a VBA function, like Nz, then you have to change it for COALESCE; etc. It is a case-by-case job. If you have a, one, but specific problem at that step, you can use the newsgroup to ask what can be done ! for a very *specific* problem, that is. So, do the most that can be done, automatically, and in the end, add the required 'manual' touch. Vanderghast, Access MVP "Dee" wrote in message ... How can I fix thist kind of problems? I am running into problem where it stops at the same query every time and I do not know what the problem is. Is there any documentation I can view that would tell me what to look for? Is there anything I need to be running that would check the database before upsizing it? Thanks Dee "Michel Walsh" wrote: As far as the data is concerned, there should be no problem as long as the table design is similar. So, if your actual data is coherent under Jet, it will also be coherent under MS SQL Server 2005, most probably. A notable exception is that Jet allows multiple NULL under an index not allowing duplicated values, while MS SQL Server 2005 only allows one row with a NULL under the same design. That does not mean that all your queries will be fine, though, due to many difference between Jet and MS SQL Server... In fact, it is highly probable that some queries won't be upgraded automatically (such as those implying a crosstab, among others), or those using VBA functions. Vanderghast, Access MVP "Dee" wrote in message ... I am using the upsizing tool to slq 2005 from access 2003. I have three tables which use 100's of queries. Some if the primary keys to the three tables may be the same. For example I have a storedata table, a fabric table and a section table. In the storedata table the fabrics that go to some of the queires will have the same extention like -dd and the same for sections which are arms, headrest, etc. Can I upsize one store at a time? How can I fix this problem? Thanks Dee |
Thread Tools | |
Display Modes | |
|
|