truncate table or same on linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

truncate table or same on linked server

Author

Topic

maxbet
Starting Member

5 Posts

Posted - 2009-08-25 : 04:35:27

hello, sorry for the my english

At me is SQL server 2005 and linked server (Mysql)
It is necessary for me truncate table on my linked Mysql server
I do
"Delete from openquery (name_linked_server, ' select * from table1 ') where id> 0",
where id=autoincrement
But this query is very long carried out. Whether it is possible to clear in another way the table on linked server? It can is For example possible to delete and create easier it anew?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-25 : 04:50:01

Can you try:
TRUNCATE TABLE <LinkedServerName>.<MySqlDatabaseName>.Owner.Table1;

maxbet
Starting Member

5 Posts

Posted - 2009-08-25 : 05:12:44

quote:
Originally posted by YellowBug

Can you try:
TRUNCATE TABLE <LinkedServerName>.<MySqlDatabaseName>.Owner.Table1;

im need to put rows from sql to mysql EMPTY table.
problem is - "truncate table" is not possible to use over linked server.

instead of "truncate table" im using "delete ... from openquery..."
but this query taking too much time (dunno why? mb it working with every row). If in target table 40 000-50 000 rows, its take hours (
and only after this i can start to insert needed rows...

Can i drop and create again target table via openquery to make faster process? or another way to do?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-25 : 06:05:18

Have you tried the 4 part naming convention instead of openquery() as YellowBug said?

maxbet
Starting Member

5 Posts

Posted - 2009-08-25 : 07:52:07

quote:
Originally posted by RickD

Have you tried the 4 part naming convention instead of openquery() as YellowBug said?

yes

"The object name 'servername.databasename.owner.table1' contains more than the maximum number of prefixes. The maximum is 2."

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-25 : 08:26:51

Hmm...Are you running that inside the openquery?

Simply execute it on its own (NOT with openquery), like this:
TRUNCATE TABLE <LinkedServerName>.<MySqlDatabaseName>.Owner.Table1;

maxbet
Starting Member

5 Posts

Posted - 2009-08-26 : 04:16:59

quote:
Originally posted by YellowBug

Hmm...Are you running that inside the openquery?

Simply execute it on its own (NOT with openquery), like this:
TRUNCATE TABLE <LinkedServerName>.<MySqlDatabaseName>.Owner.Table1;

thanx to all

working variant is:
"exec (@sql) at Linked_server_Name"
where @sql - can be "truncate table table1" or other query
but you must turn on RPC for linked server before
GL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources