I'm using the SSMA to migrate Access databases into SQL Sever. It's a pretty nifty tool, however, I am having some trouble fully migrating the data over.
When I select 'Convert Schema' the schemas for all the tables are created on the server and *all* the table names appear in the Server Metadata Explorer. There are the usual warnings about name changes and primary key additions and the like but importantly, the output window says that there were no errors. Scrolling through all the tables on the server explorer with the 'Table' tab selected confirms that the schemas for all the tables have been migrated, but for one or two of the newly created tables, the 'Data' tab shows the error message "Failed to retrieve data: Invalid object name 'NewDB.dbo.Pref'" for example. Consequently, when I move on to migrating the data, the operation fails for the tables showing these errors and the error message 'The table 'NewDB.dbo.[Pref]' does not exist in SQL Server' is shown when it is displaying in the Metadata Explorer. When I view the server schema in Management Studio Express, it confirms that they have not been migrated. I have tried to look at the failing tables' schemas and identify any similarities which might be causing them to fail migration but they are all very different so I don't think it's a schema issue.
Hi, I am converting a schema from oracle to sql server 2005 using Sql Server Migration Assistant but for the conversion of each and every table it is asking me to connect to oracle. How can i convert this schema without again and again specifying the connection? Thanks in advance, Aru
Has anyone ever tried to use this free software to migrate an old ORACLE 8.1.7 database to SQL Server 2005? I was just wondering if there were any "gotchas" I should be aware of before hand. Thanks, Robert
I am trying to migrate an Access 2003 database to SQL Server 2005 and am having various problems:
1. On one table, the migration gives the following on an autonumber field: A2SS0023: SQL Server Migration Assistant for Access Warning Message: Default value '0' added to target column (1), followed later on by A2SS0028: Auto-increment attribute of the column 'MSEQ' was not converted to Identity because the column has a default value.
This table has about 80,000 records.
2. If I include another table in the migration, this one with 3.4 million records (hence our need to upgrade to SQL server 2005), I am getting a timeout error, even when I try to migrate the table by itself. I have verified that all timeout options are set to zero (unlimited time).
Any suggestions would be appreciated.
-- Larry West, MCSD, MCPD, MCTS: SQL Server 2005 [so yes, I should know the answer to this, but I don't], MOUS, FLMI, ACS
I selected my source Oracle database and my target SQL Server database. I click the convert SQL button and it takes about 20 minutes to convert everything. Then what? The Migrate Data button is greyed out. I configured the linked server as is outlined in the instructions. The "Migrate Data" button is always greyed out. I need to get this working soon so that I can convince the management team that moving to SQL Server is the way to go. Thanks.
i am transfering an Oracle 10g database to SQL Server 2005. i have 2 pcs one with SQL Server 2005 with SSMA For Oracle Extension 3.1 installed, also with Oracle 10g client configured to connect the Oracle database in another pc. the other pc has Oracle 10g database server installed with SSMA For Oracle 3.1. whenever i try to migrate the data there is an exception which says
"Errors: SQL Server cannot access the source table"
i dont understand why its there. i hav successfully converted the schema and loaded them in SQL Server. can anybody tell me a soltuion asap.
I can connect to my Oracle database and I can connect to my SQL Server database. But the "Convert Schema" button stays disabled. What am I doing wrong? Anyone have experience using this tool?
I'm trying to make the business case to move our business databases to SQL Server 2005 from Oracle 9i. The SQL Server Migration Assistant for Oracle will be absolutely necessary for this to happen. However, I am unable to launch the assistant. It tells me I have an old version of java installed - but I don't. It is a new XP machine and I downloaded and installed j2sdk-1_4_2_12-windows-i586-p.exe which did not help (I rebooted). Why does it think I have an old version and how can I get this assistant working.
I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.
I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).
The file is actually a backend, so nothing to migrate other than tables.
After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.
Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):
[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.
There is nothing wrong, so far as I can determine, with the relationships involved.
I can insert data into the tables using any of the following methods: 1. Directly in Access, in the source backend. 2. Using the original Access frontend application, attached to the source backend. 3. Using VB.Net forms I am developing, in the 'upsized' database. 4. Directly in the Management Studio, in the 'upsized' database.
None of these four methods complains in the least about the relationships which SSMA balks at.
I downloaded the Microsoft SQL Server Migration Assistant for Access 4 weeks ago. Today, I tried this program for the first time. I added one Database with 3 Access 2003 Tables to the Access Metadata Explorer. I right clicked Databaes on the Access Metadata pane and did not see the "Convert Schema" highlighted!!!??? What is wrong with the program? How can I make it highlighted? Please help and advise.
I was testing the SSMA with SQL Server Express yesterday and it converted the database over from Access 2000 to SQL Server without incident using the wizard; that is until I attempted to execute the final step and connect the SQL Server Express. At that point, I was unable to move the database into SQL Server because I the connection failed. It failed in the sense that I was able to enter the Server name but the boxes to move forward in the process were all grayed out.
Has anyone used SSMA (SQL Server Migration Assistant) with SQL Server Express?
I don't see all my MySQL tables inside SSMA 5.2 after connecting to the MySQL server and database. Some tables are shown but many are not.All tables are Innodb in mysql and in the same database. I can see the tables using many tools, such as MySQL Workbench. I also have a linked server in SQL Server 2012 that links to the same MySQL server and database and sees all the tables -but SSMA 5.2 does not. The LinkedServer uses the same ODBC connection, and the same credentials (username / password).
I have SSMA for MySQL Extension Pack installed, and I am using “Server Side Data Migration Engine” to migrate the tables.
I have done a "check table" from within MySQL on one of the missing tables, and there is no corruption.
I tried to use the LinkedServer to connect from within SSMA, but it appears that SSMA does not allow me to use them?
I have an ODBC connection with both the 32 big and the 64 bit driver. I'm running this from 64 bit Windows Server 2012.
I am using ssma to migrate database from oracle 9i to sql server 2000. I successfully migrated oracle tables,views,procedures,functions etc.. But can you please help me on how to migrate packages and triggers? Here i can able to view the headings like"Triggers", "packages", "Indexes" in SSMA. But none of my oracle objects are displaying under these headings. what could be the problem?
I am new to databases and need some info that I'm having trouble researching. I just migrated a access database to SQL server 2005 using SQL Server Migration assistant (SSMA). I wondered how SSMA handles NULL values? Also how it treats differences between Access and SQL? Any help would be appreciated.
I have just finished using the SSMA Access tool to migrate an Access database to SQL Server Express. I would just like some clarification on some of the warning messages please.
1. "New timestamp column 'SSMA_TimeStamp' created." How does SSMA determine whether or not to add a timestamp? 2. Why are indexes and keys represented by strings of numbers in Access? What are the numbers precisely? 3. "The foreign key '{32F3E8B2-03B3-46EE-BEBF-C202040E10F3}' is not enforced and therefore cannot be converted." Does this mean that the represented relationship will not be created on the server if referential integrity is not enforced in Access? If so, why doesn't SQL Server allow for this unenforced relationship?
I have 53 tables I need to load from Oracle into SQL Server as part of a migration to a new system. All but three of the tables load perfectly. Three tables load approximately 95% of the rows. The problem tables have between 300,000 and 1,300,000 rows. Oddly some of the tables that migrate successfully have more than 10,000,000 rows. What to check to determine why these tables are failing to load?
I want to migrate DB from sybase to SQL server 2012 and i am using SSMA for it. Till yesterday it was working fine. Today when i try connecting sybase i am getting below error.
Unable to find specified provider.Error occurred while establishing connection to Sybase server. If you have 64-bit connectivity components installed on the machine, you can run 64-bit version of SSMA application whose shortcut can be found under the Programs menu. Or you can install the 32-bit connectivity libraries form Sybase product media or download it from Sybase web site.
I am running SQL Service Migration Assistant for Access 6.0 and I am getting the following error when I attempt 'Refresh from database':
Access Object Collector error: Database
Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit SSMA application if you have 32-bit connectivity components or 64-bit SSMA application if you have 64-bit connectivity components, shortcut to both 32-bit and 64-bit SSMA can be found under the Programs menu.
You can also consider updating your connectivity components from [URL] ....
An error occurred while loading database content.
I've clicked on the link above and installed the Access 2010 Runtime that I found there, but that is not working the problem. I'm running Access 2013 (32-bit) on a Windows 8.1 (64-bit).
Hello everybody. I want to use Web Assistant with option "..data Change" so Web assitant will create trigger.
The database generated from Erwin 3.5.2 and use triggers for integrity. When I run Web assistant wizard I am getting message ".. can't create trigger becouse existing trigger created with encryption option
sp_helptext tI_prcode (my insert trigger) shows all code in the trigger where shoild i look to disable encryption option
I found way around this problem A.Save and delete existing trigger B.run Web assistant trigger will created C. Save and delete web assiatnt trigger d. Insert code from web assitant trigger into code from original erwin trigger and recreate it
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users. these html files that are generated are processed via a JOB in sql server. I am in process of replacing of the web assistant procedures. Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure. Is there any other possible way to do it?
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
I used SQL Server's web assistant wizard to create a web assistant job. In this job I chosed "When SQL Server Data Changes" for schedule options. Late, I don't want this job and by right click web assistant job I deleted this job.
However, I have a stored procedure which use the same table with the above deleted web assistant job. Normally, the stored procedure runs only 1 minute. Today it runs more than 5 minute and then failed. Here is the error messages:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++ Server: Msg 16882, Level 11, State 1, Line 0 SQL Web Assistant: Web task not found. Verify the name of the task for possible errors. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
Please somebody can tell me how can I solve this problem (or totally delete the above web assistant job).
HiI've created a stored procedure (see below) which accesses the ActiveDirectory and SQL server to get "real names" back. When I run thestored procedure in Query Analyzer it returns the expected results,however when I try to create a Web Assistant job based upon theprocedure I get the SQL-DMO message:Error 7410 Remote Access not allowed for Windows NT Useractivated bySETUSER.The procedure is being run (and the job created) as the account whichowns the SQL Server installation, and this account has AD adminpermissions.Any suggestions?CREATE VIEW dbo.vw_account_adASSELECT a.Name AS ad_name, dbo.Accounts.*FROM dbo.Accounts INNER JOINOPENQUERY(ADSI,'select SamAccountName, Name FROM''LDAP://w2k-bspad1/ ou=users,ou=bsp,DC=ad,DC=bl,DC=uk'' whereobjectcategory=''person'' ') a ONSUBSTRING(dbo.Accounts.Account_Name,CHARINDEX('', dbo.Accounts.Account_Name) + 1,LEN(dbo.Accounts.Account_Name) - CHARINDEX('',dbo.Accounts.Account_Name)) = a.SamAccountNameCREATE PROCEDURE [dbo].[usp_event_report] ASSET ANSI_NULLS ONSET ANSI_WARNINGS ONSELECT Code_Name, Account_Name + ' ('+ad_Name+')' as 'Account Name',Date_Occured, ResultFROM Usage_Codes, Usage, vw_account_adWHERE Usage.Code_ID = Usage_Codes.Code_IDAND Usage.Account_ID = vw_account_ad.Account_IDAND datepart(month,Date_Occured) = datepart(month,getdate())ORDER BY Code_Name, Account_Name, Date_OccuredGOChloe CrowderThe British Library
I've a merge rep running with 4 subscribers for a while. Now I wanted to add another subscriber and when I open the publisher in the pull subscription assistant dialog, there are no publications in it. (even not if i switch to "allow anonymous sub") But every- thing seems to be alright wirh the publication itself! It even doesn't work to select the publication in a pull subs. from an other db on the same server! Any ideas?
I set up a connection file in order to move data from sql to csv files. I should be at the last step, the data flow. but:I don't see any flat file in my destination assistant.
