CODEWITHSUNDEEP
sql-server
olf
olf

Reputation: 872

SQL Server : Insert data from Linked Server

I have two SQL Servers: Server1 and Server2. The servers are linked and the user linking the two servers is db_owner.

When I am at Server1 and write the following:

  INSERT INTO Server2.dbname.dbo.ContractPermission (ContractNo, UsrCode, Extra)
  VALUES  ('29977', 'xxx', 1)

it works like a charm!

But when I am at Server1 and write this:

INSERT INTO Server2.dbname.dbo.ContractPermission
   SELECT 
       [ContractNo]
       ,'xxx'
       ,[Extra]
   FROM 
       Server2.dbname.dbo.ContractPermission
   WHERE 
       UsrCode = 'yyy'

it does NOT work. The error code returned is:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

The funny thing is that if I connect to Server2 and use the exact same code it works!

So my confusion is: Why does this not work form Server1 when I have proven that I can INSERT elements in the table from Server1. And I have also proven that the SQL works when I am on Server2.

There is a slight difference in the SQL Server versions:

It is not a big problem as I can always execute it from Server2. I am just curious why it doesn't work from Server1.

Upvotes: 0

Views: 92

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

You are missing the columns you had in your first example.

Try this:

INSERT INTO Server2.dbname.dbo.ContractPermission
  (ContractNo, UsrCode, Extra )
SELECT 
  [ContractNo]
  ,'xxx'
  ,[Extra]
FROM Server2.dbname.dbo.ContractPermission
WHERE UsrCode = 'yyy'

Upvotes: 1

Related Questions