Inconsistent Linked Server Query Results

Hello,

I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query;

SELECT*FROMOPENQUERY(Charlie_file,'SELECT * FROM [Feb$]')

Sometimes, on subsequent runs of the above query, I get the following message;

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file".

There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span?

Thank you for your help!

cdun2

It turns out that the problem was with security settings on the linked server. I didn't have a mapping set up between the linked server and the SQL Server login for myself, so under 'For a login not defined in the list above, connections will;' I selected 'Be made using the login's current security context.'

cdun2

Inconsistent Excel Exports

Hi
We've got a oddity when exporting to Excel. If you export the same
report to Excel twice in sucession (don't refresh between - just
export) some report give different results between exports, with some
columns being shifted. Subsequent exports seem to be conistent.
The problem may be related to images on the reports - not sure yet.
Has anyone else encountered this, and is there a workround (other than
not using images in our standard header format).
Chloe Crowder
The British LibraryOn Jun 28, 6:15 am, Chloe C <c...@.mcrowdd.plus.com> wrote:
> Hi
> We've got a oddity when exporting to Excel. If you export the same
> report to Excel twice in sucession (don't refresh between - just
> export) some report give different results between exports, with some
> columns being shifted. Subsequent exports seem to be conistent.
> The problem may be related to images on the reports - not sure yet.
> Has anyone else encountered this, and is there a workround (other than
> not using images in our standard header format).
> Chloe Crowder
> The British Library
The first thing you should check is to make sure that you have the
latest MS Office updates and the latest SQL Server SP. Also, I would
look into index tuning for better performance of the query/stored
procedure that is used in the report. If you are using SQL Server
2005, run the query/stored procedure in the Database Engine Tuning
Advisor, if you are using SQL Server 2000, run it against the Index
Tuning Advisor/Wizard. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Incomplete Exports

Hello,
I hope that someone will be able to help us with a perplexing issue. When
generating a reporting in either Excel or PDF every 5th report or so, we will
get a corrupt / damaged file. The issue is repeatable but not reproducible.
The same report will fail a random number of times in a row, but will then
execute correctly. It appears that the corrupt file is really truncated.
The reports are big, but we don't have any very long columns or many rows.
However, they do contain quite a few charts. The final file size in excel
is ~1.4 MBs.
We have been unable to locate any errors in the logs. The web site
behaves like it successfully created the report. I've watched the memory
usage and it does not appear to be memory bound or disk bound.
I'm hoping for any suggestions on avenues to try or finding someone with a
similar problem.
Thank You,
--Chris Swinefurth
MID Technologies, Inc.You may want to try this:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
--
Adrian M.
MCP
"Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> Hello,
> I hope that someone will be able to help us with a perplexing issue.
> When
> generating a reporting in either Excel or PDF every 5th report or so, we
> will
> get a corrupt / damaged file. The issue is repeatable but not
> reproducible.
> The same report will fail a random number of times in a row, but will then
> execute correctly. It appears that the corrupt file is really truncated.
> The reports are big, but we don't have any very long columns or many
> rows.
> However, they do contain quite a few charts. The final file size in excel
> is ~1.4 MBs.
> We have been unable to locate any errors in the logs. The web site
> behaves like it successfully created the report. I've watched the memory
> usage and it does not appear to be memory bound or disk bound.
> I'm hoping for any suggestions on avenues to try or finding someone with
> a
> similar problem.
> Thank You,
> --Chris Swinefurth
> MID Technologies, Inc.
>|||Adrian,
Thank you for your reply. Unfortuntely, we are unable to utilize ether
suggesting in the KB article. 1) Sending a link to a report will not work as
we're pulling data from a non-database provider and we need a point-in-time
report. I believe that sending a link will produce another execution of the
report when the link is clicked and not when the subscription email is sent.
2) We've tried web archives, but unfortunately, printing becomes an issue.
IE renders extra pages and cuts off parts of the graphs.
Thanks again for your help. Any more suggestions would be greatly
appreciated.
--Chris
"Adrian M." wrote:
> You may want to try this:
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> --
> Adrian M.
> MCP
>
> "Chris Swinefurth" <Chris Swinefurth@.discussions.microsoft.com> wrote in
> message news:241B48D8-8163-4952-920D-1FE019DB92D9@.microsoft.com...
> > Hello,
> > I hope that someone will be able to help us with a perplexing issue.
> > When
> > generating a reporting in either Excel or PDF every 5th report or so, we
> > will
> > get a corrupt / damaged file. The issue is repeatable but not
> > reproducible.
> > The same report will fail a random number of times in a row, but will then
> > execute correctly. It appears that the corrupt file is really truncated.
> > The reports are big, but we don't have any very long columns or many
> > rows.
> > However, they do contain quite a few charts. The final file size in excel
> > is ~1.4 MBs.
> > We have been unable to locate any errors in the logs. The web site
> > behaves like it successfully created the report. I've watched the memory
> > usage and it does not appear to be memory bound or disk bound.
> > I'm hoping for any suggestions on avenues to try or finding someone with
> > a
> > similar problem.
> > Thank You,
> > --Chris Swinefurth
> > MID Technologies, Inc.
> >
>
>

Incomplete data in excel fileshare -missing many rows

Hello,
I have a report with 35 columns, meant as a datafeed. The report uses a view
on the (separate) SQL server so it only needs to put the values in the table.
When ik run the report for medium datasets, everything works fine. However
when I run it for more then 20,000 rows the exel-sheet only contains about
16300 rows. The report is meant as a subscription with a fileshare on the
local server. When i run the report via the viewer, an export to excel is
timed out, however an export to csv works and contains all rows. Sadly this
is not the format in which i need to supply the data.
can anybody offer any assistance?What version of Excel are you using? Have you passed the row limit?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> Hello,
> I have a report with 35 columns, meant as a datafeed. The report uses a
> view
> on the (separate) SQL server so it only needs to put the values in the
> table.
> When ik run the report for medium datasets, everything works fine. However
> when I run it for more then 20,000 rows the exel-sheet only contains about
> 16300 rows. The report is meant as a subscription with a fileshare on the
> local server. When i run the report via the viewer, an export to excel is
> timed out, however an export to csv works and contains all rows. Sadly
> this
> is not the format in which i need to supply the data.
> can anybody offer any assistance?|||I kept testing in the weekeind and it's my bad. RS generates the file fine,
Our sending software seems to truncate the file. Sorry.
"Mary Bray [MVP]" wrote:
> What version of Excel are you using? Have you passed the row limit?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> > Hello,
> >
> > I have a report with 35 columns, meant as a datafeed. The report uses a
> > view
> > on the (separate) SQL server so it only needs to put the values in the
> > table.
> >
> > When ik run the report for medium datasets, everything works fine. However
> > when I run it for more then 20,000 rows the exel-sheet only contains about
> > 16300 rows. The report is meant as a subscription with a fileshare on the
> > local server. When i run the report via the viewer, an export to excel is
> > timed out, however an export to csv works and contains all rows. Sadly
> > this
> > is not the format in which i need to supply the data.
> >
> > can anybody offer any assistance?
>
>