June 9, 2011 at 9:20 am
I have two databases, both identical, with only 2 tables in the database. One database is up on the network server, and the other is on my local machine. I would like to be able, from time to time, to take the data from the network server, and replace all data in my local copy with that.
This seems such an easy task to do, but I am either coming up with problems trying to do it, or solutions that are WAY too complex for such an easy task. My thinking here is that I can just write a query and do this... But the query produces errors. Here is what I tried...
SELECT * INTO LOCAL.DBO.MYTABLE1 FROM NETWORK.DBO MYTABLE1
This seems, syntactically at least, to make sense. But SQL 2008 R2 tells me it doesnt know what "NETWORK" is - even though I am logged into it, can see and manipulate it and have BOTH databases open and operable at the same time. What happens is that I get an "invalid object name: NETWORK.DBO.MYTABLE1" error - and yet the spelling is correct, and that is the very database I can easily manipulate.
Can anyone tell me what I am missing here?
Once again, I can login and open BOTH databases without a problem - and can have them BOTH opened at the same time... So how can this be an "invalid object"?
Thanks for any help you can offer!
June 9, 2011 at 9:38 am
A couple of things you might want to check. Do you have the network box as a linked server on your local box? Also when you are selecting, trying using servername.databasename.dbo.tablename. Also, a select into will create a new table - you could insert into...select * from.
Or even just restore a backup of the database from the network box onto your local.
H.
June 9, 2011 at 9:43 am
Have you tried using data import/export?
Also, RedGate has a Data Compare tool that can do that for you.
Codeplex also has some tools that can help with this - such as this one[/url].
It might simplify things greatly by using one of those tools.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 9:44 am
Thanks for the reply...
Yes, I have the server linked to my local box - as mentioned, can open both databases no problem.
I already tried the Restore option - good suggestion of course - and my first thought - but as it turns out though both servers are running SQL 2008 R2, I get an error when trying to restore the networked data locally that the network server is running 10.50.1600 and the local machine is running 10.00.4000 - thus the backup is not compatible. (
Talk about really ticking me off on that one! Contacted our network guy about that - but he usually responds weeks later...)
Gotcha on the formulation of the query - sorry about that, my bad - but I still dont understand why its telling me that the network copy of the database is an "invalid object".
Frustrating morning here! Where is SQL 6.5 when you need it!!! (just kidding...)
June 9, 2011 at 9:49 am
Jason,
Thanks for the reply, and yes I have looked over the Import Export tool - but that is exactly what I am saying in my original post - I have two tables with very little data to move and to take the time to do all the Imp/Exp stuff is a complete pain in the neck - was just thinking there has to be an easier and quicker way!!!
If I have to go Imp/Exp I will, but to me that is just way overkill for such a simple thing and many postings suggest this can be done with a quick, easy query.
If I could get by this "invalid object" on a database I can otherwise, open, play with, edit, delete, etc... I would be home free on this one.
June 9, 2011 at 9:55 am
Have you tried openquery like the following?
insert openquery('my_linked_server', 'select column_1, column2 from table_schema.table_name')
values ('my_value1', 'my_value2')
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:26 am
Jason...
OPENQUERY is a bit of new one on me, but looks very interesting. I will study to come up to speed on that, but at present, trying to use it, I still get an "invalid object" error.
Thankfully however, (or maybe just good luck) I just captured the Network Admin and he is right now updating all Network-based SQL versions - so shortly I am hoping I can just do a simple backup and restore.
I am no expert here - but it appears that even though someone is running SQL 2008 R2, versions within that differ, and when they do, they cant "talk" or "play nice" with each other.
Amazing... Used to be the more things "improve" the more they stay the same. Now, the more they "improve", the less you can use them at all... What a world!
Sorry for the whine... Thanks for the help!
June 9, 2011 at 10:30 am
blandry (6/9/2011)
I am no expert here - but it appears that even though someone is running SQL 2008 R2, versions within that differ, and when they do, they cant "talk" or "play nice" with each other.
The SQL Versions that you posted are not both SQL 2008 R2. 10.0 is SQL 2008 and is a version behind R2 which is 10.50. So if trying to backup the db on 10.50 and restore to 10.0 you will get that error.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:48 am
Jason,
Yeah - gotcha on that. We just showed our Network Admin the difference... He insisted he had done the "R2" update. He didnt on the server in question... Of course, as he said earlier this morning over the phone "Who pays attention to version numbers?!?!" Uh... WE DO!!!
I love working for big corporations - titles are everything...
Thanks for all your help and comments - and I will educate myself on OPENQUERY - sounds pretty cool!
June 9, 2011 at 10:53 am
This is straigtforward and using the four part name to access the Linked server data shouldn't cause any problem.
Not sure how using openquery is going to solve anything if the four part name doesn't work.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 9, 2011 at 11:01 am
ricer (6/9/2011)
This is straigtforward and using the four part name to access the Linked server data shouldn't cause any problem.Not sure how using openquery is going to solve anything if the four part name doesn't work.
You're right. I missed the count on the names. The database name appears to be missing from the four part name.
linkedserver.database.schema.table should be used.
But going back to my original suggestion. It would only take a couple of minutes to have the data transferred for both tables using import/export.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2011 at 5:57 am
Thanks to you guys for your suggestions...
I beat, tortured, bribed, cajoled, prayed to, and worshipped the Network Admin and he finally got the versions updated across our servers, and I was able to simply backup one, and restore on another. Job done. That said, I shall look into OpenQuery - sounds like a useful bit of knowledge to have.
Thanks again very much, and God save us from Network Admins - the most powerful people in the world!
June 10, 2011 at 9:50 am
Glad you got sorted! Thanks for the update.
H.
June 10, 2011 at 2:48 pm
Excellent. Glad it is working now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply