TSQL code for writing to a text file

whiterob81 · 21-09-2009 11:19AM #1

I'm currently with a fairly large sql database that I can't back up too often.
I have set up a vb application that allows me to take data from tables in these files and put them on to csv files so that I have a copy of the data on these tables if needed. The way it's set up is that I have a list of the tables in the database on a textbox. I then select the table I want to output to a csv file.
What I'm hoping to be able to do with this application is to be able to write the code for stored procedures I've created to text files by selecting the name of the stored procedure from a text box. Does anyone know of a way that this can be done?

nialo · 21-09-2009 11:33AM

you can use SSIS, bcp or osql.

SSIS would probably be the cleanest as you can configure it to run on a schedule easily..

check out bcp at this link

and this is how you would run osql

EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC sp_who2" -o "E:\output.txt"'

amen · 21-09-2009 12:59PM

look at Data Management Objects (DMO)

but

I'm currently with a fairly large sql database that I can't back up too often

you are looking for trouble. Do you back up the log files?

Even if you export the data what about the Stored Procedures, Table definitions, Primary Keys, Foreign Keys etc

whiterob81 · 21-09-2009 01:39PM

amen wrote: »

look at Data Management Objects (DMO)

but

Even if you export the data what about the Stored Procedures, Table definitions, Primary Keys, Foreign Keys etc

But that's what I was asking. How can I write the code for these stored procedure to a text file using my visual basic application. I figured once I was able to do this with my stored procedures and my views I could do table definitions in the same way

kayos · 21-09-2009 02:00PM

Sorry but reading your questions and replies I just get the feeling your re-inventing the wheel. SQL Server has a huge number of backup options and I'm pretty sure you could find one that will suit your needs.

Going down the roll your own method here just screams trouble.

As for getting the text of stored procedures syscomments is where you need to look. Well as long as the stored procedure does not have the with encryption option.

But as amen said look at DMO or what ever its called these days (not used it since 7 myself) and you should be able to do a lot of this the way SSMS would do it.

nialo · 21-09-2009 02:24PM

if your able to backup tables to csv files you should be able to do a database backup! its quicker and easier.. do one weekly fully backup and then differential backups if your worried about time and space. and a maintainence job will clean up after that for a week. unless your database is above 10gb the time it will take is a couple of minutes at most..

why you want to backup to flat file is a big question?

whiterob81 · 21-09-2009 02:32PM

kayos wrote: »

As for getting the text of stored procedures syscomments is where you need to look. Well as long as the stored procedure does not have the with encryption option.

.

This is perfect. Thanks a million

Evil Phil · 21-09-2009 05:30PM

Can you update the thread to [Solved] please?

amen · 21-09-2009 05:31PM

if you are going down the flat file route how are you going to handle transactions that maybe in progress when you are backing up the data?

what about restores?
user persmission? security?

you should really up on SQL Server backups.
Everything you need is already provided

whiterob81 · 21-09-2009 05:48PM

It's actually kind of a long story. Basically, the database I'm working with right now is a test copy of a database and is several months old. We're going to be restoring over this test database with a more up to date copy of the live database. That's pretty much why I'm only looking to back up certain tables and procedures. Because when the restore is done from the live I'll lose the stored procedures that I've been working on in this environment.

I know it sounds long winded and kind of silly but I just wanted to be able to copy certain files and procedures out to text files and the like and then move back in whatever's required in to my test environment after the restore from the live environment's done.

How do I mark this as solved?

Evil Phil · 21-09-2009 06:54PM

In your first post click on the Edit button, then click on the Go Advanced button, then you should be able to select it from the dropdownlist beside the post title. Click on the Save button and your done.

kayos · 22-09-2009 08:00AM

whiterob81 wrote: »

It's actually kind of a long story. Basically, the database I'm working with right now is a test copy of a database and is several months old. We're going to be restoring over this test database with a more up to date copy of the live database. That's pretty much why I'm only looking to back up certain tables and procedures. Because when the restore is done from the live I'll lose the stored procedures that I've been working on in this environment.

I know it sounds long winded and kind of silly but I just wanted to be able to copy certain files and procedures out to text files and the like and then move back in whatever's required in to my test environment after the restore from the live environment's done.

Ahhhhh totally different to what I took from your first post. You just want to create release scripts. You can get tools to compare two DB's and create the change scripts for you (Redgate have one).

whiterob81 · 22-09-2009 09:03AM

kayos wrote: »

Ahhhhh totally different to what I took from your first post. You just want to create release scripts. You can get tools to compare two DB's and create the change scripts for you (Redgate have one).

sound, thanks. That sounds like it could come in handy. I'll google that

amen · 22-09-2009 12:42PM

put your stored procedures in a source code repositry.
also scripts that are need to for releases.

TSQL code for writing to a text file

Comments