I am in the process of porting all of my Access 2003 databases to SQL Server. I started with my smallest database, which has only two tables. I have run the wizard five times, with different settings, but every time it only imports the table structure without the data.
Can anyone tell me what I'm missing here? Also, I have created several databases which I can't use, named InstructorsSQL, InstructorsSQL1, InstructorsSQL2, etc. I deleted the database files, but the names are apparently still in the system, since SQL Server won't allow me to use the same name again. How can I get rid of these old names.
I need to upload a very large access database to sql 2005. Do I break this down by tables, queries or what? Will it take a long time. I believe that database is 3488,888 kb in size. Thank youDee
A few days ago I installed SQL Server 2005 Express edition and successfully used the upsizing wizard to convert a MS Access database to SQL Server database. Today I found I could get VB.Studio Express. The instructions said to delete SQL Server Express and .NET before installing VB Studio because it would re-install them both. I faithfully did that but since then I have been unable to get the upsizing wizard to work again. I have uninstalled SQL Server and re-installed it but still cant get it working. From SQL Server Configuration manager I can confirm that Shared Memory and TCP/IP protocol are both enabled for both protocols for clent and protocols for SQLEXPRESS Both the SQL Server and the SQL Browser are running. And from SQL Server Surface Configuration I can confirm that OPENROWSET AND OPENDATASOURCE SUPPORT is disabled; CLR Registration is disabled; OLE AUTOMATION is enabled; and xpCommandshell is disabled.
When I run the upsizing module it goes through the motions but the report at the end says "table was skipped or export failed" for each of the tables in the database.
I have looked everywhere I can think of to try to find a solution but no one so far has been able to help.
I have inherited some 4 SQL servers running 6.5 and 7.0
Some of the servers have MS Access Installed and some have an "upsizing to SQL Server" wizard under the MS Access TOOLS/Add-Ins/.
Some do not. I need to locate where this utility comes from. I think it must come from the 6.5 or 7.0 SQL Server Install CD.
Does anyone know for sure?
Thanks,
Mark Blackburn o `"._ _ M onterey mark@mbari.org o / _ |||;._/ ) B ay Science at its Best! ~ _/@ @ /// ( ~ A quarium (831) 775-1880 ( (`__, ,`| R esearch http://www.mbari.org/ '.\_/ |\_.' I nstitute
Database Administrator MBARI Personal Web Page: http://www.mbari.org/~mark/
I am not sure if this is appropriate forum to ask but I have the folowing problem: My client has MS Access database with about 116 tables in it. They need to transfer all tables to MS SQL server and keep application in working mode :) so far so good. I decided to use MS Access Upsizing wizard to do the job and this is the reason I am writing now... when the wizards proceeds (I am moving to SQL 2005) about 50/116 tables it just stops without warning or error in report... The only error I got is that some tables has no unique index and they cannot be updattable.
Hi, I want to upsize a db from Access to SQL Server. I have a version of Access 2000 that has not got the Upsizing Wizard add-in loaded, and can't find the install disc. Does anyone know if it is downloadable, and if so, from where?
I am using the Upsizing Wizard to create a copy of an Access database as a SQL Server database. The original .mdb is 14 mb. The resulting MSSQL database is 72 MB. Why the huge increase in size?
This is my first post ever so forgive me if this is too basic a question.
I have an Access 2000 database that I'm trying to make into a SQL 2005 Express database. Both databases are on the same machine (I've given up with the networking of this) and, after straightening out several problems I've ran into one I can't seem to fix.
Using the Upsizing Wizard in Access 2000 I try to connect to SQL 2005 Express but I keep getting this error: http://www.paulmauer.com/SQL%20connection%20error.doc
SInce both programs are on the same machine I don't understand what is happening. Any help would be appreciated.
By default, the Upsizing Wizard transfers all indexes as nonclustered indexes. You can modify the Upsizing Wizard to transfer the primary key index to a clustered index. To make this change, start Microsoft Access and open the upsizing wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, click the Modules tab and open the UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change the default value from False to True.
<<< end quote Question: I am using ACCESS 2002 upsize wizard, I searched my computer for *.mda and could not find any wzcs*.mda. How do I set it so that it creates clustered-index instead of non-clustered-index?
Has this happened to anyone else?I have a large job and all the queries run fine when usedindividually, but in the job, SQL says completed with success but intwo cases the queries did not run. One of them is even ridiculouslysimple: DROP TABLE tblMyTable, etc.
Hi Guys, My little bulk insert is only bringing every second row of a CSV file. this is not good as i need every row. My SQLcommand is thus. InsertCommand="BULK INSERT TBL_Unitel_services FROM 'C:/webroot/servicedesk/csvs_Services/csv.csv' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '', MAXERRORS = 0) "
Hello all. I have a stored procedure that seems to skip the execution of an entire line of code (which happens to be a call to execute another stored procedure), but ONLY if it is being used from C# (using the System.Data.SqlClient namespace... object: SqlCommand, etc).
Basic structure:
Event SP: top-most parent -->Calls UpdateXs SP: This is the SP that will not call his child SP ----->Calls UpdateX: This is the SP that is being ignored when called from .NET (In a loop, each iteration, max iterations 10).
(1) I can call the top-most stored procedure (Event SP) with the same arguments in Query Analyzer and all SP's will execute. (2) I can call UpdateXs SP with the same arguments that are being passed to it from Event SP in the Query Analyzer, and again, it's child sp executes (UpdateX). (3) I can also call the last child (UpdateX) directly in Query Analyzer with the same arguments that are passed in production (from executing the top-most parent in C#), and it executes correctly.
I'm positive that at no point the last child (UpdateX) is being passed NULL values. I made debug tables and before execution of an SP from within another SP, I store all the arguments in a debug table--none are null. Also, to figure out what was wrong with the last child (UpdateX) , the first statement in inserts into another debug table, just to show that that execution has made it into the sp--when the top parent is called from C#, it doesn't insert into my table (doesn't execute).
There are statements inside Event SP that execute after the sp call to UpdateXs SP. These statement always execute. Also, there are statements inside UpdateXs SP that execute after the sp call to the last child. These statement always execute too.
I am now clueless. I had developed & debugged all sp's in Query Analyzer before trying to use it live (with the C#). It's always fine in Query Analyzer, but ALWAYS skips the last sp in production.
Is there some fundamental principle I am missing here in MS SQL stored procedures? I am only 3 levels deep in SP calls, so I didn't think that would be an issue (I have made SP's that went a LOT deeper in calls than that in the past with no issues).
Apologies if this has been raised in the past, but 6 hours of web searching today hasn't turned up anything!
I'd like to use the Slowly Changing Dimension (SCD) Wizard to keep track of tables in my relational database. This means 200+ tables. I don't want to step through the UI Wizard for each table. Ideally I'd like to be able to create the SCD transformation in code, but I can find no good examples for doing this. The MSDN examples here are too brief and don't allow me to expand out to the level I need.
As in any database, columns come and (very rarely, go), and having a programmatic solution to this would mean that I could be flexible and cope with these situations.
So, my question is: Has anyone implemented SCD functionality in code, or have any code examples that do this, that I might learn from. Or, any tips/pointers if I'm barking up totally the wrong tree.
I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:
1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".
2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.
A further note: I created all tables in the SQL2005 database before running the Import.
I€™m trying to copy data from production to my local machine using the SQL Server 2005 import and export wizard. It works fine if I select a small number of tables but throws errors When there€™s a lot of tables. Have you ever experienced problems using it? Is there a better way to transfer the data?
the data source is SQL Server 2000 and the target is 2005. I have the optimize for many tables and transaction options selected
Here€™s the errors I get
Execute the transfer with the TransferProvider. (Error) Messages · ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
We have restored a database on new server without keeping replication settings. Now while creating the publication no tables are shown in new publication wizard window. In fact we have hundred of tables in database and included in replication on source server from where backup was taken.
What may be the reason that tables are not appearing?
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component. Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions: 1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection 2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
I have an Access 2000 DB that I've been considering upsizing to SQL Server2K. I'm wondering if anyone can share their experiences in upsizing and letme know of any tips or pratfalls. I've been playing around with the upsizewizard however not all of my tables have properly converted. None of theprimary keys transfer (I understand this is a limitation) and the wholething hasn't really worked the way I thought it would. I have triedimporting the data using DTS from SQL Server which has been more successfulhowever now I'm considering whether it's even worth upsizing to SQL.What I'm looking for is some advice from people who have gone through theprocess about how smooth the transition was, and whether it worked out asplanned. The DB I'm working on is an educational database, approx 80,000records and would ultimately have 30 - 40 users max (and on most times nomore than 5 - 10 users). Part of the reason I'm thinking about upsizing isto put the DB online to allow for dynamic updates and web access.Thanks for any advice.Mike
When I try using the Upsizing Wizard to upsize a database to SQL database, Iam unable to login because I get an error message which says: "Login failedfor user 'Jim Richards'. Reason: Not associated with a trusted SQL ServerConnection." I am using 'Windows Authentication' and I am entering myWindows User Name and Password. How do I fix this please? Thanks in advance,Jim Richards
I have four complex Access 97 Databases which I need to upsize to SQLServer 7. The first thing I did was convert them to Access2000 because I was told that the upsizing wizard wal great. When I ran the wizard the report said that the queries couldn't be upsized because they had criteria which referred to a textbox on a form and that SQLServer couldn't handle this. When I remove the criteria, the queries seem to upsize very nicely. Dows anyone have a way to get the criteria information from the form to the query without having to do a major rewrite of database?
I'm quite new to MS SQL and I don't really have a clue in what I am doing. I set up an evaluation of SQL 2005 to help me create a DB locally for testing an development.
I would continue to use Access, but the db is quite large and has 18 tables with lots of relationships. It also has anywhere from 20 to 50 concurrent users at one time, so therfore I am getting a lot of "Too Many Client Tasks" errors.
I tried using the Upsizing Wizard from access, but everytime I try to create a new DB in SQL, I get an error saying "Overflow". Thats it, no more, no less. If it said more I could possibly troubleshoot, but I have no idea what this means. Has anyone encountered this issue before? Does someone know where I can go to get help? I'm lost, so any help will be much appreciated.
We have a document tracking application at my company which is extremely slow to load up and causes delays when staff log into their machines. This is because its launched from the login script. The application has an Access DB as the backend and is getting quite full which is why I think the performance is so poor.
My question is, can I upsize the DB to SQL server and link the tables into Access to improve performance?
I wasn't sure if this would make that much of a difference.
HiI have a fairly complex access frontend/backend app which I need to upsizeto SQL Server. At this stage I would prefer to use access as frontend. Ihave the following questions;1. Should I keep using the mdb as frontend or switch to an adp (accessproject) instead? Any reasons?2. I have used IIF (immediate if) frequently in access queries. Whenconverting queries to SQL Server, is there an easy way to replace the IIFfunction?3. Is it possible for access frontend to connect/link to two separate SQLServer dbs, main and archive at the same time?ThanksRegards
I keep getting the warning I don't have administrative privilege but I'm the only user on the computer and have verified in vb that I am the administrator.
I am having a problem with the MSAccess Upsizing wizard. I have seen the problem with both Access 2002 and 2003. I believe the problem is more SQL related. Here is the problem.
I have two large Access 2002 Dbs. They are being merged in to one SQL DB.
I run the wizard on the first DB using the 'create new DB' option and everything works perfectly.
On the second DB I connect to the DB created in the first Upsize. BUT, some of the tables upsize and some do not. It usually creates the table and then fails on moving the data so none of the indexes, relationships, etc. get created.
IF I UPSIZE THIS DB TO A NEW SQL DB IT WORKS FINE! So I know there are no data issues.
I have used both a trusted connection and SA login. It makes no difference.
I am doing this all from the server directly, no workstations involved.
I thought maybe the DB was not large enough and unable to expand during upsize so I doubled the initial size before attempting to upsize the second DB - made no difference.
I was just wondering if there were any problems or defects when upsizing a db from access to sql server..........i've been using access for awhile, but new to sql server and prefer the initial creating of a db to be done in access
Hi i am upsizing a db from access to msde. I provide it a username and password. But when i try to connect to the db in Visual Studio i get this error "Login failed for user 'user': Reason. not assocaited with a trusted SQL Server Connection"Thanks
When you Upsize from Access using the wizard, unsurprisingly, a Unique index is created on the PK field, but these are all non-clustered. I presume there isn't one definitive answer to whether a index should be clustered or not, (which I understand means the table's records are held on disk contiguously), but generally, is it worth altering these all to become clustered? Would you selectively cluster only those tables which you think would benefit most? Leave them all unclustered and look for bottle-necks?
