December 15, 2010 at 12:02 am
I am trying to create a procedure against all my databases and I am not able to see the error. I have look at the code but I have looked at it so long I do not see the issue. a new set of eyes will be helpful.
This is my error "Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'ALTER"
Thanks in advance
--This statement creates a stored procedure in each user database
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''ALTER
PROCEDURE [dbo].[sp_AddInputError]
@InputID BIGINT,
@IsPrereg BIT
AS
BEGIN
DECLARE @Tmp TABLE (
ID bigint not null
, HospitalID varchar(50) not null
, Account varchar(50) not null
, EmployeeID varchar(50)
, PatientTypeID varchar(50)
, FinancialClassID varchar(50)
, InsPlanCode varchar(50)
, LocationCode varchar(50)
, regdate datetime
, ImportDate datetime
)
INSERT INTO @Tmp
( ID
, HospitalID
, Account
, EmployeeID
, PatientTypeID
, FinancialClassID
, InsPlanCode
, LocationCode
, regdate
, ImportDate)
SELECT
[Account].ID
, [Account].HospitalID
, [Account].Account
, [Account].EmployeeID
, [Account].PatientTypeID
, [Account].FinancialClassID
, [Account].InsPlanCode
, [Account].LocationCode
, [Account].regdate
, [Account].ImportDate
FROM [dbo].[Account]
INNER JOIN [dbo].[Input] ON [Account].[HospitalID] = [Input].[HospitalID]
AND [Account].[Account] = dbo.GetAccount([Input].[Account], @IsPrereg)
AND [Account].[ID] = [Input].[ID]
WHERE ([Input].[ID] = @InputID)
IF NOT EXISTS (SELECT * FROM @Tmp) RETURN 0
INSERT INTO [dbo].[InputError]
( [ID]
, [HospitalID]
, [Account]
, [EmployeeID]
, [PatientTypeID]
, [FinancialClassID]
, [InsPlanCode]
, [LocationCode]
, [regdate]
, [ImportDate]
, [EntryDate]
, [CreationDate]
, [error1]
, [error2]
, [error3]
, [error4]
, [error5]
, [error6]
, [error7]
, [error8]
, [FieldCount]
, [NACount]
, [ErrorPercent1]
, [ErrorPercent2])
SELECT
tmp.[ID]
, tmp.[HospitalID]
, tmp.[Account]
, tmp.[EmployeeID]
, tmp.[PatientTypeID]
, tmp.[FinancialClassID]
, tmp.[InsPlanCode]
, tmp.[LocationCode]
, tmp.[regdate]
, tmp.[ImportDate]
, null as [EntryDate]
, tmp.[ImportDate] as [CreationDate]
, 0 as [error1]
, 0 as [error2]
, 0 as [error3]
, 0 as [error4]
, 0 as [error5]
, 0 as [error6]
, 0 as [error7]
, 0 as [error8]
, 0 as [FieldCount]
, 0 as [NACount]
, 0 as [ErrorPercent1]
, 0 as [ErrorPercent2]
FROM @Tmp as tmp
LEFT JOIN [dbo].[InputError] ON [InputError].[HospitalID] = tmp.[HospitalID]
AND [InputError].[Account] = tmp.[Account]
AND [InputError].[ID] = tmp.[ID]
WHERE ([InputError].[ID] IS NULL) -- does not exists
IF (@@RowCount = 0) BEGIN
UPDATE [InputError] SET
[EmployeeID] = tmp.[EmployeeID]
, [PatientTypeID] = tmp.[PatientTypeID]
, [FinancialClassID] = tmp.[FinancialClassID]
, [InsPlanCode] = tmp.[InsPlanCode]
, [LocationCode] = tmp.[LocationCode]
, [regdate] = tmp.[regdate]
, [ImportDate] = tmp.[ImportDate]
, [EntryDate] = null
, [CreationDate] = tmp.[ImportDate]
, [error1] = 0
, [error2] = 0
, [error3] = 0
, [error4] = 0
, [error5] = 0
, [error6] = 0
, [error7] = 0
, [error8] = 0
, [FieldCount] = 0
, [NACount] = 0
, [ErrorPercent1] = 0
, [ErrorPercent2] = 0
FROM @Tmp as tmp
INNER JOIN [dbo].[InputError] ON [InputError].[HospitalID] = tmp.[HospitalID]
AND [InputError].[Account] = tmp.[Account]
AND [InputError].[ID] = tmp.[ID]
IF (@@RowCount = 0) RETURN 3 -- something weird
END
UPDATE [InputError] SET [CreationDate] = [Summary].[MinImportDate]
FROM @Tmp as tmp
INNER JOIN [dbo].[InputError] ON [InputError].[HospitalID] = tmp.[HospitalID]
AND [InputError].[Account] = tmp.[Account]
INNER JOIN (SELECT [HospitalID], [Account], Min([ImportDate]) as [MinImportDate]
FROM [dbo].[InputError]
GROUP BY [HospitalID], [Account]
) as [Summary] ON [Summary].[HospitalID] = tmp.[HospitalID]
AND [Summary].[Account] = tmp.[Account]
RETURN 2
END'')
END'
EXEC sp_MSforeachdb @command
December 15, 2010 at 3:13 am
It looks like your command is getting truncated so the opening quotation mark never gets terminated.
You could declare @command as varchar(max) but unfortunately in this case I don't think it will work as there seems to be a limit to the size of the command you can pass to MSforeachdb, here's the definition from master db:
ALTER proc [sys].[sp_MSforeachdb]
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
If you need to go down this route it might be best to look into doing this through dynamic sql (using sp_executesql) and looping through each db with a cursor.
December 15, 2010 at 3:51 am
Hi
This link may be useful
http://nickstips.wordpress.com/2010/10/18/sql-making-a-stored-procedure-available-to-all-databases/
December 15, 2010 at 6:57 pm
I appreciate the assistant but the suggestion did not work. Using sp_executesql what is your recommendation on proceeding.
December 15, 2010 at 6:59 pm
The method would work but I cannot use this solution as I need for this procedure for each database. do you have any other solution or method to carry out.
December 15, 2010 at 8:12 pm
I received this cursor, that works fine if the procedure does not exists, what I want to acccomplish is to alter the procedure, how can I change this cursor
DECLARE @DbName VARCHAR(100),
@SP_Name VARCHAR(100),
@sql NVARCHAR(MAX),
@sp_def VARCHAR(MAX)
SET @SP_Name = 'sp_AddInputError'
--get the definition of the stored procedure
SELECT
@sp_def = sm.definition
FROM sys.sql_modules sm
WHERE sm.OBJECT_ID = OBJECT_ID(@SP_Name)
--cursor to loop through the databases, excluding system dbs
DECLARE cursorDB_Action CURSOR FAST_FORWARD FOR
SELECT d.Name
FROM SYS.DATABASES d
WHERE d.Name not in('Master','model','msbd', 'tempdb') or database_id > 4
OPEN cursorDB_Action
FETCH NEXT FROM cursorDB_Action INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'
Use ' + QUOTENAME(@dbname) + ';' + CHAR(13) + '
IF NOT EXISTS (
SELECT 1
FROM sys.procedures
WHERE
object_id = OBJECT_ID(@dyn_sp_name)
AND type in (N''P'', N''PC''))
BEGIN
PRINT ''Creating sp in database: ' + @dbname + '''
EXEC(@dyn_sql)
END'
--PRINT @sql
EXEC sp_executesql
@sql,
N'@dyn_sql VARCHAR(MAX), @dyn_sp_name varchar(100)',
@dyn_sql = @sp_def,
@dyn_sp_name = @SP_Name
FETCH NEXT FROM cursorDB_Action INTO @dbname
END
CLOSE cursorDB_Action
DEALLOCATE cursorDB_Action
December 17, 2010 at 2:22 am
You could probably just use the If..else logic already built into your procedure to do this.
So declare the alternative ALTER statement at the top of the query, something like this..
DECLARE @dyn_sql_ALTER varchar(max) = REPLACE(@dyn_sql,'CREATE PROCEDURE','ALTER PROCEDURE')
then expand your IF NOT EXISTS to include an else
IF NOT EXISTS (
SELECT 1
FROM sys.procedures
WHERE
object_id = OBJECT_ID(@dyn_sp_name)
AND type in (N''P'', N''PC''))
BEGIN
PRINT ''Creating sp in database: ' + @dbname + '''
EXEC(@dyn_sql)
END
ELSE
EXEC(@dyn_sql_ALTER)
END
'
December 20, 2010 at 6:13 am
this link can help you to apply any sql script on multiple DBs one time
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 20, 2010 at 7:03 am
I appreciate all the help and suggestions, I went with this selection that was provided. Many thanks
DECLARE @DbName VARCHAR(100),
@SP_Name VARCHAR(100),
@sql NVARCHAR(MAX),
@sp_def VARCHAR(MAX)
SET @SP_Name = 'sp_AddInputError'
--get the definition of the stored procedure
SELECT @sp_def = sm.definition
FROM sys.sql_modules sm
WHERE sm.OBJECT_ID = OBJECT_ID(@SP_Name)
--cursor to loop through the databases, excluding system dbs
DECLARE cursorDB_Action CURSOR FAST_FORWARD FOR
SELECT d.Name
FROM SYS.DATABASES d
WHERE d.database_id > 4 or d.name not in('master','model','msdb','tempdb')
OPEN cursorDB_Action
FETCH NEXT FROM cursorDB_Action INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'
Use [' + @dbname + '];
IF EXISTS (
SELECT *
FROM sys.objects
WHERE
object_id = OBJECT_ID(N''' + @SP_Name + ''')
AND type in (N''P'', N''PC''))
BEGIN
PRINT ''Dropping sp in database: ' + @dbname + '''
DROP PROCEDURE [' + @SP_Name + ']
END
PRINT ''Creating sp in database: ' + @dbname + '''
EXEC(N''' + @sp_def + ''')
'
--PRINT @sql
EXEC(@sql)
FETCH NEXT FROM cursorDB_Action INTO @dbname
END
CLOSE cursorDB_Action
DEALLOCATE cursorDB_Action
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply