13

I just want to call the GenerateScript method of Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator from PowerShell.

#C

public void GenerateScript(
    IScriptFragment scriptFragment,
    out string script
)

I found this, but I do not get it to work

$sg = new-object  Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator

$sql = 'select * from PowerShell'

$out = ''
$sg.GenerateScript($sql, [ref] $out)

$out

this gives 

Cannot find an overload for "GenerateScript" and the argument count: "2".
At line:6 char:19
+ $sg.GenerateScript <<<< ($sql, [ref] $out)
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Edit:

Current version is

$sql = 'select * from PowerShell'

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = ''
$fragment = $parser.Parse($sr,([ref]$errors))

$out = ''
$sg.GenerateScript($fragment,([ref][string]$out))

$out

But I get an error in line 

$fragment = $parser.Parse($sr,([ref]$errors))



Cannot find an overload for "Parse" and the argument count: "2".
At line:11 char:26
+ $fragment = $parser.Parse <<<< ($sr,([ref]$errors))
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

I'm trying to convert 

    IList<ParseError> errors;

    using (StringReader sr = new StringReader(inputScript))
    {
        fragment = parser.Parse(sr, out errors);
    }

Edit:

OK this works:

$sql = @'
select * from PowerShell -- a comment
where psRefnr = 1
'@
$options = new-object Microsoft.Data.Schema.ScriptDom.Sql.SqlScriptGeneratorOptions

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator($options)
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = $null
$fragment = $parser.Parse($sr,([ref]$errors))

$out = $null
$sg.GenerateScript($fragment,([ref]$out))

$out

and generates ( it removes the comment as intended )

SELECT *
FROM   PowerShell
WHERE  psRefnr = 1;
Community
  • 1
  • 1
bernd_k
  • 11,558
  • 7
  • 45
  • 64
  • "but I do not get it to work": Please expand: are you getting an error/warning? Is the behaviour you're getting not what you expect: what do you expect, and what are you getting? – Richard Mar 11 '11 at 18:18
  • possible duplicate of [How to call method with output parameters in Powershell?](http://stackoverflow.com/questions/821744/how-to-call-method-with-output-parameters-in-powershell) – Joey Mar 11 '11 at 18:31
  • The question there is WMI related and the answer doesn't apply. – bernd_k Mar 11 '11 at 18:42
  • You are again passing the wrong type of parameter. You are passing a string for $errors to Parse, when it expects a IList. – CodeNaked Mar 11 '11 at 20:46
  • Forgot to mention, try setting $errors to $null. By setting it to '' you are making it a string. – CodeNaked Mar 11 '11 at 20:52
  • Please can someone add a tag sql-parser? – bernd_k Mar 12 '11 at 06:51

2 Answers2

3

I believe your issue is with your first parameter, which should be a IScriptFragment. You are passing a string.

You would need to pass something that derives from TSqlFragment. Using something like the TSql100Parser.ParseStatementList method, you will get a list of fragments.

EDIT: This blog post has a similar issue to your second error.

CodeNaked
  • 40,753
  • 6
  • 122
  • 148
1

Not entirely sure how this works with Powershell, but in normal C# you need to call an out parameter with the keyword "out" instead of the "ref" you have. Sorry if this is off base, but figured it might help.

skaz
  • 21,962
  • 20
  • 69
  • 98