How to Transfer View only?

Hi,
I'm using MS SQL Server 2000
I have 2 Northwind databases on the same server (local)
NwindA and NwindB
In NwindA I have vNewView
How do I transfer this vNewView from A to B (just the view not the data)
( I have hundreds of view that I need to transfer)
Thanks,
Oded DrorOded,shalom
I'd prefer doing such tasks with SQL DMO objects. Also , you can generate
script of all objects by using EM (right click on the database then
'Generate SQL Script')
Look at this example scripting all views into the file.
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
Save the module as MyModule.
To call the procedure, open the Immediate window, type the following line,
and then press ENTER:Call
ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Oded Dror" <odeddror@.cox.net> wrote in message
news:O6lABUGAGHA.208@.tk2msftngp13.phx.gbl...
> Hi,
> I'm using MS SQL Server 2000
> I have 2 Northwind databases on the same server (local)
> NwindA and NwindB
> In NwindA I have vNewView
> How do I transfer this vNewView from A to B (just the view not the data)
> ( I have hundreds of view that I need to transfer)
> Thanks,
> Oded Dror
>|||In EM, right-click your source db and choose All Tasks | Export Data. In th
e
wizard, fill out the connection and database names.
Choose "Copy objects and data between..."
Deselect "Include all dependent objects"
Deselect "Copy Data"
Deselect "Copy all objects."
Click "Select Objects".
Find and select the views you want.
Deselect "Use default options"
Click "Options" and verify the settings such as copying the database users,
etc.
Finish the wizard with run immediate or schedule later.
Hope that helps,
Joe
"Oded Dror" wrote:

> Hi,
> I'm using MS SQL Server 2000
> I have 2 Northwind databases on the same server (local)
> NwindA and NwindB
> In NwindA I have vNewView
> How do I transfer this vNewView from A to B (just the view not the data)
> ( I have hundreds of view that I need to transfer)
> Thanks,
> Oded Dror
>
>sql

how to transfer to multiple tables based on environment

How is it possible to set data to flow through development,test,and production environment?

I have created one package.Right now i am transferring data to tables in the development environment.

I want to use the same package for several diffrent distinations two based on environment.

Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..

I think Package configurations will do that for you. You can make your package 'portable' through environments by using them. Search this forum; there is a lot of info on that|||Configurations are designed to help you change the environment without alter the code in your package. You can use them to set connect strings, etc dynamically at runtime. If you right-click in an empty area of your package, the menu will contain an option for package configurations. There's good information in Books Online and this forum on how to use them.|||

Is it possible to change the connection for the lookups which i have used in the package..

|||Yes, by setting the connection string for the connection manager used in the lookup.|||sorry for not been clear ...is it possible to change connection in lookup using package configuration..|||I don't think I am understanding your question. Do you want to change the connection the lookup is using? If so, the answer is yes. If you are asking if you can use the lookup to change another connection string, the answer is no (at least not easily.) Could you describe your scenario in a little more detail?|||

sureshv wrote:

sorry for not been clear ...is it possible to change connection in lookup using package configuration..

The lookup component like the OLE DB components and other source destinations, use connection managers. The connection managers have properties. Then you can use package configurations to change those properties at run time (e.g connection strings) without having to change the package.

So, the short answer is YES.

Look in this forum and BOL for package configurations and you will get a better understanding.

|||will it cause any problem if i set the package configuration after creating the entire package..|||No, you should be able to set up configurations at any time.

how to transfer to multiple tables based on environment

How is it possible to set data to flow through development,test,and production environment?

I have created one package.Right now i am transferring data to tables in the development environment.

I want to use the same package for several diffrent distinations two based on environment.

Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..

I think Package configurations will do that for you. You can make your package 'portable' through environments by using them. Search this forum; there is a lot of info on that|||Configurations are designed to help you change the environment without alter the code in your package. You can use them to set connect strings, etc dynamically at runtime. If you right-click in an empty area of your package, the menu will contain an option for package configurations. There's good information in Books Online and this forum on how to use them.|||

Is it possible to change the connection for the lookups which i have used in the package..

|||Yes, by setting the connection string for the connection manager used in the lookup.|||sorry for not been clear ...is it possible to change connection in lookup using package configuration..|||I don't think I am understanding your question. Do you want to change the connection the lookup is using? If so, the answer is yes. If you are asking if you can use the lookup to change another connection string, the answer is no (at least not easily.) Could you describe your scenario in a little more detail?|||

sureshv wrote:

sorry for not been clear ...is it possible to change connection in lookup using package configuration..

The lookup component like the OLE DB components and other source destinations, use connection managers. The connection managers have properties. Then you can use package configurations to change those properties at run time (e.g connection strings) without having to change the package.

So, the short answer is YES.

Look in this forum and BOL for package configurations and you will get a better understanding.

|||will it cause any problem if i set the package configuration after creating the entire package..|||No, you should be able to set up configurations at any time.

How to transfer or migrate from old SQL cluster 2000 to new SQL cluster 2000

I have two Windows 2000 servers (Advance Edition) to form a Windows Cluster. I also install MS SQL 2000 Enterprise Edition on the cluster to form a MS SQL cluster. Now, I want to upgrade the hardware and OS (but keep on using SQL 2000), so I install Windows 2003 server Enterprise Edition on two new servers to form a new Windows Cluster. I am planing to install MS SQL 2000 Enterprise Edition on the new cluster, so the old SQL cluster and new SQL cluster are side by side. I would like to know how to setup a new SQL cluster (I know it has problem to rename SQL Cluster name, so how to fix this problem)? And how to transfer everything (such as system databases, users database, sql user account, password and maintenance plan jobs etc) from old SQL cluster to new SQL cluster? And how to switch over from old SQL cluster to new SQL cluster?

Thanks a lot !

This article which seems to be quite comprehensive:

http://vyaskn.tripod.com/moving_sql_server.htm

Though i've not done this for a while, broadly speaking i think you'll want to follow these steps:

1) install sql 2000 on the NEW cluster

2) backup all the databases (inc master, model, msdb)

3) detach the databases from the OLD cluster

4) move the files to the NEW cluster

5) restore the system databases on the NEW cluster

6) attach the user databases on the NEW cluster

You'll probably want to check out the sections in BOL about starting sql in single user mode and the info on sp_detach_db and sp_attach_db

Hope this helps!!

|||

Hi richbrownesq,

Thank you for your reply. The article is quite good. However it works for MS SQL single server, not for MS SQL Cluster.

The problem of MS SQL cluster is to rename the SQL Cluster name (i.e. switch over from old cluster to new cluster). If the new SQL cluster uses different cluster name from the old SQL Cluster, the job (created by old SQL cluster name) in new SQL cluster cannot be modified or deleted. So any idea to solve this problem?

Thanks !

|||

Sorry, can you expand on what you mean by:

"the job (created by old SQL cluster name) in new SQL cluster cannot be modified or deleted"

|||

For example, I create a scheduled backup job in old SQL cluster using Enterprise Manager to backup user databases. And now, I transfer everything (including the backup job) in old SQL cluster to new SQL cluster which uses different cluster name. However, I cannot modify or delete the scheduled backup job in new SQL cluster using Enterprise Manager, because the new SQL cluster name is different. The problem can be solved if the new SQL cluster name uses the same name, but how can I do so?

Thanks

|||

This may be due to the value of originating_server in msdb..sysjobs being the old clustername. Try updating the value of this to be your new cluster name in the form server/instance.

Hope that solves the problem.

|||

Updating the value of originating_server in msdb..sysjobs can solve the problem. Thanks!

By the way, if I setup a new SQL cluster with new cluster name and new cluster IP, and then I update the DNS entry (old SQL cluster name mapping to new SQL cluster IP) for switching over from old SQL cluster to new SQL cluster. Is it any drawback or potential problem using this switching over method?

Thanks a lot !

|||

I've not used this specific approach- i've generally been fortunate enough to only have one connection string and a decent size window of downtime to be able to just update it without worrying about DNS changes.

However, i know of people who use an approach similar to this for DR and it seems to work, so hopefully you won't see any issues.

Cheers

|||

Hi Richbrownesq,

Thank you very much !

How to transfer or export dbf file to SQL server 2005 express edition?

I need to transfer dbf file to sql server 2005 express edition with some periodic interval. Can any one please recommend which is the easiest and efficient method to do?. Like polling every 5 to 10 seconds transfer data to sql server 2005 ex edition.

Is it recommend to do it visual basic program?, how to do it. pls help

Hi Stephen,

In SQL 2005 you can set up a linked server, a stored procedure, and a recurring job to run the stored procedure. I'm assuming you want to import only new or changed rows from the DBF and the stored procedure would allow you to do this before you update your main table.

I don't usually work with SQL Express, and I see that you can set up a linked server, but SQL Server Agent is not available to set up the recurring job. However, you could write an external program (such as in VB) to call the stored procedure at the desired intervals.

How to transfer Microsoft Outlook data into SQL Server 2000 \ 2005

Hello,
I am looking for a process where by I can transfer Outlook data (Emails)
into SQL Server 2000 \ 2005 for certain urgent analysis.
Can some one please explain me how to accomplish this task?
Thanks in advance.
SurajitDo an EXPORT from Outlook into some sort of CSV file - and then do an IMPORT
into SQL Server via the import/export function
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"surajits" <surajits@.discussions.microsoft.com> wrote in message
news:FBA08A4D-3094-428C-8B87-56C47DA98C84@.microsoft.com...
> Hello,
> I am looking for a process where by I can transfer Outlook data (Emails)
> into SQL Server 2000 \ 2005 for certain urgent analysis.
> Can some one please explain me how to accomplish this task?
> Thanks in advance.
> Surajit
>sql

How to transfer Microsoft Outlook data into SQL Server 2000 \ 2005

Hello,
I am looking for a process where by I can transfer Outlook data (Emails)
into SQL Server 2000 \ 2005 for certain urgent analysis.
Can some one please explain me how to accomplish this task?
Thanks in advance.
SurajitDo an EXPORT from Outlook into some sort of CSV file - and then do an IMPORT
into SQL Server via the import/export function
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"surajits" <surajits@.discussions.microsoft.com> wrote in message
news:FBA08A4D-3094-428C-8B87-56C47DA98C84@.microsoft.com...
> Hello,
> I am looking for a process where by I can transfer Outlook data (Emails)
> into SQL Server 2000 \ 2005 for certain urgent analysis.
> Can some one please explain me how to accomplish this task?
> Thanks in advance.
> Surajit
>

how to transfer logins,pwds and current permissions

I need to restore a database and would have orphaned users I notice the
Transfer logins KB articles creates those logins and passwords.. But what
about the security such as if the login was dbowner or sysadmin or
processadmin . I am using SQL 2000I mean the transfer of logins and passwords in the MS KB articles
I believe the special sprocs listed there take care of the logins, passwords
and the SIDs so that the users in the databases are not orphaned but I was
just worried about how to transfer the Server roles. I believe the Database
access roles are within the database
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e86%23PdHaDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I'm not sure which transfer you are referring to (if you mean a mthod
which keep the correct SID or
> not).
> However, assuming that you do have matcing SID's (this is the first thing
to handle):
> Things like db_owner etc are inside the database, hence are transferred
with the restore.
> I'm pretty certain that the transfer of logins doesn't handle stuff in
master for your logins,
> though (sysadmin etc).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eRpy$0EaDHA.4028@.tk2msftngp13.phx.gbl...
> > I need to restore a database and would have orphaned users I notice the
> > Transfer logins KB articles creates those logins and passwords.. But
what
> > about the security such as if the login was dbowner or sysadmin or
> > processadmin . I am using SQL 2000
> >
> >
>

how to transfer logins between 2k5 instances

Hello,
I am wondering if there is a kb article on how to transfer logins
between two sql2k5 instances? I have seen one for sql2k but not for sql2k5.
Thanks.
JakeHi Jake,
You can use a SSIS package to transfer logins. Also you can check my blog. I
updated the MS procedure to SQL Server 2005. it is in Spanish, but the code
is T-SQL, of course :-)
http://solidqualitylearning.com/blo...02/25/1618.aspx
Regards
Antonio Soto
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jake" <noreply@.nowhere.com> escribi en el mensaje
news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am wondering if there is a kb article on how to transfer logins
> between two sql2k5 instances? I have seen one for sql2k but not for
> sql2k5. Thanks.
> Jake
>|||Antonio,
Thanks alot for the help.
Jake
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:OsSMC92QGHA.4608@.tk2msftngp13.phx.gbl...
> Hi Jake,
> You can use a SSIS package to transfer logins. Also you can check my blog.
> I updated the MS procedure to SQL Server 2005. it is in Spanish, but the
> code is T-SQL, of course :-)
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
> Antonio Soto
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Jake" <noreply@.nowhere.com> escribi en el mensaje
> news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
>|||You can still do it using SSIS (as opposed to DTS) . One thing to watch out
for is that for SQL logins the account is disabled and the password set to
random value as per Books Online "Transfer Logins Task"
Apart from that pretty sure the same rules applies - ie have to fix up with
sp_change_users_login
cheers,
Andy.
"Jake" <noreply@.nowhere.com> wrote in message
news:enqPtusQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am wondering if there is a kb article on how to transfer logins
> between two sql2k5 instances? I have seen one for sql2k but not for
> sql2k5. Thanks.
> Jake
>

how to transfer gdb format data to SQL server

My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??Assuming that you have an ODBC or OLEDB driver that allows you to use the GDB file, you could use that.

-PatP|||I feel this .GDB is from mainframe or unix if so as suggested use the compatible odbc driver.|||Thank you everybody
I found ODBC driver and everythink's working well (for the time being at least:))

how to transfer DTS from one computer to other

How to transfer DTS from one computer to other?
I have a DTS package runing on one server1. I need to transfer this DTS to some other server2. But these two servers are on the different network. Hence temme the way to transfer it to server2.
Thnks,
Rahulopen that DTS and click save-as, select Structured-Storage-File as location. a disk file will be generated. this can be imported to the new server by right click on Data-Transformation-Services and selecting Open-Package. after opening save by selecting SQL-Server as locationsql

How To Transfer databse ..

Hi,

I am doing one E-commerce Application , throgh C#. & sql server express editon. Both my Local & Web Hosting servers are in sql server express 2005. I designed Databse Throghly, to my local server. Now I want to Transfer tables to my web server. I have already Sql server mangement studio Express. I can not do.. I spent, more than 5 yrs time..

Please help asasp..

Thanks

Partha
SSIS is not a component of SQL Server Express. You should be able to script the table structures and run the resulting scripts against the remote server. As far as data, there are some utilities available for scripting your data to INSERT statements if you search the internet. Or you can do it yourself by using the management views in SQL Server 2005.

How to transfer database to mobile device?

Hi all, I'm new to mobile devices.

I have a desktop application that uses SQL Server Compact Edition. I will soon have a mobile application that will use the same database. How can I (at runtime of the desktop app, say when the user clicks a button) transfer the sdf file to the mobile device? I'm not necessarily interested in synchronizing the databases, simply overwriting the database on the mobile device with the one on the desktop would suffice.

Is something like this possible?

Thanks!

You can copy the SDF file from the desktop to the device using RAPI.|||Thanks, I'll check that out.

How to Transfer database from mssql2005 to mssql2000 by using generate script.

Currently i need to transfer database (complete with primary key, foreign key ,constraint) from mssql2005 to mssql2000 by using generate script. After getting the script, i execute the script in the sql analyzer 2000, i get a lots of syntax error. Following are some of the error :

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 99
Line 99: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Warning: The table 'HACOMP_TMP' has been created but its maximum row size (16427) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Warning: The table 'HAAPPRAISE_DEV_TMP' has been created but its maximum row size (9196) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Below are the first part of the script :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HRMP_Details]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HRMP_Details](
[Comp_CD] [smallint] NULL,
[MP_ID] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Replacement] [bit] NOT NULL,
[Employee_Replaced] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Terminate_Date] [int] NULL,
[Addition] [bit] NULL,
[Approved_Budget] [bit] NULL,
[MP_Permanent] [bit] NULL,
[MP_Temporary] [bit] NULL,
[Time_Needed] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MP_Contract] [bit] NULL,
[Contract_Period] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Full_Time] [int] NULL,
[Part_Time] [int] NULL,
[Hours_Required] [int] NULL,
[Status] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hire_By] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Headcounts] [int] NULL,
[Approved_Headcounts] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applicant_Score]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Applicant_Score](
[COMP_CD] [smallint] NOT NULL,
[Score_ID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_ID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dimension_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Interview_Code] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Interviewer] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Score] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment_Number] [int] NULL,
[ID] [int] NULL,
CONSTRAINT [PK_Applicant_Score] PRIMARY KEY CLUSTERED
(
[Score_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Advertisement]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Advertisement](
[COMP_CD] [smallint] NOT NULL,
[Advertisement_ID] [int] IDENTITY(1,1) NOT NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Advertisement_Through_Internet] DEFAULT ((0)),
[Channel_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Languages] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Advertisement_StartDate] [int] NULL,
[Advertisement_EndDate] [int] NULL,
[Monday] [bit] NULL,
[Tuesday] [bit] NULL,
[Wednesday] [bit] NULL,
[Thursday] [bit] NULL,
[Friday] [bit] NULL,
[Saturday] [bit] NULL,
[Sunday] [bit] NULL,
[Daily] [bit] NULL,
[Instruction] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL,
CONSTRAINT [PK_Advertisement] PRIMARY KEY CLUSTERED
(
[Advertisement_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HAAppraise_COMPETENCY_Approve]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HAAppraise_COMPETENCY_Approve](
[COMP_CD] [smallint] NOT NULL,
[COMPETENCY_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COMPETENCY_ID] [int] IDENTITY(1,1) NOT NULL,
[COMPETENCY_DESC] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Appraisal_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_Form_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_Login] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL,
[Competency_Tracking] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Competency_Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
END

Can anyone help me in this?

ps:I cant use the import/export utility because using this method does not include some of the primary key,foreign key, constraint and etc.

You can't script out and transfer the entire database.

There are incompatibilities between SQL 2000 and SQL 2005 schemas. (For example, there is not a [sys.objects] table in SQL 2000, SQL 2005 contains datatypes [such as (varchar(max)] that are not allowed in SQL 2000, etc.

However, you can script out specific data tables and transfer them to a SQL 2000 database as long as the datatypes are supported in SQL 2000, and there is not the use of any CONSTRAINT 'code' that is incompatible.

I suggest that you use the IMPORT/EXPORT wizard, or explore some of the third party products that will create scripts.

|||Using SSMS you can use the Create Script wizard which also support the creation of compatible scripts. Run the script after creating it to create the new database. Afterwards you can transfer the data(base) to the new database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi, using Sql Server 2005 Developer Edition Service Pack 2, right clicking on an existing database, selecting Generate Script and using the option to generate scripts for Sql 2000 does NOT prevent the generated script from containing "max". Is there a set of guidelines somewhere on what types/objects to avoid if you want to create a schema that is truly Sql Server 2000 compatible please?
|||

The (max) types cannot be converted to SQL Server 200 using the compat script, you will have to tweak the script on your own, changing the max to the appropiate types like text/image.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to Transfer database from mssql2005 to mssql2000 by using generate script.

Currently i need to transfer database (complete with primary key, foreign key ,constraint) from mssql2005 to mssql2000 by using generate script. After getting the script, i execute the script in the sql analyzer 2000, i get a lots of syntax error. Following are some of the error :

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 99
Line 99: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Warning: The table 'HACOMP_TMP' has been created but its maximum row size (16427) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Warning: The table 'HAAPPRAISE_DEV_TMP' has been created but its maximum row size (9196) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Below are the first part of the script :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HRMP_Details]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HRMP_Details](
[Comp_CD] [smallint] NULL,
[MP_ID] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Replacement] [bit] NOT NULL,
[Employee_Replaced] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Terminate_Date] [int] NULL,
[Addition] [bit] NULL,
[Approved_Budget] [bit] NULL,
[MP_Permanent] [bit] NULL,
[MP_Temporary] [bit] NULL,
[Time_Needed] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MP_Contract] [bit] NULL,
[Contract_Period] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Full_Time] [int] NULL,
[Part_Time] [int] NULL,
[Hours_Required] [int] NULL,
[Status] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hire_By] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Headcounts] [int] NULL,
[Approved_Headcounts] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applicant_Score]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Applicant_Score](
[COMP_CD] [smallint] NOT NULL,
[Score_ID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_ID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dimension_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Interview_Code] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Interviewer] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Score] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment_Number] [int] NULL,
[ID] [int] NULL,
CONSTRAINT [PK_Applicant_Score] PRIMARY KEY CLUSTERED
(
[Score_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Advertisement]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Advertisement](
[COMP_CD] [smallint] NOT NULL,
[Advertisement_ID] [int] IDENTITY(1,1) NOT NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Advertisement_Through_Internet] DEFAULT ((0)),
[Channel_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Languages] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Advertisement_StartDate] [int] NULL,
[Advertisement_EndDate] [int] NULL,
[Monday] [bit] NULL,
[Tuesday] [bit] NULL,
[Wednesday] [bit] NULL,
[Thursday] [bit] NULL,
[Friday] [bit] NULL,
[Saturday] [bit] NULL,
[Sunday] [bit] NULL,
[Daily] [bit] NULL,
[Instruction] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL,
CONSTRAINT [PK_Advertisement] PRIMARY KEY CLUSTERED
(
[Advertisement_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HAAppraise_COMPETENCY_Approve]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HAAppraise_COMPETENCY_Approve](
[COMP_CD] [smallint] NOT NULL,
[COMPETENCY_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COMPETENCY_ID] [int] IDENTITY(1,1) NOT NULL,
[COMPETENCY_DESC] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Appraisal_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_Form_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_Login] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL,
[Competency_Tracking] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Competency_Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
END

Can anyone help me in this?

ps:I cant use the import/export utility because using this method does not include some of the primary key,foreign key, constraint and etc.

You can't script out and transfer the entire database.

There are incompatibilities between SQL 2000 and SQL 2005 schemas. (For example, there is not a [sys.objects] table in SQL 2000, SQL 2005 contains datatypes [such as (varchar(max)] that are not allowed in SQL 2000, etc.

However, you can script out specific data tables and transfer them to a SQL 2000 database as long as the datatypes are supported in SQL 2000, and there is not the use of any CONSTRAINT 'code' that is incompatible.

I suggest that you use the IMPORT/EXPORT wizard, or explore some of the third party products that will create scripts.

|||Using SSMS you can use the Create Script wizard which also support the creation of compatible scripts. Run the script after creating it to create the new database. Afterwards you can transfer the data(base) to the new database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi, using Sql Server 2005 Developer Edition Service Pack 2, right clicking on an existing database, selecting Generate Script and using the option to generate scripts for Sql 2000 does NOT prevent the generated script from containing "max". Is there a set of guidelines somewhere on what types/objects to avoid if you want to create a schema that is truly Sql Server 2000 compatible please?
|||

The (max) types cannot be converted to SQL Server 200 using the compat script, you will have to tweak the script on your own, changing the max to the appropiate types like text/image.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to Transfer Database

I am trying to transfer my database from my computer where built it to another computer. I have microsoft SQL server 2005 CTP on my computer and the other computer has Microsoft SQL server.

Does anyone have any suggestions on how to do this? I cannot find much info on SQL server 2005 CTP. I have found some info about the copy database wizzard, but I cannot find that in CTP.

Please any ideas or info would be wonderful

Thanks

Sarah

Are the PCs networked ?

Use Data Transformation Services DTS - its wizard based and is simple if both servers can be seen from the target PC

|||

Yes they are networked.

What is the Data Transformation Services, and where can I find it?

Thanks

Sarah

|||There was a CTP compatibility wizard but it was removed now that SQL Server 2005 is live so the easy way to copy the CTP database to the SQL Server is to register the CTP with the SQL Server and use the backup and restore wizard and use the restore from device option. When you register the CTP with the SQL Server the CTP becomes local to the other SQL Server. The above is assuming both are SQL Server 2005. Hope this helps.|||

okay I will try this and get back with you.

Thanks for the help

|||

Well the other sql server seems to be 2000. I am really in the dark on how to use CTP. I cannot even find my database in it. When I tried to register the CTP it tells me the connection does not exist or access is denied? I also tried the Data transformation services and it tells me the same thing.

I really have no idea how this CTP works. I can't see my database in the sql server configuration manager. I know that I can go into visual web developer and use the database, but I don't know much else about it.

Any other ideas, or maybe I am choosing the wrong options when I use these wizards. How do I know what options to choose? Any help is much appreciated!!!

|||It depeneds on where you got the CTP, if you downloaded it from your employer's MSDN open the folder and go to setup rerun it and choose repair option and select management tools to install the Management Studio which is the new Enterprise Manager, if you installed it from CD then copy the content of the CD to you harddrive and rerun setup and follow the same steps. To move a CTP based database to SQL Server 2000 is create blank database in SQL Server 2000 and run INSERT INTO statements per table. The reason is you are going back not forward. If you run into problems change the INSERT INTO statement to INSERT INTO with Column list that gives you control of the INSERT order. Run a search for INSERT INTO statement in SQL Server BOL (books online). Hope this helps.sql

How to transfer data?

I am installing MSDE, then a batch file calls osql which calls
script files to build the structure.
How can I easily transfer some table data?
I have a few tables that need some data (not a lot of data but some data in
around 8 tables).
Thanks,
ShaneHi,
Did you meant to transfer data from one MSDE SQL server to another?
then you can,
1. BCP OUT the data from source server in txt files
2. BCP IN to destination server
Have a look into the BCP command in books online.
Note:
If the data trasfer is going to happen in a single server between databases
or with in a database then you can use
Insert into table_name select col1,col2,col3... from table_a, table_b and
then conditions
Thanks
Hari
MCDBA
"SStory" <TheStorys@.TAKEOUTTHISSPAMBUSTERsofthome.net> wrote in message
news:O3#GbM1GEHA.2516@.TK2MSFTNGP12.phx.gbl...
> I am installing MSDE, then a batch file calls osql which calls
> script files to build the structure.
> How can I easily transfer some table data?
> I have a few tables that need some data (not a lot of data but some data
in
> around 8 tables).
> Thanks,
> Shane
>

How to transfer data to another site and update data later on

I have a project that requires to send dataset that meet certain criteria
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value has
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.
Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.
|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:

> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to move
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly undo
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> before
> somebody
> has
> TX's
> limited
>
>
|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
move[vbcol=seagreen]
TX[vbcol=seagreen]
undo[vbcol=seagreen]
criteria[vbcol=seagreen]
SQL[vbcol=seagreen]
value[vbcol=seagreen]
at[vbcol=seagreen]
the[vbcol=seagreen]
|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:

> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> move
> TX
> undo
> criteria
> SQL
> value
> at
> the
>
>

How to transfer data to another site and update data later on

I have a project that requires to send dataset that meet certain criteria
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value has
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to move
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly undo
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > I have a project that requires to send dataset that meet certain criteria
> > stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> > Server 2000 at TX and then update parent's dataset that has been sent
> before
> > monthly for whatever changes that have been made over the time. Can
> somebody
> > teach me a way how to do it? As I don't know which record what data value
> has
> > been changed at our office over the time so as to update the dataset at
> TX's
> > SQL Server. It definitely will have changes. Also our SQL Server has
> limited
> > resource and the transmitted dataset will be about 400-500K records at the
> > first population.
> >
> > Thank you in advance for your help.
>
>|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
> > Take a look at DTS if you want to try to automate the process.
> >
> > If you would like to do it by hand, I would suggest that you use bcp to
move
> > the data out to a flat file, WinZip the file and ship it. On the TX
> > system, bcp that new data into a staging table and then perform your
> > updates.
> >
> > Note: Before performing your updates, you should probably back up the
TX
> > database. That way if you really do something strange, you can quickly
undo
> > it.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> > "Emily" <Emily@.discussions.microsoft.com> wrote in message
> > news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > > I have a project that requires to send dataset that meet certain
criteria
> > > stored in SQL Server 7.0 in my office at L.A. to the parent company's
SQL
> > > Server 2000 at TX and then update parent's dataset that has been sent
> > before
> > > monthly for whatever changes that have been made over the time. Can
> > somebody
> > > teach me a way how to do it? As I don't know which record what data
value
> > has
> > > been changed at our office over the time so as to update the dataset
at
> > TX's
> > > SQL Server. It definitely will have changes. Also our SQL Server has
> > limited
> > > resource and the transmitted dataset will be about 400-500K records at
the
> > > first population.
> > >
> > > Thank you in advance for your help.
> >
> >
> >|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> > Thanks for your help. But how can I tell which record has changes and
> > requires to do an update to the TX's database?
> >
> > B.Regards,
> > Emily
> >
> > "Rick Sawtell" wrote:
> >
> > > Take a look at DTS if you want to try to automate the process.
> > >
> > > If you would like to do it by hand, I would suggest that you use bcp to
> move
> > > the data out to a flat file, WinZip the file and ship it. On the TX
> > > system, bcp that new data into a staging table and then perform your
> > > updates.
> > >
> > > Note: Before performing your updates, you should probably back up the
> TX
> > > database. That way if you really do something strange, you can quickly
> undo
> > > it.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > > "Emily" <Emily@.discussions.microsoft.com> wrote in message
> > > news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > > > I have a project that requires to send dataset that meet certain
> criteria
> > > > stored in SQL Server 7.0 in my office at L.A. to the parent company's
> SQL
> > > > Server 2000 at TX and then update parent's dataset that has been sent
> > > before
> > > > monthly for whatever changes that have been made over the time. Can
> > > somebody
> > > > teach me a way how to do it? As I don't know which record what data
> value
> > > has
> > > > been changed at our office over the time so as to update the dataset
> at
> > > TX's
> > > > SQL Server. It definitely will have changes. Also our SQL Server has
> > > limited
> > > > resource and the transmitted dataset will be about 400-500K records at
> the
> > > > first population.
> > > >
> > > > Thank you in advance for your help.
> > >
> > >
> > >
>
>

How to transfer data to another site and update data later on

I have a project that requires to send dataset that meet certain criteria
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value ha
s
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:

> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to mo
ve
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly un
do
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> before
> somebody
> has
> TX's
> limited
>
>|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
>
move[vbcol=seagreen]
TX[vbcol=seagreen]
undo[vbcol=seagreen]
criteria[vbcol=seagreen]
SQL[vbcol=seagreen]
value[vbcol=seagreen]
at[vbcol=seagreen]
the[vbcol=seagreen]|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:

> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> move
> TX
> undo
> criteria
> SQL
> value
> at
> the
>
>