Post reply

Help with Excel 2003 data source - Urgent

  • May 8, 2007 at 8:24 am

    #177799

    I have a couple of reports that I have written in Reporting Services 2000. An Excel 2003 worksheet is the data source. Everything works great in Visual Studio 2003, but when I deploy it I get this error...

    • An error has occurred during report processing. (rsProcessingAborted) Get Online Help

      • Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection) Get Online Help

        • ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    I have set up an ODBC connection using Microsofts Excel Driver. I have done different things with the security in side both VS 2003 and the report manager. I have tried just about everything but the right thing. What am I doing wrong? Any suggestions? I am running out of options and this needs to be done today.

    Thanks in advance!

  • May 8, 2007 at 10:53 am

    #705004

    If you go into properties of the report and select 'Data Sources' what do you see?

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 8, 2007 at 11:00 am

    #705006

    Custom Datasource (I have tried a few different ways to do this)

    Connection type...

    ODBC

    Connection String....

    PageTimeout=5;FIL=excel 80;MaxBufferSize=2048;DSN=FixDS;DefaultDir=P:;DBQ=P:\FixStatus1.xls;DriverId=790

    Connect Using...

    Windows NT Integrated Security

    Is that what you are looking for? I hope that tells you something because I am not sure what else to try!!!

    Thanks you for your help!!!!!!

  • May 8, 2007 at 11:22 am

    #705017

    On the report server where this is deployed do you have the 'P' drive mapped to the same location as running it locally?

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 8, 2007 at 11:26 am

    #705019

    Yes, we have tried that. I have tried to map a drive to the client machine, connected it to the client, and it worked for the client but not the server.

    Thank You!!!!!

  • May 8, 2007 at 11:29 am

    #705021

    So have you mapped the drive on the server? If so and you're still receiving this error I would suggest in the excel connection fully declaring the path (eg. \\servername\folder\file.xls) If none of the above works try copying the file locally to the server somewhere and using it there.

    If this report is needed today you could always export it from visual studio and send the file to someone. That's probably what I would do if I was in this situation...

    Hope this helps!

    -Ben

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 8, 2007 at 1:20 pm

    #705051

    I finally got it to work....

    Make sure that all of the mapped drives have the same drive letter along with the same path. Make sure permissions are correct for all drives.

    But of course after I finally figured it out, the boss changed what he wanted. and of course I am having an issue with that also.

    The Excel worksheet is updated every morning by a macro...Again the report works in VS.net but as soon as I deploy it I get this...

    • An error has occurred during report processing. (rsProcessingAborted) Get Online Help

      • Query execution failed for data set 'DataSet1'. (rsErrorExecutingCommand) Get Online Help

        • ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'PSEnvironPublished$'. Make sure the object exists and that you spell its name and the path name correctly.

    Any Suggestions? I am running out of time.

    Thanks for all of your help,

    Kerrie

  • May 8, 2007 at 2:13 pm

    #705066

    Check the sheet name in the worksheet

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 8, 2007 at 2:34 pm

    #705069

    I wish it was that simple...I did, twice. This is kicking my butt and there is no information on this at all. Has no one tried this, I find that really hard to believe.

    What kills me is that it works fine in VS.net but as soon as I deploy it, it breaks and gives me a generic message...

    • ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'PSEnvironPublished$'. Make sure the object exists and that you spell its name and the path name correctly.

    Could it be the macro that is ran before the report? The reason I say that is because I made a copy of the .xls file without running the macro, it worked in the report manager, then I ran the macro and the report stopped working...

    Any thoughts?

    Thanks in advanced

  • May 8, 2007 at 2:52 pm

    #705082

    Hmmm...well for whatever reason the report manager is not finding the correct sheet. For my stuff that uses Excel as a source I've run into this several times and its always the sheet name. Make sure you don't put the $ at the end of the sheet name in Excel, I don't know why the report manager does that. Same thing for SSIS packages...

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 9, 2007 at 6:45 am

    #705189

    So you are telling me it should NOT be set up like this...

    SELECT A.*

    FROM

    (SELECT *, 'Environ' as PRODUCT_TYPE

    FROM [PSEnvironPublished$] 

    UNION

    SELECT *, 'Control' as PRODUCT_TYPE

    FROM [PSPublishedFixes$]) A

    I tried taking the '$' off but it still gave me the exact same message except saying 'PSEnvironPublished'.

    I wish I knew how to fix this, it is driving crazy. I know it is something simple I just cannot figure it out.

    Thanks for all of your help.

  • May 9, 2007 at 10:35 am

    #705296

    In the workbook itself I think there shouldn't be a $ in the sheet name but the Excel connection manager will add it. Is this something that you are going to need to repeat in the future? I would suggest building an SSIS package to import the data to a database somewhere and run your query off that. I've never actually produced a report that used an actual excel workbook as its source. I always import them...

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 9, 2007 at 10:41 am

    #705300

    I have never tried that before....but I will now. Thanks for all of your help, I really do appreciate it!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply