Distrubuted query for inserting

I established a linked server, managed to do select (quering the remote
table) in a SP frm the local server . The table has 2 fields, fld 1 is int
(identity), the second is nvarchar(20) as follows
When I try to insert a new line using the following syntax from the local
server, I get errors
INSERT INTO sql2k5.mytable.dbo.MarketingTable1 VALUES ('données depuis Srv1
').
The error I am getting is : "Insert Error: Column name or number of supplied
values does not match table definition."
When deleted the identity column, inserting from local server worked ok.
Any idea of why this is happening? is there restriction on data types when
using distributed queries?
ThanksSalamElias (eliassal@.online.nospam) writes:
> I established a linked server, managed to do select (quering the remote
> table) in a SP frm the local server . The table has 2 fields, fld 1 is int
> (identity), the second is nvarchar(20) as follows
> When I try to insert a new line using the following syntax from the
> local server, I get errors INSERT INTO
> sql2k5.mytable.dbo.MarketingTable1 VALUES ('donnes depuis Srv1'). The
> error I am getting is : "Insert Error: Column name or number of supplied
> values does not match table definition."
> When deleted the identity column, inserting from local server worked ok.
> Any idea of why this is happening? is there restriction on data types when
> using distributed queries?
I guess that what the local SQL Server see is a remote table with two
columns. Keep in mind that it does not know that this is an SQL Server
table - it could be Access, Oracle or Active Directory on the other
end of the line.
In any case, it is best practice to always list the columns of the table you
are inserting to. Today the table has two columns, tomorrow it has three,
and then your INSERT till blow up.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi and thanks. Why do you say that "the local server doesn't know". The
localserver knows very well that the remte server is a sql server. I used th
e
following to create the linked server
sp_addlinkedserver 'sql2k5', N'SQL Server'
GO
Best regards
"Erland Sommarskog" wrote:

> SalamElias (eliassal@.online.nospam) writes:
> I guess that what the local SQL Server see is a remote table with two
> columns. Keep in mind that it does not know that this is an SQL Server
> table - it could be Access, Oracle or Active Directory on the other
> end of the line.
> In any case, it is best practice to always list the columns of the table y
ou
> are inserting to. Today the table has two columns, tomorrow it has three,
> and then your INSERT till blow up.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||SalamElias (eliassal@.online.nospam) writes:
> Hi and thanks. Why do you say that "the local server doesn't know". The
> localserver knows very well that the remte server is a sql server. I
> used the following to create the linked server
> sp_addlinkedserver 'sql2k5', N'SQL Server'
When SQL Server talks with a linked server, it acts a client add accesses
the remote server through the OLE DB API, which exposes generic elements.
Of course, it could be conceivable, that there are separate code paths
in case the linked server is SQL Server, but I would expect this particular
piece of code to be agnostic what is at the other end as much as possible,
to keep down the complexity.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I understand that you encountered the error:
"Insert Error: Column name or number of supplied values does not match
table definition."
when you executed the insert statement on a remote table with IDENTITY key.
If I have misunderstood, please let me know.
This is an expected behavior since the scope of the @.@.IDENTITY function is
current session on the local server on which it is executed. This function
cannot be applied to remote or linked servers. You may refer to:
@.@.IDENTITY (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms187342.aspx
I recommend that you create a stored procedure in the database of the
remote server, then perform INSERT operation via that SP. For example: EXEC
[mysqlserver-01].Test1.dbo.proc_insDict 'édepu','test'.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============