SQL Server - Insert Not Working
Just wanted to share a bit of fun I had yesterday. (Actually it wasn't very fun). I had a cfquery that was supposed to copy "Template" records linked to one entity inserting them linked to another entity. So, the file was using an Insert/Select. Never seen one? OK, it looks like this.
INSERT INTO
[Template]
(
TemplateName,
OwnerID,
...
)
SELECT
TemplateName,
<cfqueryparam value="#attributes.Target_OwnerID#" cfsqltype="cf_sql_integer">,
...
FROM
[Template]
WHERE
OwnerID = <cfqueryparam value="#attributes.Template_OwnerID#" cfsqltype="cf_sql_integer">
...
</cfquery>
So, we can all agree that insert/select is a great thing. However, this particular one was not working (on the live server). I tried the normal things. I cut the SQL from the .cfm and pasted it into SQL Server Management Studio (SSMS), replaced the params with the values I knew it would be using and ran it (inside of a transaction, of course). Guess what? It worked. So, it works running in SSMS but, not in a cfquery. Hmmm... So, next, I added an "output" clause to the insert/select. That way I could see any records it would be inserting, just to make sure it really wasn't inserting them. Then I added a cfdump that would only run for me. "What's an output clause?" you say. Here I'll show you.
INSERT INTO
[Template]
(
TemplateName,
OwnerID,
...
)
OUTPUT
INSERTED.*
SELECT
TemplateName,
<cfqueryparam value="#attributes.Target_OwnerID#" cfsqltype="cf_sql_integer">,
...
FROM
[Template]
WHERE
OwnerID = <cfqueryparam value="#attributes.Template_OwnerID#" cfsqltype="cf_sql_integer">
...
</cfquery>
<cfdump var="#insTemplates#" label="insTemplates" />
I ran that and guess how many records were inserted... 0. At this point I'm a bit frustrated. But, it begins to dawn on me that I've seen similar behavior before from SQL Server. There are time when the SQL Server will cache a bad plan for some SQL. In these cases, you can either have the database flush it's entire plan cache or you can alter the SQL enough to force it to make a new plan for your SQL. So, I added "AND 1=1" to my "where" clause.
Guess what? It works now.
This is a fairly edge case. I have not seen it very often. But, if you are positive that a cfquery is not returning what it should, before you jump out of your office window, try adding something innocuous to your "where" clause.
If it does work, feel free to send some happy thoughts my way and curse MS under your breath. :-)
I've often seen this happening - and it normally seems that if you restart CF, then the query will run fine.
It normally seems to happen when you make a structural change to a table or view, and don't change a query which is accessing the table / dependant view. (This happens especialy often when using select * queries!)
If you change the query by a single character - e.g. add or remove a space - this seems to be enough to build a new QEP - and everything works ok.
It seems to happen more often with queries which use cfqueryparam (which probably makes sense as if you don't use cfqueryparam **WHY NOT** then a new QEP will be created for each parameter variation.
Just to be clear: I do not use "*" in production and only added the "*" as an output clause to see if it was inserting anything. I know the dangers of using "*" and then changing columns. I hope no one reading this would use a "select *". And I certainly hope I didn't give any tacit endorsement to using "*".