January 11, 2008 at 1:25 pm
Hi All,
I am scripting database objects to file using SqlDMO and am having trouble getting the desired results for Tables. I want to remove all delimiters in output file, but cannot reach the desired result. The core part of the code is as follows (assume all variables are being set correctly):
DECLARE [variables]
SELECT @ScriptType = 4 | 1 | 64 | 32768 -- int datatype
, @Script2Type = 8388608 -- int datatype
EXEC @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
EXEC @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID, SourcePWD
SET @Sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @SourceObject + '").script'
EXEC @rc = sp_OAMethod @objServer, @Sql , @buffer OUTPUT, @ScriptType, @tmpFileName, NULL, @Script2Type
-- Disconnect
-- Destroy Object
I thought ScriptType Constant 32768 removes delimiters, but brackets [] are still showing in the output script.
Thanks in advance for the help.
January 28, 2008 at 8:39 am
As a workaround, I used a slightly different method that allows one to manipulate the SQL
before it is writtent to file. REPLACE is used to eliminate all brackets before output to file.
Stored proc WriteToFile calls Windows FileSystemObject to output @SqlOut to @FileName.
It's not pretty but gets what I need.
DECLARE [variables]
SET @FileName = ' '
SELECT @ScriptType = 1|4|512 -- see http://msdn2.microsoft.com/en-us/library/ms136218.aspx
, @Script2Type = 16|8388608 -- gives script method constant definitions
SELECT @method = 'databases("' + @SourceDB + '").' + @collection + '("' + @ObjNm + '").script('
+ RTRIM(CAST(@ScriptType AS CHAR)) + ',,,' + RTRIM(CAST(@Script2Type AS CHAR)) + ')'
EXEC sp_OAMethod @objServer, @method, @SqlOut OUTPUT
SET @SqlOut = REPLACE(@SqlOut,'[','')
SET @SqlOut = REPLACE(@SqlOut,']','')
EXECUTE master..WriteToFile @FileName, @SqlOut
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply