MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1202

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