SQLTeam.com | Weblogs | Forums

SQL Linked Server problem

Hi,

I have a DB remote that I connect to my local SQL using a Linked Connection and named it "Homer"
I can see the data, so that works.
The problem is when I want to add data, it doesn't work.
He must use the "IDENTITY"

Any idea how to handle it?
Normally the insert does about 14 fields, but I shortened it to 1 , to make it more readable.

Thank you

EXEC sp_addlinkedserver @server=N'Homer', @srvproduct=N'', @provider=N'MSOLEDBSQL',   @datasrc=N'Testserver-dev\SQLEXPRESS'
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Homer', @useself=N'FALSE', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword=N'Test123'

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'homer', @optname=N'rpc out', @optvalue=N'true'
GO
 
Declare @MyStatement NVARCHAR(MAX) = N'
   USE [testdb]
   SET IDENTITY_INSERT [Homer].testdb.[dbo].[TUSER] ON;
   INSERT INTO [Homer].testdb].[dbo].[TUSER] (name,role_id) VALUES (''test4'',''3'')
   ''
   EXECUTE sp_executesql @stmt = @MyStatement
   '
   print  @MyStatement
   EXECUTE sp_executesql @stmt = @MyStatement

You are setting IDENTITY_INSERT to ON, which means your intention is to manually insert the identity value. Since you are manually inserting the IDENTITY then you need to include that column in the insert statement.

If you want the system to generate the identity - then don't enable identity insert and the system will generate the identity value for you.

BTW - based on what you posted there is no reason to use dynamic SQL. And you have included a call to sp_executesql inside the dynamic SQL which won't work. Finally, if you do require the use of dynamic SQL then you should also setup and include a parameter list and parameters instead of concatenating in the string:

DECLARE @sqlStatement nvarchar(max) = '
INSERT INTO Homer.testdb.dbo.TUSER (name, role_id) VALUES (@val1, @val2);
';
EXECUTE sp_executesql @sqlStatement, '@val1 varchar(10), @val2 int', @val1 = 'test4', @val2 = 3;

Thank you.
I provided a short example.

Let me post the full one, and for both DB's they are remotely, so they connect with a linked database.

so it will put data from the old DB, to a New one, and I need to have the identity on, otherwise you cannot insert.

EXEC sp_addlinkedserver @server=N'olddb', @srvproduct=N'', @provider=N'MSOLEDBSQL',   @datasrc=N'OLDPC\oldsqlserver'
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'olddb', @useself=N'FALSE', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword=N'Tyche123'

EXEC sp_addlinkedserver @server=N'newdb', @srvproduct=N'', @provider=N'MSOLEDBSQL',   @datasrc=N'NEWPC\newsqlserver'
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'newdb', @useself=N'FALSE', @locallogin=NULL, @rmtuser=N'sa', @rmtpassword=N'Tyche123'



--SET IDENTITY_INSERT [newdb].[tychedb].dbo.DEVICE_TRANSACTION ON

insert into [newdb].[tychedb].dbo.DEVICE_TRANSACTION (id,device_id,amount,currency,type_id,error_code,error_level,extended_code ,error_text,extended_error_text )
select id,device_id,amount,currency,type_id,error_code,error_level,extended_code ,error_text,extended_error_text from [olddb].[oldversion].dbo.DEVICE_TRANSACTION


--SET IDENTITY_INSERT [newdb].[tychedb].dbo.DEVICE_TRANSACTION OFF

so when i run it, i get this error

The OLE DB provider "MSOLEDBSQL" for linked server "newdb" could not INSERT INTO table "[newdb].[tychedb].[dbo].[DEVICE_TRANSACTION]" because of column "id". The user did not have permission to write to the column.

it needs to get the same ID

but

SET IDENTITY_INSERT [newdb].[tychedb].dbo.DEVICE_TRANSACTION ON

gives me this error

Cannot find the object "newdb.tychedb.dbo.DEVICE_TRANSACTION" because it does not exist or you do not have permissions.

Are you really using an instance of SQL Server with 2 linked servers - the 'old' and the 'new' - and trying to transfer data from the old to the new?

If so - do you realize that you are moving data across the network from the source (old) to your local instance and then across the network from your local to the destination (new)?

If the goal is simply to move data - then pretty much any other tool would be better. SSIS, BCP, Powershell - all would be better at extracting the data from the old system and inserting into the new system.

The problem doesn't appear to be related to setting the identity on/off. Looks like either the linked server is misconfigured or you don't have permissions on the new system.

I would not know what that permission would be then.
I am admin on both, and both logged in with the SA account.
I did miss

GO
EXEC master.dbo.sp_serveroption @server=N'newdb', @optname=N'rpc out', @optvalue=N'true'
GO

Then I was able to make it work. once I remove the restraining key.
otherwise i get

The object name 'newdb.tychedb.dbo.DEVICE_TRANSACTION' contains more than the maximum number of prefixes. The maximum is 2.

and then put the key back afterwards. (i know it's a dirty way)

But now, with my next query, I cannot get it to work at all.

insert into [newdb].tychedb.dbo.JOURNAL (id, user_id,second_user_id, journal_type_id , start_time, end_time , status, amount , currency , counting_error, message_note, terminal_id, ciamanager_id, master_device_id)
Select id, user_id,second_user_id, journal_type_id , start_time, end_time , status, amount , currency , counting_error,
  (Select TOP 1 message_data from [olddb].oldversion.dbo.JOURNAL_DETAIL Where [olddb].oldversion.dbo.JOURNAL_DETAIL.journal_id = [olddb].oldversion.dbo.JOURNAL.id),
terminal_id,NULL,
  (Select TOP 1 device_id From [olddb].oldversion.dbo.DEVICE_TRANSACTION WHERE id = (Select TOP 1 device_transaction_id from [olddb].oldversion.dbo.JOURNAL_DETAIL Where device_transaction_id<>0 and journal_id = [olddb].oldversion.dbo.JOURNAL.id))
from [olddb].oldversion.dbo.JOURNAL;

I keep getting

Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "olddb.oldversion.dbo.JOURNAL_DETAIL.journal_id" could not be bound.
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "olddb.oldversion.dbo.JOURNAL.id" could not be bound.
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "olddb.oldversion.dbo.JOURNAL.id" could not be bound.

the fields are all there, so i'm not sure what it means, or what to do.

You cannot use 4-part naming to reference a column:

There are 2 ways to get around this

  1. Create synonyms for each table - and use the synonym in your query
  2. Set an alias for each table in the query and use the alias to reference every column

First - we need to reformat your query so we can read it:

Insert Into [newdb].tychedb.dbo.JOURNAL (
       id
	 , user_id
	 , second_user_id
	 , journal_type_id
	 , start_time
	 , end_time
	 , status
	 , amount
	 , currency
	 , counting_error
	 , message_note
	 , terminal_id
	 , ciamanager_id
	 , master_device_id
	   )
Select id
     , user_id
	 , second_user_id
	 , journal_type_id
	 , start_time
	 , end_time
	 , status
	 , amount
	 , currency
	 , counting_error
	 , (Select Top (1)
               message_data 
          From [olddb].oldversion.dbo.JOURNAL_DETAIL 
		 Where [olddb].oldversion.dbo.JOURNAL_DETAIL.journal_id = [olddb].oldversion.dbo.JOURNAL.id)
     , terminal_id
	 , NULL
	 , (Select Top (1)
               device_id 
          From [olddb].oldversion.dbo.DEVICE_TRANSACTION 
		 Where id = (Select Top (1)
                            device_transaction_id 
					   From [olddb].oldversion.dbo.JOURNAL_DETAIL 
					  Where device_transaction_id <> 0 
					    And journal_id = [olddb].oldversion.dbo.JOURNAL.id))
  From [olddb].oldversion.dbo.JOURNAL;

Now that we can actually see what is going on - we can immediately see a new problem. You have 3 subqueries using TOP 1 without an order by. This is problematic because we don't know which row SQL will return since it can return any row that meets the requirements.

The other problem is that you are using the same subquery - twice. We can reduce that one and ensure that we get the correct data using CROSS/OUTER APPLY:

Here is an updated version using aliases and outer apply:

Insert Into [newdb].tychedb.dbo.JOURNAL (
       id
	 , user_id
	 , second_user_id
	 , journal_type_id
	 , start_time
	 , end_time
	 , status
	 , amount
	 , currency
	 , counting_error
	 , message_note
	 , terminal_id
	 , ciamanager_id
	 , master_device_id
	   )
Select j.id
     , j.user_id
	 , j.second_user_id
	 , j.journal_type_id
	 , j.start_time
	 , j.end_time
	 , j.status
	 , j.amount
	 , j.currency
	 , j.counting_error
	 , dtl.message_data
     , j.terminal_id
	 , ciamanager_id = Null
	 , dtl.device_id
  From [olddb].oldversion.dbo.JOURNAL                               As j

 Outer Apply (Select Top (1)
                     jd.message_data
                   , dt.device_id
                From [olddb].oldversion.dbo.JOURNAL_DETAIL          As jd
               Inner Join [olddb].oldversion.dbo.DEVICE_TRANSACTION As dt On dt.id = jd.device_transaction_id
               Where jd.journal_id = j.id
			     And dt.device_transaction_id <> 0
			   Order By
                     <<ordering columns here>> desc
             )                                                      As dtl;

Replace <> with one or more columns to define the order. Generally a date column in descending order to get the latest version.

I combine the subqueries into a single query - you need to validate the results, but based on what you have shown it seems to be the correct logic. If you don't have related data then message_data and device_id will be null - if those need a default value you can then wrap the columns in COALESCE or ISNULL.

Note: this probably won't be very efficient and could take a long time to run. That is because you are pulling from a linked server and inserting across a linked server.

Thank you
The part you wrote seems to be wrong?
you made [olddb].oldversion.dbo.DEVICE_TRANSACTION As dt

but dt.device_transaction_id does not exist. unless you meant dt.device_id ?

Typo - it probably should be 'And jd.device_transaction_id <> 0. But that probably isn't even needed because you probably don't have and device transaction = 0 anyways.

Either way - the idea is to query the journal detail and device transaction tables joined so that you get one row that has the message and device. You know your data - I don't, so adjust the query as needed.