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

Question

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

manole_i

SSC Eights!

Points: 860
More actions
March 5, 2013 at 3:22 pm

#394318

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!

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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

manole_i SSC Eights! Points: 860 More actions · Answer 2

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

manole_i SSC Eights! Points: 860 More actions · Answer 3

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.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

You're welcome. Thanks for the feedback.

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

JediSQL SSCrazy Points: 2118 More actions · Answer 5

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