August 2, 2004 at 8:16 am
Hi Everybody
I need to update a table with the data of another table that resides in another Database. The user of the first table don't have access to the second one but I have the user and login that does.
How can I do that?
Thanks
August 2, 2004 at 2:50 pm
same server?
If so, just qualify the table names with the database and owner....
Pubs.dbo.authors
Northwind.dbo.Customers
Steve
August 3, 2004 at 1:07 am
If its on the same server then all you need to do is fully Qualify the table name with the database name.owner name.table name.
If the table is on a different server...then you have to create a linked server to connect to the database and then use the table ....now qualifying the table name with
linkedservername.dbname.owner name.table name
and use it.
Hope that helps.
Cheers!
Arvind
August 4, 2004 at 4:57 am
The tables are in the same server but the user can't have grant to select. I need to log as a diferent user but I don't know how to do directly from the select statement.
Julio
August 4, 2004 at 5:10 am
If you have access to both these tables then all you need to do is run a query ...something like this
Update db1.dbo.table1 A
Set col1 = B.col1
From db2.dbo.table2 B
Where A.col2 = B.col2
I guess this gives you a fair idea on how you can go about doing this...If this doesnt help...provide more info.
Cheers!
Arvind
August 4, 2004 at 5:22 am
Arvind and Every body thanks for your help but I will more specific.
I have a table (tableA) in the database A this table, needs to be update every day with data from (tableB), it reside in another database B. I need to built a select statement to do that. The problem is that the user of tableA doesn't have access to tableB, so in the select statement I need to log as the user of tableB. All database are in the same server.
I'm using OPENROWSET embeded in the select but doesn't work. If you know another way?
Julio
August 4, 2004 at 5:38 am
Julio,
Have you tried something like this...
Update DatabaseA.dbo.tableA A
SET A.col1 = B.col1
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=YourServer;UID=username;PWD=urpwd', DatabaseB.dbo.TableB) AS B
WHERE A.col2 = B.col2
Can you try this out...and lemme know if it helps.
Cheers!
Arvind
August 4, 2004 at 5:43 am
I'm got this error:
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
August 4, 2004 at 5:51 am
Pls send me your query after masking the logon credentials!
Arvind
August 4, 2004 at 5:54 am
here u have
select * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=192.168.145.14;UID=greenadmin;PWD=kakariki', take2.dbo.greenlight_students)
August 4, 2004 at 6:20 am
Query seems to be fine ...Can you try creating DSN using ODBC driver with the following credentials and check if you are still able to access the database.
And pls dont give out the details of the server etc. on Forums...I dunno if you have masked it...but this might turn into a serious security issue for you if you havent.
Cheers!
Arvind
August 4, 2004 at 6:25 am
If you change SERVER=localhost does it remove the linked server error?
If not, then add the linked server using the IP address above in Enterprise Manager (under Security). The localhost thing above should fix your problem though.
Personally, if I was you, I'd be using DTS to push the data from B across to A - you can set connections inside DTS to easily use any authentication you wish.
Dave Hilditch.
August 4, 2004 at 6:34 am
Whao, it works finally with localhost
Thanks all for your help and time.
Don't worry by data I bring about server or connections all local and prepared for this try.
Thanks again
Julio
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply