I am currently in the process of migrating data from Sybase to Sql server and would like to know how to test the data migrated.
As of now, we took one table data from both source and destination and compared it in Excel to check if the data migrated looks good (note, we used SSIS to migrate data). However, I would like to check if there are any other best & easy ways to apprach data validation post migration.
Hi all,
Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.
I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).
I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)
An example of my branch script :
select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
I am currently working for the Sybase to Sql Server migration project and have been able to test migrate few tables using SSIS. After migration, i was doing some data comparision and could see the date formats are different between Sybase and Sql server. However, there are no issues with data like day in sybase becoming month in Sql server except the formats are different.
Do I need to act on this date formats? Not sure if this would cause any issues in front end application that will consume sql server date data.
In my database some of the store procedures getting the data from xml nodes.so I need to implement the validation to xml data for prevent sql injection.
View 0 Replies View Related
Hello,
I took our SYBASE DDL and created the tables on an SQL Server Express datbase on a PC and then downloaded our data from UNIIX using the WinSCP utility and BULK Inserted the data into the SQL Server database and everything uploaded fine apparently, but when we go to use the data we are getting some data differences.
Do you know of any data conversion issues that may be coming into play here maybe?
Thanks very much!
BobK
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
I Have a table with #Sample like below
=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
Â
Sample Data:
ID SSN STATE
1 999-000-000 AB
2 979-000-000Â BC
3 995-000-000Â CD
=================================
We used filter logic based on the SSN & State.
We are passing these values through variables like
Declare @State varchar(2)
Declare @SSN varchar(20)
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?Â
Could I ask how to spit the data into training and validation sets when doing data mining?
Thanks
Hi all,
I am Migrating a database from sybase to sql server 2000. I Have already created objects in sql server 2000. I have to only populate it with the data. I have decided to genrate insert script from the aquadata tool and run the script againgt sql server database. It works fine except for few table which have 1,50,000 and 9,00,000 rows. It shows insufficent memory error when i try to run the script with 1,50,000 and the script with 9,00,000 doesn't open in management studio. Please help.
Bharath JrDBA
Greetings,Can someone recommend me some online redbooks or anylink regarding "Migration from Sybase to Sql Server"?Any feedback will be appreciatedTIA
View 2 Replies View RelatedI am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?
Thanks,
Jay
Hi all,
Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.
I have migrated sybase database to sql server it seems successfully migrated, we required to validate data and datatype before going to production, how can this be achieved, kind of stored procedure which can be run both sides (sybase and sql) and validated in some way.
View 5 Replies View RelatedHi,
A partner company unfortunately chose Sybase SQL Anywhere v5 as their database and I have to return data from the tables for a web service that I am writing - YUCK !
The ODBC driver is riddled with errors and has no support. Third party .NET providers do not seem to support v5, so I have resorted to playing with DTS in Enterprise Manager.
After fumbling around for a while, I managed to get an "Execute SQL Task" to return data and saved the package in both SQL and VB.
Now, before I waste any more time heading in this direction, can anybody advise if DTS is supported by MSDE as the full blown SQL Server will not be an option ?
If it is supported - is this a good way to go, or is there a better solution ?
Thanks in advance.
Steve.
I need to move data from Sybase ASE to SQL Server 2005. What is the best way fo doing it?
View 1 Replies View RelatedHello,
Does anyone know the error number, syntax to use in Sybase?
For SQL Server, the following works:
RAISERROR ('No data found today for the GetOOHHedgeFundTrades Report.',16,1) WITH SETERROR
I am not sure about how to use this in Sybase.
Many Thanks
sqlnovice123
I have a coworker who needs to extract data from Oracle into SQL Server 6.5 and Sybase as part of an interface that runs daily. How is the best way to do this?
Tony
I'd like to select data out of an oracle table, and UPDATE a sybase table. So far I've got as far as the following:
1. create a package
2. add 2 connection managers, set retainsameconnection=true on both just to be sure.
3. add an executesql command which creates a temporary table
create table #temp_wm ( instru_id int)
4. add a dataflow task with:
i) an oledb source with a select statement: "select instru_id from mytable"
ii) an oledb destination with open rowset: #temp_wm
added an external column called instru_id in the inputs and outputs tab
when i run, the create temp table task work, the select works, but the insert into fails. If I change the select statement to:
"select instru_id from mytable where 1=0" it all executes fine.
So everythings copacetic as long as i don't need to actually insert any real records = brilliant!
1. HAS ANYONE OUTTHERE SUCCESSULLY USED SSIS TO INSERT DATA INTO A SYBASE TEMPORARY TALBE - MAYBE ITS JUST NOT POSSIBLE??
2. Any idea how I can fix my setup?
I'm usign Sybase ASE OLE DB Drivers
Note: i also tried ## temp tables, no difference.
This is the error output.
[OLE DB Destination [255]] Error: An OLE DB error has occurred. Error code: 0x80004005.
[OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (255) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Is it possible to get SQL Server Migration Assistant to read data from a Sybase view and store it in a SQL table. My problem is that I have a legacy sybase application that is being decomissioned and I have been asked to migrate the data into SQL Server. The problem I have is that:
The account I have been provided to connect to the Sybase database does not have select privileges on all the tablesI don't have the sa password for the Sybase database to alter the security. The vendor support contract has expired and it does not appear I have any way to get the sa password
The account I've been given has access to query data in several views even though it does not have access to the underlying tables that the view pulls data from. Since this is a legacy application whose data is now static, I wonder if it's possible to read the data from the Sybase views and dump it into SQL tables.
Hi guys I am fairly new to t-sql. I am sure there are SPs or scripts that I can use to create a procedure that will do the data validation in the staging table...
Client send us data and often some of the records have bad values... what i have to create is a process that will check for those values and update a flag in the staging table for each column if the data is not valid....
Please help me out if you have something that can be used for this.
Thanks a lot help in advance.
Being relatively new to SSIS, I'm looking for advice, or a best practice, regarding data validation before extracting the data for a transformation.
One of my project's require that certain data be validated in staging tables before it is loaded. The validations include checking for null values, verifying that a field is populated with apropriate values etc... The entire batch of data (good records and bad records) may be rejected depending on the validations.
I have a couple of different thoughts on how this could be handled...
Run a series of validation queries on the data before executing an SSIS package
Run some kind of validation transformation (does one exist or should I write a custom transformation?)
Place contraints on the target tables so that bad records error out on the load
Something else... I could be missing the completely obvious
#3 doesn't seem to viable as the entire load may be rejected if some of the data is bad...
Any thoughts?
Hello all...I am trying to validate that the new work date that is being imported from the text file does not exist in the table. So in other words I do not want duplicate data. This is what I haveSqlDataReader dr = new SqlDataReader(); SqlParameter sp = new SqlParameter("@WorkDate", Data.SqlDbType.datetime, 8, Data.ParameterDirection.Input); if (dr.HasRows) sp = "@WorkDate"; else *How does that look? Am I in the ball part…
View 5 Replies View RelatedHi.
I have one question regarding SQL Server replication.
I have setup SQLServer merge replication at my work, one of our subscriptions has unreliable connection to the publisher, after setting up the replication to this subscriber, it happened once that the connection was really bad so the merge agent for this subscription was failing before synchronizing data changes, and after that , the connection went good & the merge agent completed the synchronization successfully.
After a while I decided to validate all subscriptions, I discovered that this subscription has different row count for many articles although the merge agent was finishing all synchronizations successfully!!
so my question is:
how could this happen? I mean finishing the sync with success and still have different records!!
I thought that SQL Server will keep make the sync fail just because it can't do it!!
Is it right or there is something I misunderstood here?
How can I prevent this from happening again? at least I want SQL Server to inform me by failing the merge agent synchronizations.
I new here so hopefully I'm asking this question in the correct forum. I'm have a flat file that contains numbers that I need to verify that they begin with certain prefixes so they load to the correct client. For example in the flat file if I'm loading data for client A and their account numbers begin with 045XXXXXXX then it loads the data. But if there is a record that begins with 037XXXXXXX it should be loaded to client B instead and that records gets written to a error file.
So to summarize what I need , I'm looking for a check to kick out records if I'm loading client A's data versus if I'm loading client B's data.
Can this be done in a Conditional Split ?
Shanon
Hello everyone,
I have spent hours trying to figure out the validation issue with SQL Server Reporting Services with no luck.I am hoping someone can help me with the issue.
Basically I am looking for a way to validate the data entered by the user in the parameterized Reports.
say, the field is defined as int datatype. If user enter String, it should display the message saying string cannot be entered. Currently, My report does not check the user entered datatype, runs the underlying query and displays the error thrown by the DB ( oracle in my case).
I get :
An error has occured during report processing
Cannot read the next data row....
ORA-01722: invalid number
Instead of this, validation even before query is executed would have been perfect. Is there a way to do that?
Thanks in advance for the help
Phewa Taal
Hi all,
I want to make a conditional split based on the data type provided by the input.
For example : If the comming (Column x) is of data type (numeric) then pass , else do not pass.
(pass = Case 1
Do not pass = Case 2).
Is there any way for doing so ?
Hi All,
Is there any option present in SSMS that ll audit all the changes(insert/update/delete) done to data in a database from SSMS( not through any application/front end).
I dont want to place any trace or triggers, is there any in-built option.
Requirement: Some data (basically a SELECT) FROM SQLServer(2K) toOracle/Lnux on a CONTINUAL basis.Does NOT need to be Real-Time...can be like (Oracle's) Materialized View,i.e. automagically refreshable.What are my options in impleneting this process ?thanks--10gR2/Linux
View 1 Replies View RelatedI have a .Net database application that we've successfully deployed in a connected environment. Now we have a client that has the need to store data on a central SQL Server and publish that data out to tablet PC's that will be able to disconnect from the central SQL Server. At some point those tablet PC's will come back in and connect to the central server via VPN and will need to push their changes back to the server. Some fundamental questions:
1) Am I correct in assuming that replication is the best way to accomplish this?
If so,
2) Which replication type sounds appropriate to the above scenario?
3) Am I correct in assuming that the tablet PC's will need some version of SQL Server to support editing of the data in the disconnected state?
My perhaps incorrect first take on this was that we could use a licensed SQL Server on the central server and SQL Server Express as a replication subscriber on the tablet PC's.
Any guidance greatly appreciated!
I'd like to transfer some records between the following 2 tables. Surely this should be a no-brainer - what am i missing that is making this so impenetrable?
I am currently: Hoping someone can help me get here: (this is my first time of using SSIS btw).
here is the source table (MS Sql Server 2005, SP 2)
CREATE TABLE [dbo].[imagine_divs](
[div_mnemonic] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_date] [int] NULL,
[div_amount] [float] NULL,
[div_status] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_curr_mnem] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_upd_date] [int] NULL,
[div_payment_date] [int] NULL
) ON [PRIMARY]
here is the target table (Oracle 9i)
CREATE TABLE myschema.imagine_divs
(div_mnemonic NVARCHAR2(11),
div_date NUMBER(*,0),
div_amount NUMBER(20,5),
div_status NVARCHAR2(16),
div_curr_mnem NVARCHAR2(11),
div_upd_date NUMBER(*,0),
div_payment_date NUMBER(*,0))
/
I used the SSIS Import and Export wizard to copy data between the two tables, and attempted to execute it. I use Sql Native Provider on source, and Native Ole DBOracle Provider for OLEDB. however, I get an error:
[Destination - IMAGINE_DIVS [37]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12571: TNSacket writer failure".
I notice that the wizard has created a data flow task with 3 steps: source - imagine_divs, Destination - IMAGINE_DIVS and "data conversion 1".
Data Conversion 1 seems to be taking my source nvarchar columns and converting them to DT_STR with twice the size (for example div_mnemonic become DT_STR, size: 22).
If I change the mappings in the OLE DB Destination Editor, such that only the numeric and date-typed columns are included in the transfer, it works fine.
If I include any string-typed column in the destination editor mappings, I get the TNS Packet Writer error. If I remove the Data Conversion step and connection teh source and destination tasks directly, i get validation errors saying that:
Error 2 Validation error. Data Flow Task: OLE DB Destination [294]: Columns "div_mnemonic" and "DIV_MNEMONIC" cannot convert between unicode and non-unicode string data types. Package4.dtsx 0 0
this is despite the fact that everything is unicode here (right?)
what in the world is going on?
I am working on a coupon redemption project that appears to require dynamic data validation and need some help. For example, a coupon could require a person to buy XXX number of XXX items from XXX manufacture before the transaction is approved. The logical operator between each validation could also be “AND� and “OR�. My first attempt at this has been something along the lines of creating a coupon table with a child table of validation logic. However, being able to apply this logic (and at what layer) has turned out to be a challenge. I am very concerned about speed as all requirements have to be met before a transaction is approved. Any help with trying to figure out the best solution for this would be great@!
Coupons
C_ID
C_DESC
MFG_ID
MFG_OFFERNUM
Coupon_Validation
CV_ID
C_ID
MFG_ID (Manufacture)
MFG_PARTNUM (Manufacture Part Number)
CV_QTYREQUIRED (Number to meet requirements)
CV_ITEMLOGIC (AND, OR)
I need some suggestions on validating a string of text based on some business rules using T-SQL. I have a string similar to the following:
This is some text (1.00); this is some more (there's more text here) text (2.00); this is yet more text (1.10)
The above example illustrates a valid string. You'll notice the multiple sets of parens The parens that contain numbers must be followed by a semi-colon except for the last set of parens. Furthermore, any parens followed by a semi-colon must contain only numbers and not text. I can easily identify the positions in the string by using PATINDEX.
Get the closing paren not followed by a semi-colon, ignoring the last closing paren PATINDEX('%)[^;]', @string)
Get the closing paren followed by a semi-colon, ignoring the last closing paren PATINDEX('%);%', @string)
My question is, is there a way to quickly validate the data between the parens without visiting each character. This is SQL 2000 so using CLR regular expressions is out.
Thanks.