CSI: Visual Studio – Unable to translate Unicode character at index X to specified code page

[Originally Posted By]: http://www.hanselman.com/blog/CSIVisualStudioUnableToTranslateUnicodeCharacterAtIndexXToSpecifiedCodePage.aspx

A crazy internal error from Visual Studio

A customer emailed me a weird one. I tend to have a sense for when something is up and when an obscure thing will turn into something interesting.

The person says:

…mysteriously most of my projects refuse to build.  “The build stopped unexpectedly because of an internal failure… something about unicode… blah blah”

There are a few messages out there on the web about it — even a really old hot fix.  What’s the best way to proceed with the VS team / MS?  Is there anyone actively interested in glitches like this?

My spidey-sense is tingling. First, when something says “internal failure” it means some fundamental expectation wasn’t met. Garbage in perhaps? He says “most of my projects” which implies it’s not a specific project. There’s also the sense that this is a “suddenly things stopped working” type thing. Presumably it worked before.

I say:

“Have you checked all the source files to make sure one isn’t filled with Unicode nulls or something?”

And says no, but sends a call-stack (which is always nice when it’s sent FIRST, but still):

Error    1    The build stopped unexpectedly because of an internal failure.
System.Text.EncoderFallbackException: Unable to translate Unicode character \uD97C at index 1321 to specified code page.
   at System.Text.EncoderExceptionFallbackBuffer.Fallback(Char charUnknown, Int32 index)
   at System.Text.EncoderFallbackBuffer.InternalFallback(Char ch, Char*& chars)
   at System.Text.UTF8Encoding.GetByteCount(Char* chars, Int32 count, EncoderNLS baseEncoder)
   at System.Text.UTF8Encoding.GetByteCount(String chars)
   at System.IO.BinaryWriter.Write(String value)
   at Microsoft.Build.BackEnd.NodePacketTranslator.NodePacketWriteTranslator.TranslateDictionary(Dictionary`2& dictionary, IEqualityComparer`1 comparer)
   at Microsoft.Build.Execution.BuildParameters.Microsoft.Build.BackEnd.INodePacketTranslatable.Translate(INodePacketTranslator translator)
   at Microsoft.Build.BackEnd.NodePacketTranslator.NodePacketWriteTranslator.Translate[T](T& value, NodePacketValueFactory`1 factory)
   at Microsoft.Build.BackEnd.NodeConfiguration.Translate(INodePacketTranslator translator)
   at Microsoft.Build.BackEnd.NodeProviderOutOfProcBase.NodeContext.SendData(INodePacket packet)
   ...

OK, so it doesn’t like a character. But a character in WHAT? Well, we’d assume a source file, but it’s important to remember that there’s other pieces of input to a compiler like path names, environment variables, commands passed to the compiler as switches, etc.

It says Index 1321 which seems pretty far into a string before it gets mad. I asked a few people inside and Sara Joiner says:

It looks like the only place in BuildParameters that we call TranslateDictionary is when transferring the state of the environment [variables] across the wire.

Ah, so this is splitting up name-value pairs that are the environment variables! David Kean says “ask him what his PATH looks like.” I ask and I get almost 2000 bytes of PATH! It’s a HUGE path, it looks like it may even have been duplicated and appended to itself a few times.

Here’s just a bit of the PATH in question. See anything?

\;C:\PROGRA~1\DISKEE~1\DISKEE~1\;C:\Program Files (x86)\Windows Kits\8.0\Windows
Performance Toolkit\;C:\Program Files\Microsoft SQL
Server\110\Tools\Binn\;C:\Program Files\Microsoft\Web Platform
Installer\;C:\Program Files\TortoiseSVN\binVN\???p??;C:\Program
Files\TortoiseSVN\bin;C:\PHP\;C:\progra~1\NVIDIA
Corporation\PhysX\Common;C:\progra~2\Common Files\Microsoft Shared\Windows
Live;C:\progra~1\Common Files\Microsoft Shared\Windows
Live;C:\q\w32;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;
C:\Windows\System32\WindowsPowerShell\v1.0\;C:\progra~2\WIDCOMM\Bluetooth
Software\;C:\progra~2\WIDCOMM\Bluetooth

See those ??? marks? That doesn’t feel like question marks to me. I open the result of “SET > env.txt” as a binary file in Visual Studio and it looks like it’s 3Fs, which are ? marks.

I think the text file was converted to ANSI

This makes me think that there’s unicode goo in the PATH that was converted to ANSI with it was piped. Phrased differently, this text file isn’t reality.

However, elsewhere in the Windows UI his PATH variable looks like different.

C:\Program Files\TortoiseSVN\binVN\�侱ᤣp䥠؉;

Sometimes that corruption in the path looks like this and you might assume it’s Chinese. No, it’s corruption that’s getting interpreted as Unicode. Interestingly the error said the naughty character was 0xD97C which is &#0xD97C; � which implies to me that something got stripped out at some point in processing and turned into the Unicode equivalent of ‘uh…’ Regardless, it’s wrong and it needs to be removed.

I ask him if cleaning his PATH worked and the customer just send me a one line response via email…the best kind of response:

========== Build: 12 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========

Yay! I hope this helps the next person who goes aGoogling for the answer and thought they were alone. Thanks to David Kean, Sara Joiner and Srinivas Nadimpalli for looking at the call stack and guessing at solutions with me!

Use parameterised SQL Server stored procedure in Excel database query

[Originally Posted By]: https://social.msdn.microsoft.com/Forums/en-US/d86fbdfa-82e2-4da7-92df-ae5f0f60090b/use-parameterised-sql-server-stored-procedure-in-excel-database-query?forum=isvvba

I managed to solve my problem.  Hopefully it will help you solve yours.

When you create a parameterized query in excel using a select statement like “Select * from mytable where xdate between ? and ?” two parameters are created:
“Parameter1”
“Parameter2”

When you change the command from a select statement to a stored procedure it should, but doesn’t, look for the same parameters.  After much trial and error I discovered excel is looking for:
“Parameter 1”
“Parameter 2”

Note the spaces.  While “Parameter1” is what was created with the excel query when using a select statement, “Parameter 1” is what excel is looking for when the command is referencing a stored procedure.

I found that creating the parameterized query as a stored procedure in the first place, instead of creating a select statement first and changing it later, seems to work just fine as long as the query is using the correct syntax.

That syntax is: “{CALL MyStoredProcedure (?,?)}”

So the steps on a new 2007 workbook:

1. go to Data tab, get external data from other sources, from Microsoft Query.
2. Select/Create DSN
3. Select a table and some columns to enable “next”
4. Click “next” until you can select “view data or edit query in Microsot Query”
5. Click Finish to open MS Query.
6. Click SQL button in toolbar
7. change SQL statement to “{CALL MyStoredProcedure (?,?)}” and click OK.
8. enter parameters.
9. close MS Query and tell Excel where you want the results.

You now have a parameterized SP as a Data connection in your workbook.  The parameters will have the spaces and will work properly.

There is probably a better way than this convoluted method, but at least it works.

  • Proposed as answer by wffOrdeith Saturday, September 26, 2009 5:23 PM
  • Marked as answer by danishaniModerator Saturday, October 04, 2014 3:59 PM

Saturday, September 26, 2009 12:26 AM

wffOrdeith

Wait until ActiveWorkbook.RefreshAll finishes – VBA

[Originally Posted By]: http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba

As commented:

‘~~> your code here before refresh

Activeworkbook.RefreshAll
DoEvents

‘~~> rest of your code here after refresh

What DoEvents does is like momentarily allowing Windows to take a break from Macro to process all pending events before returning to the macro.
So when you refresh your workbook and put DoEvents after, it will not resume the macro until the refresh is finished.

shareedit

I had the same issue, however DoEvents didn’t help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn‘s answer as a jumping-off point, I created the following solution, which works just fine for me;

Sub Refresh_All_Data_Connections()

For Each objConnection In ThisWorkbook.Connections
‘Get current background-refresh value
bBackground = objConnection.OLEDBConnection.BackgroundQuery

‘Temporarily disable background-refresh
objConnection.OLEDBConnection.BackgroundQuery = False

‘Refresh this connection
objConnection.Refresh

‘Set background-refresh value back to original value
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next

MsgBox "Finished refreshing all data connections"

End Sub

The MsgBox is for testing only and can be removed once you’re happy the code waits.

Also, I prefer ThisWorkbook to ActiveWorkbook as I know it will target the workbook where the code resides, just in case focus changes. Nine times out of ten this won’t matter, but I like to err on the side of caution.

EDIT: Just saw your edit about using an xlConnectionTypeXMLMAP connection which does not have a BackgroundQuery option, sorry. I’ll leave the above for anyone (like me) looking for a way to refresh OLEDBConnection types.

shareedit

Running a SQL Stored Procedure from Excel (No VBA)

[Originally posted by]: http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel/

We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better.

 

So today, I’ll show you how to easily make Excel run a Stored Procedure to get data.

Step 1: Data tab – > From Other Sources -> From SQL Server

 

 

Step 2: Enter Credentials. Your server name can be an IP address

 

 

Step 3: Choose any old table or view. Pick a small one because we’ll discard it later anyway.

 

 

Step 4: Excel will pop up the Import Data dialog box. Click Properties here (NOT THE OK BUTTON).

 

Step 5: Click on the Definition tab. There, change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input.

 

 

Step 6: Excel complains about something….blah…blah…blah. Click Yes – (as in yes I know what I’m doing).

 

 

Step 7: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.

 

 

Step 8: Marvel at your results

 

 

Notes:

  1. Excel will fire the Stored Procedure each time you “Refresh”
  2. If you have to pass a parameter, you can enter it in the command text like this:

     

  3. If you have to pass dynamic parameters you’ll have to turn to VBA. See this post.
  4. I assume you can do this with ORACLE databases too.
  5. I’ve yet to test whether this will fire a Stored Procedure that doesn’t return data. In other words, Stored Procedures that perform Insert, Update or Delete actions. I assume that if you can, there is the possibility of updating SQL from Excel through a simple connection. Pretty cool.

Running Advanced SQL Stored Procedures from Excel

[Originally posted by]: http://datapigtechnologies.com/blog/index.php/running-advanced-sql-stored-procedures-from-excel/

A while back, I posted two articles that showed you how to run Stored Procedures from Excel.

In “Running a SQL Stored Procedure from Excel“, I showed you how to fire a simple SQL Stored Procedure that returns a simple dataset. In “Running a SQL Stored Procedure from Excel with Dynamic Parameters“, I showed you how to pass parameters to a SQL Stored Procedure so you can filter the returned dataset.

.

In both of those examples, the common denominator is that the Stored Procedure can only contain a select statement. For example, ‘Select * from Market Sales‘.

.

But in some cases, your Stored Procedures may need to contain advanced functionality such as; creating temp tables, deleting data, updating data, etc. In these cases, you can’t simply fire the Stored Procedure from Excel. You’ll need to adjust the Procedure so that the results will return to Excel.

.

The Cause of the Problem

When you create a new Stored Procedure in SQL Server, you will automatically get a line that reads SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is intended to improve performance by suppressing unnecessary messages to the client.

.

However, this will also suppress the returning of data to Excel, because SET NOCOUNT ONtells SQL Server that there is no need for this information to be passed back to the client.

.

The Fix

In this example, procedure, I’m creating two Temp tables in the procedure then creating a select statement which returns data from the two created tables. In order to be able to return the data to Excel with this Stored Procedure, I will need to add a line at the end of the Procedure that reads SET NOCOUNT OFF.

.

.

By wrapping your Stored Procedures with SET NOCOUNT ON … SET NOCOUNT OFF, you can fire all kinds of Stored Procedures from Excel:

  • Stored Procedures that create and use Temp Tables
  • Stored Procedures that contain Delete and Update Statements
  • Stored Procedures that contain Truncate and Insert Statements

.

In short, you can use this trick in conjunction with the other tricks you learned (in the other articles mentioned above) to trigger virtually any SQL Stored Procedure directly from Excel!

Running a SQL Stored Procedure from Excel with Dynamic Parameters

[Originally posted by]: http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

In my life-changing post, Running a SQL-Stored Procedure from Excel, I showed you how to make Excel interact with a SQL server Stored Procedure.

.

Since that post, I’ve had several requests to cover the topic of passing dynamic parameters to a Stored Procedure. So today, I will FINALLY show you.

.

Step 1: Data tab – > From Other Sources -> From SQL Server

.

Step 2: Enter Credentials.

.

Step 3: Choose any small table or view. It doesn’t matter which one because we’ll be changing the connection anyway.

.

Step 4: In the Data Connection Wizard dialog, give your connection a friendly name that you can remember. This is how you’ll point to this connection in VBA. In this case, I’m calling this connection MYSERVER.

.

Step 5: Excel will pop up the Import Data dialog box – Click Properties here (NOT THE OK BUTTON).

.

Step 6: Click on the Definition tab. Here, you’ll want to change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input. As you can see, the SQL statement is simply a call to the Stored Procedure along with the Parameter name (in this case, the procedure is expecting one parameter that accepts a market name).

.

Step 7: Excel warns you about something unimportant – Click Yes (which in this case, means go away).

.

Step 8: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.

.

Step 9: Note the results you get from your original hard-coded command you entered in Step 6. At this point, you know your connection is working.

.

Step 10: Create a mechanism to dynamically select and enter your parameters.

In my case, I need to pass a market name to my Stored Procedure. So I added a simple dropdown where my selection goes to Cell B2. The mechanism you choose to select your parameters is not the important thing here. The important thing is to note where the final selection will be housed. In this example, my market selection will end up in Cell B2.

.

Step 11: The final step is to copy and paste this Macro into a standard module. Notice that all we are dong here is changing the definition fo the CommandText property of the Connection. This basically means that as long as you can point to the correct connection (MYSERVER in this case), you can rebuild the CommandText on the fly. Here, I’m rebuilding the CommandText to point to my chosen cell range (cell B2 – where my market selection is housed).

Sub RefreshQuery()
 
    With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
        .CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value &"'"
    End With
    ActiveWorkbook.Connections("MYSERVER").Refresh
   
End Sub

At this point, it’s just a question of firing the Macro each time your parameter selection changes.

Now you’re cooking with gas!

Searching for marked (selected) text in Emacs

[Originally posted by]: http://stackoverflow.com/questions/202803/searching-for-marked-selected-text-in-emacs

I use emacs for viewing and editing code and other text files. I wanted to know if there is a way to search forward or backward for text which is marked in the current buffer. Similar to what I can do in notepad or wordpad. As in can I mark some text in the buffer and do a C-s or C-r and be able to search with the marked text without actually typing in the whole search text?

Thank you,

Rohit

shareedit

Yes. M-W (to get a copy of the selected text) C-s <RET> C-y <RET>. Then repeat C-s as needed. Similarly for C-r.

shareedit

Another option I use quite often is C-s C-w to search for the word after the current mark. Hitting C-wrepeatedly increases the search with additional words (e.g., C-s C-w C-w C-w searches for the 3 words after the current mark).

Similarly, C-s C-y searches for the rest of the line after the current mark and C-s C-M-y searches for the character after the mark. These are both repeatable in the same way.

shareedit

Other answers describe how to search for copied text, or how to search for the word at point. But none of them actually describe how to “search with the marked text.”

Adding the following hook will make it so that the currently-selected text is the text used for an isearch:

(defun jrh-isearch-with-region ()
  "Use region as the isearch text."
  (when mark-active
    (let ((region (funcall region-extract-function nil)))
      (deactivate-mark)
      (isearch-push-state)
      (isearch-yank-string region))))

(add-hook 'isearch-mode-hook #'jrh-isearch-with-region)

Tip: This pairs nicely with expand-region.

shareedit

There is a great function for this: isearch-forward-symbol-at-point. It highlights all occurrences of the word where your point is located – no need to place the point at the beginning of the word. Then you can move to next or previous with C-s or C-r.

Note that it is an exact match: if you use it on hi it won’t match chill for instance.

I mapped if to command-f (mac OSX): (global-set-key (kbd "s-f") 'isearch-forward-symbol-at-point) in the init file.

shareedit