SQL Server : Insert data from Linked Server

Question

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:

Server1 is SQL Server 2012 - 11.0.5532.0 (X64)
Server2 is SQL Server 2012 - 11.0.5058.0 (X64)

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.

Most likely, your ContractPermission table has more than just those three columns that you provide data for in your SELECT in the second example. That's why you should always explicitly define the list of columns in your INSERT statements — marc_s
– marc_s, Commented Jan 15, 2015 at 10:12
Yes, you are right! And t-clausen.dk below is right as well. But still strange that it works when executed directly on Server2 then! The only extra column in the table is an Id field with is having its Identity Specification set to Yes. — olf
– olf, Commented Jan 15, 2015 at 11:21

t-clausen.dk · Accepted Answer · 2015-01-15 10:00:04Z

1

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'

answered Jan 15, 2015 at 10:00

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : Insert data from Linked Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related