Cant fit results of tsql query in excel

Hi,

I am using a .dqy file to import some rows from a sql server 2003
database to microsoft excel. Unfortunately, I have am importing more
rows than excel can handle. Is there a way around this?

It appears that there is no equivalent of the LIMIT(offset, number of
rows to return) function of mysql in tsql. Had there been an
equivalent, I would have created multiple .dqy files, which would query
the database for pages of results as appropriate. There is an upper
limit to the number of records that there can be in the database, so
this would have been a feasible solution.

Also, I must use .dqy files (or something equivalen) because the
queries are invoked from a website, and it is necessary to download the
results, sort/filter, etc. (in excel).

Thanks for any suggestions.imagine if you will a world where you get all the data you ever wanted
with subsecond response times.
you can slice and dice all of your data, sort the results, and
otherwise find exceptional values in an instant, no matter how large
your data is.

The answer is OLAP.

Barring that, you can use a reporting tool like Crystal.

Barring that, well, hmmmm.|||Thanks for your opinions.

Unfortunately, my reports are for non-technical users, otherwise I
wouldn't even need to bother with all of this - they could have queried
the sql database directly. However, your comments are appreciated.|||RA wrote:
> Thanks for your opinions.
> Unfortunately, my reports are for non-technical users, otherwise I
> wouldn't even need to bother with all of this - they could have queried
> the sql database directly. However, your comments are appreciated.

Non-technical users are exactly the audience that the BI tools are
designed for. I recommend you check out that option. There are much
richer, easier and cheaper solutions than the "dump the database in
Excel" method.

That said, it is perfectly possible to segment the data in the manner
you are proposing. In SQL Server 2005 you can make use of the
ROW_NUMBER() function to filter results. For earlier versions take a
look at: http://www.aspfaq.com/show.asp?id=2120.

BTW there is no SQL Server 2003. I expect you mean 2000.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Ok, thanks for the advice guys.

I meant SQL server 2000 (it was a typo). It now appears that the
requirement is no longer critical - the users are happy to be taught
how to query with SQL, until office 12 comes out (excel will have a row
limit of about a million [2^20])sql

Can't export report to CSV,XMl correctly, sql 2005 reporting service

Hi all,

I am developing reports using sql2005 report service. I can export reports in excel and pdf format, but can't export to csv and xml correctly.

The exported csv and xml files only contain some thing like the textbox,..., but no report content are there.

Anybody can help? thanks in advance.

BTW, all reports are matrix, is martrix can cause some issues?

Hi,

For most items on your report, you can define how it needs to be rendered to XML. For example the xml output for an item in a matrix can be set as follow:

select the item in the matrix|||

Thanks, Greert.

Your reply is really helpful. Now I know how to export report in xml format. But, how about CSV? I still can't export report to csv.

Thanks again

|||

Hi,

The same goes for CSV. For example if you set the Output of a column to No, you will see that the column is not included in the CSV file.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Hi Geert,

Thanks a lot.

When I set th output of a column to yes, it works. Before, the the output of column was set to Auto.

Can't create excel file with SQL Server 2000 client.

Hi ;

I am trying to create several Excel sheets using SQL 2000 views like so:

Select * INTO [Excel 8.0;Database="C:\spreadSheets\aNew.xls"] FROM [aView].

When I try and execute this in my app I get the following - Specified owner
name 'Excel 8.0;Database=c:\spreadSheets\aNew.xls' either does not exist or
you do not have permission to use it.

If I use the above Select statement with an OLEDB connection it works.

I am using Imports System.Data.SqlClient, instantiating a new SQlConnection
object, opening the connection, etc..

Thanks,

Gordon

This special select into sql statement syntax only works if you use the Jet OLEDB Provider, it's not supported by SQL Server.

So you need to open a connection to some Access database (even a dummy one will work) and do something like this:

I wrote this KB article about a decade ago (arg where does the time go) that demos the syntax:

200427 How To Export and Import Access Tables Using DAO or ODBC
http://support.microsoft.com/default.aspx?scid=kb;EN-US;200427

Here is some demo code that uses .NET:

System.Data.OleDb.OleDbConnection conn;

System.Data.OleDb.OleDbCommand cmd;

conn = new System.Data.OleDb.OleDbConnection(@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\junk.mdb;");

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select * into [Excel 8.0;Database=C:\\Book1.xls;].[MyJunk] FROM [ODBC;Driver=SQL Server;Server=mySQLServer;Database=Pubs;Trusted_Connection=Yes;].[authors]";

cmd.ExecuteNonQuery();

However note there are tons of annoyances with using Jet driver and Excel, the Jet and Excel teams just never seem to want to make this any easier for the customer. The other way to do this is poke values into Excel speadsheet using ole automation (which used to be easy cheesy in VB6 but now is a nightmare in .NET since .NET deprecated COM). With newer version of Excel 2007 I wonder maybe this is easier in .NET.

Can't connect to SeptCTP x64

I've built and deployed cubes on a x64 server, with x64 SQL2005 SeptCTP.

Works pretty much fine. I can run 32 bit Excel on the server, and browse the cubes no problem.

However, if I fire up 32 bit Excel on my 32 bit client machine, and go through the MultiDimensional data source wizard to connet to the cubes, after I put the server name in, it throws this error:

"The peer prematurely closed the connection"

Then this error:

"An error was encountered in the transport layer"

Like a fool, I went from 32 bit to 64 bit and from JunCTP to SeptCTP at the same time, so I don't know which is to blame.

I'm aware of the classic "re-register msolap90.dll on the client" fix, and I've tried it, and it aint that.

Any ideas?

Some more info on this:

If I open up SeptCTP Management Studio on a 32 bit client machine, connect to the Analysis Server on the 64 bit server, it connects. I can see the database on teh x64 machine, and below that, I can see the Data Source, DSVs, Cubes etc.

BUT

When I right click on cube, and try to browse, it fails with the following error:

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Deserialization failed: The 'EditionID' element in the 'http://schemas.microsoft.com/analysisservices/2003/engine' namespace is unexpected. (Microsoft.AnalysisServices)

Exactly the same error presents whether I try to get the properties of a cub, data source, dsv, whatever.

I do not understand this at all.

|||I got the same error on deploying my project. It works fine on Jun CTP but got the error in Sep CTP and the MSSQL 2005 evaluation copy. Any advise? Thanks in advance!

Can't connect to SeptCTP x64

I've built and deployed cubes on a x64 server, with x64 SQL2005 SeptCTP.

Works pretty much fine. I can run 32 bit Excel on the server, and browse the cubes no problem.

However, if I fire up 32 bit Excel on my 32 bit client machine, and go through the MultiDimensional data source wizard to connet to the cubes, after I put the server name in, it throws this error:

"The peer prematurely closed the connection"

Then this error:

"An error was encountered in the transport layer"

Like a fool, I went from 32 bit to 64 bit and from JunCTP to SeptCTP at the same time, so I don't know which is to blame.

I'm aware of the classic "re-register msolap90.dll on the client" fix, and I've tried it, and it aint that.

Any ideas?

Some more info on this:

If I open up SeptCTP Management Studio on a 32 bit client machine, connect to the Analysis Server on the 64 bit server, it connects. I can see the database on teh x64 machine, and below that, I can see the Data Source, DSVs, Cubes etc.

BUT

When I right click on cube, and try to browse, it fails with the following error:

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Deserialization failed: The 'EditionID' element in the 'http://schemas.microsoft.com/analysisservices/2003/engine' namespace is unexpected. (Microsoft.AnalysisServices)

Exactly the same error presents whether I try to get the properties of a cub, data source, dsv, whatever.

I do not understand this at all.

|||I got the same error on deploying my project. It works fine on Jun CTP but got the error in Sep CTP and the MSSQL 2005 evaluation copy. Any advise? Thanks in advance!

Can''t connect to Analysis Services Cube using Excel 2007

Hi, i made a cube using Analysis Services 2005, but when i try to connect to Analysis Services using Excel 2007, i got the following error: (excuse me but i had the spanish version of Microsoft Office 2007, y hope you understand):

"El asistente para conexión de datos no puede obtener una lista de las bases de datos del origen de datos especificado"

i got that error when i put the name of the server and the authentication. Previously i made a report on excel 2007 using other cube with exactly the same configuration of server an credentials, but i erase that cube and made a new one, i only change the structure of the cube, the credentials, even the names, are the same as the old one.

i don't know what happens or what i'm missing out this time.

i had sql server 2005 with sp2 installed.

i'm working on Windows Vista. i applied all the patches to make sql server 2005 and visual studio 2005 work perfect on vista. I GOT ALL THE PATCHES FOR VISTA.

i also started all the services for Sql Server 2005 (Analysis Services, etc etc.)

I hope you can help me.

Thanks.

Guillo.

I found the solution for my problem:

Because of the securities of windows vista, it's necesary to delegate the right permissions to the current user you are working with.

In my case, i delegate the permissions when i installed SQL Server 2005, but it seems that for some unknown reason they were erased. So, i open "Configuración de superficie de SQL Server" (sorry, i'm using sql server 2005 spanish), then i re-delegate the right permissions to my current user in windows, and i made the report on excel 2007 with my cube.

That easy.

Can''t connect to Analysis Services Cube using Excel 2007

Hi, i made a cube using Analysis Services 2005, but when i try to connect to Analysis Services using Excel 2007, i got the following error: (excuse me but i had the spanish version of Microsoft Office 2007, y hope you understand):

"El asistente para conexión de datos no puede obtener una lista de las bases de datos del origen de datos especificado"

i got that error when i put the name of the server and the authentication. Previously i made a report on excel 2007 using other cube with exactly the same configuration of server an credentials, but i erase that cube and made a new one, i only change the structure of the cube, the credentials, even the names, are the same as the old one.

i don't know what happens or what i'm missing out this time.

i had sql server 2005 with sp2 installed.

i'm working on Windows Vista. i applied all the patches to make sql server 2005 and visual studio 2005 work perfect on vista. I GOT ALL THE PATCHES FOR VISTA.

i also started all the services for Sql Server 2005 (Analysis Services, etc etc.)

I hope you can help me.

Thanks.

Guillo.

I found the solution for my problem:

Because of the securities of windows vista, it's necesary to delegate the right permissions to the current user you are working with.

In my case, i delegate the permissions when i installed SQL Server 2005, but it seems that for some unknown reason they were erased. So, i open "Configuración de superficie de SQL Server" (sorry, i'm using sql server 2005 spanish), then i re-delegate the right permissions to my current user in windows, and i made the report on excel 2007 with my cube.

That easy.