Error pulling unicode data from DB2 Linked Server

Greetings,

We have been Selecting data from A DB2 AS400 via Linked Servers in SQL 7.

We began to pull data from new intstance/tables that contain some fields with foreign characters. (double byte, unicode) Some data will pull fine, then we will get an error:

Unexpected NULL value returned for column '[MYLINKEDSERVER].[DB2NAME1].[DB2NAME2].[MYTABLENAME].DESC2' from the OLE DB provider 'MSDASQL'. This column cannot be NULL.

The DBA from the other country tells me that the field is never null.

KB article Q239458 seemed related but doesn't help. We are running SP4, and will be trying on SP3 soon.

Is there any way to turn off this checking for NULL? My guess is that it interprets the unicode as a NULL incorrectly. I've tried to explicitly cast into varchar(30.)

Sometimes this fields is readable in English and it contains data that is critical when we can see it.

Thanks In Advance,
MikeyrowThe Work-Around developed was to use a pass-thru query to the DB2. I used a NULLIF function to replace any nulls, and that prevented the OLE-DB layer from erroring and failing.

Ideally we would want the DBA in the other country to fix this themselves, but sometimes time won't allow the cogs to churn enough to get the ideal solution developed.

Mike Rowland

Error opening table in Windows 2003

I'm running SQL Server 2000 SP3 on Windows 2003 Server.
I can create tables, indexes, etc. in SQL Enterprise
Manager. But I'm unable to open a table to view the data
in SQL Enterprise Manager. It give me the following
error message: Provider cannot be found. It may not be
properly installed.
I've uninstalled SQL Server 2000 and re-installed it with
SP3 but still running into the same problem.
Anyone know what's going on or have any idea to correct
this error?
ThanksDid you try using Query Analyzer? How are you connecting to SQL Server
service (windows auth, sql auth)?
<tchen@.ceira.com> wrote in message
news:03a901c399be$c3fb7b20$a601280a@.phx.gbl...
> I'm running SQL Server 2000 SP3 on Windows 2003 Server.
> I can create tables, indexes, etc. in SQL Enterprise
> Manager. But I'm unable to open a table to view the data
> in SQL Enterprise Manager. It give me the following
> error message: Provider cannot be found. It may not be
> properly installed.
> I've uninstalled SQL Server 2000 and re-installed it with
> SP3 but still running into the same problem.
> Anyone know what's going on or have any idea to correct
> this error?
> Thanks

Error on SQL Enterprise Manager Display

SQL Enterprise Manager Version 7.0, Windows XP professional version 5.1

The list panels (right half of the screen) will not show the tables.
The top of the panel reads: 245 items, which is correct, but the panel
reads: there are no items to show in this view. This error is occurring
on one database on one server. The tables are actually there but I
cannot access them through Enterprise Manager.

I assume that a re-install will probably fix it, but I'm hoping
to avoid it.

Help

--
Posted via http://dbforums.comJohn Boyack <member44155@.dbforums.com> wrote in message news:<3482109.1066179713@.dbforums.com>...
> SQL Enterprise Manager Version 7.0, Windows XP professional version 5.1
>
> The list panels (right half of the screen) will not show the tables.
> The top of the panel reads: 245 items, which is correct, but the panel
> reads: there are no items to show in this view. This error is occurring
> on one database on one server. The tables are actually there but I
> cannot access them through Enterprise Manager.
>
> I assume that a re-install will probably fix it, but I'm hoping
> to avoid it.
>
> Help

Looking in the MS Knowledge Base, there seem to be a few issues with
this functionality - have you applied the latest servicepack (SP4) to
the workstation (and not just the server)?

Simon

Error on Export to Excel with Bookmarks and Navigation Jumps

Hello All,
We're running RS 2005. I have a problem in a report using two main sections. There's a top level report, made up from 4 tables within a rectangle. The second section of the report contains the detail of the calculations making the top level figures.
The second section of the report contains bookmarks and jump links to drill to different levels of the reporting structure. This works fine.
The top level of the report also has jump to bookmark links to drop into the relevant section of section two, and display the detail.
The problem I'm having is that although the jump links work fine within report manager, the report will not export to excel from report manager. It errors with the error at the bottom of this post.
Removing the bookmark links from the top level of the report solves the export issue (but obviously without the functionality I require). The strangest thing is that the export works fine from the development environment in VS .NET 2005, although the bookmarks do not work and claim an "invalid link" (or something similar).
Any ideas, anybody?
Thanks in advance.

Jon

Error message below:

w3wp!reportrendering!1!06/22/2006-16:51:19:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. > System.ArgumentException: Item has already been added. Key in dictionary: ' 2' Key being added: ' 2'
at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
at System.Collections.Hashtable.Add(Object key, Object value)
at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkBook.AddBookmarkLink(String name, String uniqueName, Boolean keepName)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderTextBox(TextBox textBox, PageReportItemInfo reportItemInfo, PageCell pageCell, PageTableGrid pageTableGrid, ReportItem dataRegionParent, Hashtable& duplicateItemsTable, Hashtable riReferenceTable, Int32 row, Int32 col, Boolean addHeaderRows)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderGridCell(PageLayout pageLayout, Int32 row, Int32 col, Hashtable& duplicateItemsTable, Boolean addHeaderRows, ReportItem parentItem, Hashtable riReferenceTable)
at Microsoft.ReportingServices.Rendering.BIFFUtility.WorkSheet.WriteRowBlocksAndCells(ExcelRenderer excelRenderer, Stream stream, UInt32 indexBeginOffsetPosition, Int32 minCol, Int32 maxCol)
at Microsoft.ReportingServices.Rendering.BIFFUtility.BaseWorkSheet.WriteWorkSheet(ExcelRenderer excelRenderer, Stream stream, Int32 offset)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout pageLayout, Int32& currentPageNumber, Stack& stack)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout& lastPageLayout)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)

A little more on this issue.
I've two extra tables to the top level of the report to represent other views on the same data. I've found that the top level figure - which is a single table, with only a detail row, and only a single row of data feeding into it - works fine, whilst another new table (with again, a single table, only a detail row, but a different dataset with the same fields) does not work.
Confused
|||Again... some more on this issue!
A colleague of mine has a similar problem. Making a couple of assumptions, the common issue seems to be when you have bookmark/jump IDs that are generated from the dataset. If you have two different jump points that jump to the SAME bookmark, there appears to be an issue.
As mentioned previously, I can export through the development environment but the bookmark links show up as being invalid.
I'm sure this is a feature supported by Excel. Is this possibly a bug with the excel renderer?
Cheers,

Jon
|||

It definately seems like a bug, although I haven't been able to duplicate it yet. I don't have an idea for a workaround at this time.

The fact that you can run it successfully from Visual Studio is puzzling. Do you have different versions of Reporting Services installed on your server vs. where you are developing?

- Geoff

|||Hi Geoff,
Thanks for looking at this post too.
I do run both RS 2000 and 2005, but they're hosted on seperate servers, and I develop using the appropriate version of VS .NET (2k3 for RS 2k, and 2k5 for RS 2k5). In this case it's VS 2k5 with RS 2k5.
It will export from VS, but the bookmark links on the Excel version do not work and come up with a "Reference Not Valid" error when clicked. It simply won't export from the report manager and errors (as above). Would you like me to mail you spreadsheet with the export from the development environment? With the error message posted above, you can see what happens from both VS and report manager.
Regards,

Jon
|||

Hi Jon,

This looks like a bug to me. The best way to continue here is to submit it via Microsoft Connect, at https://connect.microsoft.com/SQLServer. This will get the issue into our bug pipeline so that it can be properly addressed and tracked for a fix.

Thanks, Geoff

|||OK Geoff, thanks for your help.
I've now logged this bug, so we'll see if something comes back from it. Unfortunately, connect isn't particularly compatible with Firefox so I ended up submitting it 4 times!
Shall I submit this bug too
Thanks again,

Jon
sql

Error on a view

I am getting an error when I open a view err is:

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

How do I work aorund this issue?

On the assumption that you are using SQL Server 2000, there are a couple of KB articles on this:

http://support.microsoft.com/kb/818406
http://support.microsoft.com/kb/828269

Either way, it seems that the solution is to upgrade to SQL Server 2000 SP4.

Chris

|||

Chris,

That would be a long term fix. Is there any shortterm workaorund for this? pl let me know.

|||

Well, according to the second link I posted:

"If you still receive the "Msg 4414" error message, you may have to break up the query so that a lower number of tables or views are referenced in the query. You may change the view definition that is referenced in the problematic query to lower the number of tables that are referenced in the view."

Chris

error occur while delete large volum records

I had write a ActiveX service to delete several tables and those records are more than 100000. When I test it by deleted 1000 records it is ok, but once the volum is increase until 100000, it will give me a error message said timeout operation fail.

how can i overcome this problem. please!!!!Are you deleting the entire table or only a part of it.|||only part of it|||Originally posted by yawhum80
only part of it

Can you post the delete query.. i believe an index would help a lot over here.|||Sub DeleteByReqID(TableName, myConn)
dim rsResult
Set rsResult = CreateObject ("ADODB.Recordset")

strSQL = "DELETE * FROM " & Trim(TableName) & " WHERE ReqID >= " & getFirstReqID & "AND ReqID <= " & getLastReqID
Set rsResult = myConn.Execute(strSQL)

Set rsResult = Nothing

End Sub|||Is reqid the primary key in this table .. if not ... a clustered index on the same would be my recomendation|||not all the table is using reqid as a primary key, may i know what is the problem?|||i mean to say that if you create a clustered index on the reqid column .. it might help speed up your query . The reason i asked whether reqid was the primary key was coz a clustered index is automatically created on the primary key column in case one doesn't exist already...

Can you post the ddl for the table|||i had test it in isql and all the datas are successful to deleted but once i using DTS ActiveX script it will pop up the error message.

no matter i using primary index, cluster index or non primary and non cluster index.

is this because the DTS ActiveX script has a time limit for a operation?

Error message as a result of a DELETE

I'm executing a delete in a table and the result is the error 431:
Could not bind foreign key constraint. Too many tables involved in the query
.
There are no triggers associate with the table. What's this could be?
Thanks.
RenataHi
I assume this is a table! Are there any foreign keys referencing it?
John
"renata" wrote:

> I'm executing a delete in a table and the result is the error 431:
> Could not bind foreign key constraint. Too many tables involved in the que
ry.
> There are no triggers associate with the table. What's this could be?
> Thanks.
> Renata

error message 8152

When I am run a Stored Procedure -
dbo.sh_UploadPreTransfer task on SQL 7 and try to upload
PreTransfer tables, it showed a erroe message "String or
binary data would be truncated (message 8152)", could you
please give some advice? thanksIt means that your stored procedure is trying to insert more data than it
would fit into a character column. For example, you have a column called
TaskURL, which is of length char(128). But you try to insert a string of
length 200 characters, into this column, and you get this error.
It is bad that the error message will not show you which column is resulting
in the error.
You could use Profiler to trap the events SP:StmtStarting, and Error (with a
filter on Error for error number 8152), to see which statement is causing
the error.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"YanLing" <luo_yanling@.hotmail.com> wrote in message
news:02da01c3bcd7$e890a550$a001280a@.phx.gbl...
When I am run a Stored Procedure -
dbo.sh_UploadPreTransfer task on SQL 7 and try to upload
PreTransfer tables, it showed a erroe message "String or
binary data would be truncated (message 8152)", could you
please give some advice? thanks