create procedure which contains special caracters from .sql file with sql powershell

  • Hello,

    I have a .sql file which is actualy the body of a stored procedure. The procedure containts characters like 'é, à, è...'. when i run the command:

    invoke-sqlcmd -server "servername" -database "databasename" -inputfile "filename.sql" against MS SQLServer, the procedure is succesfully created, but in place of characters listed above I have a '?'.

    As work around I found somthing like:

    PS SQLSERVER:\>$server="servername"

    PS SQLSERVER:\>$database="databasename"

    PS SQLSERVER:\> $query=get-content -path "path_to_file..."

    PS SQLSERVER:\> $connection=new-object System.Data.SqlClient.SQLConnection

    PS SQLSERVER:\> $connection.ConnectionString="Server={0};Database={1};IntegratedSecurity=True" -f $server,$database

    PS SQLSERVER:\> $command = new-object System.Data.SQLClient.SQLCommand($query, $connection)

    PS SQLSERVER:\> $connection.Open()

    PS SQLSERVER:\> $command.ExecuteScalar()

    PS SQLSERVER:\>$connection.Close()

    Now the troublesome characters are all in place, but the generated procedure is a loooooooong line.

    Is there a way to make this right with SQLPS? Is there a option(like sqlcmd -u)for unicode files?

    Thank you!

  • From http://stackoverflow.com/questions/11905649/unicode-support-for-invoke-sqlcmd-in-powershell/11911050#11911050

    Update 3 The encoding of the file seems to be the key. Looking at [System.IO.File]::ReadAllText, the MSDN doc states it will only detect UTF-8 or UTF-32 encoding. http://msdn.microsoft.com/en-us/library/ms143369(v=vs.90).aspx

    If I save the .sql file with UTF-8, using the -inputfile param [of Invoke-SqlCmd] works. You can choose UTF-8 when saving .sql file in SSMS...

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Sorry, no good! It does exactly what i said in my post.

  • Sorry again, my mistake. Is working. Strage is that I did the same steps 2 days ago and it did not worked. Anyway here is the steps:

    -copy the files in a new directory for test purpose and not loosing data. Save the files with UTF8 encoding option:

    1. create new files(same files actually) with UTF8 encoding

    PS SQLSERVER:\sql> foreach ($file in get-childitem -path "old path" | sort-object ascending)

    >> {

    >> try {

    >> $content = get-content $file.fullname

    >> $path = "new path" + $file.name

    >> $content | out-file -filepath $path -encoding UTF8 -force

    >> "Creates the file: $file.fullname"

    >> }

    >> catch {

    >> "Error at file creation: $file.name"

    >> }

    >>

    Create the objects on SQL Server

    2. Procedure generation - my .sql files are sql stored procedures, functions, views, etc...

    PS SQLSERVER:\sql> foreach ($file in get-childitem -path "p:\Document\SVN\trunk

    4G\DATABASE\db_objects\Socle\Bcm\" | sort-object ascending)

    >> {

    >> try {

    >> invoke-sqlcmd -server "servername" -database "databasename" -inputfile $file.fullname

    >> "Creation of tthe object: $file.fullname"

    >> }

    >> catch {

    >> "Error on running file: $file.name"

    >> }

    ...and it worked like a dream. Thank you opc.three you have opened my eyes.

    For me topic closed.

  • You're welcome. Thanks for the feedback.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Hi,

    I posted a request to fix this at Microsoft's User  Voice forum:

    https://feedback.azure.com/forums/908035-sql-server/suggestions/41700883

     

    Sincerely,
    Daniel

Viewing 6 posts - 1 through 5 (of 5 total)

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