The Setup: I'm moving a SQL Server 7 Database from a SQL Server sp'd somewhere between 3 and 4 (7.00.1028) to a SQL Server without any service packs. The old machine is a Dell PowerEdge 2400 (about 3 years old) and the new machine is a Dell PowerEdge 2650 with dual gigabit nics (and I have them teamed). Both machines run Windows 2000 Server. Mostly default installation on both the OS and SQL Server.
The Problem: When I restore the database to the new server (and resolve the orphaned login issues, etc.) the server hangs unbelievably. Most of my users can't even connect, much less work in their vb apps. I can ping the machine, but can't run the simplest T-SQL against it.
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success) - Add task for transferring database objects (Success) - Create package (Success) - Start SQL Server Agent Job (Success) - Execute SQL Server Agent Job (Error) Messages * The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
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)
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?
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.
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.
I have another question regarding migration process to diffrent SQL Server.
Last time when I did migration I didn't restore system databases (master in particular). I only restored user databases and of course I've got login errors such Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database. I just used sp_change_users_login to fix those orphans.
I've read articles in MS knowledge base and not quite sure is this a good idea to restore master database prior to restoring all user databases on the new server in order to keep all logins?
I'll try to use recommended article for transfering logins with password instead of restoring master database from backup http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP
I am migrating MS SQL Server 6.5 databases to a new server and wanted to create the data & log devices differently than they are on the old box. My dbcc checks are giving me 2558 errors because my devices weren't built the same size in the same order. I thought if I didn't copy the master, I didn't have to rebuild the database exactly the same. Can anyone tell me if there is a way to rebuild different device sizes and do a successful restore ? Thanks in advance !
I have a program that uses ADO to read/write to/from an Oracle back-end database. I'm hoping that given the underlying schema is the same, I can simply change the database driver I'm using and use a SQL Server database instead. Has anyone migrated an application using ADO from Oracle to SQL ? Are there any issues/problems/tips that I should be aware of ?
I need to move all existing dbs to a new server. One of the database is over 90GB in size. If I plan to use to restore database option, should I restore all user database first, then the system databases(master, msdb) or should I restore the system database first and the user databases second?? Thanks.
I got a project of moving a web site of one a firm from one ISP machine to another ISP machine. They are using Cold Fusion with SQL Server as the database. I am new to SQL Server. I want some tips on how to migrate the database from one machine to another.
Hope somebody can help a novice, so I can impress my boss.
I've got to move a database to a new server and I'm trying to use Gregory A Larsen's sp_help_revlogin script http://www.databasejournal.com/features/mssql/article.php/2228611.
It works perfectly for all users, but I want to limit it to one database by using his suggestion shown on that page:
SELECT sid, name, xstatus, password FROM master..sysxlogins Your_db..sysusers b on a.sid = b.sid WHERE srvid IS NULL AND name <> 'sa'
However, when I make the changes, so it looks like this:
SELECT sid, name, xstatus, password FROM master..sysxlogins a join int_test..sysusers b on a.sid = b.sid WHERE srvid IS NULL AND name <> 'sa'
I get this error when I try to run it:
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12 Ambiguous column name 'sid'. Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12 Ambiguous column name 'name'. Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12 Ambiguous column name 'password'. Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12 Ambiguous column name 'name'.
Can anyone see what I'm doing wrong.
The database that I want to limit the script/procedure to is called int_test on the same server, and I've checked that the table sysusers does exist, and that it has a column called name.
Hello,I am trying to migrate data from a DB2 database to SQL Server 2005database. Does anyone know about any migration tool that does that? Ihave heard about DB2 Migration Tool kit, but I think you can onlymigrate data to a DB2 database with that. Thank you.Sincerely,Eldhose Cyriac
Hello,I have got the job to migrate data from an Excel-sheet to asql-server-database. I have a lot of experience with Access butSql-Server is not in my line.So who can give me some advice about how to do this job?thanx for your help, Helmut
Hello. I am using the built-in SQL Server 2005 Express edition which comes bundled with Visual C# 2005 Express and found that, apparently the separate download of SQL, i.e., 2005 Express Edition with Advanced Services SP2 offers more features.
I wanted to know the following.
Can I somehow migrate the instance of SQL Server Express bundled with Visual C# to SQL Server 2005 Express Edition with Advanced Services SP2?
Or is there a way to have my IDE tie into the other SQL Server natively?
I would like to be able to write distributeddeployable C# applications which can connect to the more full-featured SQL if possible.
We currently have a SQL 2000 server setup with the data and log files (including those for the system) placed on a SAN. Our current server is old and starting to have problems so we are transitioning to a new server. I just wanted to see if anyone has done this before or if my plan makes sense.
1) Hook the new server into the the SAN Arrays holding the data and log files.
2) Take the old Server off-line, and bring the new one up.
3) Point the Master db to the location of its data and log files.
4) Restart the server.
In theroy, since the master "knows" where all of the other databases are located when the it starts the recovery process it should bring up all of the other databases on that server. Does this make sense or am I missing something?
I'm migrating SQl Server Reporting Services from 2000 to 2005.The reports are generated normally in 2000 but it seems that it is taking more time in 2005 or sometimes it does not generate the report at all.Could you kindly suggest a solution?
I have successfully moved my data from a SQL Server 2000 hosting site to a SQL Server 2005 hosting site. I Made a backup of my database using Enterprise Manager (2000) and imported the database tables using SQL Server Management Studio (2005). I do not know how to move the 25 or so stored procedures that I have in SQL Server 2000. I have a very short amount of time to figure this out and am hoping that someone can give me a brief step by step answer on how to get this done. I would appreciate any information you can provide. Thank you!!
i have tried the below three steps but i dont get script to any output file or clipboard after the executing the script generation. And i would also like to why i get lo of errors when i run the scritp from the step one,i got the script and i try to run it on sql server 2000,there is lot of errors. I there any order i need to follow when i run the script in sql server 2000.
Plz help me out. This is urgent.....
How to Downgrade a Database from SQL Server 2005 to SQL Server 2000
As you may all know, SQL Server 2005 request a minimum of 8GB RAM to work€¦ let say satisfactorily. I first didn€™t knew that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!
At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to an SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the procedures I had to follow for this action.
Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.
Step 1 Generating Scripts for the Database Elements and Structures
1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option). 2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button. 3) Set the following Elements to the following Values a. Script Collation , set to TRUE b. Script Database Create, set to TRUE c. Script of SQL Version, set to SQL SERVER 2000 d. Script foreign keys, set to FALSE e. Script Triggers, set to FALSE Then Hit the Next button 4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end. 5) Click Finish
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.
Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.
Step2 Moving the data from 2005 to 2000
1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option). 2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button. 3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next. 4) A List of all the Source Database€™s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.
CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.
Step 3 Generating Scripts for the Database Foreign Keys and Triggers
Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:
1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option. 2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button. 3) Set all the Elements on the List to a False Value except the ones that follow: a. Include IF NOT EXISTS , set to TRUE b. Script Owner, set to TRUE c. Script of SQL Version, set to SQL SERVER 2000 d. Script foreign keys, set to TRUE e. Script Triggers, set to TRUE Then Hit the Next button 4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button. 5) At the screen that follows hit the Select All button and the Next. 6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end. 7) Click Finish Button.
After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.
After these steps the database should be fully functional under the SQL Server 2000 edition.
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.
i am migrating data and schema from oracle server to sql server., when i try to do the dts it gives me an error saying could not resolve tnsnames.ora. iam not sure what is the problem.
My oracle server host is dbserver.hari.com.edu and its name is Prod (name of the machine)
i tried both it still gives me the same error., but when i install an oracle client on the machine and configure the same it is working great.
My question is, is it necessary to install oracle client to migrate database from oracle to sql server. If not how do i do the same...what is the process to do it. since this is a prodction machine we don't want more of applications running on the same machine.
I need to move several databases to a new server while retaining the same logins/permissions. Books Online indicates that DTS can move the SQL Server logins, but it sets the passwords to NULL in the process. Is there any way to move the logins and keep all passwords/permissions intact?
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?
Any suggestions on migrating Access97 tables to sql server 2000? The DTS seems cumbersome and the Access97 upsizing wizard add-in is only compatible up to sql server 7.0.
Can anyone let me know how to migrate the sql server jobs to new cluster server from a stand alone box? Is it easy to just restore the msdb on the new server?
