SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/09/2006 :  11:55:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You need to use Inner Join to join four tables. Read about Joins in sql server help file
Also make sure that the file is closed when executing the query

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/10/2006 :  08:51:39  Show Profile  Reply with Quote
how can i close the windows form after executing it? is it (windows form name).CLOSE(); ?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/10/2006 :  12:13:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I meant, when you run that query make sure your EXCEL file is closed

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/11/2006 :  05:31:03  Show Profile  Reply with Quote
not the excel sheet, i am asking how to close the windows application window after executing the program.. it still remains opened even after the execution.
Go to Top of Page

ssrikrish
Yak Posting Veteran

69 Posts

Posted - 08/11/2006 :  09:33:16  Show Profile  Reply with Quote
I am getting the following error when trying to export the SQL table data to excel. Please help.

[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

I used the following query:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT * FROM [Sheet1$]') select TOP 100 FirstName, LastName from Employee

what am i doing wrong?

Also, how can i create the xls on the fly rather than creating the xls manually and then specifying the column names etc? Is there a way to do that?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/11/2006 :  11:41:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
shybi,Which front end application are you using?

ssrikrish, as error specifies, check the name of the sheet whether it is sheet1 or sheet 1

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ssrikrish
Yak Posting Veteran

69 Posts

Posted - 08/11/2006 :  21:21:49  Show Profile  Reply with Quote
It is Sheet1.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/14/2006 :  12:53:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Check it again. If the name is correct you wont get that error

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Rusty0918
Starting Member

9 Posts

Posted - 08/14/2006 :  13:06:19  Show Profile  Reply with Quote
Is there a way I can store query results (I'm only doing it with one field) into an array, and then loop through and run queries that way?
Go to Top of Page

BCI_Guy
Starting Member

2 Posts

Posted - 08/14/2006 :  16:05:17  Show Profile  Reply with Quote
Thank you to everyone posting on this thread! It has been so helpful to me so far. I am having one big issue, though. I was trying to setup my sproc that exported to Excel (works fine) to be called from a trigger that passed the sproc an OrderNumber. Through my reading, I've learned that you can't dump to Excel when you're calling the sproc from a trigger. I guess it's because you can't do a distributed trasaction through Jet. Is there anyway to do this?? The other idea I've read is to use the trigger to fire an alert that starts a job to call the sproc, but I don't know how to pass the OrderNumber through all that mess! Any help would be great!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/15/2006 :  09:24:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Do you want to automate exporting data to EXCEL?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Rusty0918
Starting Member

9 Posts

Posted - 08/16/2006 :  15:12:49  Show Profile  Reply with Quote
Essentially, that's what I want to do, Madhivanan. Automate the export.
Go to Top of Page

BCI_Guy
Starting Member

2 Posts

Posted - 08/16/2006 :  16:26:38  Show Profile  Reply with Quote
yup! I want SQL Server to export several records to Excel when a record is updated/inserted that has a particular value. I think I have been able to do it, but it's very guly. The trigger looks for the value, inserts the ordernumber into a new table, then raises an error. The error is set to fire a job. The job pulls the ordernumber where MAX(ID) of the new table. Then the job calls my sproc passing the ordernumber. The sproc pulls all the details for that ordernumber into a temp table and then copies the temp table to Excel, which maps to a different spreadsheet that formats it into the form I need.

It's ugly, and it's not perfect, but it works... except for sometimes, but I have no idea when/where/why it sometimes won't fire everything correctly.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/17/2006 :  03:07:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If you want to automate the process, use stored procedure having the code and schedule it as a job to run periodically

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shybi
Starting Member

41 Posts

Posted - 08/17/2006 :  05:23:14  Show Profile  Reply with Quote
hi madhi,

i want to insert textbox value into the sql database like :
string sql = "insert into dummy (UserID,FirstName,LastName,Dept,Location,Company,Groups) values ('" + TextBox1 + "','" + TextBox2 + "','" + TextBox3 + "','" + TextBox4 + "','" + TextBox5 + "','" + TextBox6 + "','" + TextBox7 + "')";

here TextBox1,2,..,7 is the ID of the seven textboxes where i write values which should be passed to the code behind on click of submit button.but i am not able to pass the values

what should i do?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/17/2006 :  08:09:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
what does that have to do with export to excel?
start a new thread rather than continuing this one.

request("TextBoxName") gives you the value of the posted textbox.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Rusty0918
Starting Member

9 Posts

Posted - 08/17/2006 :  10:48:06  Show Profile  Reply with Quote
How do I do a stored procedure? Do I do it via VBScript with a FOR loop? Essentially what I want to do is to first off get data from a specific field from one query and store it into an array, and then use the array to loop through and run the queries.

Essentially my Algortithm is this:

RUN QUERY WITH SPECIFIC SCHOOL FIELDS AND STORE THEM INTO ARRAY

FOR X = 1 to ARRAYLENGTH

SELECT * FROM DATABASE TABLES WHERE SCHOOL = ARRAYVALUE
WRITE TO EXCEL FILES

NEXT X
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/17/2006 :  10:59:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
just use this stored procedure:
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

you just input the query you want and that's it.

You have to have permissions for running xp_cmdshell and bcp.




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/17/2006 :  11:26:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Rusty0918,
If two tables are used, cant you simply join them and export the desired result to EXCEL?

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 08/17/2006 11:30:30
Go to Top of Page

Rusty0918
Starting Member

9 Posts

Posted - 08/19/2006 :  13:48:20  Show Profile  Reply with Quote
No I don't want to join them. These are supposed to be seperate. Essentially I have a database with four tables and want to divide them up into multiple excel files with their own copies of those specific four tables, except that it exports the results from a certian query. And I want to do this multiple times, in a loop essentially, take the data from one of the column (a distict query) and store them into an array, then loop through that array, using the data field in the array to determine the query, the excel file name, and the data to be exported.
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000