October 3, 2008 at 6:26 am
This may be a little out of the way solution for you. I just tried and verified it works using a SQL-2005 table with over 28,000 lines and 57 columns into Excel-2003. I created an external link to the SQL using Access-2003. I then opened the table and did file export with a save as excel-97-2003 type. 5 minutes to set up the odbc and link, 10 seconds to export. done. Sometimes Access is easier than to screw with sql-server. Hope it helps.
October 7, 2008 at 3:01 pm
Try the following SQl Query, you can use it Excel 2007.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
GO
October 8, 2008 at 2:12 pm
for 1GB size, I'd suggest BCP QUERYOUT to a .csv or .txt
then import as Excel (2007) if you really want
or How to Export Data to Microsoft Office Excel 2007 Using SQL Server 2005
http://www.sql-server-performance.com/articles/biz/How_to_Export_Data_to_Excel_2007_p1.aspx
October 9, 2008 at 3:37 pm
Junior_DB
What do you want as the end result? Is it an application which will produce excelfiles
with different selections SQL from your database per day to different users?
Or is it add hoc? If so you can just copy paste records (a result) from a query to
Excel.
October 10, 2008 at 6:19 am
Hi, I was trying, but nothing worked...I tried the one from SSMS to access and then excel, but it failed...what I need to do is migrate databases, I am going to explain it:
1) I have a SQL Server 2005 database running with application A.
2) I have excel spreadsheets in which people have been saving records as a historical data, instead of put them in the database (#1).
3) We bought a new application, an this new application has to pointed to a new database, this new database has to have a similar estructure than the one under #1, plus including the data that my excel docs has.
4) So what I trying to do is export all my database data to put the data from my excel on that export, and then import to a new database....
How can I do that.
Do you know any kind of tool or software to help doing those tasks?....
Thank you.
October 10, 2008 at 6:34 am
Export #1 database to Access and import to your new database.
October 10, 2008 at 7:30 am
There's absolutely no reason you can't use queries to take the information from one database and get it inserted in the other. HOWEVER, a couple of necessities first:
Be absolutely sure about exactly where you want the data to go, and be sure to match data types. You can use CAST or CONVERT in most cases to convert data types if need be.
The basic idea is that if you have an existing table in the new
database, and both databases are on the same server, then you should end up with something along the lines of:
USE NEW_DATABASE
INSERT INTO NEW_TABLE (FIELDA, FIELDB, ...)
SELECT FIELD1, FIELD2, ...
FROM OLDDATABASE.schema.ORIGINAL_TABLE
WHERE
You'll have to adapt this to your particular fields. If the database is on a separate server, you can just add the old server name as part of the FROM clause, and you'll just have to create a linked server on the new server that points to the old one before you can run this kind of query.
Steve
(aka smunson)
:):):)
Junior_DBA (10/10/2008)
Hi, I was trying, but nothing worked...I tried the one from SSMS to access and then excel, but it failed...what I need to do is migrate databases, I am going to explain it:1) I have a SQL Server 2005 database running with application A.
2) I have excel spreadsheets in which people have been saving records as a historical data, instead of put them in the database (#1).
3) We bought a new application, an this new application has to pointed to a new database, this new database has to have a similar estructure than the one under #1, plus including the data that my excel docs has.
4) So what I trying to do is export all my database data to put the data from my excel on that export, and then import to a new database....
How can I do that.
Do you know any kind of tool or software to help doing those tasks?....
Thank you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 10, 2008 at 8:29 am
Is the new database in SQL Server? This sounds like a job for SQL Server Integration Services (SSIS). You can export the data from both the old SQL Server database and the Excel files without having to merge the two first.
Wendy Schuman
October 10, 2008 at 12:41 pm
Yes, both databases are SQL Server 2005, the one I need to export from, and the other I need to import to.
Thank you.
October 10, 2008 at 1:02 pm
I just want to clarify my previous post that even if the new database wasn't in SQL Server, you could still use SSIS for data transformation using the Ole DB provider (either in the source or destination).
Wendy Schuman
October 10, 2008 at 1:50 pm
I tried to do that but it failed, it gave my a package file...and then trying to execute that pachage it failed, it gave me or too much table to run...but it cannot allow me to unselect teh tables, all of them were selected as one. Than starts running and gives me Package1.dtsx, the I execute this.."package", and failed with the following message:
"[Execute SQL Task] Error: Executing the query "CREATE TABLE `sysdiagrams` ( `name` LongText, `principal_id` Long, `diagram_id` Long, `version` Long, `definition` LongBinary ) " failed with the following error: "The Microsoft Jet database engine could not find the object 'sysdiagrams'. Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "
Any idea why??
Thanks.
October 10, 2008 at 2:33 pm
Are you creating an Integration Services project in Visual Studio?
Using SSIS you would use the Data Flow Task and then on the Data Flow tab, create an OLE DB source for your SQL Server database and specify the appropriate connection and table. You will have to create one for every table you need to export. For your Excel file, you would create an Excel source and specify the path to the Excel file. Then you need to create an OLE db destination for each source and then map the input columns to the destination columns.
Wendy Schuman
October 12, 2008 at 3:27 pm
Thank you, I will be working on that.
Just a question, does someone know about some tol that helps me on "Integration products", I found one under Pervasive company that helps me in move data from one database says SQl to Oracle or between any others. Also it gives me the posibility to built a map designer and send me alerts. It looked insteresting, but I was reluctant to go with that because it didn't give me the functionality of do it all in once, like move databases I had to do it tableby table...
Some suggestion please?
Thank you.
October 14, 2008 at 3:08 pm
Hi Guys,
You were right I didn't see it, I was trying touse SQL Visual Studio, but is the only way I can get into SSIS, please, could you give me some help...is there any way to just get into SSIS?
Thank you.
October 14, 2008 at 11:58 pm
From your specification it looks like you are going to do a product migration to a new database structure in SQL 2005.
With the new database structure you also want to include the historical Excel data that the users have been using instead of database #1. In the end you want the data from database #1 and the Excel file data in the new database so you can get all the users to use the new database application.
There is no Single button you could press to solve this problem. But the easiest way to do a migration like this would be to use SSIS. With 1 or more packages for the SQL Data and 1 or more packages for the Excel Data.
If this is in fact the situation you would need to make sure that your source data is shaped correctly and is at the correct level of uniqueness and quality (especially from Excel) before you insert it into the new database tables.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply