Problem with a distributed query
-
Hi everybody
I have two server linked , VSQL3 and NETROSP005
When I do the next query (in VSQL3 )
INSERT NETROSP005.ALERTASSMS.DBO.POLIFONICAS
SELECT * FROM #TEMP_POLIFONICAS
I have the next error message:
Server: Msg 7344, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[NETROSP005].[ALERTASSMS].[DBO].[POLIFONICAS]' because of column 'pol_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=pol_id STATUS=DBSTATUS_S_OK], [COLUMN_NAME=pol_msisdn STATUS=DBSTATUS_S_OK], [COLUMN_NAME=pol_gateway STATUS=DBSTATUS_S_ISNULL], [COLUMN_NAME=pol_melodia STATUS=DBSTATUS_S_OK], [COLUMN_NAME=pol_descargada STATUS=DBSTATUS_S_ISNULL], [COLUMN_NAME=pol_timest...
What is the problem?
The user I use to connect to the linked server has all the permissions on the remote database, and, the next query is working fine:
UPDATE NETROSP005.ALERTASSMS.DBO.POLIFONICAS
SET pol_msisdn = T.POL_MSISDN FROM NETROSP005.ALERTASSMS.DBO.POLIFONICAS P , #TEMP_POLIFONICAS T
WHERE P.POL_ID = T.POL_ID
Thanks in advance!!!
And merry christmas!
-
Is pol_id an identity column?
-
Yes it is....
-
Change this:
INSERT NETROSP005.ALERTASSMS.DBO.POLIFONICAS
SELECT * FROM #TEMP_POLIFONICAS
to:
INSERT NETROSP005.ALERTASSMS.DBO.POLIFONICAS (fld1,fld2,fld3...)
SELECT fld1,fld2,fld3... FROM #TEMP_POLIFONICAS
It is not a good idea to leave field ordering to take place implicitly because you may not get what you asked for, use EXPLICIT Field list instead.
HTH
* Noel -
That didn't worked
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply