March 5, 2013 at 3:22 pm
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!
March 7, 2013 at 12:36 am
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
March 7, 2013 at 7:10 am
Sorry, no good! It does exactly what i said in my post.
March 7, 2013 at 8:46 am
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.
March 7, 2013 at 10:07 am
You're welcome. Thanks for the feedback.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 21, 2020 at 8:23 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy