IDENTITY_INSERT Error

I have been trying to get this to work but am failing can anybody help
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT *
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=@.Server;User
ID=@.UserName;Password=@.Psw' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, Companyname, fileseq
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq )))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table
'bossdata.dbo.DailyOverLimits' can only be specified when a column list is
used and IDENTITY_INSERT is ON.Don't use select * from opendatasource. Give column names.
Idenityt insert doesn't work with *.
Hope this helps|||and its always a good practice to not use select * and to also specify the
column list in the insert.
Use it this way
INSERT INTO TBL1(COL1,COL2,COL3)
SELECT COLA,COLB,COLC FROM tbl2
because that way you make sure the right values goes to the right columns.|||Thanks Omnibuzz,
Seems like im jumping from one error to another lol, any clues
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits ON
INSERT INTO bossdata.dbo.DailyOverLimits
SELECT Licence, CompanyName, FileSEQ, AccountCode, ANLRef, FileTotal,
LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=BACSSYS;User
ID=sa;Password=22559226' ).Bossdata.dbo.DailyOverLimits AS A
WHERE (NOT EXISTS
(SELECT Licence, CompanyName, FileSEQ,
AccountCode, ANLRef, FileTotal, LimitExceeded,
LimitValue, CurentAccum, ExceededValue, ClearingDate
FROM bossdata.dbo.DailyOverLimits AS b
WHERE (a.licence = b.licence AND
a.Companyname = b.Companyname
AND a.fileseq = b.fileseq Collate
SQL_Latin1_General_CP1_CI_AS)))
SET IDENTITY_INSERT bossdata.dbo.DailyOverLimits OFF
Explicit value must be specified for identity column in table
'DailyOverLimits' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity column.
"Omnibuzz" wrote:

> and its always a good practice to not use select * and to also specify the
> column list in the insert.
> Use it this way
> INSERT INTO TBL1(COL1,COL2,COL3)
> SELECT COLA,COLB,COLC FROM tbl2
> because that way you make sure the right values goes to the right columns.
>|||:)
The answer is there in my second post..
give the column name with the insert table.
See if that works.|||Always list columns, don't leave SQL Server guessing:
insert <destination table>
(
<column 1>
,<column 2>
,...
)
select <column 1>
,<column 2>
,...
from <source table>
ML
http://milambda.blogspot.com/