Unable To Retrieve Column Information When Using Ole Db Provider For Sybase
Feb 23, 2006
Hi All,
I am in process of transfering data from Sybase to Sql Server using SSIS 2005
have taken a Data Flow Task in Control Flow tab
In Data flow tab, I have taken one Ole DB Source and One OLe DB Destination
For the source, I am using Sybase Adaptive Server Anywhere Provider 8.0
For Destination, I am using Sql Server 2005 database
In Ole Db Source Editor ,
For OLe Db Connection Manager, I choose Sybase Connection
For Data access mode, I choose Table or View
For Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database)
When i click on preview button or Columns link, I get the following Error
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
I am trying to set up a data flow task. The source is "SQL Command" which is a stored procedure. The proc has a few temp tables that it outputs the final resultset from. When I hit preview in the ole db source editor, I see the right output. When I select the "Columns" tab on the right, the "Available External Column List" is empty. Why don't the column names appear? What is the work around to get the column mappings to work b/w source and destination in this scenario.
In DTS previously, you could "fool" the package by first compiling the stored procedure with hardcoded column names and dummy values, creating and saving the package and finally changing the procedure back to the actual output. As long as the columns remained the same, all would work. Thats not working for me in SSIS.
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated.
I've developed a package that is working well at development machine from VS 2013 for a sample flat file. Also, over development machine, I've deployed it to SSISDB catalogue and even from there also it is running well for the same file.When the same package is deployed to production server's SSISDB catalogue database, it throws following error while processing the same sample flat file, “Unable to retrieve column information from the flat file connection manager”
I'm trying to get data from as400 server to sql server, trough a job. The job executes a stored procedure that retrieves data from a view in an other database on the same server. That view gets the data from as400. Now, when i run the mentioned stored procedure in the query analyzer, everything works fine, but when the stored procedure is executed from the scheduled job, it fails each time giving the error
"Could not get the column information from the OLE DB provider 'IBMDA400'. [SQLSTATE 42000] (Error 7350). The step failed."
on random points during that process (by that i mean the lenght of time it runs)
does anyone know what the diffrence is between executing an sp in query analyzer or using a job, keeping in mind retrieving data from as400?
Hi, all.. I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
I am running a .Net Windows Service hourly which connects to Sybase Link Server thru SQL Server 2000. The service is a threaded application as it spawns 40 threads to connect to Sybase DB's using 'openquery' from SQL stored procs.
After it ran for hours (approx 30 Hrs), the when the service calls SP whcih has got openqeury to Sybase Linked Servers fails. It throws following exception,
"System.Data.SqlClient.SqlException: OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error. The provider ran out of memory."
The sybase provider is told to use Out of SQL Server process memory. When the sybase provider runs out of memory, I checked the Available free memory in the machine. It is still 4GB out of 8GB.
Please let me know the reason for the Provider running out of memory often if anybody has faced this issue before.
The version of the Sybase provider in the machine is Sybase.ASEOLEDBProvider 5.0.
I have a report and it retrieves information from an etl logging table which contains this info: etl_process_name, active_since_date, last_run_date.
When the packages are executing, the table cant be queried because it is in a transaction. Therefore the report keeps generating :s The thing I want is that: If I query that table (with the report to show the logging table information) and the packages are executing, I want to see the values of the table before the transaction.
What I tried: From sql management studio:
ALTER DATABASE MyDataBase SET ALLOW_SNAPSHOT_ISOLATION ON set transaction isolation level snapshot go SELECT etl_process, last_run, active_since FROM config.etl_settings
I did this while the packages were executing and it works but it won't work when I execute the same SELECT statement from the Dataset in the report. How is that possible? Does it have something to do with setting the database options for all users?
Additional info: I'm database owner The report solution has a shared datasource which refers to the same database
If I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?
Hi, I'm getting this error [Microsoft][ODBC SQL Server Driver][SQL Server]Could not retrieve replication information for table 735393739. Verify that the table has a primary key, and then rerun the Log Reader Agent.Please help me!
Hello, I've a problem with a software developed in C# with the framework 2.0. This is the error I receive : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.". If I try directly to restart the process, it works fine. Is there someone who can help me ? This is the process 1. In C# --> Call of a Query : select from the linked server (db in sql 2005) and insert into a table SQL 2005 2. In the C# --> using (TransactionScope scope = new TransactionScope()) and insert in a table in SQL 2005 which is link server Thank in advance.
Hello, I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables. The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively. Any help or scripts would be appreciated!
Hi, I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
AS INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY) SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update 'Get a reference to the Production table. Dim dtProduction As DataTable = DS.Tables("Production") Dim dtLineItem As DataTable = DS.Tables("LineItems") ' Create the SqlCommand to execute the stored procedure. Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection) Production.InsertCommand.CommandType = CommandType.StoredProcedure ' Add the parameter for the CategoryName. Specifying the ' ParameterDirection for an input parameter is not required. 'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName") ' Add the SqlParameter to retrieve the new identity value. ' Specify the ParameterDirection as Output. Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID") parameter.Direction = ParameterDirection.Output ' Create a new row with the same schema. Dim dr As DataRow = dtProduction.NewRow() 'you need the ID from this to insert into the Production DB ' Set the value of all the columns. dr("DateOut") = CDate(DateTimePicker1.Text) dr("DateRequired") = CDate(DateTimePicker2.Text) dr("VendorID") = CInt(vendorbox.SelectedValue) dr("HomeAddress") = txtApproved.Text.ToString dr("ApprovedBy") = txtPrepared.Text.ToString dr("TCAPO") = CInt(txtTCAPO.Text.Trim) dr("CommentID") = CommentID dr("TotalCost") = CDec(txtTotals.Text) dr("TotalQuantity") = CInt(txtQtyTotal.Text) ' Add to the Rows collection or table . dtProduction.Rows.Add(dr) 'Update the Production Table and then retrieve the ID created in this case Production.Update(dtProduction)
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _ System.Data.OleDb.OleDbSchemaGuid.Columns, _ New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
used enteprise manager to retrieve a group of data from one of our files. We have id set and need to bring these key fields back online for application to work. Help !!!..
we tried a demo software called apexsql to assist in the restore, however this does not update the files one by one..
However, everytime I connect I get the following error in my IE:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][SQL Native Client]Client unable to establish connection due to prelogin failure
/inc/OpenCnn.asp, line 15 (whch is Conn.open)
When I check the SQL server express log file, I find this one:
2006-07-16 20:20:07.53 Logon The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message: 0x6d. [CLIENT: <local machine>]
My sql native client configuration is: Force Protocol Encryption: No, Trust Server Certificate: yes.
What's possibly wrong with my script or sql server configuration?
I am trying to setup custom forms authentication in Reporting Services.
I have followed the UFAIRS sample. I have the site running, reporting services running, and I have a page where I can input my login information.
I can debug the code- and I get to this section of code:
Protected Overrides Function GetWebResponse(ByVal request As WebRequest) As WebResponse
Dim response As WebResponse = MyBase.GetWebResponse(request)
Dim cookieName As String = response.Headers("RSAuthenticationHeader")
' If the response contains an auth header, store the cookie
If Not (cookieName Is Nothing) Then
Utilities.CustomAuthCookieName = cookieName
Dim webResponse As HttpWebResponse = CType(response, HttpWebResponse)
Dim authCookie As Cookie = webResponse.Cookies(cookieName)
' If the auth cookie is null, throw an exception
If authCookie Is Nothing Then
Throw New Exception("Authorization ticket not received by LogonUser")
End If
' otherwise save it for this request
Me.AuthCookie = authCookie
' and send it to the client
Utilities.RelayCookieToClient(authCookie)
End If
Return response
Notice the line: Dim cookieName As String = response.Headers("RSAuthenticationHeader")
Basically the cookie name is null after that, and therefore it processes no further and I never get a cookie and I can never authenticate. Any thoughts as to what I am doing wrong?
Also, this is on a test box, so I don't have SSL capabilities. Unless there is a way to "emulate" SSL, I pretty much don't know if this works without SSL, or if this is causing the problem. We have environments that use SSL, but I do not have access to it at the moment- I will have to do additional work to get that up and running.
I receive this error "Setup was unable to load the master information file' when I lanch Control Panel, Add/Remove Programs, Add/Remove Windows Components.
Hi, I am using VS2005 and Sql Server 2000 (personal edition). I am able to connect with SqlClient Provider on Local Machine (when db server and VS2005 on same machine). But I try to connect SqlServer on different machine it is not allowing me to connect. If I use SqlOledb Provider it got connected. Please tell me how could I connect with DB server (Sql Server 2000) from remote machine.
I have been attempting to set up a linked server in SQL Server 2000 to point to an Oracle database (a very old Oracle database, v7!).
The linked server set up works fine. Then I created a database on the same SQL Server. Within that database I created a view which reads information from a view in the Oracle database (linked server). When I attempted to create this view in Enterprise Manager, I get the following error:
"The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."
And so I created the view in Query Analyzer. This worked perfectly, no errors, and I can now go into Enterprise Manager and call up the view, which happily goes off to the view in the Oracle linked server and pulls back the info. BUT, when I attempt to do the same thing through an application on a different machine (using OLE Db, and a UDL to connect) the same error as above appears.
Does anyone know what this error means, and where I went wrong? Any help greatly appreciated. My view onto the linked server looks like this:
"CREATE VIEW dbo.NLPG_VIEW AS SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')"
Many thanks in advance for your wisdom!
P.S I wondered whether I need to check if the distributed transaction co-ordinator is running but don't know how. Thanks,
I want to migrate DB from sybase to SQL server 2012 and i am using SSMA for it. Till yesterday it was working fine. Today when i try connecting sybase i am getting below error.
Unable to find specified provider.Error occurred while establishing connection to Sybase server. If you have 64-bit connectivity components installed on the machine, you can run 64-bit version of SSMA application whose shortcut can be found under the Programs menu. Or you can install the 32-bit connectivity libraries form Sybase product media or download it from Sybase web site.
I am getting following Error: Oracle linked server in SQL server 2005 to update tables in Oracle database. I have used OPENQuery to update tables in Oracle from a SQL Server 2005 Stored procedure.
If we run query from sql management studio for inserting or updating table of linked server then it runs fine. But if we use Begin distributed transaction then its gives error -
Server: Msg 7391, Level 16, State 1, Procedure sp_test, Line 105 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. We are using Sql server stored procedure to run the open query syntax for updating oracle interface table.
Hi,I'm trying to do retrieve some data from a table where the content isin Greek, however, thequery is not working. It's a very simple statement, but I'm missingsomething.Here is the table...if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REPORT_LOCALE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[REPORT_LOCALE]GOCREATE TABLE [dbo].[REPORT_LOCALE] ([XL_REPORT_ID] [int] NULL ,[TEXT_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[LOCALE] [int] NULL) ON [PRIMARY]GOThe first statment shows me a number of rows. I copied the content ofthe Text_Name column and pasteit into QA to form the second statement. However, the second statementreturns no data.SELECT * FROM Report_LocaleSELECT * FROM Report_Locale WHERE Text_Name = 'Λογ.Διαχ. – Τ?.-*?ουπ.-Διαφ.'Hopefully the Greek characters will display properly within this post,but the idea is basically to take the Greek text and build that into aquery. I can do the remainder later once I understand why this doesnot work as I expect. I realise my expectation is based on doingthings in English so I need to understand the differences. We've donethis for various other languages using other character sets, which iswhy I am puzzled.Any pointers ?ThanksRyan
In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.
CREATE proc SProc_DeleteRecord @TableName varchar(50), @DeleteCondition nVarchar(200) As Begin Begin Transaction Declare @DelString nvarchar(4000) set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition execute sp_executeSql @DelString RollBack Transaction --because i donot want to delete the record in any case If @@Error <> 0 Begin select '1' End Else Begin Declare @SQLString nvarchar(4000) set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition execute sp_executeSql @SQLString select '0' End
What query should I run? 1. To determine whether a column is a rowguid or not using C# .NET 1.1 2. To add/modify column information and be able to set/change: - Primary key - Column Name - Data Type - Length - Allow Null - Default value - Precision - Scale - Identity - Identity Seed - Identity Increment - Row guid
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have a Insert statement which i am using to insert data into a remote server from my local server. I have added the remote server as a linked server in my local server but when i try to execute the statement i am getting the above mentioned error. I am using Windows Enterprise Edition 2003 and SQL Server 2000.
I have also enabled the Allow InBound and Allow OutBound in the security configuration of My Computer properties but still i am facing the same problem. Kindly let me know how to resolve this issue.
I am able to work fine in my development environment, however when I deploy the application to a clean machine, I get the dreaded "Unable to find the requested .Net Framework Data Provider" error.
I have included the eight dlls required by the Sql Server 2005 CE engine (per the documentation) in the deployment project.
The error message that comes while I preview the source data:
Error:
"The Preview sample contains embedded text qualifiers. The flat file parser doesnot support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at runtime"
Is there any alternative to remove these text qualifiers from the file. Do we have any utility that can convert these files into text qualifier free CSV file?
I am currently working for the Sybase to Sql Server migration project and have been able to test migrate few tables using SSIS. After migration, i was doing some data comparision and could see the date formats are different between Sybase and Sql server. However, there are no issues with data like day in sybase becoming month in Sql server except the formats are different.
Do I need to act on this date formats? Not sure if this would cause any issues in front end application that will consume sql server date data.
