I am using data migrator and I want to use a store proc to create a table in sql server 2012.
I have created my adapter, I can connect to the sql, I can load data to the sql using flows and synonyms. But I cannot connect using store procedure in data migrator.
Can someone please advice how to do that or just write me a store procedure to create a table.

code should look like below

-*SQL SQLMSS SET SERVER servername
-*END

SQL SQLMSS
COMMIT WORK ;
END



SQL SQLMSS
CREATE TABLE Test_SP.dbo.test_07182013
( RepDate varchar(8) NULL,
CompCode varchar(3) NULL,
InventNumber char(15) NOT NULL
) ;
END

SQL SQLMSS
COMMIT WORK ;
END

Assuming you want to use your first connection and Test_SP is a valid database name, all you need to do is this:

SQL SQLMSS
CREATE TABLE Test_SP.dbo.test_07182013 
  (RepDate varchar(8) NULL, CompCode varchar(3) NULL, InventNumber char(15) NOT NULL ) ;
END

There should be no response unless there is an error. If there is an error, let us know what it is.