Hi-
Can you give me any help/assistance on the development process involved with migration from MS Access to SQL Server 7.
Specifically, I need my current MS Access data migrated to SQL, and then I need two (2) fairly simple forms developed for data input and searching on the web.
The developers I have been speaking too seem to want to buy their Hawaiian condo from this one project...which seems like A LOT OF $$ for the development of an off-the-shelf product that costs $15,000.
Any leads and/or thoughts would be GREATLY APPRECIATED.
THANKS.
Hi all, In the Object Explorer of my SQL Server 2005 Management Studio Express, I do not have €˜Northwind€™ Database installed yet. I executed the following source code (that was copied from a book) in my VB 2005 Express: /////////////////////----Form9.vb----////////////////////////// Imports System.Data.SqlClient Imports System.Data Public Class Form9
Dim cnn1 As New SqlConnection
Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Compute top-level project folder and use it as a prefix for 'the primary data file Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin") Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1) Dim pdbfph As String = strPath & "northwnd.mdf" Dim cst As String = "Data Source=.sqlexpress;" & _ "Integrated Security=SSPI;" & _ "AttachDBFileName=" & pdbfph cnn1.ConnectionString = cst
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a command to create a table Dim cmd1 As New SqlCommand cmd1.CommandText = "CREATE TABLE FromExcel (" & _ "FirstName nvarchar(15), " & _ "LastName nvarchar(20), " & _ "PersonID int Not Null)" cmd1.Connection = cnn1
'Invoke the command Try cnn1.Open() cmd1.ExecuteNonQuery() MessageBox.Show("Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Create a command to drop a table Dim cmd1 As New SqlCommand cmd1.CommandText = "DROP TABLE FromExcel" cmd1.Connection = cnn1
'Invoke the command Try cnn1.Open() cmd1.ExecuteNonQuery() MessageBox.Show("Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Declare FromExcel Data Table and RowForExcel DataRow Dim FromExcel As New DataTable Dim RowForExcel As DataRow
'Create TextFieldParser for CSV file from spreadsheet Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser Dim strPath As String = _ Microsoft.VisualBasic.Left( _ My.Application.Info.DirectoryPath, _ InStr(My.Application.Info.DirectoryPath, "bin") - 1) crd1 = My.Computer.FileSystem.OpenTextFieldParser _ (My.Computer.FileSystem.CombinePath(strPath, "Book1.csv")) crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited crd1.Delimiters = New String() {","}
'Loop through rows of CSV file and populate 'RowForExcel DataRow for adding to FromExcel 'Rows collection Dim currentRow As String() Do Until crd1.EndOfData Try currentRow = crd1.ReadFields() Dim currentField As String Dim int1 As Integer = 1 RowForExcel = FromExcel.NewRow For Each currentField In currentRow Select Case int1 Case 1 RowForExcel("FirstName") = currentField Case 2 RowForExcel("LastName") = currentField Case 3 RowForExcel("PersonID") = CInt(currentField) End Select int1 += 1 Next int1 = 1 FromExcel.Rows.Add(RowForExcel) RowForExcel = FromExcel.NewRow Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException MsgBox("Line " & ex.Message & _ "is not valid and will be skipped.") End Try Loop Try cnn1.Open() Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1) sqc1.DestinationTableName = "dbo.FromExcel" sqc1.WriteToServer(FromExcel) End Using Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try
'Read the FromExcel table and display results in 'a message box Dim strQuery As String = "SELECT * " & _ "FROM dbo.FromExcel " Dim str1 As String = ""
Dim cmd1 As New SqlCommand(strQuery, cnn1) cnn1.Open() Dim rdr1 As SqlDataReader rdr1 = cmd1.ExecuteReader() Try While rdr1.Read() str1 += rdr1.GetString(0) & ", " & _ rdr1.GetString(1) & ", " & _ rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf End While Finally rdr1.Close() cnn1.Close() End Try MessageBox.Show(str1, "FromExcel")
End Sub
End Class /////////////////////////////////////////////////////////////////////// This is Part 1 (The length of input exceeds 50000 characters). Part 2 will be posted in this site shortly.
At work we are migrating several Access databases to SQL Server 2005 standard edition. These Access databases have no user interface forms. They do however have a series of macros that execute make table queries.
I'm trying to determine what are options are for replacing the make table queries in the macros. My first thought was to do the work in a SQL Server stored prodecure. I am asking for advice on what our options are. Is a stored procedure the way to go? Any suggestions on how to do it?
Hello all, Does anyone know of a utility to migrate Access 97 Queries to SQL Server 7? I've over 400 of them and I am trying not to have to do them by hand.
Also I tried to use to migration wizard from MS instead of the DTS and all I got was Error after error. 'Invalid Date/Time', 'Error with YES/NO field' and 'Timed out' were the most common. In the end only 4 tables out of 89 were migrated.
I would like to migrate an access data to SQL Server. However, the Access database has an OLE data type field that stores word documents. Is there a way to handle this in SQL Server?
Hello,We maintain a VB6 front-end application using an Access 2000 database. Allcode and forms are in VB6. The program also uses several queries/reportsdefined in Access. For corporate reasons we must move to SQL Server rightaway. Based on our configuration, we're hoping someone can give us a feelfor what's involved here. Since we're only using Access for the data we'renot sure what an "upsizing" would entail. Can someone point us in the rightdirection, tell us the main gotchas, etc. Thanks a lot.Teri Welch
I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.
Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.
I am in the process of migrating 40 access databases to SQL Server. I am migrating the tables to the backend. All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to stay seperated by each location. So I added a location field to each table in the database; so that each locations data will be seperated by location. How do I go about filtering out the the data so that each location can only update, delete, an view their own data? (I dont mind a long answer.)
got some MS Access SQL Code that needs converting into TSQL:
SELECT dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1)) AS Completed, Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName ORDER BY dbo_qryMyServices.ServiceName;
because it's Access and got VBA stuff in it - IIF and ISNULL, I can't figure out how to make it work in proper SQL.
I was really happy to see the Migration Assitant for transferring the Access2000 data to SQL 2005. Unfortunately, during the migration wizard, when it comes to the window of "connect to SQL Server 2005" it gives an error. It says "Connection to SQL Server Failed. An error has occured while establishing connection to the server... also point out it could be SQL server not allowing remote connections (I checked), and something that says: provider: named pipes provider , error 40" ... I'm stuck at this window. Please please help? you can email me directly if you wish to fh.erdogan(at)gmail.com Thanks. p.s. I have a fresh install of SQL Server 2005. All on the same machine.
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.
Venkatesh writes "In MS-Access table a coulmn is specified as Auto number property. I want to migrate this table into SQL Server.
We can create a new column that has identity property in SQL Server 2000, which simulate autonumber property of MS-Access.
My access table contains 700 records and I need to set the column (ListID) as Auto number. i.e., I m going to modify this coulmn with identity property.
But I cant set the identity property for the existing column.
Can u pls send me the sql query that modify the existing column(ListId) with Identity property.
I've downloaded the migration tool to convert an Access 2000 DB to a SQL DB. The VB code utilizes DAO (3.6) for hourly automatic compaction and the creation of an encrypted security database. ADO is used for the data maintenance functions. I expect the current connection methodology of ODBC using Jet 4.0 will need to be modified (many articles here address this). All the SQL code is inline and also must be modified. The Coldfusion code utilizes Micrsoft Access with Unicode via an ODBC connection with only SQL Select statements. At another job, I migrated an Informix 7 database to MS SQL 7 and for the most part the SQL code remained the same except for the wildcard spec from * to %.
I'm looking for insights and/or shortcuts on this effort, primarily in the SQL command mods area. I'm perusing the threads here as we speak, but hoped someone knew of a specific thread I could go to....laziness is alive and well for us chronologically gifted people, i.e. over 55.
I migrated the Northwind database using SQL Migration Assistant for Access. it worked fine and I was able to link the access forms to Local machine SQL Server 2005 and it worked fine.
Now i want to link the same Northwind access database to Another Server on the network.
When i try to run the wizard again, it won't migrate the user objects like tables to server, it will just create a database and that is it. it will give me this error too.
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.
we want to completely do away with access, on my report tool,which is crystal report, we want query to be our data source so , we do not want to use ssis packageor import from access to sql, how can l handle bringing all table and the query from Access together.
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.
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any  password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
What I want to do is remove part of a result from a query. I am selecting the srvname from sysservers as follows.
SELECT srvname from sysservers Where srvid ='0'
When the name is returned some times it might be like servernameinstance. What I need to do is cut off the and everything after that so that my result is just servername. I am then using this result in a cursor to go after server shares. The length of the servername varies. Any suggestions.
Hello: I'm scheduling DTS packages to run as a job but when I do that, the job siply does not works, someone tolds me that it's a problem with the SQL and Windows NT authentication and security and he gaves me this URL http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074. but it not so clear to me, i'm working with SQL Server 200 and Windows Server 2000.
Currently I am using the BCP functionality to import/export tables between different SQL Server 2005 databases. The process is working ok, but it creates alot of manual work, when 70 or more tables need to be transferred, every week. I am new to RMO development and am interested if the following may be possible.
- I would like to build an interface in VB.net allowing users to select a source database, and select the tables that need to be transferred from the Source DB, to the Destination DB.
-Put in place RMO funtionality to replicate the source database tables into flat files.
-Within the Vb.net app transfer the created flat files into the destination database.
I would greatly appreciate any advice on this, or if there may be a better approach at replacing the BCP functionality.
Need help from experts on web service (ReportingService class).
I am trying to change a datasourcereference of a report from one location to another location. I am kind of stuck at this point where i am able to get the location but only dataSource.Item, dataSource.Name are not enough items for me to solve this problem.
any possible help or any threads already answered these type, greatly appreciated.
Hi!I want some tutorials on how to actually program database applications. Everything seems so bound with the data controls provided by Visual Studio and coming from PHP I don't like to not have full control over my applications. So where can I learn about creating my own database applications without using repeater, gridview or formview?Thank you in advance!
I need to create a database with one table and its column names. I got this script from the query analyzer templates as a database basic template: IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'<database_name, sysname, test_db>') DROP DATABASE <database_name, sysname, test_db> GO
CREATE DATABASE <database_name, sysname, test_db> GO
I am not sure what it all means, what I need to do it name the database a certain name each time. and then create one table with 6 column names.
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
What do all of you use to create and edit stored procedures? I am looking for a better way to code than to use the default window supplied with Enterprise Manager.
Do most of you use the Query Analyzer? Are there third party products you like using better?
I am looking into a production geared software called MassGroup. (www.massgroup.com) It says that its FactoryServer builds are on a standardized SQL framework and web architecture for data collection, work order requests, reporting and asset management. My questions are:
1. Is SQL its own programming language? If not what is it? Answer: After going thru several pages and reading the fine print I discovered they use VB, ASP, COM technology, and Crystal Reports.
2. How powerful do you think this would be at gathering data and does it have the capability of creating a number sequence automatically? (example: 0001; 0002; 0003...)
Any help would be appreciated. Thank you in advance!!!!!
I have just started in Sql 6 months ago. I was an IT major in graduate school and am now in my late 30's (career change). It took me 4 years to finally get what I thought was my dream job. Well, I was promised training which I have yet to receive and bascially went from doing basic queries that I did in class in college to doing advance Sql - stored procs, etc. I completely hate it. I do not know if it is from the lack of training or if it just not for me. I am completely bored out of mind. I am also tired of struggling everyday to complete assignments. The first 2 assignments I received were way out of my league. I was told this by a project mgr who did a similiar assignment and they have years of sql experience and it took her 3 months with assistance to complete it. I have been with the company for almost 6 years. I am not sure if I should just complete my current assignments and ask if they can reassign me to a different IT department, look for a new job or just continue to struggle and pull my hair out. Please give me some feedback as to how you would handle this situation.
I need to know what exactly other languages [C#/VB.net/ADO/XML/ASP.net..etc] that I should learn in order for me to be able to excel in SQL Development.
I started getting my hands dirty with some SSIS / SSRS & SSAS, but inorder for me to handle situations such as [Find if file exist in ftp before loading, Checking a certain row of a data file before processing/ Zipping and Unzipping data files and etc...] What should I need to know well in order for me to add more value to my skills? I would like to learn 1 language and master it pretty well.What are your suggestions?
And what is this CLR that is being talked about ?Does it use other language besides SQL?
