When does a SQL Table think it's a View?

By BG -
Try this one out:
  1. Create a SQL database
  2. Create a couple of tables
  3. Create a view that queries those tables and make sure to include a calculated column
  4. Create an empty table from the view using SELECT INTO...WHERE 1=0
  5. Drop the view
  6. Rename the empty table to the name of the dropped view
  7. Try and insert some data into the table
  8. Scratch your head...
Yup, SQL still thinks the object with the same name is a view and because it includes a calculated column it won't let you insert data into it.

To solve the issue, execute the sp_refreshview proc using the name of the new table.

Comments

Post a Comment

Popular posts from this blog

MSDTC in Windows Core

By BG -
Simple one this... How can we enable inbound transactions in MSDTC on a core machine? Well we use PowerShell of course! :-) PS C:\>Set-DtcNetworkSetting -InboundTransactionsEnabled $True Nice one.
Read more

The Dreaded 403 PowerShell Remoting Error

By BG -
Image
If you have worked with PowerShell remoting then you will have seen this error before: : Connecting to remote server BLAH failed with the following error message : The WinRM client received an HTTP status code of 403 from the remote WS-Management service. For more information... It is not a happy message, especially when you have been using PowerShell to remotely manage the particular server for ages! So then you try remoting from another client and it works! You go back to your original client and try remoting to anything else and it fails...dohh! "But this worked just yesturday!" you scream. Ahh, little things can make a big difference and in my case the issue was related to a VSTS Agent update that I did the day before. In order for the new version of the agent to communicate with VSTS in the cloud I needed to set a WinHTTP proxy. Once the agent was configured I could use a .proxy file in the agent directory instead...but I forgot to remove the WinHTTP proxy and in s...
Read more

Biztalk vs OIC

By BG -
Image
 So we are developing integration solutions using Biztalk 2016 and recently we started working with Oracle's Integration Cloud iPaaS service. We have found it quite painful and limiting and until this week have been singing the praises of Biztalk, saying that it's just far easier to work with and more functionally rich. Well... This week I had what seemed a simple task. Take an OAGIS BODFailureMessage (OAGIS is awesome) and send the details to Service Desk Plus, then put the resultant Request ID back into the original message and send it onwards. Seemed pretty straightforward. Problem #1 Service Desk Plus has an API that enables us to POST a Request BUT the API does not accept a message-body; it wants you to send the details of the request (which is a JSON payload) as a parameter called input_data.  Now from Postman, this is quite easy. Simply add a key-value pair in the Body using the x-www-form-urlencoded option like so: However, when it comes to doing the same thing in Bi...
Read more