Inserting into Remote Database
-
Hi alll
I have created a remote linked server and I am trying to insert into remote database from query analyzer connected to my local database server.
I am getting the below error..
Server: Msg 7344, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[MUSSORI].[pubs].[dbo].[jobs]' because of column 'job_id'. The user did not have permission to write to the column.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the provider: [COLUMN_NAME=job_id STATUS=DBSTATUS_S_OK], [COLUMN_NAME=job_desc STATUS=DBSTATUS_S_OK], [COLUMN_NAME=min_lvl STATUS=DBSTATUS_S_OK], [COLUMN_NAME=max_lvl STATUS=DBSTATUS_S_OK]. Data status returned from the provider: [COLUMN_NAME=job_id STATUS=DBSTATUS_E_PERMISSIONDENI...
Can anybody help inthis regard.....
Thank you
Shekhar
-
the error clearly indicates that it is a permissions issue.
Check if the account that you are using to connect to the remote server has and INSERT permission on the table that you are inserting.
-
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[MUSSORI].[pubs].[dbo].[jobs]' because of column 'job_id'. The user did not have permission to write to the column.
Just check the permission that the user has for the column job_id and also for the table job. The error states that the user does not have proper permissions.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I have created a linked server with following credentials...
the following is the output from sp_helpLinkedSrvLogin
Linked server, Local Login, Is Self Mapping, Remote Login
MUS , sa , 0 , sa
i am using the user SA to connect to local server & specified SA to connect to remote server.
even then also I am getting the same error.
Thank You
Shekhar
-
Hello,
In my version of the [pubs] database, the job_id column has the identity property set. That may be the cause of the insert problem.
jg
-
Hi There Shekhar,
You need to use this syntax in order to insert into dbo.jobs
SET IDENTITY_INSERT [dbo].[jobs] ON
---Your insert sql...
SET IDENTITY_INSERT [dbo].[jobs] OFF
In most cases, [dbo].[jobs] uses [job_id] as the Primary Key and Identity Column.
Regards,
Meng
MCTS
- All right, let's take this one step further and see if you can help me figure out what my problem is. In order to facilitate some data massaging on the linked server, I have created a view with an "instead of" insert trigger. I don't believe security is the problem - the linked server is actually a self-reference my local server (but a different database) using the Current Security Context (which I am SA). I can issue insert statements for the view in each database independently without error, and I can issue an insert across databases using 3 part naming (DB.owner.Table). However, it breaks whenever I include LinkedServer in my insert statement.Admittedly, ProfileID is an identity field for one of the base tables, but I don't have to do anything with that when executing the insert without the LinkedServer. It's almost like the insert is ignoring the Instead Of trigger when hitting the Linked Server.
--[executed from database ProfileCache]INSERT MYSQL2000.PC.dbo.vwProfileUserExternalID
(
ProfileID,
ApplicationUserID,
ExternalSystemID,
ExternalSystemCode,
ExternalSystemUserID,
CreatedBy,
CreationDate
)
SELECT
P2.VALU2 as ProfileID,
NULL,
NULL,
'D2D',
P2.ACNO2,
'D2DXfer',
getdate()
FROM dbo.PRMG002 P2
WHERE
P2.NUTP2 = 'PROFILEMGR'
and ACNO2 = '68072742'
(results...)[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the provider: [COLUMN_NAME=ProfileID STATUS=DBSTATUS_S_OK], [COLUMN_NAME=ApplicationUserID STATUS=DBSTATUS_S_ISNULL], [COLUMN_NAME=ExternalSystemID STATUS=DBSTATUS_S_ISNULL], [COLUMN_NAME=ExternalSystemCode STATUS=DBSTATUS_S_OK], [COLUMN_NAME=ExternalSystemUserID STATUS=DBSTATUS_S_...
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[MYSQL2000].[PC].[dbo].[vwProfileUserExternalID]' because of column 'ProfileID'. The user did not have permission to write to the column.
-
Is the ProfileID column an IDENTITY column or not?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Yes, ProfileID is an identity column for the Profile table. What is weird here though, is that the statement works fine if I insert into the view on the same server. Adding the LinkedServer to the table name causes the previously stated error.
-
- In response to Tim (Stax68) 's statement(s) (on the T-SQL forum where I cross-posted a link to this thread)
- You could do something kludgy like specifying job_id+0 AS job_id or isnull(job_id,0) AS job_id in the view definition: this would (I think!) 'screen off' the underlying table column so that the view column won't inherit the identity property, but this will prevent you using the index on the underlying table, and cause performance problems.
I tried "kludging" the ProfileID, but that didnt' help. Unfortunately, ProfileID is the one key identifier I have from the local system that can direct the insert on the Linked Server, so it is required in some fashion. It isn't just a problem with the Insert trigger on the view because it works fine when a Linked Server isn't involved. I suppose I should just abandon the approach I was taking, but I was sooo close! everything worked on one server.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply