OLEDB Connection Error With (win 64 Bit Server)
Jun 26, 2006
Hi
Im trying to connecting from SQL 2005 (win 64bit server) to a Oracle database via OLEDB but get the following message. The OraOLEDB.Oracle.1 provider is not registered on the local machine.
If we try ODBC we get the following message "error in initializing provider. Attemt to load Oracle client libraries threw BadImageFormatException.This problem will occur when running in 64bit mode whit the 32 bit Oracle Client components installed."
It should be the 64 bit client that is installed.
In the test environment (32 bit win) both works.
Pleas help
Rickard
View 3 Replies
ADVERTISEMENT
Mar 25, 2008
Hi List
Im trying to set up an implementation of Project Real --it works like this-
Create two system environment variables called REAL_Root_Dir and
REAL_Configuration with the values given below. Click on
Start -> Control Panel -> System. Go to the Advanced Panel, click Environment Variables button, then New in the System variables box.
If the Project REAL files were installed at C:Microsoft Project REAL, then the variable values will be:
Variable Name: REAL_Root_Dir
Variable Value: C:Microsoft Project REALETL
Variable Name: REAL_Configuration
Variable Value: %REAL_Root_Dir%REAL_Config.dtsconfig
The package OLEDB connections work like this
First read enviroment variable to get location of config file
Next read Config File to get connection string for Config Database
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[SQL - Configuration].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=(local);Initial Catalog=DataWarehouseABC;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Next read Config database to get connection strings for Source and Destination databases
Destination database is called "DataWarehouseABC"
Source database is called "SnapshotABC"
the Source database OLEDB connection works 100%
however the destination OLDB connection we get this error below
PS--Both source and destination databases are on the same development machine , however both databases are restored bak files from another production machine
Error 1 Error loading LoadGroup_Daily.dtsx: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'xxxxxx'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "DataWarehouseABC" requested by the login. The login failed.".
Any ideas on how one OLEDB Connection in this package can get this corruption
thanks in advance
Dave
View 5 Replies
View Related
Feb 9, 2006
I have an SSIS package which takes input from Flat file and transfer the data to SQL Server using OLEDB Destination Data Flow Item. The OLEDB Connection Manager used for the destination is configured to use SQL Server Authentication for the user €˜sa€™. The package works fine without enabling the configurations. But when I enable package configuration and save the configuration of the various connection managers in an XML configuration file and then run the package it gives the following error in the validation phase:
[OLE DB Destination [21]] Error: The AcquireConnection method call to the connection manager "<Connection Manager Name>" failed with error code 0xC0202009.
And after the validation phase is complete the following error message is given for the package execution:
[Connection manager "<Connection Manager Name>"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client€? Hresult: 0x80040E4D Description: "Login failed for user 'sa'."
Has anyone else run into this?
I am running
SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1.
Any suggestions would be welcome.
TIA,
Robinson
View 35 Replies
View Related
Jan 25, 2007
howdy,
i have a sqlserver2005 ssis package which connects to a mysql webserver to upload data. i am connecting via mysql oledb connection and am able to test connect ok through the oledb connection manager. However in my ole db destination editor i get an 0x80040E21 error when trying to preview a table . The full error is :
Error at Data Flow Task [Destination - Query [70]]: An OLE DB error has occurred. Error code: 0x80040E21.
Error at Data Flow Task [Destination - Query [70]]: Opening a rowset for "INFORMATION_SCHEMA.address_book" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
I can see all the tables in the drop down box in connection manager. This has been driving me nuts for the last 3 days! I have tried changing some of the security connection settings to no avail, (although i am not sure on what these do).I have also tried setting up as an odbc connection using script components but had memory errors after copying several hundred records and the package just hangs. I can go into this in more detail however i dont think they are related issues and i would prefer oledb as i dont have to script my data transfer.
any help much much appreciated.
thanks
View 3 Replies
View Related
Feb 28, 2008
Hi All:
I have a SSIS which have a main connection to a SQL Server 2005 DB, and also another connection to a Oracle database, and fetch data from the Oracle db.
I have the 2 connection managers, and everything runs fine when I test it in dev studio.
However, once I imported it to the DB, and run it on command line, I get the "Class not registered" error.
Log file as follows:
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 1:23:53 PM
Info: 2008-02-28 13:23:53.91
Code: 0x40016040
Source: CRDA_D_GIM_CONN_TEST
Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_SOURCE";".
End Info
Info: 2008-02-28 13:23:54.03
Code: 0x40016040
Source: CRDA_D_GIM_CONN_TEST
Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_USER";".
End Info
Info: 2008-02-28 13:23:54.05
Code: 0x40016040
Source: CRDA_D_GIM_CONN_TEST
Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_PWD";".
End Info
Error: 2008-02-28 13:23:54.55
Code: 0xC0202009
Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-02-28 13:23:54.56
Code: 0xC00291EC
Source: Execute SQL Task Execute SQL Task
Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Warning: 2008-02-28 13:23:54.58
Code: 0x80019002
Source: CRDA_D_GIM_CONN_TEST
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 1:23:53 PM
Finished: 1:23:54 PM
Elapsed: 1.547 seconds
I DID TRIED MY BEST to search before posting, and most of them are having problem with 32 bit and 64 bit executions.
However, in my case, both are 32 bit (as shown in the log file).
In the above test, I'm setting DoNotSaveSensitiveData, and then fetch it with package configuration.
To make sure the package configuration is not creating pblm for me, I also tried to hardcode everything in the package, and then encrypt the whole ssis with password, and run the package with the /DECRYPT option, so that everything is already intact.
But I still get the same error. Log file as follows.
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /De "painful" /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:24:25 PM
Error: 2008-02-28 14:24:27.11
Code: 0xC0202009
Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-02-28 14:24:27.14
Code: 0xC00291EC
Source: Execute SQL Task Execute SQL Task
Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection.
End Error
Warning: 2008-02-28 14:24:27.14
Code: 0x80019002
Source: CRDA_D_GIM_CONN_TEST
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:24:25 PM
Finished: 2:24:27 PM
Elapsed: 1.422 seconds
Can anyone suggest what is missing ?? or what can be done ??
Any pointers are greatly appreciated.
Thanks,
- Henry.
View 18 Replies
View Related
Sep 3, 2015
I am getting the following error when trying to EDIT a OLEDB Connection Manager. I am using VS2010. Im not coming up with any answers to this problem. I just reinstalled SQL SERVER CLIENT TOOLS 2012 That includes the SQL DATA TOOLS/VS 2010 But I still receive this error.
Error:Unable to find the requested .Net Framework Data Provider. It may not be installed. (System.Data)
View 3 Replies
View Related
Apr 16, 2008
An application vendor is telling me that their product will not run with SQL Server 2000 64-bit. Here is the layout:
* Dedicated 64-bit machine running Windows Server 2003 Enterprise, 64-bit.
* Separate application server running Windows Server 2003 - SBS.
Application server uses DCOM and OLEDB to obtain data from SQL Server.
My question: Is there any way for the application server to know if the data supplier is running 32-bit or 64-bit?
If so, then perhaps the application vendor is right.
If not, then they are just trying to put me off and avoid fixing a problem. In this case, I need an authoritative statement as ammunition to take back to their support department.
.... Warren
View 11 Replies
View Related
Jan 23, 2007
When I configure and test and OLEDB connection SQL Server 2005 keeps stopping with an error message as shown below. The problem seems to happen when I open the connection for ediing in Data Flow Window. There is a consistent pattern of errors (in there Event Log) relating to the Evnet Log prior to this error occurring.
Any idea what the problem is?
I have to say that my experience with SSIS has not been very encouraging so far.
Event Type: Error
Event Source: MSSQL$SQL2005
Event Category: (2)
Event ID: 17311
Date: 23/01/2007
Time: 2:06:41 PM
Description:
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 9f 43 00 00 10 00 00 00 ŸC......
0008: 14 00 00 00 53 00 59 00 ....S.Y.
0010: 44 00 2d 00 4c 00 2d 00 D.-.L.-.
0018: 30 00 31 00 33 00 32 00 0.1.3.2.
0020: 36 00 5c 00 53 00 51 00 6..S.Q.
0028: 4c 00 32 00 30 00 30 00 L.2.0.0.
0030: 35 00 00 00 11 00 00 00 5.......
0038: 45 00 78 00 61 00 6d 00 E.x.a.m.
0040: 70 00 6c 00 65 00 48 00 p.l.e.H.
0048: 69 00 73 00 74 00 6f 00 i.s.t.o.
0050: 72 00 69 00 61 00 6e 00 r.i.a.n.
0058: 00 00 ..
View 1 Replies
View Related
May 28, 2008
hello,
I have an application wrote in Power Builder 11 and use OLEDB connection for SQL SERVER 2005 DB.
it works excellent till multiple users access the application. 2 seconds passes and locks appears and blocks all the DB!
I realy don't know what is the reason. In past I used SQL Native driver and it works well with the same application!!! (other reasons caused me to change the connection. not this).
Does someone can help me????????
View 3 Replies
View Related
Feb 6, 2008
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel data source will fail with an oledb connection error.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
Thanks,
John T
View 3 Replies
View Related
Oct 10, 2006
Hi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
View 5 Replies
View Related
May 3, 2008
Hi,I need some help. I am getting this error after I complete the asp.net register control and click on the continue button. It crashed when it tries to get it calls this Profile property((string)(this.GetPropertyValue("Address1")));When I look at the stack, it is coming from my ProfileWrapper class which adds user address, city, etc.. from a class which inherits fromSystem.Web.Profile.ProfileBase. From the stack, it is calling the System.Web.Profile and crashed when it tries to open a connection atSystem.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject). I already migrated all aspnet_user, aspnet_roles, etc.. and don't get any connection errors. It is only when I try to get the profile data. This works on my pc, but throws an error on my lunarpage website.Any help is greatly appreciated.Thanks,AJAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)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: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:Line 100: public virtual string Address2 {Line 101: get {Line 102: return ((string)(this.GetPropertyValue("Address2")));Line 103: }Line 104: set {Source File: c:windowsMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 021d50639a6858cApp_Code.54nvluyo.1.cs Line: 102Stack Trace:[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +435 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +782 System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428 System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +404 System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117 System.Configuration.SettingsBase.get_Item(String propertyName) +89 System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36 System.Web.Profile.ProfileBase.get_Item(String propertyName) +68 System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4 ProfileCommon.get_Address2() in c:windowsMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 021d50639a6858cApp_Code.54nvluyo.1.cs:102 ProfileWrapper..ctor() in d:inetpubvhostsjavcentral.comhttpdocsApp_CodeProfileWrapper.cs:242 ProfileDataSource.GetData() in d:inetpubvhostsjavcentral.comhttpdocsApp_CodeProfileDataSource.cs:17[TargetInvocationException: Exception has been thrown by the target of an invocation.] System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0 System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +308 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29 System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +480 System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1960 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.FormView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.FormView.EnsureDataBound() +163 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.FindControl(String id, Int32 pathOffset) +21 System.Web.UI.Control.FindControl(String id) +9 CustomerDetailsEdit.OnPreRender(EventArgs e) in d:inetpubvhostsjavcentral.comhttpdocsUserControlsCustomerDetailsEdit.ascx.cs:60 System.Web.UI.Control.PreRenderRecursiveInternal() +86 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
View 1 Replies
View Related
Aug 9, 2006
Is there anyone who tried to use a connection from connection manager to create a new connection in a script task? Including the password?
Now i passed the connection to the script task and called it in the vb script but then the password is not passed into the connect string.
Im searching for an example that works with passing the password in the connectstring?
Any help will be greatfull.
View 1 Replies
View Related
Jan 8, 2004
Hi,
I am using WIN XP, SQL Server 2000 database and vb, asp application running on MTS. I have a transaction where it will process 500 items and write to the tables in database. When opening the connection I tried to cache the connection and made use of it when ever the code is called. Here is how I did it.
Public Function GetCachedADOConn(ByVal eConnect As DBConnectVar, Optional ByVal blnUseShapeRS As Boolean = False) _
As ADODB.Connection
Dim cnnADO As ADODB.Connection
On Error GoTo EH
If m_CachedConnection Is Nothing Then
Set cnnADO = New ADODB.Connection
' Check if we need to load the cache first...
If Len(mstrConnect(eConnect - 1)) = 0 Then Call CacheOneConnectionSet(eConnect)
' Now just return the cached data
With cnnADO
If blnUseShapeRS Then
.ConnectionString = "Provider=MSDataShape;Data " & mstrConnect(eConnect - 1)
Else
.ConnectionString = mstrConnect(eConnect - 1)
End If
.CommandTimeout = mintCommandTimeout(eConnect - 1)
.ConnectionTimeout = mintConnectTimeout(eConnect - 1)
.CursorLocation = adUseClient
.Open
End With
Set m_CachedConnection = cnnADO
End If
Set GetCachedADOConn = m_CachedConnection
'Set cnnADO = Nothing
Exit Function
Here the main part is making use of m_CachedConnection as a public variable declared as adodb.connection.
When I run the application it throws me an error
Unable to establish connection(Micorsoft OLEDB Provider for SQL Server , New transaction cannot enlist in the specified transaction coordinator.)
Can some one help me How I can address the above issue.
Thank you,
Nagesh.
View 5 Replies
View Related
Mar 8, 2004
Dear all,
Still i am having problem with connecting sql server with ASP (Intranet).
Following is the code which i am using for the connection now,
Set conn=Server.CreateObject("ADODB.Connection")
conn.open "DSN=aspfirst;Uid=;PWD=;"
The error mesage , generated by this code is attached with this mail. Please check the attachment and hel me in the same
Graceson
View 2 Replies
View Related
May 5, 2008
Hello all,
I want to create a table in MS-Access. So I have written the following query.
Code Snippet
select * from Openquery(KKACC, 'select * Into Temp from Table select 1 as col1')
But I am getting the following error.
Code Snippet
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC" returned message "Syntax error in FROM clause.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * Into Temp from Table select 1 as col1" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC".
How can I resolve this error. Does any one have any work arround for this..?
Thanks
-- Krishna
View 3 Replies
View Related
May 9, 2008
Hi all: I am getting the following error when I try to review the deployed reports on localhost.
"An error has occurred during report processing.
An attempt has been made to use a data extension 'OLEDB' that is not registered for this report server."
I checked the report manager, and for "connection type", there is a warning message as shown below:
"The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly."
I will appreciate any help in this regard.
Thanks
Reez
View 1 Replies
View Related
Dec 25, 2007
Hi,
I have been trying to export data from a Microsoft Excel 2007, Tester.xlsx to a SQL Server 2005 table as :
SELECT * INTO CUSTMAS FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:Tester.xls;Extended Properties=Excel 8.0')...[Providers$]
I have an error:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)"
View 9 Replies
View Related
Oct 24, 2007
After installing sql2005 sp2 a simple select query to a linked server reports the following error message:
Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.
Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.
The linked server is a Visual FoxPro database.
After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.
View 3 Replies
View Related
Aug 9, 2007
My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.
If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()
Code 1
Dim sqlAdapter As New SqlDataAdapter
Dim dataRow As Data.DataRow
Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)
sqlAdapter.Fill(ds)
Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.
Code 2
Dim oledbAdapter As New OleDb.OleDbDataAdapter
Dim dataTable As DataTable
oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)
Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb
It works all right when I use an OLEDB Connection Manager with the second code sample.
Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?
If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.
View 3 Replies
View Related
Oct 16, 2006
Are there compelling reasons to use one versus the other? I am completely redeveloping all of our site from .ASP, VBScript and Access, to ASP.NET, VB.NET and SQL Server 2005 Express. I have a lot of code in ADO and OLEDB already, and would like to make the change to the System.Data.SQLClient classes if I should.
Thanks, Tom
View 1 Replies
View Related
Jan 5, 2008
I have tried to connect ce database by using c++ and oledb, and failed in every turn, i dont know what i did wrong...
I created a new database by using create a new database sample in ssce bol, but i couldnt connect that database, it says invalid class string or authentication failed...
here is a simple example...
HRESULT hr;
CDataSource db;
hr = db.Open( "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=Newdatabase.sdf;Password='db_pwd'" );
if (FAILED(hr))
{
return;
}
and also, afx oledb headers causing lots of redefinition warnings, if i include both ssce headers and afxole headers...
and also oledb example in ssce bol download is missing, there is no such like example...
can you provide me simple example to connect any ce database?
Installed programs
visual c++ 9.0
sql server ce books online
sql server ce 3.5 runtime
sql server ce 3.5 server tools
windows xp sp2
thanks in advance
View 1 Replies
View Related
Jan 7, 2008
Hi there,
I know this has been a topic of discussion before and I have read and tried the suggestions to no avail. I have a few .dbf files to import into my sql database and I know that one needs to make a oledb connection to these files.
I am using the Jet provider and have tried setting the extended properties to all of the DBASE versions and still cannot get it to connect. These files' names are shorter than 8 chars so that issue is no problem in this case.
I can actually get these files to show in a Microsoft Access 2008 database when I import it from there so I know that I must be missing something in my SSIS project.
After doing some reading on this topic, it would seem that this shouldn't be such a schlepp but, here I am
Maybe I need to install some driver or somethin?
Regards
Mike
View 6 Replies
View Related
Jul 23, 2005
I need to conect my software from a remote client to a server, mystandard connection:Provider=SQLOLEDB.1;Persist Security Info=False;UserID=sa;Password=;Initial Catalog=MyDb;DataSource=192.168.0.100;Network=DBMSSOCN;andProvider=SQLOLEDB.1;Persist Security Info=False;UserID=sa;Password=;Initial Catalog=MyDb;DataSource=192.168.0.100;Network=DBNMPNTW;make a error connection,i have tryed with ODBC connection and its OK only in Named Piped.Can any one help me write the right connection string?thanksrm
View 2 Replies
View Related
Jan 15, 2008
Hi
I am using OLEDB Connection in script component. I get a login failure when I run the package. I use SQL Server authentication. Do you have to use windows authentication in script component? Thanks
View 6 Replies
View Related
Sep 12, 2007
Hello world.
I have a big problem.
Im writing an application in Sql 2005 Etl.
This application use 2 kind of ole db connection. One towards a Sql Server, and the other towards As400
to use ole db towords As400 is necessary to save user and password to connect.
There is a flag to save password.........and obviously a checked it. !!!!!
The problem is:
If I stay inside Visual studio and i use this application.......all is correct.
If I use my application in sql like a job.........loose password information and crash immediatly
someone can help me ?????????
thanks all in advance.
Alberto.
View 5 Replies
View Related
Mar 21, 2006
Hello,
I'm trying to connect to a DB2 database via SSIS and I'm getting some problems:
I'm creating a new OLE DB Connection Manager and I'm getting two distinct errors:
1) When I try to use "IBM OLEDB Provider for DB2 Servers" I can create the connection manager and the connection is tested successfully.
But when I try to use the connection in OLE DB Source when I will list the tables I get this error:
Could not retrieve the table information for the connection manager 'MyDataSource'.
truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004
ADDITIONAL INFORMATION:
truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004 (IBM OLE DB Provider for DB2 Servers)
2) When I try to use "Microsoft OLE DB Provider for DB2" when I try to test the connection I get this error:
Test connection failed because of an error in initializing provider. The parameter is incorrect.
Anyone had these problems ?
Thanks,
Guber
View 5 Replies
View Related
Apr 12, 2007
Hi, I had an old web application created during asp.net 1.1 and it have a connection problem with the sql server 2005 when it is mirgrated to a new webserver with dotnet framework 2.0 platform. I have enabled the remote access(TCP/IP and named pipes) in sql server 2005, did all the neccessary things, check whether the TCP/IP is enabled, named pipe is enabled... I created another web application using VS 2005. The database connection works perfectly well.This are the connectionString from the old web application.<appSettings> <add key="ConnectionString" value="Server=127.0.0.1;Database=somedb;User id=user; Password=somepassword; Trusted_Connection=False; POOLING=FALSE"/></appSettings> Thankyou in advance!
View 4 Replies
View Related
Jan 30, 2007
I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables:
DataSource Name, Name(for a WHERE clause), user and password.
For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions:
1. How do I set the connection information for the source connections? Do I just set the properties for the connection manager and then it sets for all the sources? What property do I set--do I need a script task?
2. How do I use the values from the "Name" column in a WHERE clause in each data flow?
Thanks!
Kayda
View 4 Replies
View Related
Aug 9, 2007
Hi,
I am trying to Create an OLEDB Connection from SSIS to Inforrmix without any success. Would someone be able to help.We are running Informix version 9.4. We were able to create an OLEDB connection with SQL server 2000 and DTS. But, when trying to create an OLEDB connection using SQL server 2005 and SSIS, I am getting an error message. The connection is created successfully but cannot access databases and tables on informix. The error message when trying to load the Informix tables:
'Could not retrieve table information for the connection manager ....
'Ifxoledbc.e' failed with no error message available '
Your feedback is appreciated
Thank you,
Patrick.
View 1 Replies
View Related
May 15, 2007
Hi,
1. If I have millions of rows to be compared, then which cache type is prefereed for lookup, Partial or no caching?
2. If I have lookup connected to Oracle Oledb, cache type as partial and SqlCommandParam as following
select * from (SELECT ORDER_ID, OPER_KEY, STEP_KEY, SUM(OCCUR_COUNT) AS OCCUR_COUNT FROM SFWID_OPER_DESC_EXPLD GROUP BY ORDER_ID, OPER_KEY, STEP_KEY) refTable where refTable.ORDER_ID = ? and refTable.OPER_KEY = ? and refTable.STEP_KEY = ?
then it doesn't allow me to add the parameters from Advance tab of Lookup transformation edition, and raises following error
"Provider cannot derive parameter information and SetParametInfo has not been called"
what am I missing?
View 5 Replies
View Related
Jun 9, 2006
I have OLE DB Connections set up in my connection manager (Native OLE DBMicrosoft OLE DB Provider for SQL Server). I would like to reference and query these connections from a custom task, written in C#. I currently reference it as follows:
using System.Data.OleDb;
...................................
OleDbConnection connection = (OleDbConnection) connections["MyConnection"].AcquireConnection(null);
What may be obvious to some (though wasn't to me, as I am new at this), when I run the task, I get an error saying that I cannot make this cast. After perusing the boards, I understand that this is because I am not making a cast to the right connection type. Well, that is where I am lost. What connection type (and corresponding library) do I need to reference? I want to continue to use the "Native OLE DB..." connection.
Thanks!
View 3 Replies
View Related
Feb 1, 2007
I am trying to set the connection string in a connection manager at runtime. Here is what I have done:
1. Created a gv_DataSource, gv_Username and gv_Password
2. Created a ForEach Loop that reads DataSource, Username and password values from a variable (it is an For Each ADO loop Enumerator). The ADO recordset is read into by an Execute SQL task before the loop.
3. Mapped values from the recordset to variable in the ForEach loop's "Variable Mappings" page.
4. Used the variables in my Sybase OLEDB Connection Manager's "Expression" property, setting the "ConnectionString" property to:
"Data Source=" + @[User::gv_DataSource] + ";User ID=" + @[User::gv_Username] + " ;Password=" +
@[User::gv_Password] + ";Initial Catalog=blue;Provider=Sybase.ASEOLEDBProvider.2;Persist Security Info=True;"
5. I set the values in my database table for the connection-I set 2 connections for which I have Sybase OLEDB datasources setup.
When I run the package, I just get the first server's data twice, it doesn't set the second server's data during the second loop. I made sure the first one was working (i.e. the ConnectionString's property was being set by the data from the current variables) by setting the variables incorrectly in the variable properties page, and then running the package. So the first row of connection information is working, but the second loop around it doesn't seem to be working. I used a msgbox in a script task to show that the variables are mapping correctly in the loop, so it seems the second time around the connection information isn't taking from the variables.
What am I doing wrong here?
Thanks,
Kayda
View 1 Replies
View Related
