SQL Server Performance Forum – Threads Archive

How to get rid of this while taking script

Guys, I’m generating script of all sps as one file per object. But after the scripting it adds SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO in each file which i don’t want. How do i disable this so that my script should have only drop and create statement[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Please reply asap.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

I guess you use EM to generate scripts. I don’t know how to disable it, but you can remove it later (replace with empty string). I think awk can be used for such task. Here is the link to one awk implementation on windows:http://gnuwin32.sourceforge.net/packages/mawk.htm

Any useful text editor should be able to replace a search string in multiple files. At least Ultraedit can. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Yes Mirko,
I’m using EM to generate scripts.
Okay so now i will assume that there is no possibility of disabling those unwanted set statements.
Also i don’t want to use any other tool to replace them as i’m already using VB and ill do that in VB though but taking whole lot of time to open each file, search and replace and close the file. Thanks guys.

Hi ya, while the statements are ugly and Ihave often removed them for sake of tidiness, they are actually harmless and won’t cause your script to fail or anything… Do you need to remove them? Cheers
Twan

Hi Twan,
Actually i have created a VB appln. which reads all these script files one after other and creates them in the db which i have specified.
In my VB code what i do is, i select command till "GO" and then the next command, so it takes time. So finally i call execute atleast 6 times for a file.
So if the file does not have those ugly set statements i will need to make execute call only twice. So i want to remove them from being created.

Split the content by Go so that you have three parts in VB where last is the one that you need to execute Madhivanan Failing to plan is Planning to fail

Why can you not read the whole text from the file, and execute that? What is your problem with the harmless SET statements?

Yup, Im with Adrian. Whilst they may be spurious, you seem to have gone to huge amounts of work just to get rid of them. The overhead of remioving them all is probably more than the overhead of simply including them into the batch in the first place <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

quote:Originally posted by Madhivanan Split the content by Go so that you have three parts in VB where last is the one that you need to execute
Madhivanan
Failing to plan is Planning to fail

Hi Madivanan,
Thats not the case because when i split them i actually get 6 parts(not guranteed i guess)
Part 1>SET QUOTED_IDENTIFIER OFF
Part 2>SET ANSI_NULLS ON
Part 3>If exists object statement
Part 4>Create Object statement
Part 5>SET QUOTED_IDENTIFIER OFF
Part 6>SET ANSI_NULLS ON here i’m only interested in part 3 and 4 which may be not always be the case.
How to be sure that there will always be those set statements at the start of the file[|)]

quote:Originally posted by Adriaan Why can you not read the whole text from the file, and execute that? What is your problem with the harmless SET statements?

Hi Adriaan,
I had already given this a try but its erroring in VB.
Errors are like: some text missing or create has to be first statements.
These errors are because it reads the whole content and fires it as one line in VB

Are you executing through an ADO command object? Doesn’t ADO have an equivalent to the DAO pass-through query? You can ptq any valid T-SQL script as if it was through a QA window.

Yes adriaan,
I’m using ADO command itself.
And thats right that the content of file if i open in QA i can run all at one go but no idea why it errors thru VB if i try whole content at one GO.

You’re right, you can only execute a T-SQL script from a ADODB.Connection object provided that it doesn’t have a line that only says "GO". You could read the whole script into a String variable, then remove all instances of … vbCrLf & "GO" & vbCrLf … from the string, then execute the script. However, this would give you a problem for CREATE statements that must be the first one in a batch. Are you sure you want to be doing this in a VB app? If you can add a reference to the DAO library, you can take advantage of the much simpler object model, and enjoy the lack of limitation in this respect. DAO may be outdated, but it is SO much easier when you need a QA-like interface with SQL Server.

Hi ya, also be careful of scripts with comments that use the word GO in the comments… you don’t want the vb app to split it then… Cheers
Twan

]]>