Hello,
I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.
Can I do it ? If yes, how to do it?
Thanks
Hi,
I need to synchronize the data between two SQL Server 2005 Express.
The database is really simple, just few tables (but data are inserted very often) without any triggers.
Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.
Does it sound a good idea?
Has anyone done this before o there are better solution?
Thanks in advance,
Stefano
I was trying to set up a replication process to move data from SQL 2005 to DB2/AS400.
To get it started, I have create a simplest table (say, PERSON) on SQL 2005 server, and the same table (PERSON) on DB2/AS400. I have only one column SSN (of type int). I have journaled the table on DB2 end. I was able to have successfully configured publisher (on SQL 2005) and subscriber (i.e. a pushed subscription). I used transactional replication. And I also installed HIS 2004 to have DB2OLEDB ready. I was able to use query/insert/delete against table on DB2 based the linked server (that I created for testing purpose).
I did a lot of reading on this and my impression is that replicating to DB2/AS400 is supported from SQL 2005. But I always have this error from replication monitor. Can anyone enlighten me what was wrong.
Command attempted:
CREATE TABLE MY_DB.PERSON(
SSN
INT NOT NULL)
(Transaction sequence number: 0x00000020000000F9000100000004, Command ID: 4)
Error messages:
A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601
A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601
Is there a good way to port data from an AS400 over to SQL server 2005? If anyone has any experience with this, can you tell me where to go to get info?
View 3 Replies View RelatedHi Guys
I am trying to replicate data from DB2/AS400 to SQL Server2005 (ENT edition) currently we use 3rd party tool to replicate data from DB2 to SQL Server2000 (ENT edition) and like to get rid of this 3rd party tool. I am searching for the last 3 weeks but didn€™t get a good starting point. I have linked DB2 to SQL Server2005 and can run queries against DB2/AS400 box. Now I want to set up transactional replication from DB2 to SQL Server 2005. I have read about peer to peer topologies but I don€™t know if that€™s the route I have to take?
So can someone please help me? I really appreciate your help.
Thanks
Tariq
Hi all,
I have a issue need your great help. Would you please help to check it and tell mw how to fix this issue? Thanks.
*****All of softwares are English version****
Server:
WIndows Server 2003 SP2 + SQL Server 2005 SP2
AS400:
V5R4
Client :
Winsows XP SP2 + SQL Server 2005 SP2
Client Access V5R4 Full Version.
*****All of softwares are English version****
Issue:
I have a file in the AS400 with Chinese characters and need to dowload to SQL Sever 2005 daily.
In the SQL Server, I created a database and table which collection is "SQL_Latin1_General_CP1_CI_AS", and I modified one of field which collection is from "SQL_Latin1_General_CP1_CI_AS" to "Chinese_Taiwan_Stroke_CS_AS" (( the filed data type is "nvarchar" and the length is 42).
That mean that field can display the Chinese characters correctly.
In the SSIS,
1.Craeted a OLE DB Source to get the data ( The field name is "CUST_CHINESE_NAME" and the length is 42) from AS400
2.Craete a Data Conversion to convert that field from "String" to "Unicode String[DT_WSTR]" (The new field name is "Copy of CUST_CHINESE_NAME")
3.Create a Data Conversion 1 to convert that field from "Unicode String[DT_WSTR]" to "String[DT_STR]" and code page is change to "950"(The new field name is "Copy of Copy of CUST_CHINESE_NAME")
4. Creatd a OLE DB Destination to get that convert data and input the SQL Server ( the filed data type is "nvarchar" and the length is 42).
When I execute that SSIS, I got the error message in the third step.
****************************************************************************************************************************
[Data Conversion 1 [521]] Error: Data conversion failed while converting column "Copy of CUST_CHINESE_NAME" (487) to column "Copy of Copy of CUST_CHINESE_NAME" (544). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Data Conversion 1 [521]] Error: The "output column "Copy of Copy of CUST_CHINESE_NAME" (544)" failed because truncation occurred, and the truncation row disposition on "output column "Copy of Copy of CUST_CHINESE_NAME" (544)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 1" (521) failed with error code 0xC020902A. 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. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC020902A. There may be error messages posted before this with more information on why the thread has exited.
****************************************************************************************************************************
Hi all
I am trying to connect to an AS400 to download data to a 64bit sql server 2005 server. I am able to do this easily by migrating existing sql2000 DTS packages but how do i do this is in an SSIS project creating the process from scratch? and how do i incorporate activex transforms like you could in DTS2000 into the trasnform with copy columns?
Please help i am slowly turning grey trying to get this to work.
thanks
Chris
Want to implement replication in sql server 2005
Kindly let me know how to do it.
Please
regards
cmrhema
Hi,
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
It€™s Very Urgent Requirements.
We need your timely help.
With Thanks & Regards,
Sundar
Hi All,
I am now working on the design phase of my project, we are looking to implement Change Data Capture (CDC) but i need some help if you guys has implemented before using the SSIS 2005 componets. I am trying to use the Following:
Source---------Derived Column---------Lookup---------------Conditional Split (to split New records and Updated Records)-----------Destination. Respectively.
Lets make it clear, my source holds (Old records and newly added or Updated records), the Derived Column is to Derive new columns called Insert_Date and Update_Date. The Lookup i am Using is to look the Fact_Table(the Old Records) as Reference, and then based on this lookup i will split the records on timely based using the Conditional Split. My question is
1. Am i using the right components?
2. what consideration should i have to see to make it true (some Logics on the conditional split)?
3. Any script which helps in this strategy?
4. If you have a better idea please try to help me, i need you help badly.
Thank you,
SamiDC
Hi All,
I am required to setup Transactional Replication between two database servers (SQL Server 2000) but am expected to do it through scripts and not through wizards.
So can anybody help me out on that front?
Thanks & Regards,
Rohit Saksena
how do i implement Snap shot replication..?
(IN SQL EXPRESS SERVER)
thanks.
I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.
How do you get the linked tables to display in the Linked Server folder?
David
how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
Hi Guyz
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
thanks in advance.
I am creating a migration tool that retrieves data from an AS400 and an SQL 7 server to create a new database in SQL 2005. I have managed to create a linked server in SQL 2005 to the AS400. I don't know if it works or not. I've been trying to create a linked server to the SQL 7 server with no luck. The AS400 and SQL servers are on the same network. The SQL 2005 server is hosted somewhere else. I have access to everything. The SQL 2005 server is running Workstation. I tried to use Integration Services but finally discovered it was unavailable to Workstation.
I could use some good ideas or help.
I have a simple SSIS package that I want to move data from SQL 2005 to DB2/AS400. In order to use OLEDDB destination, I installed HIS2004 that provides DB2OLEDB support.
For simplicity and testing purpose, I created a connection pointing to a SQL table that contains only one ccolumn "last_name" of type "nvarchar(50)". This is my source, I then created another connection using DB2OLEDB pointing to a table on DB2 that contains only one field "name" of type "GRAPHIC(50)". This is my destination and the connection works fine to DB2 database. When I run the package, it has type conversion error as follows:
[OLE DB Destination [34]] Error: An OLE DB error has occurred. Error code: 0x00040EDA. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E07 Description: "Data or literal value could not be converted to the type of the column in the data source, and the provider was unable to determine which columns could not be converted. Data overflow or sign mismatch was not the cause.".
If I manually assign a vaule to the "last_name" using the "Derived Column" task like "Hello John", it just works fine.
Anyone knows what went wrong? Did I miss anything here? Thanks.
As a FYI: DTS works great in moving data from SQL 2000/2005 to DB2.
Your help will be appreciated.
I am trying to send a file from SQL 2005 back to our AS400 DB2 db. First time trying to get this to work in SSIS.
I have a couple fields that are datetime in SQL Server and see SQL server sees datetime fields from DB2 as string so I am converting them to Strings using derived columns. That part seems to work fine. Get no errors on that part.
The conversions seem like they are working but it is faling when it tries to send it to as/400. I get the following error: any ideas on what is wrong?
[OLE DB Destination [1569]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5029: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ".
I even created a sql table with same field sizes as one I am sending to AS400 and its sucessful there. So seem like it mght have somthing to do with sending to AS400 but not sure what.
Any ideas?
Stacy
Hi All,
I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis
IBM DB2 UDB for isereis IBMDA400 OLE DB Provider
Regds,
Anu
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Thanks in advance!
Scott
Hi,
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
thnx,
pdns.
Hello everybody!
I hope that someone could help me.
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
I configure the connection to use the IUSR.
The source code that i use:
repl.InternetUrl = @"http://laptop/SQLMobileIIS/sqlcesa30.dll";
repl.Publisher = @"laptop";
repl.PublisherDatabase = @"database";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"Pubdatabase";
repl.Subscriber = @"SQLMobile";
repl.SubscriberConnectionString = @"Data Source='" + nomeFicheiroBD + "';Password='3409'";
The error that returns is:
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
Does anybody knows what i can do?
HELP I NEED SOMEBODY TO HELP!
Thanks!
I have written following code in my application
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program
Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition
Error: A request to send data to the computer running IIS has failed. For more information see HRESULT
Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider
Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace DeviceApplication1
{
public partial class Form1 : Form
{
string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1()
{
InitializeComponent();
}
private void DeleteDB()
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"DotNetCF";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"PubDotNetCF";
repl.Subscriber = @"SubDotNetCF";
repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(filename))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
// Display Same Data In Another DataGrid : dataGrid1
SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'");
SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn);
if (dsJobLists == null)
{
dsJobLists = new DataSet();
}
try
{
dsJobLists.Clear();
daJobLists.Fill(dsJobLists, "JobLists");
dataGrid1.DataSource = dsJobLists.Tables["JobLists"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
DeleteDB();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime())
{
// TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'.
this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists);
}
}
}
}
I have created a merge replication correctlly( I suppose, there were no errros)
Please help
Your help will be appriciated
I'm trying to connect SQL SERVER with my AS/400.
I linked my as400 with linkedserver.
When I execute a query with analyse query it works fine, but if I make a store procedure as schedule this job to get information from my as400 I got this msg:
Executed as user: NT AUTHORITYSYSTEM. OLE DB provider 'IBMDA400' reported an error. Access denied. [SQLSTATE 42000] (Error 7399) OLE DB error trace [OLE/DB Provider 'IBMDA400' IUnknown::QueryInterface returned 0x80070005: Access denied.]. [SQLSTATE 01000] (Error 7300). The step failed.
Why? anyone have any idea?
thx
I have been tring to connect to the as400 through the IBM ODBC (IBMDA400),
but have run into a wall. I was wondering, could i set up the 400 in SQL Server 2K as a DB or Table or something and maybe access it through SQLOLEDB?
Thanks
fvlmasl2
i have two computers with Sql server 2000 std edition(SP4) and windows 2003 std edition(SP1).
how should i do to balance the loading of sql server and sync these two servers?
The sql server mirroring means that if one server is down, then the other one will replace it?
I have been asked to make it possible for our SQL 7 server to pull infromation from AS/400 and utilize it in web applications, and such. Is there any way of doing this? I have heard that you can export the data on the as400 to a DB2 file, and import it with SQL. Is this the only way of access info off of the 400?
View 2 Replies View RelatedHello...Our company has been on an AS400 for eternity. We have chosen a new erp package called Syteline 7 which runs on 5 servers on of them being a SQl server. I currently have 1 IT person on staf now. Can anyone tell me what my workload for my one IT person maintaining these 5 servers be? I would have 35 users and 50 PC's in total.....also does anyone have any negative comment about Syteline 7?Thanks
View 1 Replies View RelatedCan SQL Server run in AS400 Machine?
Thanks.
-vince
Hi,
Im finding the way of connecting to DB 2 database which is exixst in the AS400
machine to do some data manupulation.Please attach the SQL cording.
Thanks
Pubudu
hi,
i have developed an web-enabled database application using ASp.net, C# and sql server 2000.
now i want to implement transaction controls over the same
can anyone plz help me in implementing the same?
thanks in advance
I wanted to know whether it is possible to query a database in AS400 server from MS SQL Server 7.0 using linked server. If it is possible, could you show some pointers as to how it can/should be implemented.
Thanks in Advance.
Manojkumar
DMT Team
DaimlerChrysler Capital Services
Phone - 203-845-7326
EMail - manoj.kumar@dcxcapital.com
Hi!
Please
I got the following Error Message when trying to update a AS400 Database
"(Table Name) on (Data Source) not valid for operation"
My question is? Does Sql Server require journaling the files? How Should I change this option?. Please Help!
I did try to fix the above error, but then I got a new message:
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
Could you help me?
I will appreciate your help.
Thanks in advance
Ivette V