November 22, 2012 at 9:46 pm
I am getting error while executing this stored procedure
Please help.
/*==========================================================================================
Name: Export all stored procedures for all user databases to particular location
Author: Aadhar Joshi
Parameters:
@ExportDataPath specifies location to where backup of sp needs to store. eg. 'C:\Backup\StoredProcedure\'
Returns:
Description: It creates main folder in @ExportDataPath which contains current date and time, in that folder it creates different folders for each databases and
creates stored procedure related to database.
==========================================================================================*/
ALTER PROCEDURE [dbo].[USP_BackupAllStoredProcedures]
(
@ExportDataPath NVARCHAR(1000) = NULL
)
AS
BEGIN
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
BEGIN TRY
DECLARE @ExportPath AS NVARCHAR(1000)
SET @ExportPath = @ExportDataPath
IF ( ISNULL(@ExportPath, '') = '' )
BEGIN
SET @ExportPath = 'C:\Backup1\StoredProcedure\'
END
SET @ExportPath += ( SELECT CONVERT(VARCHAR(100), GETDATE(), 102)
+ '_'
+ REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108),
':', '.')
) + '\'
-- variables for first while loop
DECLARE @DatabaseName AS NVARCHAR(1000)
-- variables for second while loop
DECLARE @ExportFilePath NVARCHAR(1000)
DECLARE @ServerName NVARCHAR(100)
SELECT @ServerName = CONVERT(SYSNAME, SERVERPROPERTY(N'servername'))
DECLARE @GetProcedureNames NVARCHAR(MAX)
IF OBJECT_ID('tempdb..#Databases') IS NOT NULL
DROP TABLE #Databases
SELECT name ,
ROW_NUMBER() OVER ( ORDER BY name ) AS RowNum
INTO #Databases
FROM sys.databases
WHERE database_id > 4
DECLARE @DatabaseCurrentPosition INT = 1
WHILE @DatabaseCurrentPosition <= ( SELECT COUNT(1)
FROM #Databases
)
BEGIN
SELECT @DatabaseName = NAME
FROM #Databases
WHERE RowNum = @DatabaseCurrentPosition
SET @ExportFilePath = @ExportPath + @DatabaseName
EXECUTE master.dbo.xp_create_subdir @ExportFilePath
IF OBJECT_ID('tempdb..#Procedures') IS NOT NULL
DROP TABLE #Procedures
CREATE TABLE #Procedures
(
RoutineName NVARCHAR(100) ,
RoutineDefinition NVARCHAR(MAX) ,
RowNum INT
)
--SET @GetProcedureNames = N'SELECT r.Routine_Definition as RoutineDefinition,r.Routine_Name as RoutineName,ROW_NUMBER() OVER ( ORDER BY r.Routine_Name) AS RowNum
--into ##Procedures FROM ' + @DatabaseName
--+ '.INFORMATION_SCHEMA.Routines r '
SET @GetProcedureNames = N'INSERT INTO #Procedures SELECT QUOTENAME(s.[name]) + ''.'' + QUOTENAME(o.[name]) AS RoutineName
,sm.[definition] AS RoutineDefinition ,ROW_NUMBER() OVER ( ORDER BY sm.[definition]) AS RowNum FROM '
+ @DatabaseName + '.sys.objects AS o INNER JOIN '
+ @DatabaseName
+ '.sys.schemas AS s ON s.[schema_id] = o.[schema_id] INNER JOIN '
+ @DatabaseName
+ '.sys.sql_modules sm ON o.[object_id]=sm.[object_id]
WHERE type IN (''p'',''v'',''fn'') AND o.is_ms_shipped = 0 '
--EXEC(@GetProcedureNames)
EXECUTE sys.sp_executesql @GetProcedureNames
DECLARE @ProcedureCurrentPosition INT = 1
WHILE @ProcedureCurrentPosition <= ( SELECT
COUNT(1)
FROM #Procedures
)
BEGIN
DECLARE @ProcedureContent NVARCHAR(MAX)
DECLARE @ProcedureName NVARCHAR(MAX)
SELECT @ProcedureContent = RoutineDefinition ,
@ProcedureName = RoutineName
FROM #Procedures
WHERE RowNum = @ProcedureCurrentPosition
DECLARE @Que NVARCHAR(1000)
SET @ExportFilePath = @ExportPath + @DatabaseName
+ '\' + @ProcedureName + '.sql'
DECLARE @procData NVARCHAR(MAX)
SET @procData = @ProcedureContent
DECLARE @DynamicTable NVARCHAR(100)
SELECT @DynamicTable = '##ResultSetFinal' + REPLACE(CAST(NEWID() AS NVARCHAR(50)),'-','')
PRINT '123'
EXEC('IF OBJECT_ID(''tempdb..' + @DynamicTable + ''') IS NOT NULL
DROP TABLE ' + @DynamicTable + '
SELECT *
INTO ' + @DynamicTable + '
FROM ( SELECT ''' + @procData + ''' AS ProcData
) AS FinalData ')
PRINT '456'
DECLARE @c1 nvarchar(1000)
SET @Que = N'"SELECT ProcData from ' + @DynamicTable + ' "'
PRINT @Que
DECLARE @Quest NVARCHAR(1000)
--EXECUTE sp_executesql @SQLString, N'@result varchar(30) OUTPUT',@result=@CountSQLQuery OUTPUT;
--EXECUTE sp_execute @c1,N'@Que nvarchar(100) OUTPUT', @Que = @Quest OUTPUT;
--SELECT @Quest AS a
--SELECT @Que AS b
PRINT '789'
DECLARE @sql NVARCHAR(4000)
SELECT @sql = 'bcp ' + @Que + ' queryout '
+ @ExportFilePath + ' -c -t -T -S' + ''
+ @ServerName + ''
PRINT @sql
exec('select * from ' + @DynamicTable + '')
EXEC xp_cmdshell @sql
SET @ProcedureCurrentPosition = @ProcedureCurrentPosition
+ 1
END
SET @DatabaseCurrentPosition = @DatabaseCurrentPosition
+ 1
END
END TRY
BEGIN CATCH
-- Raise an error with the details of the exception
DECLARE @ErrMsg NVARCHAR(4000) ,
@ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE() ,
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity,1)
RETURN
END CATCH ;
END
November 22, 2012 at 10:25 pm
Aadhar Joshi (11/22/2012)
I am getting error while executing this stored procedurePlease help.
Please provide the actual error you're getting.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2012 at 10:51 pm
When it takes procedure data from any stored procedure and that procedure contains '' then it produces error
Eg..
for following stored procedure it consider as procdata
USE [LocalTestData]
GO
/****** Object: StoredProcedure [dbo].[spExportData] Script Date: 11/23/2012 11:19:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spExportData]
(
@dbName varchar(100) = 'master',
@sql varchar(5000) = '',
@fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]
from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
Exec master..xp_cmdshell @sql
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
it produces
Msg 50000, Level 15, State 1, Procedure USP_BackupAllStoredProcedures, Line 147
Incorrect syntax near ',
@sql varchar(5000) = ',
@fullFileName varchar(100) = '
)
as
if @sql = ' or @fullFileName = '
begin
sel'.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply