TSQL stored procedure (or script) to generate scripts

  • r.gall

    SSC Eights!

    Points: 864

    Is it possible to have a stored procedure or script that I can run which will generate a script in a new query window without truncating data?

    I am aware of the options in SSMS to increase column width/characters returned, however my data exceeds this length. My aim is to streamline my process of scripting data in one database, modifying the script, and running it against another database on a different server on a physically separate network.

    I have 2 tables to script data for (tlkpProduct, and tlkpModule). When I go to Tasks > Generate scripts... I can save the results to a new query window or file and save as .sql, but before I can run the script on the other server, I need to modify it in the following ways:

    1. remove lines that say

      "SET IDENTITY_INSERT [dbo].[tlkpModule] ON

      GO"

      because the data i am scripting is coming from a db with primary keys, and is going into a db that cannot have primary keys defined.

    2. insert some code at the start of the generated script delete the existing data, which is:

      "TRUNCATE TABLE [dbo].[tlkpProduct]

      GO

      TRUNCATE TABLE [dbo].[tlkpModule]

      GO"

    I wrote a stored procedure which was a series of select statements, also using SET NOCOUNT ON so I didn't get the "rows affected" lines. However I noticed that the resulting data was getting truncated because of the limitation of about 8k characters per column. Each insert statement could exceed ~8k characters because I am scripting columns of type VARCHAR(MAX) .

     

  • Jeff Moden

    SSC Guru

    Points: 994523

    The script is likely generating the correct amount of code and you're just having problems displaying it because of the limitations you spoke of in SSMS.  You can prove this one way or another by checking the DATALENGTH() of the variable you're building the script into it.  If it's essential that you be able to view the full script, post back and I'll show you a nice little trick to do so.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • DesNorton

    SSC-Insane

    Points: 22637

    You could also try outputting the results directly to file

  • Sue_H

    SSC Guru

    Points: 90142

    Another possibility is using xml to work around that issue. It has some "gotchas" - some of the methods you can find in this post:

    How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

    Sue

     

  • r.gall

    SSC Eights!

    Points: 864

    Thanks, I tried, however outputting to file also seems to have the truncating problem

  • r.gall

    SSC Eights!

    Points: 864

    I tried putting the results in to a variable and selecting the datalenght() of that, I can see that the lenght is longer so I assume it does contain all the data I need that represents the sql script.

    Yes - please do share a way to get at the complete data that I can try

  • Jeff Moden

    SSC Guru

    Points: 994523

    r.gall wrote:

    I tried putting the results in to a variable and selecting the datalenght() of that, I can see that the lenght is longer so I assume it does contain all the data I need that represents the sql script.

    Yes - please do share a way to get at the complete data that I can try

    Did you read the post at the link Sue provided?  Just use the method there as an iTVF (Inline Table Valued Function).  I'm talking about the solution with the reference to [processing-instruction(x)] FOR XML PATH('') in it.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • r.gall

    SSC Eights!

    Points: 864

    Thanks Sue, I read the post. The stuff about XML was a bit over my head, however there was a nifty answer in the comments that resolved this for me, it was:

    "1. set query options to "Results to Grid", run your query

    2. Right click on the results tab at the grid corner, save results as any files"

    Originally I was copying the results and pasting in to notepad, it looks like this is where the truncating was happenning. I was also getting truncating when setting the results to output to file. Instead, as suggested if I right click and choose to save the results, the data is not truncated regardless of the setting in the SSMS options dialogue box.

    I can now achieve what I need to do by using UNION to join all the bits of my sql script together as a single column, and saving those results out as a .txt file.

  • Sue_H

    SSC Guru

    Points: 90142

    Glad you got it working - Thanks for posting back with the details!

    Sue

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply