AS400 Driver Issue
Oct 14, 2004
I am connecting to an AS400 from SQL Server using the iSeries Client Access. I am getting the following error when attempting to select from 1 table:
Server: Msg 7317, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' returned an
invalid schema definition.
The select looks something like: SELECT * FROM <instance>..<owner>.<tablename>
Selecting a subset of the fields (instead of all) does not make a difference.
All other table selects work fine - this is only a problem for one table
Any ideas? Your insight would be greatly appreciated. Thanks!
View 2 Replies
ADVERTISEMENT
Sep 1, 2004
Hi,
I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.
Thanks,
Vivek
View 1 Replies
View Related
Oct 19, 2006
trying to install sql server2005 on a windows 2003 server box.
getting msg below at the sql server . i looked at other posts on trying to uninstall SQL Native Access Client and norton antivirus. i could never find the snac on the add - remove programs and this server does not have a virus protection program yet.
here's the history of the installs on the server:
wanted to test a 2005 upgrade so:
1) installed sql server 2000 then sp4 then restored some databases to it - all OK
2) tried to upgrade to sql 2005 but ran into problems and left it at that.
had a disk drive crash on the d drive so lost the installs but not the operating system
when the drive was replaced, left alone for a while
then wanted to test a straight 2005 install
1) removed the broken 2005 attempt
2) removed the 2000
3) installed 2005 and got the error on the subject line:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
i've gone through as many of the forums that i can and have tried several things - like uninstalling 2005 and installing pieces and parts but but nothing seems to work.
Thanks!
Dan <><
View 17 Replies
View Related
Feb 4, 2008
i am attempting to run phpbb using ms sql 2005 on the same box but get the following error during the setup
Could not connect to the database, see error message below.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
i am not sure yet if this is an issue with sql, php or phpbb
php is installed in iis and has all the modules installed which the php msi installer supported. i can run php code but in this case when i'm running the install.php file for the phphbb setup, i fill in the values for the database and got that error
phpbb detects all the required server settings and sees that i have ms sql installed
any suggestions on what that error means or how to go about configuring odbc driver?
probably a simple issue. but i'm still new with sql stuff
View 1 Replies
View Related
Oct 13, 2004
Hello,
I want to have a comparison about operation of a power builder application with sql server database, using sybase native driver and ole db driver.
View 2 Replies
View Related
Oct 28, 2004
Hi,
Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).
Any help will be appreciated.
Thanks,
vtluu.
View 1 Replies
View Related
Jul 20, 2005
Hi,Could some please tell me whether Microsoft provides Type 2 and Type 4jdbc driver ? For Type 4 MS providescom.microsoft.jdbc.sqlserver.SQLServerDriver driver. What is thecofiguration required for type 2 driver and what driver class filesrequired ?Its very urgent to me please reply.Ajay
View 1 Replies
View Related
Jan 4, 2002
Hi Gurus.
One of my client is using AS400 machine and wants to migrate from flat files to SQL server/Oracle. But they do not want to buy a new hardware. They like to use the same machine.
Is there any Emulator or simulator available for AS400 machine to simulate NT or Alpha environment?
Any suggestions?
-MAK
View 1 Replies
View Related
Jun 6, 2000
I was wondering if anyone could give some insight into the tools or applications needed to utilize a AS400 to query info. from a sql database to gain performance and reliability. I need to know about methods for connection, library changes needed, software etc. Please Help,I do not have much experience with databases.
View 1 Replies
View Related
Apr 8, 2004
Hi, all!
I try run DTS from SQL with connection to AS400 (Client Access 32-bit ver. 8).I know this should be about 9000 records, but after 5000 records
it's stopped.
Plz , explain!
I'll gone to be crazy.... :(((
View 1 Replies
View Related
Jul 7, 2006
I have been handed the directive that I am to ftp flat files from our AS400 to the SQL server where the web interfaces will read the data.
I need to know if anyone out here knows how to automate the FTP download from the AS400 system.
I don't know any UNIX and even if I did the SQL box is also running a 3rd party shipping label system that disallows the use/installation of MSK Toolkit.
I thought I could use the FTP Task in the DTS package but I don't know what to put for the internet location and this is certainly not a Mapped path.
Please Help!
View 1 Replies
View Related
Jun 8, 2000
Has anyone succcessfully configured aa AS/400 as a Linked Server with MS SQL 7.0 using IBMDA400 - IBM AS400 OLE DB Provider.
Thanks
View 2 Replies
View Related
Nov 30, 2000
Looking for the fastest way to transfer data from an AS400 to a SQL server 7.0 database. Anyone with experience doing this?
View 3 Replies
View Related
Mar 14, 2004
Hi,
I need some help here. I am trying to do a DTS from As400 from SQL Server 2000. However, the file that I require have members. IF I just do a select statement, it will retrieve the earliest member.
Does anyone know how I can access the latest member?
Thanks a lot!
View 3 Replies
View Related
Feb 15, 2005
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
View 3 Replies
View Related
Feb 17, 2005
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
View 1 Replies
View Related
Nov 9, 2005
Hi All,
I had tried this connection before using ODBC to connec to AS400 using the SQL statment "SELECT * FROM TableName1 LEFT JOIN TableName2 ON fieldname1=fieldname2" but Im using the same statement but only one table to be queried and is giving me an error, I use this one :
...
strAs400="DSN=DsnName"
connAS400.Open strAS400,UserID,Password
rsAS400.activeconnection=connAS400
rsAS400.Open "SELECT * FROM TableName1 WHERE fieldDate >=" & RangeDateFr
" AND TableName1 <=" & RangeDateTo ,,adopendynamic,adlockoptimistic
...
On the above codes, I dont know what might wrong. It is giving me an error message ...SQL0104 - token fieldDate was not valid. Valid Tokens: FOR WITH FETCH...
Please help if someone knows this. I would appreciate it a lot...
God BLess and best regards,
Ronald
View 2 Replies
View Related
Sep 11, 2007
Simplest, fastest way to copy 7 tables from as400 to SS2K weekly. Any idea?
I have a as400 server and 2 SS.
as400
SSbox1: db1 and db2 (two databases)
SSbox2: db2 (one database called db2)
SSbox1: db1 is getting data from as400 then sending it into db2 daily.
SSbox2: DTS is copying everything from SSbox1.db2 into SSbox2.db2 weekly.
I wanna simplify process from SSbox2. Maybe a linked server from SSbox2.db2 to SSbox1.db2?
let me know if you don't understand.
=============================
http://www.sqlserverstudy.com
View 20 Replies
View Related
Jul 31, 2007
I am migrating from 2000 to 2005 and dts to SSIS on several projects. I have not been able to successfully (without error) pull from an AS400 table using "SELECT *". If I reference all field names, it works fine. SELECT * actually works in the sense that it pulls over all the data, but it fails at the end, almost like an unepected end of stream. Normally it wouldn't be a problem tp qualify each field, but when I do the data gets sorted. In this instance I can't have it sorted because (without going into the stupid details) the production needs to match the development to suit my boss.
Connection = .Net Provider for OleDb/IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
Data Source = DataReader Source (I have yet to be able to use and OLE Source for AS400 without error, although my colleagues can - weird)
Here is the error I get when I change:
SELECT FieldName1, FieldName2, FieldName3 FROM LIBRARY.FILENAME
to
SELECT * FROM LIBRARY.FILENAME
Error: 0xC0047038 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE_SRC_Tebosspf8H" (4492) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
I
Any suggestions? Is there a property on the task or connection that will fix this? Or any idea why I can't use an OLE Source to pull from AS400?
View 1 Replies
View Related
Apr 7, 2007
I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
View 1 Replies
View Related
Apr 26, 2006
Hi,
I am trying to transfer information from the AS400 to SQL Server tables but I am having problems trying to connect and get information from the AS400. In the data source I select the iseries connection and type in the ip address, username, and password. When I click the test connection button, it connects but under the initial catalog all I get is the computer name. If anyone has any solutions to my problem that would be great
Thnaks
Brian
View 2 Replies
View Related
Aug 7, 2006
I need to query data through SSIS from what I was told is an AS400 DB2 member table. I am assuming this is a sub-table of the main table. I was going to write a correlated sub-query in SQL to get this data, however our AS400 contracted programmer says that there is an easier way and she pointed me to the main table's member. I do not know how to go about accessing this.
Has anybody had experience with this? The AS400 programmer is familiar with SQL syntax, however she does not know how to have SQL grab the data from a member table.
If all else fails, I will just construct my correlated sub-query.
Thanks for the information.
View 5 Replies
View Related
Jul 2, 2007
Hallo
We got a €œSql Server€? and an €œAS400€? System. I can Read the information from the AS400 using a DataReader- Source my problem ist that I can not manage to write in the €œAS400€?. Using a DataReader- Destination I am not able to specify the destination table.
Does any one know what I am missing to Configure?
View 4 Replies
View Related
Dec 14, 2006
Hi everybody,
I'm new in SSIS and I'm trying to activate a lookup on a table reside on DB2/AS400. Iget the following message when I try to join the input column to the lookup column:
TITLE: Microsoft Visual Studio
------------------------------
The following columns cannot be mapped:
[STOREK_S, STOREK_S]
One or more columns do not have supported data types, or their data types do not match.
------------------------------
BUTTONS:
OK
------------------------------
I know both columns have the same format on the DB2 table.
What I'm doing wrong ?
P.S: I'm using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider as connection.
If I try with the Microsoft OLE DB Provider for DB2, I was unable to get the list of my table on teh AS400.
Could you help me ?
View 10 Replies
View Related
Oct 11, 2007
Hi All,
Does anyone know how to use parameters in Reporting Services with IBM/DB2? Keeps giving me errors whenever I try to use parameters (@).
Regards,
Joseph
View 2 Replies
View Related
Feb 27, 2008
Hi,
Getting this error. Any ideas?
Test connection failed because of an error in initializing provider. The host resource could not be found. Check that the Initial Catalog value matches the host resource name. SQLSTATE: HY000, SQLCODE: -360
Thanks
View 8 Replies
View Related
Aug 27, 1999
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 Related
Sep 16, 2002
Hello
I am trying to link SQL2000 to AS400. I have created the link and can access the data on the Server, but when I try to access from a client I get the following error:
Error 7399: OLE DB Provider 'MSDASQL' Reported an error. Data Source Name not found and no default driver specified.
Has anyone run into this and if so does anyone have a solution to this problem?
Any help would be greatly appreceiated.
Thanks
Phil
View 5 Replies
View Related
Jun 24, 2004
Hello...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 Related
Mar 21, 2006
Can SQL Server run in AS400 Machine?
Thanks.
-vince
View 3 Replies
View Related
Nov 6, 2007
Hi,Is there anyway to connect to AS400 table (something like link table feature in MS Access). OR. I have to use the DTS package and schedule it to update in intervals, this i am not preferring due to the performance issue ? your help highly appreciate thanksMurali.
View 1 Replies
View Related
Apr 9, 2004
Hi!
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.
I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to
configure DB2OLEDB drivers.
Could any one suggest how do go about doing this.
Do I need to buy Host integration server or is there any other way to do it.
your help is much appreciated
Nalina
View 1 Replies
View Related
Apr 16, 2004
Im currently trying to query an AS400 table which I know contains 18 rows. However when executing a query through Query Analyzer' using
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT col1,col2,col3 FROM DS.TABLE')
I'm only getting one row returned.
Has anyone any experience of this kind of issue, and if so a resolution?
Cheers
View 1 Replies
View Related
