Migrating From Access To SQL

Mar 21, 2007

Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));

View 14 Replies


ADVERTISEMENT

Need Help -- Migrating From Access To MS SQL

Dec 19, 2006

I am migrating my database from Access to MS SQL. I successfully migrated most of the Tables but I am going through a hellish time with queries/views

Can somwone tell me how to convert this query from Access to MS SQL:
-------------------------------------------------------------------------------------------------------------------
SELECT Manage_Pre_Award.A_ID, Manage_Pre_Award.Event_Name, Manage_Pre_Award.Plan_Date, Manage_Pre_Award.Actual_date, Manage_Pre_Award.Rev_Date, IIf([actual_date] Is Not Null,[actual_date],IIf([rev_date] Is Not Null,[rev_date],IIf([plan_date] Is Not Null,[plan_date],Null))) AS workdate,
FROM Manage_Pre_Award
WHERE (((Manage_Pre_Award.Rev_Date) Is Not Null)) OR (((Manage_Pre_Award.Actual_date) Is Not Null)) OR (((Manage_Pre_Award.Plan_Date) Is Not Null));

-----------------------------------------------------------------------------------------------------------------------

I have already tried this but get:
The Query Designer does not support the CASE SQL construct.

SELECT Manage_Pre_Award.A_ID, Manage_Pre_Award.Event_Name, Manage_Pre_Award.Plan_Date, Manage_Pre_Award.Actual_date,
Manage_Pre_Award.Rev_Date,
CASE WORKDATE
WHEN [actual_date] IS NOT NULL THEN [actual_date]
WHEN [actual_date] IS NULL AND [rev_date] IS NOT NULL THEN [rev_date] ] ELSE [plan_date]
END AS WORKDATE
FROM Manage_Pre_Award
WHERE (((Manage_Pre_Award.Rev_Date) IS NOT NULL)) OR
(((Manage_Pre_Award.Actual_date) IS NOT NULL))OR (((Manage_Pre_Award.Plan_Date) IS NOT NULL));

View 2 Replies View Related

Migrating From Access To MS-SQL

Jan 7, 2008

We have a huge Access database located on a server, but now it is going very slow due to the transport of Mb file to the clients.

We could export the tables to a SQL server and attach the tables to the access clients.

So the question is, will access still treat the tables as it use to or could we expect access SQL querys to request the sql server as normal. Will access process the query on the client side?

Thanks

Provo

View 4 Replies View Related

Migrating Access To SQL Server

May 4, 2004

We use MS Access 2000 as our database, but run into lots of proplems. So we decided to research the migration to SQL Server.

I used Access Upsizing utitlity to migrate tables and their data to SQL server very easily, and all the relationships, indecies and other information are converted correctly.

Next is to migrate the queries. I found that to be a pain. Is there any tool out there that can do it for me? Any help is greatly appreciated.

Once that's done, then it's the application itself, but I am not worrying that for now.

Thanks, guys

View 1 Replies View Related

Migrating With From Access With VB Scripts

Jan 13, 2004

The company i am working for has been using an Access database for purchasing and inventory. this has become to small/dangerous due to the number of records and frequent 'database needs repair' errors. I am trying to migrate the database to a sql server w/ access front end and am runing into problems with most of the VB proceedures that are built into the database. the most common at the moment is a 'Write Conflict' error saying someone else changed the record i am editing. i know this can't be the case becasue i am working on this in an isolated environment. I have traced it to a VB script that changes a field from 'Entering' to 'Pending' (text field) on the click of the 'new record' button. any ideas?

Access 2000/2003, SQL server 2000

View 3 Replies View Related

Need Help In Migrating From Access To SQL Express

Feb 21, 2007

Hi there,

I am totally new at SQL/SQL express.I have downloaded the sQL express server and wanted to know how i can go about exporting my tables which i created using MS access into SQL express.

Also my application is currently working by using Access and ASP...what will be the changes i would need to incorporate in my code other than the queries and Data connection string.

How would the data connection string in SQL look like if i am using it in my asp code?

Any help or useful links would be a very good help!

thanks in advance

smita

View 4 Replies View Related

Migrating From Access 2003 To SQL CE

Sep 4, 2007

I've a database in Access 2003 that I want to migrate to SQL CE since Pocket Access is no longer supported.

I've researched extensively this issue to no avail.

Even tried to use the ADS but it's not feasible,

Please, any help is welcomed!

View 3 Replies View Related

Migrating Scripts Written Around Access To Sql.

Feb 27, 2007

Hey guys, I had purchased a program that generates full operational asp.net pages from an access db. I was unfamiliar with the language, and so that was the route I took. After disecting it and studying I've learned a bit and made it work in my environment. However, I now need to migrate everything over to mssql. I got the db portion down and now I'm trying to modify my scripts to point to a sql db rather than an access db. I thought it was going to be relatively simple but it's proving to be quite a challenge for me.Was wondering if anyone could check my work.
 The original access driven page had the following connection strings.vb page had [ public strConn as string="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("../") & "/db/db.mdb"end class]and in my aspx. pages I hadAccessDataSourceControl1.selectcommand="SELECT * FROM table "AccessDataSourceControl1.ConnectionString = strConn
 My new page now has the following.I got rid of the .vb page and put this in.
Dim myConnection as New SqlConnection("Server=mysqlserver;Database=db;UID=myusername;PWD=my pw;")
Const strSQL as String ="SELECT * FROM table"
Dim myCommand as New SqlCommand(strSQL, myConnection)
 
further down I have a wmx data grid. I'm assuming I don't need to touch that but there is a line that has,
<wmx:AccessDataSourceControl id="AccessDataSourceControl1" runat="server" ></wmx:AccessDataSourceControl>
and I don't know what the equivilant of that is. I know its pointing to the accessdatasoucrcontrol1 that has the select command, but what would be the equivilant ofwmx:accessdatasourcecontrol?Also, is this the way to go? by that i mean, should I just rescript everything? Also,
1) Am I connecting to the sql db properly? 2) some sites say I have to put a connection string in the web.config file, is that true?3) Does any one have any references I can goto, I'm having some trouble finding a good article that lays down exactly what needs to go where in an sql asp.net environment.
 Thanks guys.

View 2 Replies View Related

Migrating From Access Database To SQL Server 7.0

Mar 28, 2001

Hi,
We are planning to migrate data from access to sqlserver. I have good knowledge on sql server...but never used access to migrate. Can anyone help me with the basics to be kept in mind and methods and errors you get in migrateing data.If possible explain in detail and what is to be done while migrating data.....thank you in advance.And its urgent please.

View 3 Replies View Related

Migrating Access Queries To SQL Server 7.0

Nov 2, 2000

I have a query in Access that has an iif statement in the select system like so:

SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription,
tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not
Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not
Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%]))))
AS [%], 0+round2([Amount]*[%]) AS Allocated,
0+round2([Allocated]*[Salvage%]) AS Salvage
FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON
([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND
([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN
tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND
([01_qryCommonCost_01].Group = tblPercent_Afe.Group)

But I can't figure out how to nest the iif statement in T-SQL. Any hints?

View 2 Replies View Related

Migrating Access To SQL 2000 Server

Apr 25, 2007

Hi,

I need to migrate my access database to SQL2000 Server. I have three tables in my access database. In each table have 3000-6000 records.
So how to put all these records in to sql2000 server tables.
The table structure in Access database and sql2000 database are same.

Here core work is just insert all these 3000 - 6000 records (rows) from Access database to sql2000 database

Thanks in Advance!!

With regards
Shaji

View 5 Replies View Related

Migrating From Access To SQL Server With A Web Front End

Jul 20, 2005

Afternoon all,Apologies for cross-posting but as my query covers both Access and SQLServer I thought I'd send it both!I have inherited a project to migrate a fairly complex series ofAccess databases into a single proper SQL database with a web frontend.Its quite a nasty job as people are working on a variety of data setsat several Universities around the world and the data has got verymessy; hence the requirement to put it all on one live web enableddatabase server and provide a web-based front end (particularly assome users insist on using Macs so can't run Access as a front endanyway).If anyone could give me hints on how to perform such a migration or ifanyone knows of any good books or other documents on this I'd begrateful for assistance.Many thanksRich MayMuseum of London

View 9 Replies View Related

ASP.NET Application - Migrating From Access 2000 To SQL Server 7

Jul 23, 2005

First off, sorry if my cross posting offends anyone. I'm posting thisin Access and SQL Server groups - not sure which one is appropriate.I have a relatively simple ASP.NET/VB.NET application that is nowhitting an Access 2000 database over an intranet. We have to migratethe database to SQL Server 7. My experience with ASP.NET is prettylimited and my experience with SQL Server is nonexistent.We have an MSDN subscription, so I went to the downloads section to getSQL Server 7. Guess what? SQL Server 6.5 is available, as is 2000 and2005, but no SQL Server 7. So my first question is, does anyone know ifit is available for MSDN subscribers?The next question is, does anyone know of a good resource that explainshow to make the transition from Access 2000 to SQL Server 7? I know thedata has to be migrated and the connection between the application andthe database modified, but am really not sure exactly what to first andthe correct way to go about it. The application is a simple productconfigurator. There's not a lot of data and it's not a very complexdatabase.Thanks in advance. If you'd like, please copy responses tolcifers(AT)yahoo.com (AT) = @TIA.Cheers.- Luther

View 2 Replies View Related

Pbm Migrating Access 2003 Database To Sql 2005Express Edition

Jan 5, 2007

I tried various ways to migrate an access 2003 database with 1 table to
sql Express 2005 Edition without success, I can only find the resulted
sql database in Sql Server Express Edition 2005 without data.
Please advise if any help.

View 2 Replies View Related

Migrating Access XP Database To SQL Server 2005 Express

Sep 5, 2006

I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.

How to migrate it?

View 4 Replies View Related

Migrating MS Access Data To SQL Express Data

Feb 17, 2007

Hi all,
We have access database with lots of data in it. I want to move all the dat to the new database which has completely differenet database structure (e.g different tables, different rows).
What I was thinking is that there are 2 ways. one is manually, which will take long tome probably, and the other is to rum queries that will move the data from access to the new sql express. The question is how to that. I know that I used onw CSV files exported for ms access, but now the queries will be much more complex and I will use more then one table for te query.
How can I query from 2 different databases, which from one I read data(access), and from the other I write data (express)?
Thanks alot.

View 5 Replies View Related

Migrating From Access 2000 To SQl Server 2000

Jul 20, 2005

Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin

View 1 Replies View Related

Migrating A SQL 6.5 Db To SQL 7.0

Jan 24, 2001

Help!!!!!!

I cannot get the SQL Server Upgrade Wizard to work!!

I have one machine with NT 4 (SP4) and SQL 6.5 and another machine with NT 4 (SP4) and SQL 7.0 (new install, never had SQL 6.5 one it). I want to upgrade a database to 7.0.

Each machine can 'see' the other via shares and even trying to register a SQL database in each other machines Enterprize manager (although that fails because they are different versions of SQL)
Using the Upgrade Wizard, on the third screen, where you specify the Server Name for the export server, it even has the other server's name in the dropdown list {I never even put it in}. And yet, after I set the export server name and put in the database SA account password and then put in the SA account password for the local server (the one that is running the Upgrade Wizard).
After I select next, I get a message that states...
'Unable to connect to Export Server. Please verify that you are an NT administrator on that machine.'

These are standalone servers. Not BDC's. I am logged into each machine as the administrator.
I have tried adjusting the SQL services to allow logging as the System Account and the administrator account and a whole bunch of options....
but I can't get it to work.
HELP!!! This shouldn't be this hard.... should it???

View 2 Replies View Related

Migrating From SQL 6.5 To 7.0

May 24, 2000

Does creating the tables first before copying the data from 6.5 to 7.0 is the best way to migrate from SQL 6.5 to 7.0?

Thanks in Advance!

View 1 Replies View Related

Migrating From SQL 6.5 To 7.0

Apr 21, 2000

I run SQL 6.5 on an NT 4.0 platform in a client/server environment with Windows 95 as the clients. I use VB 6.0 as my user interface with Access 97 utilizing DAO (yes, still DAO) for the ODBC connection to the SQL server. When I install SQL 7.0 and move my data to it, can I refresh my links in Access 97 from ODBC to OLE DB and automatically take advantage of the new connection, or do I have to rewrite my queries to take use OLE DB rather than DAO? Thanks for any suggestions and help.

View 2 Replies View Related

Migrating

Aug 27, 2004

Hello People,

I currently have a MS Access database that will eventually be migrated to MS SQL Server. There won't be an application now, just the database and the reports. My doubts are on how easy this transition will be.
1 - After I build all the database with tables and relationships, is there a tool that helps me to import data?
2 - Is there a way to insert the data on the database other than writing the SQL codes, I mean maybe creating some 'forms'?
3 - Once the data is in, it there a way for me to create report on the SQL Server itself or do I need a report generator like Cristal Reports for example;
4 - If yes, then how easy it is to integrate them;
5 - Is it complicated to generate these reports or it is pretty much the same interface as in Access or Delphi?

Thanks a lot

Atha

View 1 Replies View Related

Migrating Db

Mar 28, 2007

I have an ms sql 2000 db which I am migrating from one shared environment to another. How specifcally can I do this?

I have a copy of enterprise but not a full version of SQL on my local desktop.
The hosting company from which I am moving has placed the db as a zip file on the root of my site via ftp. I have unzipped it and it appears as a backup copy (.bak).

I appreciate any detail steps, I assume via enterprise, that anyone can give me. Thank you.

View 1 Replies View Related

Migrating SQL From NT4.0 To AD

Jul 12, 2007

I am very new to SQL so please bear with me. I have an SQL 2000 server running on Windows 2000 server in an NT4.0 Domain. We are planning to migrate (not upgrade!) everything to a new WIndows 2003 AD environment using ADMT. I wanted to know if there are any issues with the migration of an SQL server if we are to move forward with this strategy. I have heard that an SQL database keeps domain related information within its database and wanted to know if this is true.

Thank you

View 5 Replies View Related

Migrating DB

Jun 11, 2008

Hi,
I need to migrate db,Im planning to use attach & detach option.Is there any cons in migrating the databases(32 bit,not clustered ,sql 2005) to databases(64 bit,clustered,sql 2005).What are the things do i need to take care of?what are the things do i need to do after the migration?

Thank

View 8 Replies View Related

Migrating Sql Server

Mar 8, 2008

I've developed a website and it's hosted by a hosting company. Lately i've registered a new domain with a new virtual hosting account. I've transfered my web files succesfully. But migrating the sql server failed for a couple of times now. My database containes many tables with many products. My hosting provides doesn't give me the opportuniy to restore my backup which i've backuped from my old server. First I tried to use the import and export tool of sql server 2005. This was very easy and it succeeded. But this tool only migrates the tables and the views. All the common stored procedures of asp.net membership are not exported. So I can't login at the moment. I've tried to generate a sql script with all the stored procedures with sql server management studio. But If i want to run this script on my new db, it fails with various errors. I tried to use the aspnet_regsql tool to create all the membership tables and stored procedures. If this succeeds, I can import and export my old db to the new one. But i'm getting a error while trying to use the aspnet_regsql tool. The error number is 290 and the error is: How can I fix this? Er is een fout opgetreden bij het uitvoeren van het SQL-bestand InstallMembership.sql. Het SQL-foutnummer is 290 en het SqlException-bericht is: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal".----------------------------------------Details van fout----------------------------------------SQL Server: Database: [samet]Geladen SQL-bestand:InstallMembership.sqlOpdrachten mislukt:/*************************************************************//*************************************************************//*************************************************************/DECLARE @ver intDECLARE @version nchar(100)DECLARE @dot intDECLARE @hyphen intDECLARE @SqlToExec nchar(400)SELECT @ver = 8SELECT @version = @@VersionSELECT @hyphen  = CHARINDEX(N' - ', @version)IF (NOT(@hyphen IS NULL) AND @hyphen > 0)BEGIN    SELECT @hyphen = @hyphen + 3    SELECT @dot    = CHARINDEX(N'.', @version, @hyphen)    IF (NOT(@dot IS NULL) AND @dot > @hyphen)    BEGIN        SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)        SELECT @ver     = CONVERT(int, @version)    ENDEND/*************************************************************/IF (@ver >= 8)    EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000/*************************************************************//*************************************************************/IF (EXISTS (SELECT name              FROM sysobjects             WHERE (name = N'aspnet_Membership_CreateUser')               AND (type = 'P')))DROP PROCEDURE dbo.aspnet_Membership_CreateUserSQL-uitzondering:System.Data.SqlClient.SqlException: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal".   bij System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   bij System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   bij System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   bij System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   bij System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)   bij System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)   bij System.Data.SqlClient.SqlCommand.ExecuteNonQuery()   bij System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)  

View 2 Replies View Related

Migrating Syslogins

Apr 6, 2001

We are looking to migrate an entire server with several databases which collectively contain every user in the company. Obviously, I would prefer to directly migrate the logins without having to make everyone reset their own passwords. Anyone know of a way to do this?

I tried dts'ing the syslogins table but it fails due to password collumn being read only.

View 5 Replies View Related

Migrating To Different Server

Jun 27, 2001

Hello!
We are planning migrating to different server.
I know the answers how to migrate all jobs and DTS packages.
But the only question I have is "How to migrate logins and passwords?"

What is the best way to transfer logins?
Can't you just export syslogins table into text file and then import it into the new server?

Thank you
Lena

View 12 Replies View Related

Migrating Userids

Dec 19, 2000

Has anybody been successful migrating userids from one server to another server using SQL7.0? If so, is anyone willing to please provide any sample scripts.

View 1 Replies View Related

Migrating To Sql Server 7.0 From DB2

Apr 22, 2000

HI all,
Could any one pls suggest me, how i have to migrate to sql server 7.0 from db2. Pls also recommend some sites where will i get the information regarding this migration.
Thank you all.

---Chowdary

View 3 Replies View Related

Migrating Sql Logins

Jun 26, 2003

We have a "spanking new" machine to run SQL server and moving the databases over was absolutely no problem.

However, we discovered that we left the user ids and passwords behind.

Is there a way to get them copied from the old database to the new one?

View 7 Replies View Related

Migrating Logins To Another

Oct 6, 2004

When using the script to transfer logins and passwords from one server to another. After doing so... Does the logins and passwords still appear on the server transfering from??? I am doing a parallel install and need to keep the production database logins and passwords still intact. Thanks...

I did a detach on each database/logs.
Moved to the exact location on the new server.

View 4 Replies View Related

Migrating The Master DB

Dec 15, 2005

This is long post but I really want some feedback on this urgently so please hang in there!

I am migrating a SQL Server over to a new server.
I have done the following and although everything appears to be working like a charm I would like to know if there are any gotchas that I have not considered.

These are the steps I used to 'move' the master db.

/*
I restored all the users databases onto the new sesrver from backups of the old (file copies and attaches would also do).

I restored a db named masterbak from a backup of the master on the old server. And again into a second db called masterbak2. I then detach masterbak2. I need to use those files later on.

So at this point I now have all the user databases that the old server had but I dont have logins, extended stored proc defs, and any user objects that were created in the old master.

Now I want to put all those missing things into the new master db. These are the steps that I used to achieve this.

*/

-- where doing open heart on sys tables so need this turned on

EXEC sp_configure 'allow updates' , 1
reconfigure WITH OVERRIDE


-- remove all database defs from the copy of the old master
delete masterbak..sysdatabases
-- and load it with all the database defs from the new master
INSERT INTO [masterbak].[dbo].[sysdatabases]([name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename])
SELECT [name], [dbid], [sid], [mode], [status], [status2], [crdate], [reserved], [category], [cmptlevel], [filename]
FROM [master].[dbo].[sysdatabases]


-- SYSSERVERS
-- remove all the servers defined in the copy of the old master
delete masterbak..sysservers
-- replacing them with those that are in the new master
INSERT INTO [masterbak].[dbo].[sysservers]([srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout])
SELECT [srvid], [srvstatus], [srvname], [srvproduct], [providername], [datasource], [location], [providerstring], [schemadate], [topologyx], [topologyy], [catalog], [srvcollation], [connecttimeout], [querytimeout]FROM [master].[dbo].[sysservers]

-- sysfiles1
-- remove the old
delete masterbak..sysfiles1
-- replace with new
insert masterbak..sysfiles1
select * From sysfiles1

-- sysdevices
-- remove the old
delete masterbak..sysdevices
-- replace with new
insert masterbak..sysdevices
select * From sysdevices

-- sysconfigures
-- remove the old
delete masterbak..sysconfigures
-- replace with new
insert masterbak..sysconfigures
select * From sysconfigures

-- sysaltfiles
-- remove the old
delete masterbak..sysaltfiles
-- replace with new
insert masterbak..sysaltfiles
select * From sysaltfiles

-- were done
-- turn off sys updates
EXEC sp_configure 'allow updates' , 0
reconfigure WITH OVERRIDE


/*

this leaves
syslogins intact
sysmessages intact
sysoledbusers intact
sysremotelogins intact (no records in mine anyway)
all other system tables are left intact as we want to import all the objects into the new server
*/


/*

now stop the SQL server instance;
rename the physical files master.mdf and mastlog.ldf to masterORIG.mdf and mastlogOrig.ldf
rename masterbak.mdf and mastlogbak.ldf to master.mdf and mastlog.ldf.
rename masterbak2.mdf and mastlogbak2.ldf to masterbak.mdf and mastlogbak.ldf.
I wanted to preserve the new master files so didnt rename to masterbak but I suppose you could.

Start up SQL server and everything should be fine.

Configuration settings are as expected, databases started up ok and file locations are ok, logins both SQL and NT are ok, all master user objects are present.
User and login SIDs match.

A linked server access that was setup failed but after redoing security on the linked server it was ok? Sysoledbusers holds the security for this but not sure about this one.

Important:
You need to check sql log for any errors.
It should be clean.
If you have auto start procs that reference reg keys that are not on the new server you with be told which key is missing and you can export key from old server and import into new server.
Dlls of course will need to come accross but again error messages will indicate the dll that is missing and its path.

So we now have a server that starts up clean but its of a different name of the original of course and since we have a myarid of external servers etc that depend on the server name we are going to keep the old name as well.

So now we change the server name, requires reboot, start up QA and logging into to each 'new' instance name, run the following
-- 'SQLSERV02 -> SQLSERV01'
sp_helpserver
sp_dropserver 'SQLSERV02'
sp_addserver 'SQLSERV01', local

and that should be that.

you might get this :

-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
-- There are still remote logins for the server 'SQLSERV02'.

sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE

-- have a look at it
select srvid from master.dbo.sysservers where srvname = 'SQLSERV02'
select * from master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)

-- get rid of it

delete master.dbo.sysxlogins
where srvid = 0 and
not(ishqoutmap = 1 and
xstatus&192 = 192 and
sid is null and
name is null and
password is null)

sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE

My last step is to get all those jobs and log shipping over to the new server. And I have not yet done this so am not sure if any further open heart is required.

But the theory is that I just need to restore an msdb taken from the old server.

Naturally you will need to put in place the same file path structure on the new server for those scripts / jobs that create files on disk - such as backup files.

I will find out about msdb soon enough as this is going down tonight.

Can anyone see any obvious gotchas in this method?

cheers and thanks for your feedback

*/

View 2 Replies View Related

Migrating Foxpro

Sep 11, 2007

Hello, all. I need to migrate a visual Foxpro database to SQL server. Is there a way I can import the visual Foxpro data files into Enterprise manager?

Thanks in advance.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved