Run a query from a file

Question

I don't have permission to execute xp_cmdshell procedure.

Is there anyway I could execute a query from different file?

I tried this and don't know if it is a good option:

 --query1.sql
 Create Procedure sp_1
 as
 Begin
    declare @q1 varchar(MAX)
    set @q1 = ' some complicated query '
    exec(@q1)
 End


 --query2.sql
 Create Procedure sp_2
 as
 Begin
    declare @q1 varchar(MAX)
    set @q1 = ' some complicated query '
    exec(@q1)
 End

 --mainquery.sql
 Exec sp_1
 Exec sp_2

Any advice?

My advice is to explain what kind of trouble you have and also explain where there is any relation to the `sp_cmd` procedure which you are not allowed to execute. — alzaimar, Jun 28 '14 at 07:24
@alzaimar - [Execute a text file from SQL query](http://stackoverflow.com/questions/241925/is-it-possible-to-execute-a-text-file-from-sql-query) — Emman Bangis, Jun 30 '14 at 15:20

score 0 · Answer 1 · answered Jun 30 '14 at 21:20

If you happen to have BULK INSERT permission you could try to import the file using the BULK INSERT command, construct the query from the imported 'table' (which should consist of exactly one column), combine the contents into one varchar varialbe and then execute it.

create table #tmp (cmd varchar (255))
bulk insert #tmp from 'd:\foo.txt'
declare @cmd varchar(max), @row varchar(255)

declare x cursor for select cmd from #tmp
open x

set @cmd=''
fetch next from x into @row
while @@FETCH_STATUS = 0 begin
  set @cmd = @cmd + @row
  fetch next from x into @row
end
close x
deallocate x
exec (@cmd)

But it requires you to have BULK INSERT permission.

Run a query from a file

1 Answers1