Need Help With Microsoft's DB2 Driver In SSIS Package
Feb 13, 2008
I need to re-write some SQL 2000 DTS packages in SSIS to pull data from our db2 system into SQL. I would like to use Microsoft's DB2 OLE DB driver. I'm new to SSIS and the db2 provider, so it may just simply be that I'm setting things up incorrectly.
What I've done:
- created the udl via the Data Access Tool that comes as part of the DB2 provider - connection tests successfully
- created the data source in the SSIS package explorer - connection tests successfully
- added a connection in the connection manager in the SSIS package - connection tests successfully
When I try to set up a data flow, I pull in an ODBC source and edit the source to use the db2 connection. I can get so far as to select the desired table from the drop-down list and then when I try to view columns or just click the okay button, receive the following error :
Error at Package [Connection manager "UDLFilename.catalog.username"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80070057.
An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80070057
Description: "The parameter is incorrect.".
Error at Data Flow Task [OLE DB Souce [1]]: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ULDFilename.catalog.username" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Additional information:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
I've been working on this for a week and just can't seem to make any headway or find any existing help on the internet. Can anyone give me any idea what parameter the error is referring to or any clue as to what to try next? I only have two weeks left before we implement...
Thanks much for your help!!!!
Laura
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.
odbc_pconnect() [function.odbc-pconnect]: SQL error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded., SQL state S1001 in SQLConnect
we got the error with access 2000 database and PHP as prog. language .
we created dsn for the connection.
reboot solves the problem. but we need another solution better than this.
I have a SSIS package running well in production however sometimes the package will fail when the excel file contains more than 25000+ rows.
The SSIS package is run by SQL Server Agent and is set to run in 32bit mode.
I checked the data by loading in batches and all data loaded successfully. But the funny thing is when I run the same package on my local development PC using BIDS and the same data file. The package loads all 25000+ rows successfully.
Is there some setting that is preventing all rows loading in the server environment.
On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.
Thanks,
Arif
Server Error in '/' Application. --------------------------------------------------------------------------------
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
The Microsoft SQL Server Driver for PHP Team is proud to announce the availability of the Microsoft SQL Server 2005 Driver for PHP CTP. This driver is a PHP 5 extension that allows the reading and writing of SQL Server data from within PHP scripts. The extension provides a procedural interface for accessing data in all editions (including Express editions) of SQL Server 2005 and SQL Server 2000. The API makes use of PHP features, including PHP streams to read and write large objects.
The SQL Server 2005 Driver for PHP Community Technology Preview (October 2007) is a preview release not intended for production purposes and can be downloaded here. To provide feedback or to get more information, visit our team blog at http://blogs.msdn.com/sqlphp . Please post any questions you have about this CTP release in this forum.
The microsoft JDBC driver doesn't accept CLOB & BLOB field. Does anyone know about this problem ? Is it possible to found another driver that works correctly and that is free ?
I am getting the below error when I try to access the table in 2005 database through 3rd party application from the application server.
“[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt�
This database was restored from sqlserver 2000, so the ODBC connection in the application server first was pointing to 2000 database and now its dropped and recreated to point to the 2005 database.
I dont get this error message when I do the same action from my pc.
I google'd on this error message, and I am not sure if the driver has to be upgraded or if there is anything else that I am missing here.
Well i have one question regarding the Timeout . I have an SQL SERVER 2005 database and have an VB6.0 application. I have a stored procedure which inserts more than 90,000 rows from one table to another . Some times i get an exception as [Microsoft] [ODBC SQL Driver] Timeout Expired in the Visual Basic application and some times it works fine. The exception i receive within 30 seconds , but when i try the same stored procedure in query analyser , it works fine. also i have increased the timeout in visual basic , but still i get the same error.
Tengo instalado Microsoft Windows 2003 Server Standard Edition en un servidor IBM xSeries 220, estoy tratando de instalar IBM Director 5.10 y no ha sido posible hacer que se conecte con la base de datos.
Esto ocurre cuando intento configurar el Servidor SQL en la fase final de la instalación de IBM Director.
Gracias por su ayuda, ¿puede alguien darme alguna sugerencia?
Anyone know what I'm doing wrong? My JDBC is erroring. I'm using the Microsoft 1.2 driver, I have a SQL Server 2005 back end with the following clients connecting to it:
ASP.NET 2.0 web application Visual Studio C# console applications (*2) Java command line client.
Everything runs hunky-dory on my dev. environment (laptop, local, launched via Visual Studio) but now it's on the server, the Java client is erroring.
When I use the 1.2 sqljdbc.jar file, I get:
25-Jan-2008 12:20:29 com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin WARNING: ConnectionID:1 TransactionID:0x0000000000000000 Prelogin response packet not marked as a REPLY
My Error Trap: -------------- SQL STATE : 08S01 ERROR CODE: 0 MESSAGE : The TDS protocol stream is not valid.
When I use the 1.1 file I get:
My Error Trap: -------------- SQL STATE : 08S01 ERROR CODE: 0 MESSAGE : The TDS prelogin response is incomplete. The target server must be SQL Server 2000 or later.
I don't know what these errors mean. I've tried compiling as java 1.5 and 1.6 with the same results. It works fine on my laptop (Vista Home Premium) but won't when I try and connect to the server. I've searched the web on the error messages and can't find an answer yet.
Any help appreciated.
My drivers are the Windows versions. Also, everything else is working okay on the server: The consoles are up and running and the web browser connects and SELECTs, UPDATEs & INSERTs okay. I ought to mention: My laptop is running SQL Server Development edition and the Server is running obviously something else.
My SQL Server version is 9.0.1399 and the OS is Windows Server 2003. SQL Authentication is mixed mode.
I am running a query which pulls back about 200,000 records.
When I run this in ISQL (SQL Server 6.5), it runs in almost 2 minutes. When I put it in a store procedure and run it off my ASP application, I get this error
[Microsoft][ODBC SQL Server Driver]Timeout Expired
We've tried many things including: 1) Increased connection and command time outs in Global ASA 2) Have the ODBC driver log long Quires to a file 3) Changed Query Time out in Enterprise Manager(360 seconds) Go to tools --> Preferences/configure -->Connection Tab 4) Changed Maximum allowed open database from 20 to 200 5) Changed resource timeout to 360 seconds from 5 seconds 6) Changed remote query timeout to 0, allowing infinite wait.
I inherited some really twisted asp pages from another developer that crank for a long time. They used to work, albeit slowly. Now when I try to run one I get the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
Short queries run fine.
IIS4 & SQL Server are on the same box. I didn't the ASP pages or the IIS or SQL server config. I did install VStudio 6.0 a while back.
Thank you to all who reply to this perplexing question. Mike
Database VMFGSRVRVMFG is SQL 2000 Std. Database NT002P_GL is SQL 7.0. All SQL versions are at the current SP levels.
I receive the timeout message when I am in EM, database,VMFG(database), Views, Open View, Return All Rows. I can successfully run the code in QA. I believe I am running up against a time parameter in SQL Server. I have reviewed all time parameters that I can find. However, I still receive the error. What parameters should I be looking at? I am attempting to create a view with the following code: -------------- CREATE view uv_olap_adj_co_profit as select customer.id as customer_number, customer.name as customer_name, customer_order.salesrep_id as salesperson, isnull(customer.country, 'USA') as country, '7' as business_unit, customer_order.order_type as order_type, uv_olap_co_profit.customer_order, uv_olap_co_profit.work_order, uv_olap_co_profit.close_date, uv_olap_co_profit.period_year, uv_olap_co_profit.gl_period, isnull((uv_olap_co_profit.billing + ut_man_adj.billing) *ut_fin_adj.billing, uv_olap_co_profit.billing*ut_fin_adj.billing) as billing, isnull((uv_olap_co_profit.material_cost + ut_man_adj.material)*ut_fin_adj.material, uv_olap_co_profit.material_cost*ut_fin_adj.materia l) as material, isnull((uv_olap_co_profit.labor_cost + ut_man_adj.labor) *ut_fin_adj.labor, uv_olap_co_profit.labor_cost*ut_fin_adj.labor) as labor, isnull((uv_olap_co_profit.burden_cost + ut_man_adj.burden) *ut_fin_adj.burden, uv_olap_co_profit.burden_cost*ut_fin_adj.burden) as burden, uv_olap_co_profit.service_cost as service from uv_olap_co_profit, ut_man_adj, ut_fin_adj, customer_order, customer where uv_olap_co_profit.customer_order *= ut_man_adj.cust_order_id and uv_olap_co_profit.work_order *= ut_man_adj.work_order and uv_olap_co_profit.period_year = ut_fin_adj.year and uv_olap_co_profit.customer_order = customer_order.id and customer_order.customer_id = customer.id and (customer_order.salesrep_id = 'LABELAIREP' or customer_order.salesrep_id = 'OMOHUNDR' or customer_order.salesrep_id = 'EGGERSR') union all select customer.id as customer_number, customer.name as customer_name, customer_order.salesrep_id as salesperson, isnull(customer.country, 'USA') as country, customer.user_2 as business_unit, customer_order.order_type as order_type, uv_olap_co_profit.customer_order, uv_olap_co_profit.work_order, uv_olap_co_profit.close_date, uv_olap_co_profit.period_year, uv_olap_co_profit.gl_period, isnull((uv_olap_co_profit.billing + ut_man_adj.billing) *ut_fin_adj.billing, uv_olap_co_profit.billing*ut_fin_adj.billing) as billing, isnull((uv_olap_co_profit.material_cost + ut_man_adj.material)*ut_fin_adj.material, uv_olap_co_profit.material_cost*ut_fin_adj.materia l) as material, isnull((uv_olap_co_profit.labor_cost + ut_man_adj.labor) *ut_fin_adj.labor, uv_olap_co_profit.labor_cost*ut_fin_adj.labor) as labor, isnull((uv_olap_co_profit.burden_cost + ut_man_adj.burden) *ut_fin_adj.burden, uv_olap_co_profit.burden_cost*ut_fin_adj.burden) as burden, uv_olap_co_profit.service_cost as service from uv_olap_co_profit, ut_man_adj, ut_fin_adj, customer_order, where uv_olap_co_profit.customer_order *= ut_man_adj.cust_order_id and uv_olap_co_profit.work_order *= ut_man_adj.work_order and uv_olap_co_profit.period_year = ut_fin_adj.year and uv_olap_co_profit.customer_order = customer_order.id and customer_order.customer_id
I have a J2EE based web application. There is a requirement where user triggers a process to query a database, and the application needs to save the results to an SDF file (Microsoft SQL Server CE 2.0 / Microsoft SQL Server Compact Edition) database. I need JDBC driver for it, but could not find it anywhere. Please help. Also, if there is any alternate way to do this, please let me know.
I've tried every websearch possible to try and get a solution for this problem but can't.
Development Software: SQL Server 2000 Enterprise Manager 8.0 Query Analyzer 8.00.194
Error: When I run a mildly complex view in Enterprise Manager I get the error:[Microsoft][ODBC SQL Server Driver]Timeout Expired after about 30 seconds.
When I copy and paste and run the exact same code in Query Analyzer the Query runs for longer than 30 seconds and runs until completion. Now my question is since this apparently seems to be an Enterpise Manager problem and not on the server end, where do I go in Enterprise Manager to change that setting? I've looked everywhere in Enterpise manager and can't find a "command timeout" setting.
Hello, I installed MS SQL Server 2000 Evaluation Edition on Windows XP Pro. I have database on the same PC. When I am running a query(view) from Query Analyser I get "[Microsoft][ODBC SQL Server Driver] Timeout Expired" error message . When I am running the same view on smaller amount of data it works.
I have tried updating the DSN configure option i.e. 30000 milliseconds.
But I am still facing the problem of Timeout Expired.
I am using sql server 2005 for daily importing data from Excel Dump Files into sql server table. Everyday the database gets updated. The Error mentioned in Subject normally gets once a month, due to daily updation of database whether each table structure gets changed like index, trigger, etc.
All, I need a help from you all. I am getting this error if the users are running the application which is connecting from MSAccess97 to SQL Server 2000. Error: [Microsoft][ODBC SQL Server Driver]Communication link failure I would appreciate if you send me the resolution for this problem. Thanks, Reddy
I get the message Microsoft [ODBC Sql server driver]communication link failure error message in my VB 6 application with MSDE engine. This error does not happen often. My connection string is as below.
Ive got a collection of view which piece together data from varying sources and assemble it ready for a stored procedure to squirt the data into a table.
This has worked fine up til yesterday when i had to amend one of the views to include a reference to another data set.
Essentially the data is for students and comes from our old db and our new one, each year group has different values so the views act to extract all data relevant for a child from both databases and accross all year data sets.
The problem im having is that i can get the final stored procedure to run. It ran fine the other day but since ive amended one of the views i get timeout errors wherever it's run. real problem when query analyser which has no timeout settings (apparently) times out as it means i cant run the query anywhere....enterprise manager will timeout after 30secs despite amending the query timeout setting to 600s.
so im a bit stuck, both views which the problem view is trying to combine run in under 30 seconds...when you try and combine them it times out- seems to run for a long long time.
note sure why this has started happening, from all ive read most peeps get this with EM and solve it using QA. but what if QA cant handle the query either?
heres the sql: (wont make much sense but hopefully shows that its not a complex query)
SELECT TOP 100 PERCENT dbo.StudentDetMaster.SetId, dbo.StudentDetMaster.StudentId, dbo.StudentDetMaster.Name, dbo.StudentDetMaster.CourseYear, dbo.StudentDetMaster.House, dbo.StudentDetMaster.Tutor, dbo.PGJSProfile.[Key Stage 1 Reading], dbo.PGJSProfile.[Key Stage 1 Writing], dbo.PGJSProfile.[Key Stage 1 Speaking & Listening], dbo.PGJSProfile.[Key Stage 1 Maths], dbo.PGJSProfile.[Key Stage 2 English], dbo.PGJSProfile.[Key Stage 2 Maths], dbo.PGJSProfile.[Key Stage 2 Science], dbo.PGJSProfile.[Verbal Reasoning Year 3], dbo.PGJSProfile.[Year 3 English Autumn], dbo.PGJSProfile.[Year 3 English Year Group Average], dbo.PGJSProfile.[Year 3 English Summer], dbo.PGJSProfile.[Year 3 English Year Group Average Score], dbo.PGJSProfile.[Year 3 Maths Autumn], dbo.PGJSProfile.[Year 3 Maths Year Group Average], dbo.PGJSProfile.[Year 3 Maths Summer], dbo.PGJSProfile.[Year 3 Maths Year Group Average Score], dbo.StudentDetMaster.[Year 3 Maths Set], dbo.PGJSProfile.[Verbal Reasoning Year 4], dbo.PGJSProfile.[Year 4 English Autumn], dbo.PGJSProfile.[Year 4 English Year Group Average], dbo.PGJSProfile.[Year 4 English Summer], dbo.PGJSProfile.[Year 4 English Year Group Average Score], dbo.PGJSProfile.[Year 4 Maths Autumn], dbo.PGJSProfile.[Year 4 Maths Year Group Average], dbo.PGJSProfile.[Year 4 Maths Summer], dbo.PGJSProfile.[Year 4 Maths Year Group Average Score], dbo.StudentDetMaster.[Year 4 Maths Set], dbo.PGJSProfile.[Verbal Reasoning Year 5], dbo.PGJSProfile.[Year 5 English Autumn], dbo.PGJSProfile.[Year 5 English Year Group Average], dbo.PGJSProfile.[Year 5 English Summer], dbo.PGJSProfile.[Year 5 English Year Group Average Score], dbo.PGJSProfile.[Year 5 Maths Autumn], dbo.PGJSProfile.[Year 5 Maths Year Group Average], dbo.PGJSProfile.[Year 5 Maths Summer], dbo.PGJSProfile.[Year 5 Maths Year Group Average Score], dbo.StudentDetMaster.[Year 5 Maths Set], dbo.PGJSProfile.[Verbal Reasoning Year 6], dbo.PGJSProfile.[Year 6 English Autumn], dbo.PGJSProfile.[Year 6 English Year Group Average], dbo.PGJSProfile.[Year 6 English Year Group Average Score], dbo.PGJSProfile.[Year 6 Maths Autumn], dbo.StudentDetMaster.[Year 6 Maths Set], dbo.StudentDetMaster.[JS Baseline], dbo.PGJSProfile.[Year 6 English Mock], dbo.PGJSProfile.[Year 6 Maths Mock], dbo.PGJSProfile.[Year 6 English Mock Year Group Average], dbo.PGJSProfile.[Year 6 Maths Mock Year Group Average] FROM dbo.PGJSProfile LEFT OUTER JOIN dbo.StudentDetMaster ON dbo.PGJSProfile.SetId = dbo.StudentDetMaster.SetId AND dbo.PGJSProfile.StudentId = dbo.StudentDetMaster.StudentId ORDER BY dbo.StudentDetMaster.Name
Hi,I have a php application connected through odbc to a sqlserver database.When I try to execute select queries on a smalldatetime table field Ireceive this message:Warning: [22003][0][Microsoft][ODBC SQL Server Driver]Numeric value out ofrangeThe query is something like:select dateField from tableTha field is a normal smalldatetime one:10/17/2001 12:35:00 PMWhith Query Analyzer the query works fine.I'm using php mssql_query() and ODBTP as protocol to connect to the DBMSThanksLuigi--Message posted via http://www.sqlmonster.com
Hi, We are looking for a way to create the jdbc connection with Microsoft jdbc driver on a TomCat server ? Meaning the exact information to enter within the server.xml file or within the Tomcat web server administration tool - data source GUI.
I have some code that should execute multiple statements and then commit. I am using the Microsoft SQL Server 2005 JDBC Driver 1.1.
The statements only commit ifI close the connection to the SQL Server 2005 instance. I've looked at the example given at http://msdn2.microsoft.com/en-us/library/ms378931.aspx and it doesn't require closing the connection in order to commit.
Example:
public void doCall(Connection con) { try { /*Turn off AutoCommit.*/ con.setAutoCommit(false);
Several of our customers are getting a message intermitantly throughout the day where the connection is closed. The message being generated is [Microsoft][ODBC SQL Server Driver]Communication Link Failure.
These messages started in the last two weeks.
Thanks for any direction that can be thrown my way.
