Using sp_MsForEachDb, to print quote ' inside a print clause

Transact-SQL
1

30 October is a special holiday in my country.
I had to mark holiday in a certain table present
in any database.

I decided to use sp_msforeachdb

While trying to construct the expression , I found that I am unable
to surround a character with quote character.
I even tried using four quotes '''' but no success.
I gave up and used char(39) as a workaround.

here is the complete syntax

exec sp_MsForEachDb
'
if exists( select null from [?].sys.tables where name = ''alldates'' )
begin
	print ''use '' + ''?'' +''  ''
	+ '' update a set a.isholiday = '' + char(39)
	+ ''Y'' + char(39)
	+ '', a.holiday_name = '' + char(39)
	+ ''Eid Milad'' + char(39)
	+ '', a.gz_holiday =''  + char(39)
	+ ''Y'' + char(39)
	+ ''from alldates  a where 1=1 and dt = ''
	+ char(39) + ''2020/10/30'' + char(39)
end
'

the result will be

use sqlAuthority
update a
set a.isholiday = 'Y',
a.holiday_name = 'Eid Milad',
a.gz_holiday ='Y'
from alldates a
where 1=1
and dt = '2020/10/30'

Please note that If I replace char(39) with ''''
I don't get ' (quote) printed.

My Question
I must be missing some basics, please guide
how to print quote inside a print clause

Here is shcema of the table alldates used above.


CREATE TABLE [dbo].[alldates](
	[dt] [date] NOT NULL,
	[isholiday] [varchar](1) NULL,
	[gz_holiday] [varchar](1) NULL,
	[holiday_name] [varchar](30) NULL,
PRIMARY KEY CLUSTERED 
(
	[dt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
2 
print 'use [?]  ' 
	+ ' update a set a.isholiday = ''Y''' 
	+ ', a.holiday_name = ''Eid Milad'''
	+ ', a.gz_holiday =''Y'' '
	+ 'from alldates  a  where 1=1 and dt = ''2020/10/30'''
3

Hi @ScottPletcher
I am getting error in syntax
error

4

Or it might be a good time to move such shared tables to a shared resources db

1 Like
5

Make sure it's a "standard" ?, type ? back over it. The sp_msforeachdb proc should of course replace the ? with the current db name.

6

Nice suggestion.

Thanks

7

@ScottPletcher
I have copy pasted your script.

but the same error

8

There is no issue of ? in mine expression.

Capture
Capture1104×418 51.9 KB

9

I did, but still the same error..

10

Sorry, I mistakenly copied the code after it was PRINTed out and not the original code for inside the EXEC. This is the code that should be in the EXEC sp_msforeachdb:

...
begin
	print ''use [?]  '' 
	+ '' update a set a.isholiday = ''''Y'''''' 
	+ '', a.holiday_name = ''''Eid Milad''''''
	+ '', a.gz_holiday =''''Y''''''
	+ ''from alldates  a where 1=1 and dt = ''''2020/10/30''''''
...
1 Like
11

Yes it worked.
Thanks and regards.

12

@ScottPletcher

It seems that I cannot use +''''

If I add + '''' + '''' + ''' + '''' + '''' in any line
it will simply be ignored.and the syntax will work fine.

I just can't figure out why?

13

so why do you want to print it again? to copy paste and run it? Maybe another approach with powershell

# ----------------------------------------------
Import-Module SqlPs -DisableNameChecking #may only need this line for SQL 2012 +
 
# $databases grabs list of databases from sys.databases
$server = 'your.server.name'
$databases = invoke-sqlcmd -ServerInstance $server -Database 'master' -Query "select name as DATABASENAME from sys.databases"

 
foreach ($database in $databases) #for each separate server / database pair in $databases
{
    $DBname = $database.DATABASENAME #databasename from the select query
    
    Write-Output $DBname
    invoke-sqlcmd -ServerInstance $server -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\UpdateHoliday\SetEidMilad.sql"
  
} #end foreach loop
2 Likes