Hi ,

   How do i install "'Microsoft.Jet.OLEDB.4.0'" driver on a 64bit sql server 2005 server ?

My answer is DON'T. Rather let Excel suck it in from the database.

The simplest solution I would suggest is to use the Databse Query facility within Excel. I use this extensively and you can pass parameters through to filter data.

It uses MS Query to pull the info from any ODBC linked database and feeds it directly into Excel with a simple refresh inside Excel. 

The best part is that you can format all your reports once in Excel and you never have to do it again.

If you give your users read-only access to the database they can refresh the data themselves. And they can make modifications to the Excel report on their own too.

The data can be fed into either a flat table or a pivot table in Excel.

All you need to do is provide the data in a query in your database and let Excel pull it in from the query.

I use this so much I rarely even bother writing reports in the database.

While Executing the following Query i got the output as follows
and the excel file was not created in selected path
Please help me
Query:
EXEC master..xp_cmdshell 'BCP "select * from iplan_polaris.DBO.IP_PERSON Where LEN(PE_LOGN) = 5" queryout Contacts.txt -c -T '

Output:
NULL
Starting copy...
NULL
951 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total       31
NULL

My Questin:
1. While excuting the above qry, the Excel file was not created in selected path... What can i do now?
2. Excel file was created automatically i.e.created by sql server or created by manually?
3. any other alternative option is there in sql server 2005?

Its urgent
Please help me as soon as possible
thanks in advance
Sri

I am looking for the best way to select table names that end with '_d' from a sql 2005 db and create a worksheet with the table name into one excel 2003 file.

Leaving the the format the same and with all rows from the tables where is_pickable is true.

Can you provide me with a sample T-sql script to do this?

Thanks.

Joe 

This assumes you are in the database you want to use.

How would I omit the views & pk_ , & fk_ from the list, so I only have the table names that end with '_d'?

SELECT name
FROM sys.sysobjects
WHERE name like '%[_]d'
AND xtype = 'U'

Thank you so much!

I I just need to find the best way to copy these tables into an excel file, with each tables as a sheet (with same format & all rows).

I was trying to use the openrowset, but can't seem to get the format  correct?

Do you have any ideas for an easy way to do this with t-sql script?

Thanks.

Joe 

I  just need to find the best way to copy my domain tables from a MSSQL 2005 db into an excel file, with each tables as a sheet (with same format & all rows).

I was trying to use the openrowset, but can't seem to get the format  correct?

Do you have any ideas for an easy way to do this with t-sql script?

Thanks.

Joe 

 First create a query with the commands that grobido has provided. Call it SysTableView or whatever is appropriate.

Then in an Excel sheet do the following..

\Data
Import External Data
New Database Query

Switch off the query wizard

Create a new data source pointing to your MS SQL database.

Select the query above "SysTableView"

Select the relevent fields

 From the Records dropdown menu switch off automatic query

 From the File menu select "Return Data to Microsoft Office Excel"

 Select OK

 ...from now on all you have to do is refresh the query within Excell to get the latest information.

Thanks I will try this now.

following the steps all I got was the tables names listed in cell a of the excel file.

I was looking to create a sheet in the excel file for each of the sql tables (with all columns & rows) populated into the sheet and the sheet having the same name as the table.

Can this be done using this method?

Thanks!

 Yes - using the method described; from Excel, select each table you require rather than the SysTableView. You can rename the sheet-tab with the name of the table.

If you are still having trouble we can hook-up through Skype.

Thanks.

I will see what I can create today with each select for each table, but I am not sure how I can save the sheet with the table name.

if you have time to hook-up tomorrow, that would be real helpful.

Thanks,  Joe

I was playing around with creating a Macro this morning and have one saved in my 'All open Workbooks'.

How can I copy the saved Macro and send it to a co-worker for testing on his PC?

I am not sure if this is the path I should take or did you want to show me something else from my comments yesterday?

btw ....how do we hook up with Skype?

Thank you for your support!

Joe 

 Go to Skype.com and download the latest version. I assumed that most people would have Skype but if you don't know what it is then it may not be a good option. If you want to phone me instead thenlet me know with a private message.

 To change the name of a tab you merely double click on it and type the new name.  As the spreadsheet can be saved and reused repeately this once-off operation probably doesn't warrant a macro.

 To send the macro to someone else, edit the macro then cut-and-paste the text into an emai. The co-worker can then paste it into a new macro. Probably just easier to send the spreadsheet with the macro to the co-worker.

I am getting error below, while trying to export the query data into a excel file

Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.

How to export MSSQL data to Excel 2007:

OMG, I am posting this to save others hours of frustration importing MSSQL data into Excel.  I'm with Rothy who posted earlier (THANK YOU!).  Use Excel's data import function to do this, it is so much easier than messing around with MSSQL query code. It took me ONE MINUTE to import an MSSQL table and most of that time was spent looking up my login credentials;)

Go the Data ribbon in Excel 2007, click on "From Other Sources" in the "Get External Data" area on the left side.  It will ask you for the login credentials to your database.  Data is dumped right into Excel, with automatic row formatting, and all those miserable blank cells you might get with a straight html copy/paste are automatically removed.  Easy peasy! 

Go the Data ribbon in Excel 2007, click on "From Other Sources" in the "Get External Data" area on the left side.  It will ask you for the login credentials to your database.  Data is dumped right into Excel, with automatic row formatting, and all those miserable blank cells you might get with a straight html copy/paste are automatically removed.  Easy peasy!

How to export data into Formatted Excel Sheet Using OPENROWSET From SQL Server

Formatted Sheet Means it has table which starts from 5th row of excel sheet

Eagerly waiting for answer............

Using current tools in Ms Excel (Data\From Other Sources\From SQL Server or Data\From Other Sources\From MS Query) almost give the same result, except that SQL data can be filtered before being exported inito Excel speadsheet. The export process is quite easy & there's no any issue when the number of record is not over than 65536.