Pay attention that I intentionally specified the type of @Param1 as DateTime. Now launch SQL Server Profiler and run the .NET code. When done, don’t
stop the profiler, just look at the output. I see the following text for the RPC:Completed EventClass:

exec USP_TEST_PROCEDURE @Param1='2013-12-10 12:34:56.790'

Now copy this text, open SSMS, paste to the new query window and run it without
any changes. Now stop the profiler and make sure that the SSMS produced the same text in the profiler as ADO.NET app:

exec USP_TEST_PROCEDURE @Param1='2013-12-10 12:34:56.790'

Now select from the TEST_TABLE table. I see the following result:

ID VALUE
1 Dec 10 2013 12:34PM
2 2013-12-10 12:34:56.790

The question is why the two identical commands (from SQL Server Profiler’s point of view) produce different results?

It seems like profiler does not show correctly what is going on behind the scene. After I had ran the .NET app I expected to see the following:

Answers

It is correct that profiler doesn't show use the "truth" when you execute a stored procedure propely. The reason (methinks) is that when you execute a procedure propely you do a *binary* call to SQL Server. Showing a lots of zeroes and ones or some hex representation
of that binary call isn't very helpful to a human. So it has to do something to make that call readable to us humans. Basically, what we have cooked down this to is that you don't agree with the textual representation that Profiler uses and you want a more
... precise (?) textual representation from Profiler. That I can agree with. I am a little bit surprised by what you are seeing, though. I'm not a .NET programmer, but when I execute below code:

It could be an option. (I have just voted for the feature suggested by Steve Kass). However, the main point is that currently, profiler gives us an information which is not 100% accurate. And it should be fixed.

Profiler and SQL Trace are deprecated, and they will not change.

You may have better luck with extended events. But I doubt they will ever bother of that either. The problem is that the client sends TDS packages and no T-SQL statement, and any text representation will just be a model of those packages and they will never
reproduced the TDS packages with 100% accuracy. A good example is when you have a stored procedure that accepts a TVP. SQL Trace will show you a declaration of a table variable, INSERT statements into that table variable of the data, and then the call. If
there may rows in the TVP, the INSERT statement is unwieldy, and if there are more than 1000 rows, it may not even compile. (How it looks like with extended events, I don't know.) Whatever, at the actual execution there is no extra table variable, but
the data is streamed directly to the TVP, and this can be very efficient. Which you can't say about the INSERT statement, which has a large compilation overhead.

Thank you for the response. The question is not about how to fix my code. The question is about the confusing behavior of SQL Server Profiler.

Let’s imagine I have a 3rd party application and don’t have access to the source code. So I don’t know what SqlDbType is assigned to SqlParameter. Maybe it has SqlDbType.DateTime or maybe SqlDbType.VarChar. In this case the only way to see what is going
on is to use profiler. So I launch it and see the following:

exec USP_TEST_PROCEDURE @Param1='2013-12-10 12:34:56.790'

How do I know that the application is sending a datetime, not a string? It is not obvious. Is there any parameter in the profiler that can force the profiler to show this information?

It is correct that profiler doesn't show use the "truth" when you execute a stored procedure propely. The reason (methinks) is that when you execute a procedure propely you do a *binary* call to SQL Server. Showing a lots of zeroes and ones or some hex representation
of that binary call isn't very helpful to a human. So it has to do something to make that call readable to us humans. Basically, what we have cooked down this to is that you don't agree with the textual representation that Profiler uses and you want a more
... precise (?) textual representation from Profiler. That I can agree with. I am a little bit surprised by what you are seeing, though. I'm not a .NET programmer, but when I execute below code:

To make it more useful to you, it represents this as an EXEC command. Since there is no datetime literal in SQL Server, it needs to find a text represenation of the datetime value, and it converts the value to string using the format YYYY-MM-DD. Note that
at this point, Profiler has no information that the formal parameter is varchar(23).

When procedure is invoked the datetime value is converted to varchar(23), but this time in SQL Server, and the default format for datetime-to-string conversion in SQL Server is 'Mon Day Year hh:mm' (because of legacy).

To add to Erland's post, I filed a suggestion on connect to use neutral date formats when rendering RPC call parameters for this very reason. The response was "we won't be able to address this request anytime soon." They weren't kidding since
that was 6 years ago. Feel free to vote:

It is correct that profiler doesn't show user the "truth" when you execute a stored procedure properly. The reason (methinks) is that when you execute a procedure properly you do a *binary* call to SQL Server.
Showing a lots of zeroes and ones or some hex representation of that binary call isn't very helpful to a human. So it has to do something to make that call readable to us humans. Basically, what we have cooked down this to is that you don't agree with the
textual representation that Profiler uses and you want a more ... precise (?) textual representation from Profiler. That I can agree with.

Yes, you are right. I am not satisfied with the textual representation that SQL Server Profiler uses to show us stored procedures calls. I repeated my test using SQL Server 2012 and SQL Server 2012 Profiler. Unfortunately, I saw the same statement in the
TestData column. Then I decided to use Extended Events graphical user interface in SQL Server Management Studio 2012, because Microsoft is announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture. I created a new session, started
it and launched my .net app. Eventually, I saw the same statement for rpc_completed event:

Because this would be very tedious to read when there are 50 parameters. Please keep in mind that the case you encountered is a very unusual situation. (And, no, it is not possible to get the display above only when there is a type mismatch, because the
trace/x-event does not have the information about the parameter profile.)

In essence, my SP has not changed. It still inserts the data to TEST_TABLE and it has the same type of the only parameter (varchar(23)). However, as you can see, the first line in the profiler output is the declaration of the variable and we can clearly see
the type of that variable. It is datetime. So this test confirms that behind the scene a variable of datetime type is used in both scenarios. What is not clear is why profiler in the first case does not show the type of the variable, but in the second case
it shows that.

It could be an option. (I have just voted for the feature suggested by Steve Kass). However, the main point is that currently, profiler gives us an information which is not 100% accurate. And it should be fixed.

It might be done by introducing a new syntax for typed literals or by using parameter declarations, IMHO.

It could be an option. (I have just voted for the feature suggested by Steve Kass). However, the main point is that currently, profiler gives us an information which is not 100% accurate. And it should be fixed.

Profiler and SQL Trace are deprecated, and they will not change.

You may have better luck with extended events. But I doubt they will ever bother of that either. The problem is that the client sends TDS packages and no T-SQL statement, and any text representation will just be a model of those packages and they will never
reproduced the TDS packages with 100% accuracy. A good example is when you have a stored procedure that accepts a TVP. SQL Trace will show you a declaration of a table variable, INSERT statements into that table variable of the data, and then the call. If
there may rows in the TVP, the INSERT statement is unwieldy, and if there are more than 1000 rows, it may not even compile. (How it looks like with extended events, I don't know.) Whatever, at the actual execution there is no extra table variable, but
the data is streamed directly to the TVP, and this can be very efficient. Which you can't say about the INSERT statement, which has a large compilation overhead.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.