April 10, 2015 at 10:51 pm
I have one environment where we get number of changes everyday. It takes time to take backup of database and then apply the script. I am thinking to automate it as
1. SQL Script (Hot-fix) will put in Shared folder accessible by SQL server.
2. I am writing a SP which will have parameters. Database name, SQL script, HF number.
SP will first take copy_only and compressed (all db's are 2008R2) backup of database. It will record SQL HF script from table into one row and it will apply that script to database using executesql.
Problem I am facing here is I am not able to take that SQL script in row.
Anybody have any smarter solution?
Sagar Sonawane
** Every DBA has his day!!:cool:
April 10, 2015 at 11:48 pm
Quick suggestion, use openrowset bulk
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SCRIPTFILE') IS NOT NULL DROP TABLE dbo.TBL_SCRIPTFILE;
CREATE TABLE dbo.TBL_SCRIPTFILE
(
SF_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SCRIPTFILE_SF_ID PRIMARY KEY CLUSTERED
,SF_FULL_PATH_NAME NVARCHAR(2048) NOT NULL
,SF_CONTENT VARCHAR(MAX) NOT NULL
);
DECLARE @FILE_FULL_PATH_NAME NVARCHAR(2048) = N'C:\SCRIPT_FILES\MY_SCRIPT_001.sql';
DECLARE @PARAM_STR NVARCHAR(MAX) = N'@FILE_FULL_PATH_NAME NVARCHAR(2048)';
DECLARE @SQL_STR NVARCHAR(MAX) = N'
SELECT
@FILE_FULL_PATH_NAME
,X.BulkColumn
FROM OPENROWSET
(
BULK ' + NCHAR(39) + @FILE_FULL_PATH_NAME + NCHAR(39) + N'
,SINGLE_CLOB
) AS X
';
INSERT INTO dbo.TBL_SCRIPTFILE(SF_FULL_PATH_NAME,SF_CONTENT)
EXEC sp_executesql @SQL_STR, @PARAM_STR, @FILE_FULL_PATH_NAME;
SELECT
SX.SF_ID
,SX.SF_FULL_PATH_NAME
,SX.SF_CONTENT
FROM dbo.TBL_SCRIPTFILE SX;
April 11, 2015 at 12:42 pm
Thanks I got what was missing. Thank you so much.
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply