February 16, 2010 at 5:00 am
I am trying to insert into an SQL Server table from Oracle. The SQL Server table has a column "Id" set as a uniqueidentifier, Identity=False, Allow Nulls=False.
If I try to compile a procedure with the code below
INSERT INTO MYTABLE@SQLSERVER ("Id","Name") VALUES (1,'Fred');
I get
“Id” invalid identifier
If I remove Id the code compiles but I get a runtime error...
INSERT INTO MYTABLE@SQLSERVER ("Name") VALUES ('Fred');
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_Execute: errors.h (1492): ; [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Id', table 'SQLSERVER.dbo.MYTABLE'; column does not allow nulls. INSERT fails.[unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL State: 00000; SQL Code: 3621)
I need to be able to insert the Id, how do I achieve this?
February 16, 2010 at 5:19 am
Few things:
1) INSERT INTO MYTABLE@SQLSERVER ("Id","Name") VALUES (1,'Fred');
doesn't look like compatible to insert syntax for MSSQL
Meaning
MYTABLE@SQLSERVER...is not MS SQL syntax
MYTABLE.SQLSERVER ..is correct syntax
2)Configure your Oracle Server as Linked server in SQL.
3)Can you give us MYTABLE structure in SQL.
February 16, 2010 at 5:30 am
Just to be sure... transaction is running on Oracle, inserting on remote SQL Server database - is that correct?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 16, 2010 at 5:33 am
That's correct.
February 16, 2010 at 5:38 am
I mocked up the previous examples, here is the real thing....
USE [FirstClassUpgrades]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 02/16/2010 12:37:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[Id] [uniqueidentifier] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
In Oracle pl/sql
CREATE OR REPLACE PROCEDURE TEST_SQLSERVER_INSERT AS
BEGIN
INSERT INTO MYTABLE@FIRSTCLASSUPGRADESTEST ("Id","Name") VALUES (1,'Test it');
END TEST_SQLSERVER_INSERT;
Compile.....
PL/SQL: ORA-00904: "Id": invalid identifier
Okay, so I try to remove "Id"...
CREATE OR REPLACE PROCEDURE TEST_SQLSERVER_INSERT AS
BEGIN
INSERT INTO MYTABLE@FIRSTCLASSUPGRADESTEST ("Name") VALUES ('Test it');
END TEST_SQLSERVER_INSERT;
It now compiles, but at runtime....
begin
TEST_SQLSERVER_INSERT;
end;
I get...
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_Execute: errors.h (1492): ; [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Id', table 'FirstClassUpgrades.dbo.MyTable'; column does not allow nulls. INSERT fails.[unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL State: 00000; SQL Code: 3621)
ORA-02063: preceding 2 lines from FIRSTCLASSUPGRADESTEST
ORA-06512: at "CMDP.TEST_SQLSERVER_INSERT", line 4
ORA-06512: at line 2
February 16, 2010 at 5:46 am
This might be caused by uppercase/lowercase missmatch.
Check how mytable.id column looks in terms of upper/lowercase spelling on SQL Server catalog - be sure to use exactly same upper/lowercase spelling on Oracle side query.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 16, 2010 at 5:50 am
I wish it was that simple. It's related to "Id" being set as a uniqueidentifier.
February 16, 2010 at 5:59 am
Globalidentity (2/16/2010)
I wish it was that simple. It's related to "Id" being set as a uniqueidentifier.
That's a theory you can easily test - create a second table setting "Id" as a normal column of the number datatype then try to insert on it.
Next move depends on test result 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 16, 2010 at 7:40 am
Changing the properties in SQLServer to default value of newid() and then removing "Id" from the Oracle procedure then compiles and suceeds, so pretty sure it is because it is of datattype uniqueidentifier. 🙁
February 16, 2010 at 8:00 am
I've also tried this with a second table with Id of type Bigint not null and everything works fine...It's definitely because it set to uniqueidentifier.
:ermm:
February 17, 2010 at 8:31 am
Globalidentity (2/16/2010)
I've also tried this with a second table with Id of type Bigint not null and everything works fine...It's definitely because it set to uniqueidentifier.
Nice job.
If a uniqueidentifier column is needed on SQL Server side table I see a staging table with not such a column in your future... you can always add a trigger to uniqueidentifierless staging table so to move data to its final destination.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 18, 2010 at 3:10 am
Thanks Pablo, the development team on the SQL Server side didn't like this suggestion when I made it, but we've got round the problem by changing the column type. I guess there is still some way to go with odbc!
March 2, 2010 at 2:23 pm
I am a little confused. On the oracle server you attempted to insert values(1, 'Test it')
If you tried that on MSSQL, you'd get an error because 1 is not a uniqueidentifier. A uniqueidentifer is in the format of 567D0F2E-BEAC-4538-B17F-D0679A032C5F.
I don't have dblink to MSSQL from Oracle so I can't test, but I wonder what would happen if you replaced the 1 with the UID from above and retried the insert?
Also, perhaps another option, is creating a stored procedure on MSSQL and calling it from Oracle, passing both column values or calling NEWID() for the 1st column and passing in the 2nd column value.
Another option from Oracle, is calling SYS_GUID() or similar function for the insert. One problem I do see with that.. is the MSSQL format but a function to add them in doesn't seem to difficult to create.
March 3, 2010 at 10:53 am
The seems to be related to the way Oracle and SQLServer interact. If I query MyTable from Oracle I can see the column Id, so it is to do with the mechanism of the insert statement that means Oracle cannot see, or does not recognise the label "Id". To put it another way, it appears to be the parsing of the insert statement that fails, before the validation of any values I might be supplying.
I think the use of a stored procedure probably would have worked, The way we got round it was to change the datatype from uniqueidentifier to bigint.
March 30, 2010 at 6:44 pm
There is no implicit conversion from integer to UID and your sp is trying to do just that.
See uniqueidentifier:
http://www.sqlservercentral.com/questions/T-SQL/66113/
and answer explanation.
Another method was suggested in the discussion by Peter E. Kierstead:
"You can also assign a UniqueIdentifier by casting from a VarBinary data type.
Select Cast(Cast(1 as VarBinary(16)) as UniqueIdentifier)"
I guess a string in the uid format should solve the issue.
Regards
Hrvoje
Hrvoje Piasevoli
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply