1

I have a Powershell script that will take the schema and data on some of the tables.

The relevant script being:

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
   # Scripter object
   $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s) 

   # Set settings
   $scrp.Options.AppendToFile = $True      # Append to exisiting CREATE Database above
   $scrp.Options.AllowSystemObjects = $False
   $scrp.Options.ClusteredIndexes = $True  # Include the clustered index definitions
   $scrp.Options.DriAll = $True            # Include the declarative referential integrity objects
   $scrp.Options.ScriptDrops = $False      # Include the IF EXISTS..DROP statements
   $scrp.Options.IncludeHeaders = $False   # Include header information llike the date and time the script was generated
   $scrp.Options.ToFileOnly = $True        # Generate the script to a file or store it in a string as an output as well
   $scrp.Options.Indexes = $True           # Include indexes
   $scrp.Options.Permissions = $True
   $scrp.Options.WithDependencies = $False  # Include all other dependent objects in the script

   # Get data if the table is specified in the array 'SqlGetDataFor'
   if ($SqlGetDataFor -eq $ScriptThis.Name){
       $scrp.Options.ScriptData = $True
   } else {
       $scrp.Options.ScriptData = $False
   }

   $ScriptFile = $ScriptThis -replace "\[|\]"
   $scrp.Options.FileName = "SQLSERVER.SQL"

   #This is where each object actually gets scripted one at a time.
   $scrp.EnumScript($ScriptThis) #+ "`r GO `r " | out-File "$outFile\test2.SQL" -Append
}

And when the code runs, even though it does script the data on the tables I specified, after 99 rows, it will insert a 'GO' in between

enter image description here

I then tried running SSMS's script generator and noticed it did the same thing. I wanted to make sure there wasn't a setting I was missing that would remove that 'GO'.

Right now my usage is that we have a build server which generates this script. Then I parse out the generated file by delimiting it by the word 'GO' just so that I can verify each executed query. But since this GO is in between the INSERTS, everything after the INSERT is skipped because I believe it doesn't keep the IDENTITY_INSERT = ON when using System.Data.SqlClient.SqlCommand.

4
  • 1
    Sorry having a tough time understanding your question, what are you asking? BTW, I don't believe the GO statement should have any affect on the IDENTITY_INSERT statement, as long as everything is executed within the same session. Also if you could provide a text-version of the SQL that's generated instead of a partial screenshot, that would be helpful here.
    – J.D.
    Jul 21 '21 at 16:40
  • 1
    I agree with @j.d. the problem problem is unrelated to IDENTITY_INSERT. GO is not a T-SQL statement so the batch will error if SQL Server tries to execute will error when it tries to execute it (due to use of System.Data.SqlClient.SqlCommand). You can execute a script with GO batch terminators using SMO as described here.
    – Dan Guzman
    Jul 21 '21 at 19:31
  • Did you play with the scripters Batch Size setting? docs.microsoft.com/en-us/dotnet/api/… Jul 21 '21 at 21:25
  • @J.D. I guess I was wondering if I can increase the 99 rows or why it's there in the first place. As for the batch size, I did try to modify it by decreasing and increasing and the generated script was unchanged. I recall seeing the default value being 1 so I didn't dig down too much Jul 22 '21 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.