How to import data from sql server 2000 to excel sheet through a stored procedure. Need to make the script so that it will reun every half an hour
-
How to import data from sql server 2000 to excel sheet through a stored procedure. Need to make the script so that it will reun every half an hour
Please reply as soon as possible
-
You could also make a batch job that runs a "SELECT" via OSQL to create a CSV file as the output.... Windows Task Scheduler takes care of the rest...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Madhi shows how to go straight to excel in this marathon sqlteam post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
---------------------------------------
elsasoft.org -
Good morning. How do you most of you decide to use openrowset versus bcp versus DTS? Thanks.
-
I pretty much always use bcp as it's the most efficient and controllable/automatable. cmd line apps are the best.
---------------------------------------
elsasoft.org -
Dunno about others but I only use DTS for one off, ad hoc copies of data. If it goes into production, I use either BCP or Bulk Insert. I don't use open rowset, especially for spreadsheets, because if someone has the spreadsheet open, the job fails. As someone else said, I make them "do it in the app"... if the want SQL data in a spreadsheet, let them import CSV or Tab delimited data. If the want spreadsheet data in SQL, let them export it from the spreadsheet in either CSV or Tab delimited format.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How do you most of you decide to use openrowset versus bcp versus DTS Whichever is easiest, gives the best performance and gets the job done
Because of the Excel ISAM problems with mixed data and nulls, for one of my jobs, I used a generic app to save the spreadheet as csv and uses DTS to import the data (could use BCP as well but DTS gave better control)
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for the feedback, everyone.
Chris
-
Another option, that does not use a stored procedure, but does use a view, is to use the Import External Data function in Excel. You could set up a Query (odc) to the view. then all the user has to do is to refresh the data when they open the spreadsheet. You could even set it to refresh automatically on opening.
-
Carla, I use this all the time but I didn't know you could have it refresh automatically when they open the excel file. Currently I have it such that you have to hit the refresh external data button (which can be added to the excel tool bar) and then a username/password box pops up and they have to type in the user name and pw to the SQL Server account I set up on the given database. Unfortunately our database servers are on a different domain than that of our analysts so I am not able to add the analysts active directory account to the database. Our IT department says it would be too time consuming and "not worth it" to establish a domain trust between the two domains.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply