SQL Statement Causes Invalid Row Set Accessor/UNSUPPORTEDCONVERSION Error... What?
Mar 19, 2008
Hello. I hope someone can help me.
I have the following code, which creates an error. However, if I replacement the SQL statement with the one in comments, it works fine, so I feel like my connection is okay. It's pretty simple code, really. I'm not sure what could be wrong.
public DataTable GetNextConfession() {
DataTable dt = new DataTable();
dt.TableName = "XMLConfession";
// including this line causes the Fill command to bomb
string SqlString = "SELECT TOP (1) Confession FROM Confessions";
// the next line works just fine
//string SqlString = "SELECT TOP (1) Quote, Author FROM Quotes ORDER BY NEWID()";
// Create a connection and command object so we can interact with the database.
using (OleDbConnection cn = new OleDbConnection (ConnectionString)) {
using (OleDbCommand cmd = new OleDbCommand(SqlString, cn)) {
cmd.CommandType = CommandType.Text;
cn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill (dt); // Program actually bombs on this line
}
}
return dt;
}
Below is the error I'm getting. Does this ring any bells?
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: OleDbDataAdapter internal error: invalid row set accessor: Ordinal=1 Status=UNSUPPORTEDCONVERSION.
at System.Data.OleDb.RowBinding.CreateAccessor(IAccessor iaccessor, Int32 flags, ColumnBinding[] bindings)
at System.Data.OleDb.OleDbDataReader.CreateAccessors(Boolean allowMultipleAccessor)
at System.Data.OleDb.OleDbDataReader.BuildMetaInfo()
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Search.GetNextConfession() in f:Sourcedark humanitydh.webserviceApp_CodeSearch.cs:line 93
--- End of inner exception stack trace ---
I was trying to get my data from olb table to excel. but i got this error my dataflow is as follows: oledb source-> excel destination
i have got a excel file template at the destination all ready. with the column names+ the sheet all ready. but somehow it got stuck... anybody can help? thanks!
Here's my update statement:UPDATE Item1SET reviewloop = 1, currentreviewstate=5WHERE itemid in(SELECT itemid FROM Item2)The thing is: the table Item2 DOES NOT HAVE a field called itemid.So, I should receive an error, right? Not so.Instead, every singlerecord in Item1 was updated.Does anyone know why SQL Serverr does not trown an error???Thanks guys,-Silvio Souza
I am creating an SSIS package that takes data from a SQL Server 2005 table, adds some columns, programatically changes some values based on business requirements, and then writes the output to an Excel template which I've already prepared. Everything seems to work fine, but the package always errors out when immediately after it hits the Excel Destintation component, with the following errors:
Error: 0xC0202009 at Process Quarterly Data, Export to Excel [12621]: An OLE DB error has occurred. Error code: 0x80040E21. Error: 0xC0202025 at Process Quarterly Data, Export to Excel [12621]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Process Quarterly Data, DTS.Pipeline: component "Export to Excel" (12621) failed the pre-execute phase and returned error code 0xC0202025.
I have verified that the Excel destination file is writing the correct headers to the specified file. I thought that the issue might be that one of the dynamically created columns isn't matching the Excel file, so I manually checked each and every one of them (there are 248 columns, although a lot of them aren't really used - however, we are required to use the template provided , so must include fields in the specified order whether or not they have any data) and made sure that I selected the exact same datatype for each column. However, I still get the error and no rows are written to the Excel file.
Here is the code generated by the Excel Destination Manager:
Generated Code - Excel Destination (SSIS)
CREATE TABLE `xxx_LoadData` ( `PART NUMBER` NVARCHAR(255), `PART NAME` NVARCHAR(255), `PRICE TBD` INTEGER, `PRICE` MONEY, `UOI` NVARCHAR(4), `Items per UOI` INTEGER, `NSN` NVARCHAR(255), `OEM NAME` NVARCHAR(255), `OEM PN` NVARCHAR(200), `UPC` NVARCHAR(255), `DESCRIPTION` NVARCHAR(255), `EXPANDED DESCRIPTION` ntext, `CLASSIFICATION CODE` NVARCHAR(255), `DAYS ARO` INTEGER, `IMAGE DESCRIPTION` NVARCHAR(255), `IMAGE URL IF SELF HOSTED OR IMAGE NAME MANTECH HOSTED` NVARCHAR(255), `SHIPPING WEIGHT` REAL, `SHIPPING WEIGHT UNIT OF MEASURE` NVARCHAR(4), `SHIPPING LENGTH` REAL, `SHIPPING WIDTH` REAL, `SHIPPING HEIGHT` REAL, `SHIPPING UNIT OF MEASURE` NVARCHAR(4), `PRODUCT WEIGHT` REAL, `PRODUCT WEIGHT UNIT OF MEASURE` NVARCHAR(4), `PRODUCT LENGTH` REAL, `PRODUCT WIDTH` REAL, `PRODUCT HEIGHT` REAL, `PRODUCT UNIT OF MEASURE` NVARCHAR(4), `FEDERAL SUPPLY CODE` NVARCHAR(255), `ENAC CODE` NVARCHAR(255), `PACKAGE UNIT OF ISSUE` NVARCHAR(4), `PACKAGE UNITIP OF ISSUE` NVARCHAR(4), `PACKAGE PRICE` MONEY, `CERTIFIED NSN` NVARCHAR(255), `COG CODE` NVARCHAR(255), `HAZMAT` NVARCHAR(255), `UNSPSC` NVARCHAR(255), `SALE_START_DATE` DATETIME, `SALE_END_DATE` DATETIME, `PB1 Quantity` INTEGER, `PB1 Zone 1 Price` MONEY, `PB1 Zone 1 Sale Price` money, `PB1 Zone 2 Price` money, `PB1 Zone 2 Sale Price` money, `PB1 Zone 3 Price` money, `PB1 Zone 3 Sale Price` money, `PB1 Zone 4 Price` money, `PB1 Zone 4 Sale Price` money, `PB1 Zone 5 Price` money, `PB1 Zone 5 Sale Price` money, `PB1 Zone 6 Price` money, `PB1 Zone 6 Sale Price` money, `PB1 Zone 7 Price` money, `PB1 Zone 7 Sale Price` money, `PB1 Zone 8 Price` money, `PB1 Zone 8 Sale Price` money, `PB1 Zone 9 Price` money, `PB1 Zone 9 Sale Price` money, `PB1 Zone 10 Price` money, `PB1 Zone 10 Sale Price` money,
/* Repeated through PB10 Zone 10 - code not shown for brevity */
) Does anyone have any suggestions as to what I'm doing wrong? I'm making an attempt to set up a process for the company, instead of throwing something together; while that would be much quicker (I've spent pretty much the whole day working on this), lack of processes are a big detriment to our current operations.
Any help would be greatly appreciated - I'm not that familiar with SSIS or its nuances just yet.
I'm setting up an SqlDependency and things are starting to come together, but the following SELECT query results in a callback with type = Invalid and source = statement. However, I don't understand why that should be, as the select works fine "standalone":
mWatchQuery = New SqlClient.SqlCommand( "Select [pkLogId], [fkWho], [When], [WorkId], [What], [fkEventId] From dbo.tblLog", mDatabase.Connection)
mDependency = New SqlClient.SqlDependency(mWatchQuery)
I tried changing it using a table name alias L and L.[fkWho], L.[When] etc, but that fails too.
The property mDatabase.Connection returns the SqlConnection object for the object's database connection. Note I'm using the same connection every time - problem?
The tblLog DDL is as follows. Is it the use of a Text field?
I m trying to use the insert statement with in the function ! and i m getting this errror !
Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16 Invalid use of 'INSERT' within a function.
Please help me how to rectify it and how i can use the Insert statement with in the function !
Here is the code for the function.
create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as begin Declare @valCost integer Declare @TotService integer Declare @TotParts integer Declare @TotLabour integer Declare @TotTravel integer Declare @TotSubContract integer select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1) return (@valCost) end
Hello, I get the following error when I run my package interactively. From the logs written out by the driver, it appears that all is working well as far as connecting to the data source and pulling data. It seems as if this error occurs when the DataReader source tries to process the received data.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.Data.Odbc.OdbcException: ERROR [42000] XML parse error at 162:1338: not well-formed (invalid token) at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper) Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed the pre-execute phase and returned error code 0x80131937. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (691)" wrote 0 rows. Task failed: Data Flow Task SSIS package "MyPackage.dtsx" finished: Success.
I am not sure where to look next. Any help is much appreciated.
I have done DTS to transfer all the objects(entire database) from server1 to server2.When I do a select * on any user table in server2,it says invalid object name.Any idea?
I searched the archives but couldn't find anything on this yet...
I am using the wizard to grab selected data from 6.5 Server1 to insert into a table on 6.5 Server2, no transformations necessary.
I've tried it two ways (actually more, but two ways will demonstrate the problem). The query I run to grab the data is search through approx 6.5 million records in poorly indexed tables, on a slow machine. Takes approximately 45 minutes to run the query alone, and right around the same to run the DTS package.
The difference between the two queries in the two different packages (all else equal), is that the first uses a variable to calculate a date to filter the where clause. The second hard codes the date in the where clause. The second works, but the first runs about 40 minutes before returning a Transfer Failed error that reads "Invalid Pointer". No error number, nothing in the Books online about the error.
The queries are as follows: *************************************** QUERY 1 (calculates the date for 11:00 PM night before last)
select s.SerialNumber as iwSerialNumber, MAX(p.ReceiptTime) AS iwLastReceived, p.PurchaseOrderNumber as iwPO, s.Revision as iwBomRev, s.PartNumber as iwPartNumber From SLOCAZ s INNER JOIN PORDRZ p ON s.SerialNumber = p.SerialNumber Where p.ReceiptTime > @DateLastReceived And ( (p.PurchaseOrderNumber Like 'BD%') OR (p.PurchaseOrderNumber Like 'TP%') OR (p.PurchaseOrderNumber Like 'DM%') ) GROUP BY s.SerialNumber, p.PurchaseOrderNumber, s.Revision, s.PartNumber
*************************************** QUERY 2 (Hard codes the date)
select s.SerialNumber as iwSerialNumber, MAX(p.ReceiptTime) AS iwLastReceived, p.PurchaseOrderNumber as iwPO, s.Revision as iwBomRev, s.PartNumber as iwPartNumber From SLOCAZ s INNER JOIN PORDRZ p ON s.SerialNumber = p.SerialNumber Where p.ReceiptTime > 'Aug 29 1999 11:00PM' And ( (p.PurchaseOrderNumber Like 'BD%') OR (p.PurchaseOrderNumber Like 'TP%') OR (p.PurchaseOrderNumber Like 'DM%') ) GROUP BY s.SerialNumber, p.PurchaseOrderNumber, s.Revision, s.PartNumber
***************************** I should also note that I thought maybe the hard coded date being used as a string was the difference, so I tried the following (which just converts the date back into a char variable and uses the char variable in the where clause)
select s.SerialNumber as iwSerialNumber, MAX(p.ReceiptTime) AS iwLastReceived, p.PurchaseOrderNumber as iwPO, s.Revision as iwBomRev, s.PartNumber as iwPartNumber From SLOCAZ s INNER JOIN PORDRZ p ON s.SerialNumber = p.SerialNumber Where p.ReceiptTime > @CharLastReceived And ( (p.PurchaseOrderNumber Like 'BD%') OR (p.PurchaseOrderNumber Like 'TP%') OR (p.PurchaseOrderNumber Like 'DM%') ) GROUP BY s.SerialNumber, p.PurchaseOrderNumber, s.Revision, s.PartNumber
************************************ This still didn't work...
Any Ideas on what is happening and/or how to fix it??? Amy
I am running SQL 7 Svc Pack 2 on TN 4.0 Svc Pack 6a.
I am getting the following error when opening up a DTS package on my SQL Server from EM on my workstation. If I open it up on the server by using pcanywhere the package opens fine. Ther error is a dialogue box stating:
Package Error
Error Source:Microsoft Data Transformation Services (DTS) Package Error Description: The Parameter is incorrect.
Has anyone seen this. I get nothing in the event log or SQL logs. I can't seem to figure this one out.
We are in the process of installing a new Server which has SQL 2000 on it. I transferred everything over from a SQL 7.0 server. I am in the process of updating my DTS packages. I have to go in and change the connections. THe first package worked fine. I've gone into about 5 others and am getting the following error: Error Description: unspecified error [IBM] Client Access Express ODBC Driver (32 Bit) Invalid Argument Value
I can change the server connection fine. But if I click on the tranformation and properties thats when I get this error.
This is my syntax, I have removed then added back line by line by line and determined it is the insert of the variable into the table that skews.
Code: Create Table #Table1 (ID Int Identity, p nvarchar(20)) Create Table #Table2 (date datetime, salesID int, p varchar(20)) Insert into #Table1 Values ('ZeroWireless') Declare @Str nvarchar(4000), @p nvarchar(20) Select @p = p From #Table1
Im trying to create a new table from a union all statement, im pretty sure this is the way you do it:
insert into Test_table select * from Tb1 union all select * from Tb2
However im receiving a invalid object name error. Doing a search on this forum i read it might be to do with not having tb1 or tb2 in the same database, but both select statements and the union work, just not the insert or creating a new table from the results. Any suggestions will be greatful. Champinco
I'm trying to create a report model using a set of tables from two different servers. Creating the Data Sources and the Data Source View is no problem, however, while trying to create a Report model I run into an error that says,
An error occurred while executing a command. Message: Invalid object name 'dbo.table_name. Command: SELECT COUNT(*) FROM [dbo].[table_name] t
I've checked the schemas for both these tables and they are correct. Why is this error occuring? Any suggestions would be appreciated!
i'm working on an application using vs 2005, sql server2000, with c# asp.net i can access many tables in my db that the dbo is the dbowner for them, but when i access few tables that the owner for them is dswebwork, i recieved an error says, invalid object name tbluser...which tbluser is table name...this is the error message in details..... Invalid object name 'tblUsers'. 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: Invalid object name 'tblUsers'.Source Error:
Line 57: string passWord = txtPassword.Text; Line 58: Line 59: Users users = new Users(Constants.DB_CONNECTION, Line 60: userName, passWord); Line 61: Source File: e:web worksWebworksDSCWebWorksLoginMaster.master.cs Line: 59 Stack Trace:
When I try to amend a stored procedure, I get Error 208: invalid object name when amending a stored procedureAny idea how I can amend the stored procedure?thanks
Hi, I will start step by step:1. a new web site with VS 2005.2. I added a sqldatasouce and connect with AdventureWorks sample database ,which comes with sql server 2005 developer edition, selected by drop-down list. [ server name:(local) ]3. Test connection. It is OK.4. Saved as 'AdventureWorksConnectionString'.5. Some columns are selected in the 'product' table.6. At the end while testing query with 'test query' button it gives: "There was an error executing the query. Please check the syntax of the command and if present, the types and values of parameters and ensure the are correct. Invalid object name 'Product'. "7. However when I choice NorthWind database sample I installed externally, there is no problem.Moreover, when I choice AWBuildVersion table in the AdventureWorks, and it's columns, there is also no problem.8. I compared NorthWind and AdventureWorks security properties in the SQL server managment studio, but can't find any differences.9. I have been searching all the web since two days.10. Thanks.
While I'm sure I'm missing something very stupid here.... I cannot get this sproc to run successfully. I get "Error 207: Invalid Column Name tbl_images.imgID". Yes that column exists in that table, and it's case is exactly the same as what I have in the select text.
I'm baffled, any help would be great thanx!
CREATE PROCEDURE spImagesbyCategory @categoryID varchar AS
FROM tbl_products LEFT JOIN (SELECT catalogID, MIN(imgFileName) AS imgFileName FROM tbl_images GROUP BY catalogID) tbl_images ON tbl_products.catalogID = tbl_images.catalogID WHERE tbl_products.categoryid Like '%' + @categoryid + '%'
I'm getting the following error when I try to add a new record to the db:System.Data.SqlClient.SqlException: Invalid object name 'Pub_Points'. My table is called Pub_Points. I've had an insert working before.This is the SQL string that I'm trying to send:INSERT INTO Pub_Points ('PPName','Encoder_URL','Connect_Type','Archive','Creation_Date') VALUES ('fu','barr','local ','19/26/2005 13:35:27')Yet updating an existing record during the same run works fine:UPDATE Pub_Points SET PPName='foo ', Encoder_URL='bar', Connect_Type='remote ', Archive='0' Where ID='114' So it can't be a problem finding the table itself.Only two things I can see that are different:- the Insert procedure first creates and attaches the formatted date string (which you see already inserted in the Insert query)- the database has an auto-generating key field 'ID' (which you see being referenced in the UPDATE query)but I don't see how either of those things would give me this error.Ideas?
I'm using ODBC (System DSN) to connect to SQL Server 7.0 Database. Whenever I try to connect to SQL Server Database from my client application, I'm getting the following error message -
---------------------------------------------------------- Microsoft SQL Server has reported the following error:
[Microsoft] [ODBC SQL Server Driver] Invalid Date format ----------------------------------------------------------
After this message, I'm getting another error message -
I'm obviously connecting and executing the package correct, but I can't track down this error. The installation includes dtspkg.dll, dtspkg.rll, dtspump.dll, dtspump.rll, which are all registered. The username and password are hard code set in the package object before I execute it, so security should be the same.
Any suggestions on how to solve or track down this problem?
Having some trouble with a Database email system I created. The system consists of two tables, DATA_ELEMENT and EMAIL_MESSAGE. So the email message body and recipient fields may contain substitution macros such as {![CUST_EMAIL]!}. The CUST_EMAIL data element row then stores the SELECT, FROM and WHERE clauses separately. There is a stored proc to search the message body and recipients for these substitution macros and replace them with the appropriate values from the DB.
The system is working well except I have one particular substitution macro called VENUE_NAME_BY_PPPID which is causing a problem.
Quote: Server: Msg 207, Level 16, State 3, Line 3 Invalid column name 'PARTNER_PRODUCT_PRIZE_ID'.
And here's the query which is creates this error (without the escaped single quotes):
Code:
SELECT P.PARTNER_NAME + ISNULL(' - ' + PS.SITE_NAME, '') FROM PARTNER_PRODUCT_PRIZE PPP JOIN PARTNER_PRIZE PP ON PP.PARTNER_PRIZE_ID = PPP.PARTNER_PRIZE_ID JOIN PARTNER P ON P.PARTNER_ID = PP.PARTNER_ID LEFT JOIN PARTNER_SITE PS ON PS.PARTNER_ID = PP.PARTNER_ID AND PS.PARTNER_SITE_ID = PP.PARTNER_SITE_ID WHERE PPP.PARTNER_PRODUCT_PRIZE_ID = '19'
And just after this print statement, the query is executed with sp_executesql()
Any advice is greatly appreciated as this query runs fine when I execute from the query window. However, if I escape all the necessary quotes, I can't get it to run when I put the string inside of sp_executesql().
Public Function CalcOrderSubTotal(prmOrderID As Integer, _ Optional CallFromMacro As Boolean = True) Dim varOrderSubTotal As Integer Dim db As DAO.Database Dim rs As Recordset Dim strSQL As String
I'm working in SQL2000 sp4. I've built a simple database to take snapshots from three ERPSs that contain related data and then analyse them to look for non-conforming records (items that are flagged differently between two systems, cost conversion errors, etc). The DTS packages all work fine, and suck the records out of the main systems without a problem.
For info, the DTS packages all work in the same way: Purge a holding table Connect to the source ERPS Populate the holding table via a SELECT statement Invoke a stored proc to make the required changes in the main table
Similarly, the stored procs all work in the same way: Add records from the holding table to the main table that aren't already there.
Update any records common to both: If there's a record date field, have been updated in the holding table more recently. Otherwise, match on key fields and differ on detail fields. Delete records from the main table that aren't in the holding table
The trouble started when I modified two of the tables to include the data that the record was last amended in the source ERPS. When I tried to incorporate this new column into the relevant stored proc, performing a syntax check resulted in error 207 - invalid column name.
I did some checking, and found out that stored procs tend to rely on what the table looked like when the proc was created, rather than what it now looks like. Accordingly, I tried creating a new proc. I got the same result. What have I missed?
I create 2 instances SQL Server in the same server using port 1433, unluckily when i connect they from the remote pc, it doesn't connect and alert "Invalid Instance". How can i use many instances? And what special when i replicate them?
