March 7, 2012 at 6:38 pm
Hello,
How do I execute/invoke a one or more .sql file from within another .sql file or a stored procedure?
Question 1:
I tried the below two approaches, but both does not work.
Is there anything I'm doing wrong here?
APPROACH 1: Using exec master..xp_cmdshell
DECLARE @v_currentVARCHAR(10);
BEGIN
SELECT @v_current = [Version] FROM VersionTable;
IF (@v_current = '1')
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
exec master..xp_cmdshell 'osql -E -i C:\Script1_to_2.sql'
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
SET @v_success = 'Y'
END
ELSE IF (@v_current = '2')
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
exec master..xp_cmdshell 'osql -E -i C:\Script2_to_3.sql'
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
SET @v_success = 'Y'
END
IF (@v_success = 'Y')
BEGIN TRANSACTION
UPDATE [dbo].[VersionTable]
SET [Version] = '2';
COMMIT TRANSACTION
END
GO
APPROACH 2 - Using :r FilePath\FileName.sql - SQLCMD turned ON
DECLARE @v_currentVARCHAR(10);
BEGIN
SELECT @v_current = [Version] FROM VersionTable;
IF (@v_current = '1')
BEGIN
:r C:\Script1_to_2.sql
SET @v_success = 'Y'
END
ELSE IF (@v_current = '2')
BEGIN
:r C:\Script2_to_3.sql
SET @v_success = 'Y'
END
IF (@v_success = 'Y')
BEGIN
BEGIN TRANSACTION
UPDATE [dbo].[VersionTable]
SET [Version] = '3';
COMMIT TRANSACTION;
END
END
GO
Questions 2:
I read the following and not sure Is it a good idea to use xp_cmdshell?
In order to minimize the security risk of executing malicious code outside SQL Server 2005, xp_cmdshell is disabled by default, and the following error message will be shown when xp_cmdshell is being executed.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Question 3:
Is there anyother approach for invoking a .sql from within another .sql or a stored procedure?
Article in
http://blog.sqlauthority.com/2009/01/05/sql-server-sqlcmd-vs-osql-basic-comparison/
says that SQLCMD can be used to accomplish this.
Also a posting in
http://stackoverflow.com/questions/338293/what-are-the-differences-between-osql-isql-and-sqlcmd
says that
- osql is the The older, ODBC-based way of command-line communication with SQL Server, and sqlcmd is the newest, fanciest command-line interface to SQL Server.
- osql uses ODBC to communicate to the server and will no longer included after SQL2005
- sqlcmd used OLE DB to communicate to the server and is currently the recommended command line tool.
Can you please point to be some sample to know how to do it?
Thanks!
March 7, 2012 at 11:59 pm
Is it not an option to create a stored procedure from that .sql file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2012 at 11:53 am
Sure, the script could be made as a Stored procedure.
March 8, 2012 at 1:06 pm
Then why not create a stored procedure instead of trying to bypass security to execute a .sql file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply