Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

TSQL code for writing to a text file

  • 21-09-2009 11:19am
    #1
    Registered Users, Registered Users 2 Posts: 552 ✭✭✭


    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?


Comments

  • Registered Users, Registered Users 2 Posts: 610 ✭✭✭nialo


    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"'


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    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


  • Registered Users, Registered Users 2 Posts: 552 ✭✭✭whiterob81


    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


  • Registered Users, Registered Users 2 Posts: 2,494 ✭✭✭kayos


    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.


  • Registered Users, Registered Users 2 Posts: 610 ✭✭✭nialo


    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?


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 552 ✭✭✭whiterob81


    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


  • Registered Users, Registered Users 2 Posts: 7,468 ✭✭✭Evil Phil


    Can you update the thread to [Solved] please?


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    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


  • Registered Users, Registered Users 2 Posts: 552 ✭✭✭whiterob81


    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?


  • Registered Users, Registered Users 2 Posts: 7,468 ✭✭✭Evil Phil


    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.


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 2,494 ✭✭✭kayos


    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).


  • Registered Users, Registered Users 2 Posts: 552 ✭✭✭whiterob81


    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


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


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


Advertisement