TSQL stored procedure (or script) to generate scripts

Question

TSQL stored procedure (or script) to generate scripts

Rob

Ten Centuries

Points: 1035
More actions
September 4, 2019 at 11:02 am
Go to Answer
#3677154
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) .
Sue_H

SSC Guru

Points: 90860
More actions
September 4, 2019 at 8:13 pm
Answer
#3677366

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

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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.

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)

DesNorton SSC-Insane Points: 24386 More actions · Answer 2

You could also try outputting the results directly to file

How to post data/code on a forum to get the best help.

Rob Ten Centuries Points: 1035 More actions · Answer 3

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

Rob Ten Centuries Points: 1035 More actions · Answer 4

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: 1004339 More actions · Answer 5

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

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)

Rob Ten Centuries Points: 1035 More actions · Answer 6

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: 90860 More actions · Answer 7

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

Sue