Hi All,
When i try to create OLEDB or ADO.NET Connection in SSIS package i am getting
the following error.
"Retrieving the COM class factory for component with CLSID {C8B522D0-5CF3-11CE-ADE5-00AA0044773D} failed due to the following error: 80040154".
Any ideas from you all?
Please help me out. I would be very grateful.
Regards,
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
I have a package that is basically a container for using the Execute Package task to call many "child" packages. Each child package basically uses a data flow to move data from one database to another database on the same SQL Server. In the case of the child packages, I've used a combination of OLEDB and ADO.Net connections on the destinations. I ensured that all tasks and packages are set to TransactionOption.Supported and that the "parent" package has Required set at the package level. Anyway, the problem I'm finding is that one of the OLEDB connections is being blocked. When I look in SQL Server Activity Monitor I see that there are numerous ADO.Net connections that are on multiple processes (all belonging to these packages) even though when I check the Details, I can see that it has completed the final statement of the package the process belongs to. Thinking this may have been a multithreading issue, I changed MaximumConcurrentExecutables to 1 in all of the packages. Basically, even though all of the packages appear to have their own processes, when I look at the locks for the objects I'm working with, they all show up on the lowest process ID. My assumption here is that since the processID is associated with the first package, it is the "owner" of the transaction and even though the packages have their own processIDs, they show up on the first process because they're in the transaction. If I change the connection from OLEDB to ADO.Net, it resolves the issue and the blocking does not occur. So my question is, if I have an OLEDB connection manager and ADO.Net connection manager, can they share in the same transaction or are they in their own transactions?
View 1 Replies View RelatedHi All,
Another requirement has cropped up with regard to picking up connection settings for data sources from an external File.
My source and target are both in SQL Server. What i need is that if my source or target changes I should just change my external file and same should reflect in my package.
How can I accomplish it? Please suggest some solution.
Thanks in advance.
Regards,
Aman Anand
I deployed a package on mssql-server.
How can I save passwords for OLEDB connections of the package?
I want to launch the package from server agent according to the schedule, but I don't know how can I save passwords for connectons?
Thanks in advance.
Hi:
An SQL statement like the following is generated by the Data Source View Wizard when creating a DSV in an Integration Services project:
SELECT * from [informix].[tablename]
where 'tablename' is the name of the table I'm trying to include in the DSV. Informix doesn't use the square bracket syntax, so the server returns a syntax error.
Here is the background information. The data source is Informix IDS v10, and I'm using Informix Client SDK 2.90TC6R1. I created the Data Source using the IBM Informix OLE DB Provider, and sucessfully connected. The 'Select Tables and Views' page of the Data Source View wizard shows the tables in the database, from which I select one. When I click Finish on the 'Completing the Wizard' page a dialog box appears stating that a syntax error occurred.
How can I correct the SQL-statement generating behavior to produce a statement acceptable to the Informix server?
Thanks,
Mike
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.
Hi all,
In my office computer network system (LAN), my Windows XP Pro PC has SQL Server Express and VB 2005 Express installed and I was granted to have the Administrator priviledge to access SQL Server Express. I tried to create a SQLCLR project in my terminal PC.
1) I tried to set up a remote connection to SQL Server Express in the following way: SQL Server EXpress => SQL Server Surface Area Configuration. In the Surface Area Configuration for Service and Connection - local, I clicked on Remote Connection of Database Engine, SQLEXPRESS and I had a "dot" on "Local and remote connections" and "Using TCP/IP only". Then I clicked on "Apply" and "OK" buttons. Then I went to restart my database engine in SQL Server Management. When I went to check SQL Server 2005 Surface Area Configuration, I saw the arrow is pointing to "Service", not to "Remote Connections"!!!??? Is it right/normal? Please comment on this matter and tell me how I can have "Remote Connecton" on after I selected it.
2) After I restarted the database engine (I guess!!), I executed the following project code (copied from a book) in my VB 2005 Express:
//////////////--Form9.vb---/////////////////
mports System.Data.SqlClient
Imports System.Data
Public Class Form9
Dim cnn1 As New SqlConnection
Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Compute top-level project folder and use it as a prefix for
'the primary data file
Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin")
Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1)
Dim pdbfph As String = strPath & "northwnd.mdf"
Dim cst As String = "Data Source=.sqlexpress;" & _
"Integrated Security=SSPI;" & _
"AttachDBFileName=" & pdbfph
cnn1.ConnectionString = cst
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a command to create a table
Dim cmd1 As New SqlCommand
cmd1.CommandText = "CREATE TABLE FromExcel (" & _
"FirstName nvarchar(15), " & _
"LastName nvarchar(20), " & _
"PersonID int Not Null)"
cmd1.Connection = cnn1
'Invoke the command
Try
cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show("Command succeeded.", "Outcome", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Create a command to drop a table
Dim cmd1 As New SqlCommand
cmd1.CommandText = "DROP TABLE FromExcel"
cmd1.Connection = cnn1
'Invoke the command
Try
cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show("Command succeeded.", "Outcome", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Declare FromExcel Data Table and RowForExcel DataRow
Dim FromExcel As New DataTable
Dim RowForExcel As DataRow
FromExcel.Columns.Add("FirstName", GetType(SqlTypes.SqlString))
FromExcel.Columns.Add("LastName", GetType(SqlTypes.SqlString))
FromExcel.Columns.Add("PersonID", GetType(SqlTypes.SqlInt32))
'Create TextFieldParser for CSV file from spreadsheet
Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser
Dim strPath As String = _
Microsoft.VisualBasic.Left( _
My.Application.Info.DirectoryPath, _
InStr(My.Application.Info.DirectoryPath, "bin") - 1)
crd1 = My.Computer.FileSystem.OpenTextFieldParser _
(My.Computer.FileSystem.CombinePath(strPath, "Book1.csv"))
crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
crd1.Delimiters = New String() {","}
'Loop through rows of CSV file and populate
'RowForExcel DataRow for adding to FromExcel
'Rows collection
Dim currentRow As String()
Do Until crd1.EndOfData
Try
currentRow = crd1.ReadFields()
Dim currentField As String
Dim int1 As Integer = 1
RowForExcel = FromExcel.NewRow
For Each currentField In currentRow
Select Case int1
Case 1
RowForExcel("FirstName") = currentField
Case 2
RowForExcel("LastName") = currentField
Case 3
RowForExcel("PersonID") = CInt(currentField)
End Select
int1 += 1
Next
int1 = 1
FromExcel.Rows.Add(RowForExcel)
RowForExcel = FromExcel.NewRow
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
Loop
'Invoke the WriteToServer method fo the sqc1 SqlBulkCopy
'object to populate FromExcel table in the database with
'the FromExcel DataTable in the project
Try
cnn1.Open()
Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1)
sqc1.DestinationTableName = "dbo.FromExcel"
sqc1.WriteToServer(FromExcel)
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
'Read the FromExcel table and display results in
'a message box
Dim strQuery As String = "SELECT * " & _
"FROM dbo.FromExcel "
Dim str1 As String = ""
Dim cmd1 As New SqlCommand(strQuery, cnn1)
cnn1.Open()
Dim rdr1 As SqlDataReader
rdr1 = cmd1.ExecuteReader()
Try
While rdr1.Read()
str1 += rdr1.GetString(0) & ", " & _
rdr1.GetString(1) & ", " & _
rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf
End While
Finally
rdr1.Close()
cnn1.Close()
End Try
MessageBox.Show(str1, "FromExcel")
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////
I got the following error messages:
SecurityException was unhandled
Request for the permission of type 'System. Security. Permissions.FileIOPermission,mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'failed that is pointing to the code statement "Dim int1 As Integer = InStr (My.Application.Info.DirectoryPath, "bin")."
Troubleshooting tips:
Store application data in isolated storage.
When deploying an Office solution, check to make sure you have fulfilled all necessary requirments.
Use a certificate to obtain the required permission(s).
If an assembly implementing the custom security references other assemblies, add the referenced assemblies to the full trust assembly list.
Get general help for the exception.
I am a new Microsoft VS.NET Express user to do the SQL Server 2005 Express and VB 2005 Express programming by using the example of a tutorial book. Please help and tell me what is wrong in my SQLCLR sep-up and project coding and how to correct the problems.
Many Thanks in advance,
Scott Chang
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)
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
View 1 Replies View Related
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
I'am sure this is an old quesiton but I've spent the whole morning trying to find the answer
What are the string functions suported by Jet in OLEDB queries? In particular, I am desperately looking for a replace function to remove thousands separators from imported text file
Hi.
Somewhere around I find a message, which affirmed that for perfonmance it is better to use OleDb for SQL 6.5. Is this true?.
Then el SqlClient, only have perfomance in SQL 2000?
Hi,
Does anyone know a good OLEDB provider for Oracle 7 other than the one of Microsoft and Oracle.
Thank
Martin
Is anyone using OLEDB instead of ODBC? If so why did you choose to use OLEDB vs ODBC? Our application is written in VB and we have a developer that believes switching to OLEDB from ODBC would be beneficial to us. Please give any information that you have! Thanks.
View 1 Replies View RelatedHi,
I have a DTS package which is using OLEBD provider for Oracle, it was working fine. I tried to open this package and I got this error message "There was a problem retrieving the list of OLE DB Providers"
I tried to intall MDAC8.2, it didn't help, should I reinstall SQL Server?
Thanks!
can any one please give a technical defenition for OLEDB providers
View 1 Replies View RelatedI am working on a large ASP application(s) which uses SQL Server 2K on the back end, ADO 2.7 for Data Access. The current connection strings are setup to use older ODBC drivers. I wanted to change them to OLEDB for performance gains and better support in the future.
The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.
Has anyone else gone through this process?
Is there a comprehensive listing of those methods that are not compliant between these two drivers?
Any input would be appreciated....Thanks
Dear gurus,
My problem: I have developed an application based on sqloledb to access a SQLserver application.
One of the operations is to update certain column of a certain table. This works fine. But with a specific catalog (database) of a specific customer the update hangs for ever on the execution of that sql sentence. Size of the mdb file is around 1 Gb.
The funny thing is that if I execute the same sql sentence (same record and same catalog) from the Query Analyzer ( that I belive it uses odbc ) the operation is done.
The update sql sentence does an update on a non-indexed column with the criteria ( where ...) using a index column (non-clustered)
I have tried the sqlmaint to rebuild indexes and check integrity and no special error report is given.
Also another funny behaviour is that if I stop and I restart sqlserver, the update of that specific record fails, but it continues of the following records to be updated.
This is not executed inside a transaction. Candidate records to be updated are stored in memory (maximum 1000), and one by one are updated.
Does anyone know an specific tool to integrity or to monitor what is going on?
I have tried the profiler , what can I monitor to detect this lock?
Best regards,
Jose
I want to know the difference between SQL ODBC and SQL OLEDB.
View 1 Replies View RelatedWhat are ODBC and OLEDB
1)libraries or
2)standards
Hi,
I'm having two For Each Loops, which allow me to dynamically connect to a list of servers.
ForEach 1
ForEach2
DataFlow.
Now for ForEach2 i want to OLEDB Connection to RetainSameConnection, however i want it to Disconnect-Reconnect while looping in ForEach1.
How can i do that?
Can i write a script which will can access OLEDB connection object ??
Thanks
Sandeep.
Is there an OLEDB provider for SAS? Does anyone know where I could get one? Does it ship with the SAS installation? Thanks!
View 1 Replies View Relatedi'm use this code ,in SQL2005 std and ACCESS database, it work
but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()
Exception : OleDbException
0x80040E30L
DB_E_BADTYPENAME
Code Snippet
OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");
od.Open();
OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@billno,@checkouttime,@finalprice,@handle,@ischeckout,@memo,@paymode,@trick)", od);
og.Parameters.Add("@billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@trick", OleDbType.VarWChar).Value = "";
og.ExecuteScalar();
od.Close();
why the same code is not work? i'm find all MSDN ,but there is no answer
Who can help me,Thanks
I am pursuing a course on Data Warehousing & Data Mining as part of my Post-Graduation degree. I got an introduction to Microsoft's OLEDB for DM as part of the course, which got me interested for experiencing it hands-on.
I developed a traditional Database application for my college's library using the .NET 2.0 framework, which is running fine.
Now, I want to see the power of Data Mining in general & OLEDB for DM in particular, to help me mine information & patterns from the mostly transactional data generated by the library daily, like the rush hours, the average issue duration for a book, books that are issued more frequently, books that are issued to students from multiple streams etc. etc.
Now, I have heard MSOLAP provider, ADOMD data provider, SQL Server Analytical Services, OLEDB for DM libraries etc. are required for Data Mining.
Now, what exactly is each of these tools? Are they independent servers for DW & DM like SQL Server for DBs, or they are libraries for extending SQL Server itself?
Moreover, are separate libraries required for OLAP & Mining?
I have SQL Server Express Edition. I believe that they are extensions for SQL Server. So, will they work with Express Edition? If not, are their Express Editions available (like OLEDB for DM Express!!!!) that are compatible with SQL Server Express.
Thanx in advance if you take time to answer so many questions...
I want to use SQL 2005 to import data from Visual Foxpro 6.0 free table / database into SQL 2005. Whethter I should install the latest Microsoft OLE DB Provider for Visual FoxPro 9.0 SP1 or previous mdac ???
Please advice and many thanks.
Hi
I definite a source OLEDB MS Access and my fields of the type €œText€? are seen in type of field DT_WSTR (Unicode) instead of DT_STR.
I do not include/understand why? and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution?
thank for your solution
I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.
Now I want to use bookmarks. I added the following to the accessor class:
1. CBookmark<4> m_bookmark; // member variable
2. pPropSet->AddProperty(DBPROP_IRowsetLocate, true); // rowset property
3. BOOKMARK_ENTRY(m_bookmark) // column map entry
Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.
When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.
The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.
My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0
Any ideas?
I cannot find any OLE DB reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.
The docs have a sample in C#, but nothing for OLD DB in C++.
Does anyone know how to do this?
hi,
can i use the acquireconnection method to an oledb for oracle connection manager?
I use oledb to insert the data to respective tables..
then i have a look up where i check for unique field of the data i inserted which shows up with an error No matter what i do...
I am porting a VB.NET application using Access as its backend to use SQL Express as its new backend.
The application uses .NET 2.0 OleDbProvider to connect to the database.
The porting of the database itself to Sql Express is nearly complete, now I need to modify the application.
So, does it really make sense to modify it to use Native Sql Client instead of OleDb in terms of effort & benefits???
Moreover, if I continue using OleDb, would I need to make such small changes to embedded Sql statements like using single quotes around strings or dates instead of double quotes or pound symbols that are currently being used for String or date data respectively???
Where can I get "sybase ASE oledb driver" ? I tried to get the source data from Sybase using SSIS. But I don't see an option for "sybase OLEDB driver " under the connection manager. I tried to google and went to sybase.com but still couldn't find it. could someone help?