Help on Oracle RDB Migration to SQL Server.

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra MohanOn Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

> Hi,
> We need help on following things,
> 1. Inputs on creating comments on the columns & Tables of a SQL
> Database & generating the sql script of that.

There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

> 2. Is it possible to call a .exe file in SQL server like following
> code in ORACLE
> create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
> language SQL;
> external
> name "CERT_VERIFY"
> location 'HOST_IMG:TEST_CALCS.EXE'
> with ALL logical_name translation
> language C
> GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.

> 3. We are using Rules for restricting data(now), We need inputs
> whether to use Check constraints or Rules.

Use Check constraints.

Hal

Help on Migrating from 2000 to 2005- SSIS package

Hi,

I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,

- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables

Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow

-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )

-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client

pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not

Thanks for ur help
Niru

You should be able to build just one package. You might need as many data-flows as there are destination tables. it depends on the situation.

Use precedence constraints to ensure that the data-flows are executed in the correct order according to RI declared on the destination tables.

If you ask more specific questions then more specific answers can be provided.

-Jamie

|||

Thanks Jamie for the clarifying ..yes i think i should create different dataflows instead of different packages .

Here is the scenario which iam working now

I have a table...i have to map some of those fields to the existing 5 tables.

fileds should map like this

source table Destination fileds with tables
(contract)

contract_code --> contract_code (Incident)

Date --> DateId (Dates)
TimeId (Times)

Duration --> Duration (Session)

Ended --> Completion_state(Session) will have options like 'yes','no','unknown'
Problem(session) -- need to set 'true' if completion_state is 'No'

From --> Number(Audit) -- Adding source if it is not existed
Id (Audit) -- giving id if you added target in the above column

To --> Number(Audit) -- Adding Traget if it is not existed
Id (Audit) -- giving id if you added target in the above column

[I will differentiate the difference betweeen 'from' and 'TO' columns in session tables seperatley by refernce]

I think you got some idea what iam trying to do ..like this i have 9-10 tables .

I apperciate if can help me out in this .

Thanks

Niru

Help Needed ! , Data Migration from MS Access2003 to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects.

Need Help!

Thanks

Mahesh

Hi Mahesh,

SSMA is a right tool for migration

http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en and refer discussion at TechNet for troubleshooting http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.access.sqlupsizing&cat=en_us_FB980B1A-C148-9C19-560F-548B843A67AB&lang=en&cr=us

and refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

Help needed - Parameter driven extract

Hi All,

I am designing a data migration tool using SSIS. As part of it, within a package I need to get a list of of customers from a SQL Server database table and extract the data for those customers from a seperate Sybase database. How do I make my SQL command to extract the data parameter driven? If I store the list of customer ID's in a package variable can I access it in the SQL command? I am using an ODBC connection for Sybase.

Any help would be greatly apreciated.

Nadella

If I were you; I would create a staging table to load the Sybase data that is accesible via Query-join from the SQL Server database. That way you could write single query joining the 2 tables.

I don't know how you could implement this via SSIS variable...