calling sql file from sp

Is there a way to call sql file from stored procedure and
execute it...
Hi,
Yes, it is possible using XP_CMDSHELL
create proc <name>
as
begin
xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
c:\file_name.sql -oc:\output.log'
end
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...
|||By default XP_cmdshell can only be called by SQL Administrators.. If you
wish others to be able to use this, an option can be turned on in SQM,
Choose SQL Agent->Properties... It will be on one of the tabs to allow
non-sysadms to do cmd and active scripting tasks.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...
|||it's also possible to use ethe xp_OA_xxx OLE automation proceedures to write
code to do this. xp_cmdshell as described will most likely be more effecient
and easier for you to use, but... I wanted to mention the option...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
> Is there a way to call sql file from stored procedure and
> execute it...
|||Thanks
>--Original Message--
>Hi,
>Yes, it is possible using XP_CMDSHELL
>create proc <name>
>as
>begin
>xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d
db_name -i[vbcol=seagreen]
>c:\file_name.sql -oc:\output.log'
>end
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:1028201c43f29$daf22d90$a101280a@.phx.gbl...
and
>
>.
>
|||Due to security reasons I would not recommend to grant xp_cmdshell rights on
everyone. This could be a potential threat for server hosting MSSQL. Better
use extended stored proc which will do the same, and evil users won't be
able to do anything but running the script you prepare
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ubG%23HwyPEHA.3348@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi,
> Yes, it is possible using XP_CMDSHELL
> create proc <name>
> as
> begin
> xp_cmdshell 'OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i
> c:\file_name.sql -oc:\output.log'
> end
> Thanks
> Hari
> MCDBA
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1028201c43f29$daf22d90$a101280a@.phx.gbl...