I am building an Access database that functions as a reference library. I want to use links in the Access database to execute SQL queries in a different database. Presently when I click the Access hyperlink it tries to run SQL ServerManagemenrt Studio but then errors with 'The operation could not be completed'. I also tried using the Access hyperlink to open a folder containing the SQL queries so one can double click the SQL to run it. The folder opens but the same error message occurs when I try to run SQL from the folder. Clearly something is happening due to the SQL or folder containing the SQL being opened by MS Access. Can any advise what to do?
A:
Since SQL Server Management Studio is openning, are these .sql text files you're trying to execute?
You can execute the t-sql script contained in the file by running this command line:
SQLCMD -S SQL_SERVER_NAME -d DATABASE_NAME -E -I -i "C:\QueryFile.sql" >> ResultBatch.txt
The results get sent to the file ResultBatch.txt which would be in the same folder as the sql file.
Create a batch file in the folder with all the scripts and this will execute all of them:
for %%X in (*.SQL) do SQLCMD -S SERVER_NAME-d DATABASE_NAME -E -I -i "%%X" >> ResultBatch.txt
Jeff O
2009-12-04 14:53:40
Yes they are *.sql files. We usually double click them from file manager to open and run them. The intention is to do open and run them from MS Access saving the need to rifle through various levels of folders. I was using standard Insert Hyperlink into a form to link to the *.sql.
Mark Bailey
2009-12-04 15:23:31
I added additional code you could place in a .bat file in any folder containing .sql files and it would execute all of them and still send results to the text file.
Jeff O
2009-12-04 16:52:09