0

I have a MSSQL Server 2008 R2 system that updates various tables on various systems during a nightly process. I have a MySQL linked server that has an Auto_Increment on one of it's tables. This tables contents are deleted and replaced each night. This of course does not reset the increment. I have tried:

 ALTER TABLE REMOTEMYSQL...[TableWithAI] AUTO_INCREMENT = 1

And I get:

 Incorrect syntax near 'AUTO_INCREMENT'

I can successfully:

 delete from REMOTEMYSQL...[TableWithAI]

But obviously that doesn't reset the increment on the MySQL side

I tried:

 Truncate TABLE REMOTEMYSQL...[TableWithAI]

and I get:

 Cannot find the object "TableWithAI" because it does not exist or you do not have permissions.

But the MySql user that is used in the link has full permissions. How can I ether delete the contents and zero the increment, or zero the increment by itself?

user3059028
  • 67
  • 7
  • possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) – citizenen Jan 23 '15 at 18:25
  • Nope, not a duplicate. That question is asking how to reset from the MySQL side. I am on MSSQL via a linked server connection – user3059028 Jan 23 '15 at 18:31

1 Answers1

1

Assuming you have your MySQL already configured as a linked server, you can fire off non-query statements to linked servers with EXEC:

EXEC('ALTER TABLE TableWithAI AUTO_INCREMENT = 1') AT LinkedMySQLServerName;

I believe the query should be in MySQL syntax, but I'm not in a position to test from where I'm at.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66