Excel 2003 To SQL Server 2005 OLAP Cube Timeout Issues
Nov 13, 2007
Hola
We are getting symptoms of a 30 second timeout irrespective of what we do, but only on certain machines. Can anyone shed any light on how the connection timeout can be set, as there seems to be a factor overiding any settings we set?
We are working in Excel (2003) connecting to an Analysis Services 2005 cube through a website https://Datasource connection. When we edit the connection string that the file is using (either by forcing use of an exterior .ODC file and editing the settings in there, or by converting the xls spreadsheet into an xml spreadsheet and manually editing the connection string in there) we get no joy: after 30 seconds the error appears:
"Either a connection cannot be made to the [my server address], or Analysis services is not running on the computer specified".
This error is itself rubbish: If a smaller query (one that takes < 30 seconds) is run in the same Excel file and Pivot Table, it connects fine.
What really doesn't make sense is that the exact same file works on some machines and not others. These machines are all inside the same network (and the machines that work have been proven to work on a number of other networks). What's more there is no pattern to the machine capabilities - some newer / faster machines get the error. These machines (that work and do not) have excel 2003 SP3. (Another of the ones that works has Excel 2007).
Changing the connection string to include "Timeout=300" or "Connect Timeout=300" makes no difference, and "Command Timeout" causes a different error so that it wont even attempt to run the query (as opposed to collapsing after 30 seconds of normal progress).
Similarly in the .ODV the tags <ODC:CommandTimeout>, <CommandTimeout>, <ODC:Connect Timeout>, <ODC:Command Timeout>, <meta name=CommandTimeout content=1000> etc. have no effect (the file still gets the 30 seconds error rather than just giving up so no way of working out correct syntax)
The only pattern we can see, and we can't fix this, is that the machines that work have Retail versions of excel, and the ones that do not have corporately licensed versions of excel. We cannot see any difference between them in the Help >Abouts, but thought it might be worth mentioning.
Any help would be fantastically appreciated, we are out of ideas.
Thanks kindly
Lets say i have one analysis server project on server A . I have made an excel sheet on desktop B in which i have inserted a pivot table which datasource is Server A's cube . I then have save this worksheet as webpage and save it on Server A. When i run this webpage on server A its working fine with but as soon as when i run this webpage on other PC it give me the error
"The query could not be processed:
The data provider didn't supply any further error information."
what could be the reason .. Please help me i am going mad....
I want to connect through a pivot table in excel to an OLAP cube I created in SSAS but it gives connection error. But I can connect to tables, databases in SSMS through pivot tables in excel. Any ideas what might be the problem? Some settings maybe etc
Hi, I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000. The string I need to import is composed by 5 different several blocks and looks like:
The detail of the SQL string is at: http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1 to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet Dim SqlCnt, cmd1, cmd2, cmd3 'set the properties and open a connection
cmd1="use my_db" cmd2="create table mytb" cmd3="insert into mytb"
I want to export Excell 2003 to Sql Server 2005. I am having the following error while execute the query.
declare @SQL varchar(8000) create table #temp(i int identity(1,1),SiteName varchar(100),SiteDescription varchar(5000),SiteURL varchar(500),CategoryName varchar(200)) set @SQL='Insert into #temp Select * From OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' + 'a.xls' +';HDR = YES;IMEX=0'',''SELECT * FROM [Sheet1$]'')'
exec (@SQL)
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 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 "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Hi all. I want to export data from excel 2003 to sql server 2005. I am using the following script: EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO Insert into Pamphlet Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Pamphlet.xls;HDR=YES', 'SELECT * FROM [Sheet3$]') But it is giving the following error: Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". The above script is working fine with Excel 2002 but not with 2003. Can someone please provide me exact script of importing data from excel 2003 to sql server 2005. I have also tried the Linked server option but it is still giving the same error. Please help me.
Is there a way to create a SQL 2005 x64 Linked server to an Excel (or Access) 2003/2007 file? In SQL 2005 32bit this was possible. Does anyone know of a solution or a work around?
1. Data Warehouse 2. OLAP CUBE in Analysis Services
My question is - If my Data Warehouse is changed (Having Append Data) - My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project....
I have around 600 worksheets that i need to import into sql server that are in a somewhat non-table like format. Data defractor seems to be able to do what i need, but i also need to check into doing it manually. I've seen the code to import cells and ranges into sql server, but can't seem to remember what it is anymore. I've also searched through the forums and can't seem to find any examples either. Could anyone post an example or a link with some examples or explaination code. Thanks in advance.
can any one sugest Quering AS2005 Cube to generate CSV or Excel in SISS 2005
i have a mdx query that runs fine in SQLserver 2005 but when use OLEDN source in SSIS and connect to Flatfile source i am getting OLEDB exception is there any work arround i have SQL Server 2005 SP2 on Windows 2003
What I can do for obtain the information of a cube OLAP in Analysis Services and obtain in Excel, they are in different machine, The Excel do not have Analysis Service.
I am having a problem creating an Integration Services package which executes an MDX query and place the results in a local DB.
I am using an OLE connection to connect to cube. However when I run the package I get the following error ....
[OLE DB Source [175]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid. AND [DTS.Pipeline] Error: component "OLE DB Source" (175) failed the pre-execute phase and returned error code 0xC0202025.
Is it possible to use a DMX query as an action in an OLAP cube? So when someone clicked on a product when browsing the cube, it called my DMX query with a product parameter, and the query would return a listing of associated products? I'm not asking how to do the query, I already have that, but just wondering if it's possible to even do that. Thanks!
Is there a way to create an ETL package that gets data from a flat file, puts it in a fact table and then it creates a cube based on the data without user intervention? So the package will generate automatically a cube that can be used in SSAS?
Can the reporing services read the cube definitions (backcolor,forecolor etc.)? the MDX contanins the " CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS " clause.
I'm new to datamining and have gather some knowledge by following the tutorial that comes with sql server 2005. I have following created a association mining model and tested it by using various DMX queries. I works quite satisfactory.
But I would like to use this mining model on a cube. Ie a calculated measure that would show top 1 recommendation for my customer portfolio. My knowlede reaches to the extent that you need to feed data by using the OpenQuery or OpenRowset queries. I would like to "stick" my model to the cube on some way and the extract values in the same manner as filtering criterias in stead of manually generating DMX queries.
I have googled a bit and searched MSDN without luck. Maybe I'm looking at this all wrong.
how can I use SQL Svr 2005 Express as database engine in background through VBA code in Excel 2003?
I want to CREATE and DELETE tables and SELECT, INSERT and UPDATE data. Is it possible to use ADO or other database objects to get in contact with SQL Svr 2005 Express?
CLIENT SIDE: If the query is reading from large table, (100 columns x 20000 rows) I have no problem getting results using SQL Query Analyzer on the Client side.
However, I am getting timeout problem from the client side application.
The query failed. The message from the database engine was: Microsoft OLE DB Provider for SQL Server: Timeout expired.
SERVER SIDE: I tested the same query on the server using the application. I can get the results.
ENVIRONMENT: Server machine: The Server : Windows 2003 Server SP2 Database Server : SQL Server 2000 €“ (8.00.2039 Standard Edition SP 2)
linkserver (OLE DB 9.0.0.3504 ) to FoxPro 9.0 SP1 table SQL Server Timeout Settings: Query time-out (sec, 0=unlimited)
Client machine: Windows XP SP2 : Windows Network Authentication SQL Server 2000 client
For some reason my environment doesn€™t like the outside application to connect to the server long time?
Do you have any idea how to fix this timeout problem? Do I need to configure DCOM or DTC?
Does anyone know an easy way to automatically (ie each morning) refresh a cube without having to log in to the OLAP manager and use the interface? Ideally I want to be able to schedule a job to run each morning so that the Sales & Marketing people arrive to fresh data in their Excel 2000 Pivot tables that are connected to the cube.
Any suggestions gratefully received. Regards, John Thorpe
I'm looking for a possiblity to schedule the processing of a ms olap services cube in a SQL Server Agent job. Has anyone expericiences with that? Are there any alternatives for scheduling the processing?
hello, Iam trying to build OLAP cubes in MS SQL Server 2000.But all the tutorials/docs mention about fact tables & dimensions. Can I get some good tutorials on how to create fact tables to build OLAP cube ? Also, which OLEDB provider to be used for MS SQL Server while creating OLAP Datasource ?
Thanks in advance & wishing u a prosperous new year too.
I have created a crystal report connecting an OLAP cube .But once itry to view a report it took around 15 minutes and finaly gave anerror like "cannot retrieve data from cube".I want to know why it isand how can i create a crystal report for OLAP CUB.
We have a MS-OLAP cube that has about 11 partitions and I have created a prototype package which processes these partitions conditionally based on expressions that are fed values from a SQL Server control table. It appears that one or more of the partitions seem to fail due to the fact that all of the data for the various partitions come from the same huge fact table. Is there a way to control the level of concurrency within the package itself? If not, I am thinking I should move some of the partitions to process based on other partitions completing their process successfully. Appreciate any help.
I am trying to log the processing time details so that we can identify bottlenecks. My SSIS package has a bunch of OLAP processing tasks. In the Event Handler (onPreExecute and onPostExecute events), I am trying to capture the start and end time for each OLAP processing task by using an "Execute SQL task". In the event handler, I have a conditional expression that checks the following:
@SourceName != @[User::Expression1]
where Expression1 is a variable that contains the value of "Execute SQL Task". This expression I thought would be true only for OLAP processing tasks which btw never fire the OnPreExecute or OnPostExecute events. What am I doing wrong?
Hi.The situation is:I'm showing OLAP Cube in OWC11. One of Parent-Child dimmensions (for example employees(menager->menager2->employee)) is viewed as Rows. Employees have attribute "City" (where they live). City can be selected from other dimmension. There are so meny of Employees that I have to hide empty rows. The problem is: When I select City in my filter dimmension, Parent members of Employees dimmension from other Cities are hiden even if their leaf members are from selected City. What Can I do to wiew all members of Employee dimmension who are friom selected City no mater in which level they are?? Any Ideas? I'm using Analysis Servicess 2000 SP3. Office web components are docked in web application so I can't programm it. MS Analysis Menager is handling this without problem (showing leaf members next to its parent even if parent is blank). This is realy urgent! Please help! BTW Sorry for my English ;)
I am trying to access Cube through SSIS and have been unable to set SSIS package with the work around provided here (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=219068). On pasting the MDX query using the openrowset command on the OLEDB source editor, I get a pop up window with error 0x08000405 and the message that says 'syntax used for openrowset is incorrect'
I also tried running this on SQL Management studio but, get the following error.
OLE DB provider "MSOLAP" for linked server "(null)" returned message "An error was encountered in the transport layer.". OLE DB provider "MSOLAP" for linked server "(null)" returned message "The peer prematurely closed the connection.". Msg 7303, Level 16, State 1, Line 3 Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "(null)".
The server where the cube resides is on 64bit machine and I have 32-bit..could this be the reason for the issue?
I found this article on microsoft support website (http://support.microsoft.com/kb/947512 ) which describes the possible symptoms and causes for connectivity issues But, couldn't find a work around for it.
Here is the syntax of the query I am using in SSIS and query analyser
1. SQL Server Data Warehouse 2. OLAP CUBE in Analysis Services
My question is - If my SQL Server Data Warehouse is changed (Having Append Data) - Is that My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project.
It runs SSIS packages, stored procedures fine. But when it comes to execute a command, reprocessing a Analysis Services cube it fails, saying the cube either not exists or the account has no rights. The cube does exist. If it's the account, how can I choose a different one or permit the one which is being used to execute reprocessing?
