MSSQL 2000: Copying Database

Oct 4, 2006

Hi,

I am using MSSQL 2005 but we have to work in compatibility mode with MSSQL 2000 and also we are only allowed to use 2000-compatible features, now my question:

I need to programmatically copy a database within the same server (including all stored procs, triggers and tables, indices) ...

Can anybody give me a hint what is the best way for reaching this target??

Thanks a lot in advance

Greetings
Stefan

View 5 Replies


ADVERTISEMENT

Conversion Of MSSql 2000 Database To MSSQL 2005 Database

Jan 18, 2008

How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?

View 3 Replies View Related

Copying SQL 2000 Database To SQL 7.0

Jan 31, 2002

What is a good way to do such a thing?

Kevin

View 1 Replies View Related

Copying Database From SQL 2000 To SQL 2005

Nov 7, 2007

Hi,

I need to copy database from SQL 2000 to SQL 2005 server. I was using the Copy wizard from SQL 2000 Server, I could enter the source server and server where to copy SQL 2005 but when I wanted to select the database to copy, nothing happened. Like I was kicked out, no errors etc.

Any one with idea or information, please let me know ASAP

View 9 Replies View Related

Restoring Database MSSQL 2000

Jul 16, 2004

Hi All

We restored a database but the user asscociated with that database is not being restored. Is there a way to do this?

Question: Is there a way to restore the complete database including the users.

Thanks

James

View 3 Replies View Related

Problem With Connecting With Online Mssql 2000 Database

Sep 30, 2007

dear all  i tried to connect to online mssql2000 database from enterprise manager,but i can't as i have a proxy server.how can i overcome this proxy server during connection ?

View 3 Replies View Related

Inserting CheckBox Values Into A Database (MSSQL 2000)

Sep 12, 2005

I have been trying to add values to a database and it keeps failing
i have no idea what i am doing wrong please help the code is asp.net
using vb. I have been having serious trouble passing check boxes in
forms from day one both singularly and dynamically from datagrids
if someone could show me some sample code of how to pass these
sort of values into the component and on to the query in this way
i would very much appreciate it.

Fuzzygoth

the error returned is

Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'tblStation_FK00'. The conflict occurred in database 'TealSQL', table
'tblTravelPoint', column 'travelpointID'.
The statement has been terminated.

I have marked the area of the code the error is returned in the colour Violet and with a ##

The code i am using is below


## The aspx page ##

<%@ Page Language="vb" Debug="true" Trace="True"
Inherits="Devotion2Motion.AdminComp"
Src="../CodeBehind/AdminModule.vb"   %>
<!-- Binds the ActivityResortInfo.ascx user control to the page -->
<%@ Register TagPrefix="UserContol" TagName="D2MHeader" Src="../UserControls/Header.ascx" %>
<%@ Register TagPrefix="UserContol" TagName="D2MFooter" Src="../UserControls/Footer.ascx" %>
<%@ Register TagPrefix="UserContol" TagName="TravelPointDD" Src="../UserControls/TravelPointDD.ascx" %>
<script language="vb" runat="server">
   
    Sub Page_Load()
   
        If IsPostback = True Then
       
        Dim aInternational As Integer
       
        Dim Station As String = Request.Form("StationFrm")
        Dim Type As String = Request.Form("TypeFrm")
        Dim Address1 As String = Request.Form("address1Frm")
        Dim Address2 As String = Request.Form("address2Frm")
        Dim City As String = Request.Form("cityFrm")
        Dim International As String = Request.Form("InternationalFrm")
        Dim TravelPoint As Integer = Request.Form("_ctl5:dsTravelPointDD")

            If IsNothing(International) Then
                aInternational = "0"
            Else
                aInternational = "1"
            End If

            Dim AdminTravelPoints As New Devotion2Motion.AdminComp()
            ' Select the country dropdown list
           
AdminTravelPoints.AddStation(Station, Type, Address1, Address2, City,
aInternational, TravelPoint)
               
        End If
       
        Dim ReadResultTable As New Devotion2Motion.AdminComp()
        ' Select the country dropdown list
        dsResultSet.DataSource = ReadResultTable.GetStationtbl()
        dsResultSet.DataBind()
       
    End Sub

</script>


<!-- This UserControl Pulls in the header UserControl and the Div Tag Positions it #css reffrence is TopControl -->
<Div Class="TopControl">
    <UserContol:D2MHeader runat="server"/>
</Div>

<form method="post" action="AdminTravelPoint.aspx" runat="server">
<Div Class="CentreControl">
<table border="0">
<tr>
    <td>
        <asp:Label Text="Station Name: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="StationFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="Type: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="TypeFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="Address1: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="Address1Frm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="Address2: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="Address2Frm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="City: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="CityFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="International: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:CheckBox ID="InternationalFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
</tr>
    <td>
        <asp:Label Text="Travel Point: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <UserContol:TravelPointDD runat="server"/>
    </td>
<tr>
</tr>
    <td>&nbsp;
       
    </td>
    <td>
        <input value="Submit" type="submit">
    </td>
</tr>
</table>
</Div>

<asp:DataGrid ID="dsResultSet" AutoGenerateColumns="False" CssClass="PrimaryGrid" ShowHeader="true" runat="server">
<Columns>
    <asp:BoundColumn DataField="StationID" ReadOnly="true" HeaderText="Station ID"/>
    <asp:BoundColumn DataField="Station" ReadOnly="true" HeaderText="Station"/>
    <asp:BoundColumn DataField="Type" ReadOnly="true" HeaderText="Type"/>
    <asp:BoundColumn DataField="Address1" ReadOnly="true" HeaderText="Address"/>
    <asp:BoundColumn DataField="Address2" ReadOnly="true" HeaderText="&nbsp;"/>
    <asp:BoundColumn DataField="City" ReadOnly="true" HeaderText="City"/>
    <asp:BoundColumn DataField="International" ReadOnly="true" HeaderText="International"/>
    <asp:BoundColumn DataField="TravelPoint" ReadOnly="true" HeaderText="City"/>
</Columns>
</asp:DataGrid>

</form>


<Div Class="CenterControl">
    <UserContol:D2MFooter runat="server"/>
</Div>


##the vb componet that passess to the sql query ##

Public Function AddStation(ByVal Station As String, ByVal Type As
String, ByVal Address1 As String, ByVal Address2 As String, ByVal City
As String, ByVal aInternational As Integer, ByVal TravelPoint As
Integer) As SqlDataReader

            ' Create Instance of Connection and Command Object
            Dim
myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
            Dim
myCommand As New SqlCommand("sp_call_Station_Insert", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim
parameterStation As New SqlParameter("@Station", SqlDbType.NVarChar, 50)
            parameterStation.Value = Station
            myCommand.Parameters.Add(parameterStation)
           
            ' Add Parameters to SPROC
            Dim
parameterType As New SqlParameter("@Type", SqlDbType.NVarChar, 50)
            parameterType.Value = Type
            myCommand.Parameters.Add(parameterType)
           
            ' Add Parameters to SPROC
            Dim
parameterAddress1 As New SqlParameter("@Address1", SqlDbType.NVarChar,
50)
            parameterAddress1.Value = Address1
            myCommand.Parameters.Add(parameterAddress1)

            ' Add Parameters to SPROC
            Dim
parameterAddress2 As New SqlParameter("@Address2", SqlDbType.NVarChar,
50)
            parameterAddress2.Value = Address2
            myCommand.Parameters.Add(parameterAddress2)

            ' Add Parameters to SPROC
            Dim
parameterCity As New SqlParameter("@City", SqlDbType.NVarChar, 50)
            parameterCity.Value = City
            myCommand.Parameters.Add(parameterCity)

            ' Add Parameters to SPROC
            Dim
parameteraInternational As New SqlParameter("@aInternational",
SqlDbType.Int, 4)
            parameteraInternational.Value = aInternational
            myCommand.Parameters.Add(parameteraInternational)

            ' Add Parameters to SPROC
            Dim
parameterTravelPoint As New SqlParameter("@TravelPoint", SqlDbType.Int,
4)
            parameterTravelPoint.Value = TravelPoint
            myCommand.Parameters.Add(parameterTravelPoint)

            ' Execute the command
            myConnection.Open()
           
            ## Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            ' Return the datareader
            Return result

    End Function


## The sql stored procedure ##

CREATE PROCEDURE [dbo].[sp_call_Station_Insert]
(
@Station As nVarChar(50),
@Type As nVarChar(50),
@address1 As nVarChar(50),
@address2 As nVarChar(50),
@City As nVarChar(50),
@aInternational As nVarChar(50),
@TravelPoint As Int
)
AS


DECLARE @ConVale As nVarChar(50)

SET @ConVale = (SELECT Station FROM tblStation WHERE @Station = Station)

If @ConVale = @Station
   
    BEGIN
        SELECT * FROM tblStation
    END
ELSE
    BEGIN
        insert into tblStation
            (Station, Type, address1, address2, city, International, TravelPoint)
        values (@Station, @Type, @address1, @address2, @City, @aInternational, @TravelPoint)
        SELECT * FROM tblStation

    END
GO

View 1 Replies View Related

Creating A Database In MSSql 2000, But NOT In The Default Directory

Sep 28, 2002

I am trying to find a solution to a thorny problem I have encountered...

I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.

I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.

I am using an ADO connection and trying to execute it, as follows:

The text of the relevant part of the program goes like this:

Dim strCreateDatabase As String
Dim MyCn As ADODB.Connection

Set MyCn = New ADODB.Connection

With MyCn
.ConnectionTimeout = 25
.Provider = "sqloledb"
.Properties("Data Source").Value = "MOP"
.Properties("Initial Catalog").Value = ""
.Properties("Integrated Security").Value = "SSPI"
.Open
End With

strCreateDatabase = "Create Database Rimon on (" _
& "Name = Rimon, Filename = '\mopusersMR_DiazsalpmsdataRimon_Data.mdf', " _
& "Size = 20, MaxSize = 100, FileGrowth = 5) " _
& "Log on (Name = 'Rimon', Filename = 'c:usersMR_DiazsalpmsdataRimon_Log.ldf', " _
& "Size = 2, MaxSize = 10, FileGrowth = 1)"

MyCn.Execute strCreateDatabase


Both the name of the Sql Server and the Win2K server is MOP.

All the directories exist, and I have full access to them.

When I try to run this command, I get the following message:

File
'\mopusersMR_DiazsalpmsdataRimon_Data.mdf' is on a network device not supported for database files.

When I omit this location, it works just fine.

If I run it on the server, using another directory, it works just fine.

Do you have any idea what I am doing wrong?

Thanks in advance for any help!
Rimon Rafiah

View 1 Replies View Related

Enable And Disable User In MsSql 2000 Database

May 25, 2007

Hi,




I'm using MsSql 2000 data base, how to enable and disable user in mssql 2000 version

View 3 Replies View Related

Running MSSQL 2000 Database In A 2005 Server

Jun 26, 2006

I'm running 2000 databases in a 2005 server. Can anyone tell me if there are adverse effects in doing this?
As I understand it, the 2005 performance benefits are available to databases running as 2000(Ver80) But some of the new futures may not be available..
And any documentation from Microsoft/white papapers regarding this subjects are appreciated..

View 8 Replies View Related

Inserting And Retriving Datetime Field In Database MSSQL 2000

Jan 10, 2006

Hi, Assume I have a table name "myTime". This table is simply only have 1 (one) DATETIME field "MyTestTime" (also serve as a primary number).Table MyTime- MyTestTime : SQLTYPE DATETIMETo insert a new row into this field, I simply wrote :SqlCommand sqlCommand = new SqlCommand("insert into MyTime values('2006-01-09')", sqlConnection);
I got the value of "2006-01-09" from a textbox or other relevan control.I realize when I try to use "SELECT * FROM MyTime" statement, MSSQL server 2000 automatically convert my date value from "2006-01-09" to "01/09/2006" (from YYYY-MM-DD to MM/DD/YYYY). I don't know why this one must be converted to MM/DD/YYYY automatically (I believe this behavior is depend on some "setting option" in my MSSQL server - but I don't know which one).The challenge is :In my country, the actual date format is like German Date format (DD-MM-YYY). Well I know this is only "Customization" problem. But how insert datetime value given from sql query to a datetime variable?// Connect to database, make a query, get the datareader result, and bla bla blaDateTime aDateTime = new DateTime;aDateTime = Convert.ToDateTime(myDataReader["PostDate"].ToString());// close connectionMy question isHow can I make sure that aDateTime's day is 09 not 01. How my program know that 09 is day not month. I can't use string.split() method because it's possible that my database setting will change from "mm-dd-yyyy" to "dd-mm-yyyy"thanks

View 4 Replies View Related

How Do I Make An MSSQL 2000 Server Database Go Offline/Online Using VB 6

Jun 21, 2001

Does anyone know how to do this? I am in the middle of writing a server side program, and this is one thing that I cannot do without.

Also, if anyone can answer that, how do I do the same with an Exchange 5.5 Email account?

Thanks ahead of time!
Cash Coleman

View 1 Replies View Related

Why Is My Database Re-indexing Script Not Able To Shrink Databases? (MSSQL 2000)

Aug 3, 2007

Hi,

Currently we have a variety of SQL 2000 (and 2005) database servers, we are having issues with the maintenance plan of a few SQL2000 boxes where they no longer have enough hard disk space to do a full index-rebuild on the system.

Now we want to re-build the databases indexes approximately once a week, or maybe a little less often, in the past this has worked fine with maintenance plans.

However, we now have issues because we have some databases in offline mode, and we are quite low on disk space with no plans for hardware upgrades anytime soon.
The temporary solution is to turn the index rebuilds off.

I have been working on a script that will:


Cycle through each database and within that database:

Go through each table
Run a DBCC DBREINDEX on the table
Move on to the next table
Once the reindexing of one database is complete
IF the database is not in simple mode

Backup the transaction log
Run a DBCC SHRINKDATABASE with the required amount of free space
Go to the next database until all are complete.The logic is quite simple but so far this has not worked, it would appear something is locking the transaction log until the script exits.


Now the script works fine excluding the shrinkdatabase, I always get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Shrinking database: inf_dev target percentage: 10 at: Aug 2 2007 5:33PM [SQLSTATE 01000]
Cannot shrink log file 2 (INF_PROD_Log) because all logical log files are in use. [SQLSTATE 01000]

Where I'm indexing the INF_Prod database.
A DBCC LOGINFO shows something along the lines of:



Code Snippet
BACKUP LOG successfully processed 45162 pages in 152.607 seconds (2.424 MB/sec). [SQLSTATE 01000]
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------
2 253952 8192 29618 0 128 0
2 253952 262144 29621 0 64 0
2 253952 516096 29620 0 64 0
2 278528 770048 29619 0 64 0
<cut cut>
2 9043968 370737152 0 0 0 29800000001528500007
2 9043968 379781120 29802 2 64 29800000001528500007
2 9043968 388825088 29801 0 64 29800000001528500007




Clearly there is something in the log file towards the end.
However, I don't know why this is happening as I'm running the script in the master database and I've backed up the transaction log of the database I'm working on.
I've tried doing Full backup + Transaction log + Shrink, it fails.
I've tried waiting 10minutes in the script + shrink, it also fails.

However, if I open a query analyzer and do a backup log, then a shrink it works perfectly every time.
However in the script it always fails no matter what I do.

Where am I going wrong here?

Regards,
Gareth

View 3 Replies View Related

Server Configuration For MSSQL 2000 And MSSQL 2005

Sep 6, 2006

Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache

View 9 Replies View Related

Creating Index In MSSQL 2000 From MSSQL 2005

Mar 24, 2008

Hi,

I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.

Ex:-

CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY

(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)

INCLUDE

(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);


Any help in creating such indexes in 2000 version is welcome.

Thanks,
Suresh.

View 2 Replies View Related

Generating Script For MSSQL 2000 From MSSQl 2005?

May 3, 2008

Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.

We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?

Could you please give me the step to do?

Thanks,
Sankar R

View 6 Replies View Related

Problem Copying Database From Sql Server 2000 To Sql Server 2005

May 15, 2006

Hi,

I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.

Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.

What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?

What it is that I am missing?

Kindly help me.

Thanks, regards.

Shobha

View 5 Replies View Related

MSSQL Express 2005 Vs. MSSQL 2000

Jun 15, 2006

Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.

The scrpt looks like this:


---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------


I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.

I was wondering if somebody there could help me with this problem?!

Thanks,
Ben"

View 1 Replies View Related

Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit

Nov 17, 2007

I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.

Current Environment is MSSQL 2000 32-bit with current Service Packs.

I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.

I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.

Upgrade options:

1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.

View 3 Replies View Related

Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)

Feb 10, 2008

Hi,

i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..

they have different table structures and names . . :D

i was thing of what language shall i use.. or what language is the best to work on this kind of project :)

hoping for your kind help guys. thanks :)



br
Frozenice

View 1 Replies View Related

Copying Tables From 2000 To 2005 Express

Sep 13, 2006

I have a rather sizeable SQLServer 2000 database. To work on an issue, I would like to copy just a couple tables into SQL Server 2005 Express. How does one go about this efficiently?

View 7 Replies View Related

Copying Multiple Tables From SQL 2000 To SQL 2005

Mar 27, 2008



Hello-

I need to know what would be the best way to perform a task I have been assigned. I have read multiple post online, and I came to the conclusion that the Import/Export wizard was my best choice. I'm trying to copy at least 80 tables from a SQL 2000 server to a SQL 2005 server. Currently I have these tables over on the destination server (SQL 2005) but this data is outdated and needs to be updated. The ulitimate goal would be to set up a SSIS process so that I can schedule this process to copy over once the data has passed QA. I followed through the Import/Export Wizard inside of the BID and I manually highlighted all of the tables and performed a edit "delete rows in destination table" . But to my alarm this did not occurr and now I have duplicate records in all of my 80 tables. I'm going to go through this process again, but I wanted to make sure this was going to be my best option.

Any suggestion ?



Shanon

View 4 Replies View Related

MSSQL 2000 On Top Of MSDE 2000?

Feb 28, 2004

Hi,

How does one install MSSQL 2000 on a machine that's already running MSDE 2000? Do i need to unintstall MSDE, or both can be installed seperately??

Is there a way to simply upgrade MSDE into MSSQL 2000?

Thanks!

View 1 Replies View Related

Change In Database User Perms When Copying Database

Mar 9, 2000

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?

View 4 Replies View Related

MSSQL 2000 Bcp

Oct 22, 2007

helo guys,
can someone send me the script/procedure in performing bcp on ms sql 2000? pls help me, i badly need it.. thank u so much!:)

View 5 Replies View Related

Xml Into Mssql 2000

Mar 25, 2007

HelloI watchet some posts but did not find answer to my question : how toimport data from xml into mssql 2000 using t-sql?i tried:USE Northwindif(object_id('dbo.test_xml') is not null)drop table dbo.test_xmlgocreate table dbo.test_xml (Id int identity(1,1), col1 text)goinsert into test_xml(col1) values('')godeclare @cmd varchar(512)set @cmd = 'D:Progra~1Micros~3MSSQLBinnTextCopy.exe /S /U[user] /P [password] /D Northwind /F c:pobierz.xml /Ttest_xml /C col1 /I /W "where Id = 1"'exec master..xp_cmdshell @cmd, 'no_output'godeclare@hdoc int, @doc varchar(1000)select @doc = col1 from dbo.test_xmlexec sp_xml_preparedocument @hdoc out, @docselect @hdoc as hdoc-- ...select * from openxml(@hdoc, ...--...exec sp_xml_removedocument @hdocBut my documet have more than 1000characters and more than max varchar. Sohow to do that?Best regardsAJA

View 4 Replies View Related

MSSQL 2000

Apr 20, 2007

MSSQL 2000 Replication between two servers on a workgroup

Is it possible? How do you set it up?

View 4 Replies View Related

How To Use Mssql-2000

May 30, 2007

I am using Visual Studio 2005 and MSSql-2005 Server. But i also want to add MSSql-2000 Server Database.

Both the servers are running but when i provide the connection string of MSSql-2000 in SqlConnection to access data base, i get an error. It says like MSSql-2005 is not allowing to access.

So, How can i add MSSql-2000 to Visual Studio-2005???

View 1 Replies View Related

SSIS Error When Copying Database To Database

May 5, 2008

I am trying to copy one database to another using copy wizard for SQL Server 2005. The destination database is on another server/box.
I get the following errors when executing the SSIS package: "The job failed. The Job was invoked by User abcd. The last step to run was step 1 (abcd_0_Step).".


"Executed as user: BILLSVRSYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:30:55 AM Progress: 2008-05-05 11:30:56.81 Source: crmtest_BILLSVR_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2008-05-05 11:30:57.34 Code: 0x00000000 Source: abcd_abcd_Transfer Objects Task Description: Failed to connect to server crmtest. StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp) InnerException-->Login failed for user 'abcdabcd$'. StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.... The package execution fa... The step failed."


Can this be done? Is there something that needs to be set security wise for this to work?

View 9 Replies View Related

Copying A Database

Mar 27, 2004

Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!

Vik!

View 4 Replies View Related

Copying The Database

Aug 18, 2004

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
Uday

View 2 Replies View Related

Help-for Copying Database

Oct 11, 2004

hi,
i m new 4 sql server.
i create one app. in asp.net.

i create one database in sql server.
now,
i want to make setup for my app.
so,
i want copy database and put on c:/..../wwwroot/app folder.

how can i do this?

one more things,
i create database using enterprise manager.
i want to see my database file.
where it is stored?

somebody help me.

it's urgent.
thanks in advance.

View 1 Replies View Related

Copying A Database

Nov 9, 2005

I created a DB on the server. i would like to make a copy of it (structure and data) on my local machine to play with. Then i try using the wizard i get this error:

Your SQL Server Service is running under the local account. You need to change your SQL Server Service account to have rights to copy over the net work.

Where do I go to change my rights to copy over the net work?

View 9 Replies View Related







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