December 6, 2007 at 2:26 pm
I took the sql of the view verbatim and replaced the select * with that sql statement, so yes, I had the same number of fields.
I don't want to use NEWID() for that guid field, I want that field to be NULL in the initial creation of the table. That field will only be populated when the record has been modified at some future point so will have an Autid ID generated at that time.
December 6, 2007 at 2:31 pm
I understand that you would like the field to be NULL; however, what I am asking is when you plugged a number into this field, the insert worked. Did you open up the table and verify that the new_id() went into the correct column?
December 6, 2007 at 2:33 pm
I am not sure what else we could try. I will do a little research on the INSERT INTO SELECT * FROM View to see if it has any restrictions.
December 6, 2007 at 2:34 pm
I'm sorry I misunderstood your question.
Yes, the guid went into the correct column.
December 6, 2007 at 3:01 pm
Ok this is what I am thinking. The view retrieves the records from the assoicated tables and place a value of NULL for the SPAuditID column; however, at runtime the insert is trying to insert the binary of another value other than NULL. Maybe a blank space or empty value or something, I do not know. This could explain why it is trying to convert an int.
But this is just brainstorming, as I have no evidence to support this. I need a break this is boggling my mind.
TO BE CONTINUED...
December 7, 2007 at 6:19 am
There's got to be something funky going on with some sort of settings or something. The reason I say this is because I've created a view and a table based on the code and I was able to do an the insert with the SELECT *, no issues. So, the fundamental process works. The issue is going to be identifying what thing is different.
Do you have non-ANSI settings or something in the view? Something like that? I'm just a bit stumped.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2007 at 7:03 am
The view has no out of the ordinary settings that I'm aware of. I created it by using the designer window, selecting the tables involved, checking the columns on, setting aliases, rearranging field output to correspond to my table.
I'm using Microsoft SQL Server Management Studio version 9.00.2047.00 to create view. The database is SQL Server 2005. Most of the data comes from a different database on the same server, so reference to the field includes the database name, owner, table and field name.
I don't know what else to check for.
December 7, 2007 at 7:20 am
What about collation?
If there is a difference between the databases that might cause some odd behavior, reordering columns, affecting how it deals with null values...mabye... It's just a SWAG.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2007 at 11:49 pm
Did you get a chance to check this out?
December 11, 2007 at 12:28 pm
I don't know enough to answer that question.
The databases are on two different servers with different versions of SQL Server.
My DBA is aware of the problem I'm having, so, with his help, we may be able to identify any differences.
If I resolve this issue, I'll post my findings.
Thanks for everyone's suggestions.
July 28, 2009 at 1:24 pm
My guess is your view has a hardcoded null.
This is an odd quirk in SQL Server where a NULL, which is not a value and therefore probably sholdn't have a type gets an implicit type of null and is stored as a null integer.
Because the view does not have defined types, the hardcoded null is assigned a type of INT. To get around this, you need to cast the hardcoded null in the view definition as a uniqueidentifier.
SQL guy and Houston Magician
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply