Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BruceT
Yak Posting Veteran
78 Posts |
 Posted - 2012-01-31 : 16:22:20
|
| Hi,Is there a way to save a result set to text file on a local machine?I can't use xp_cmdshell (no way to get rights/access). The problem, I have a stored procedure that builds a bunch of insert statements and returns those as a result set. I then have to take that result set, copy it and paste it into a text file, take/send the text file to a different server and run the generated inserts to populate tables. This is a custom app that lets the user create their own specific forms/tables. The form design data is stored in tables. They want to move certain forms to other servers hence the stored procedure that pulls the appropriate rows for the form and creates insert statements out of that data so the form can be moved to a different db. In short this would be way easier if I could just save the stored procedure result set to a file directly without needing bcp or xp_cmdshell.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-31 : 16:40:31
|
| you can use export import wizard for that. launch export import wizard from sql management studio, choose extract data from query option and give query as exec procedurename param1,param2 ...then map destination to required flat file.if you want repeat it, save package as a SSIS package in your server and schedule it from sql server agent job to execute it automatically.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2012-01-31 : 16:49:38
|
| Thanks visakh16, I'll have to look into that. Not sure the users who will need to run this will be given access to do that.In a related question. In Management Studio under the Query menu there's the "Results to File" option, do you know how that's implemented? Is it maybe and internal function that could be used?Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-31 : 19:07:06
|
quote:
Originally posted by BruceT
Thanks visakh16, I'll have to look into that. Not sure the users who will need to run this will be given access to do that.In a related question. In Management Studio under the Query menu there's the "Results to File" option, do you know how that's implemented? Is it maybe and internal function that could be used?Thanks again.
results to file is a setting which you can set and run the query. on running it asks where to save the result and you've to save it in your machine. It involves manual intervention always so you wont be able to automate using that method.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2012-02-01 : 08:46:36
|
Why is it never easy! I guess that's why we get the big bucks!  At this point I think I'll have to build a front end to this since it needs to be usable by non-technical users with limited server access.Thanks again visakh16. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-01 : 09:30:15
|
| you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Save result set to a text file