how to get the full sql query including parameters

Question

I have recently leaped into parametrized queries in SQL. I am now getting a complaint about a date when executing the query

The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.

When I get the query I get the parametrized query string

SELECT * FROM Table WHERE field = @p_param

What I need to get in order to debug this is

SELECT * FROM Table WHERE field = "2012-03-12 14:09:00"

How on earth do I do this? I suspect it is something simple but I just can't see it!

The datetime parameter is being added with the following:

sql2.Parameters.Add("@p_UpdateTime", SqlDbType.DateTime).Value = DateTime.Parse(updateTime);

and uptime is being set with

String updateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ff");

Post the code where you are setting up the query and then how you are printing the full query. From the look of it maybe you've accidentally included the p_param variable as a string? — BoredAndroidDeveloper, Mar 12 '12 at 14:14
The point is: the SQL command is **never** converted to the second string you expect! The SQL command with the parameter is sent to SQL Server, along with the list of parameters. The parameter values are **never** actually replaced in a SQL command as a string - so you cannot get that representation - it won't ever exist! — marc_s, Mar 12 '12 at 14:15
this is already answered. http://stackoverflow.com/questions/2000343/datetime-parse-issue-not-support-in-system-globalization-gregoriancalendar — PraveenVenu, Mar 12 '12 at 14:17
Can you not run SQL profiler against the db to see the command being executed? — Paulie Waulie, Mar 12 '12 at 14:19

score 1 · Answer 1 · answered Mar 12 '12 at 14:18

1

Use the SQL Server Profiler (ships with all sql servers). It will show full commands sent to SQL server.

answered Mar 12 '12 at 14:18

Pasi Savolainen

2,460
1
22
35

I've always ran a query to get the recent ran queries, any links to a guide to using profiler to get the query? – Neo Mar 12 '12 at 14:20
I've always gone the open-it, start the bog-standard trace, execute program until just-the-query and then pause trace. The query is usually the newest or one of them. – Pasi Savolainen Mar 12 '12 at 14:42

score 1 · Answer 2 · answered Mar 12 '12 at 14:20

1

Open Sql Server Profiler and trace what is executed against your Sql Server. See a basic tutorial on how to use the tool.

answered Mar 12 '12 at 14:20

RePierre

9,358
2
20
37

the optionj it shows under tools is not present on the server 2005 sql management and also not under my 2010 one either – Neo Mar 12 '12 at 14:23
Take a look on [MSDN](http://msdn.microsoft.com/en-us/library/ms181091(v=sql.90).aspx) on how to open it. – RePierre Mar 12 '12 at 14:30

score 0 · Answer 3 · answered Mar 12 '12 at 14:17

0

You can convert your parameter like this :

CONVERT(VARCHAR(19), @p_param, 120)

where 120 is the convert code for yyyy-mm-dd hh:mi:ss(24h).

answered Mar 12 '12 at 14:17

Spiky

509
4
11

score 0 · Answer 4 · answered Mar 12 '12 at 14:25

It looks like you're sending the string value. This is problematic due to DateTime.Format changing depending on the current thread culture.

Try something like this when passing in the parameters:

DateTime dateParam = GetDateParam();

_sqlCommand.Parameters.Add(
    new SqlParameter
    {
        DbType = DbType.Date,
        ParameterName = "@p_param",
        Value = dateParam 
    });

score -1 · Accepted Answer · answered Mar 12 '12 at 14:34

-1

I have now fixed it, I was using ff for seconds when I needed to use ss

answered Mar 12 '12 at 14:34

Neo

2,305
4
36
70

who ever marked this as a -1 please state why! It was the issue that was causing the problem just because I eventually spotted it >:( – Neo Mar 13 '12 at 15:18
Thansk Martin I thought I had :S – Neo Apr 05 '12 at 14:36
@Dan thanks for reviving an old thread that is 2 years old, the issue was the query not how to output the paramatized query, if you wish for that try another search as there is another question on here with that answer. – Neo Mar 18 '14 at 16:20
@Neo It's not my fault you asked the wrong question. I "revived" it because it was the #1 search result. It doesn't matter how old the question is, the information should be updated to be correct or it should be deleted as a whole. – Dan Bechard Mar 18 '14 at 20:14
@Dan understood in that case I will add the code I use for handling this to the answer. – Neo Mar 20 '14 at 14:22

how to get the full sql query including parameters

5 Answers5