August 2, 2004 at 1:46 am
Hey, I get this problem when I try to copy information from one server to another, to a idenitity_columns.
I have tried to set the identity_insert ON for the table, for the server I'm copying to, NOLUCK.
Any suggestions?
Server: Msg 7344, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '['SERVERNAME'].[DATABASE].[owner].[table_name]' because of column ''column_name''. 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.]
August 2, 2004 at 1:56 am
What login does the target server think you are?
What permissions does that login have?
I've found that if I create a login on one machine ,then copy that login to 2nd and subsequent machines I get around a lot of security headaches, however in your case it sounds like the specific login on your target server has column permissions.
August 2, 2004 at 2:03 am
both servers are configured exactly the same.
administrator on both of the machines, same username and password, the works?
August 2, 2004 at 2:24 am
How are you copying the data?
Are your servers linked or remote.
I had a number of problems with remote servers. As you know BOL recommends the use of Linked Servers over Remote Servers, however it creates remote servers when replication is set up.
I wanted to be able to write queries on one box that queried the other, but kept getting error messages.
I looked at sp_configure 'remote access', but this defaults to 'true' in any case.
It took a bit of working around but eventually I found that I had to run.
exec sp_serveroption <server2>,'Data Access','true'
August 2, 2004 at 3:08 am
It's a manual process, I use inserts from ,
i checked out the sp_serveroption, but it doesn't work.
Thanks anyway
August 2, 2004 at 3:18 am
If you are running stuff on server 1 and pushing to server 2 why not turn the problem on its head and run from server 2 and pull from server 1.
Looking at BOL it doesn't say that you can include the server in the table source portion of SET IDENTITY_INSERT
i.e. SET IDENTITY_INSERT server.database.owner.table ON
only that you can do the fully qualified name on the local server.
i.e. SET IDENTITY_INSERT database.owner.table ON
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply