Here is a great example of looking at code too long. Taking a break and coming back to look at it again from a different angle. We are porting over an Oracle database to SQL Server. We are using the SQL Server Migration Assistant (nice tool, I’ll write another post about it later) and need to test the functionality designed in Oracle that works differently in SQL Server. Say it ain’t so. 🙂
We have a few more tables than this. I am just scaling this down to a few tables for example sake.
I have two tables. Table one “Site”. Table two “Customer”.
I am using Site.Id as the foreign key in Customer. I added data into my site table with a simple insert to test things out. Note the witty insert data as well. (cough)
INSERT INTO SITE
, ‘Test SQL Site’
, ‘Description of Test SQL Site’
I then tried to use this code to insert data into the customer table. Using one of our stored procedures. They were designed as CRUD methods on the Oracle side and again handled a bit differently on the SQL Server side.
DECLARE @RC int
DECLARE @P_ID uniqueidentifier
DECLARE @P_CUSTOMER_CODE nvarchar(max)
DECLARE @P_NAME nvarchar(max)
DECLARE @P_SITE_ID uniqueidentifier
DECLARE @P_CREATED_BY nvarchar(max)
set @P_ID = NULL –(SELECT top 1 id from matms.CUSTOMERS)
set @P_CUSTOMER_CODE = ‘Test Customer Code’
set @P_NAME = ‘Testing Customer Name’
set @P_SITE_ID = (select top 1 id from [matms].[SITE])
set @P_CREATED_BY = ‘Tester_001’
EXECUTE @RC = [matms].[matms].[CUSTOMERS_PKG$SAVE]
I was getting this error:
Msg 515, Level 16, State 2, Procedure CUSTOMERS_PKG$SAVE, Line 28
Cannot insert the value NULL into column ‘ORGANIZATION_ID’, table ‘CUSTOMERS’; column does not allow nulls. INSERT fails.
The statement has been terminated.
What? I just manually inserted the data. I did a quick select to confirm, I was not insane. (the jury is still out on the insane part)
SELECT * FROM SITE
Data returned! Site.Id was there. What is going on here?
I don’t usually use uniqueidentifiers for primary keys. So, in my ignorance I thought maybe, just maybe I was doing something wrong. I looked up the use of Uniqueidentifiers on google and read the information on its use on MSDN.
Seems I am not doing anything against the norm or wrong. So, even though the fields are both Uniqueidentifier data types I had to be doing something wrong on the inserts. After all I just confirmed the data was there. I tried explicitly converting to different data types. Still the same error because SQL Server, depending on the data type you are converting is implicitly converts the data types. http://tinyurl.com/d3vvgv
Well, after banging my head against the wall, I moved on to another project to come back to it after lunch.
When I looked at it again. There was the problem! Plain as day!
Anyone see the problem?
I was working on the dev server and calling the stored procedure on the production (or will be) server that had no data in it! What a dumbass I am. At least I caught it before someone else did. I even posted the code on a few forums to try and get help. 30 + people viewed it and no one caught it.