Answered by:
create function in for the database having string "sandy"

Question
-
Hi ,
Kindly help for this. I am creating 10 functions in the database which has the string "sandy" . DB_SANDY_REPORT... dbs_sandy_weekly….. likewise I have 17 databases
I am running functions by going each database
USE [DB_SANDY_REPORT]
CREATE function
***************************
instead I am trying use DB cursor like below
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name like '%sandy%' --might need to exclude more dbs
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
USE [@db_name]
it is not accepting @db_name throwing error as
"must declare the scalar variable @db_name"
How can I achieve this?
Kindly help
Supersent(Oracle DBA)
- Moved by Tom Phillips Monday, July 8, 2019 7:05 PM TSQL question
Monday, July 8, 2019 10:24 AM
Answers
-
That is a very complicated way to do it. A simple solution is
1. create the function and save it as a script file
2. deploy it on every database using PowerShell script. See the deploy PS script below
$dbs=(Get-DbaDatabase -ServerInstance localhost)|select Name foreach($db in $dbs) { sqlcmd -E -d $db.Name -S localhost -i "C:\dump\Log Flush\sptest.sql" }
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Monday, July 8, 2019 11:26 AM
All replies
-
That is a very complicated way to do it. A simple solution is
1. create the function and save it as a script file
2. deploy it on every database using PowerShell script. See the deploy PS script below
$dbs=(Get-DbaDatabase -ServerInstance localhost)|select Name foreach($db in $dbs) { sqlcmd -E -d $db.Name -S localhost -i "C:\dump\Log Flush\sptest.sql" }
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Monday, July 8, 2019 11:26 AM -
The USE command cannot accept a variable. You need to use "dynamic SQL" to accomplish what you are trying to do.
DECLARE @sqlcmd VARCHAR(MAX); DECLARE @db_name sysname; DECLARE c_db_names CURSOR FOR SELECT name FROM sys.databases WHERE name like '%sandy%' --might need to exclude more dbs OPEN c_db_names FETCH c_db_names INTO @db_name WHILE @@Fetch_Status = 0 BEGIN SET @sqlcmd = 'USE ' + QUOTENAME(@db_name) + ' CREATE FUNCTION '; PRINT @sqlcmd; EXEC(@sqlcmd); FETCH NEXT c_db_names INTO @db_name; END
Monday, July 8, 2019 11:52 AM -
Hello thanks,
I executed below but getting error
DECLARE @sqlcmd VARCHAR(MAX);
DECLARE @db_name sysname;
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name like '%sandy%' --might need to exclude more dbs
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
SET @sqlcmd = 'USE ' + QUOTENAME(@db_name) + '
select @@version';
PRINT @sqlcmd;
EXEC(@sqlcmd);
FETCH NEXT c_db_names INTO @db_name;
END
incorrect syntax near NEXT...
what went wrong here?
Supersent
Monday, July 8, 2019 1:54 PM -
DECLARE @sqlcmd VARCHAR(MAX); DECLARE @db_name sysname; DECLARE c_db_names CURSOR FOR SELECT name FROM sys.databases WHERE name like '%sandy%' --might need to exclude more dbs OPEN c_db_names FETCH NEXT FROM c_db_names INTO @db_name WHILE @@Fetch_Status = 0 BEGIN SET @sqlcmd = 'USE ' + QUOTENAME(@db_name) + ' select @@version'; PRINT @sqlcmd; EXEC(@sqlcmd); FETCH NEXT FROM c_db_names INTO @db_name; END
- Proposed as answer by Rachel_WangMicrosoft contingent staff Tuesday, July 9, 2019 6:39 AM
Monday, July 8, 2019 3:58 PM -
Thanks.
select @@version is working . but my requirement is create function from the assembly
I am getting error as
Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.DECLARE @sqlcmd VARCHAR(MAX);
DECLARE @db_name sysname;
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name like '%sandy%' --might need to exclude more dbs
OPEN c_db_names
FETCH NEXT FROM c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
SET @sqlcmd = 'USE ' + QUOTENAME(@db_name) + '
CREATE FUNCTION [dbo].[UFun_MTAS_Sen](@val [nvarchar](4000))RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER AS
EXTERNAL NAME [SenUDF].[ValidationFunctions].[UFun_MTAS_Sen];
';
PRINT @sqlcmd;
EXEC(@sqlcmd);
FETCH NEXT FROM c_db_names INTO @db_name;
END
close c_db_names
deallocate c_db_names
CREATE FUNCTION [dbo].[UFun_MTAS_Sen](@val [nvarchar](4000))RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER AS
EXTERNAL NAME [SenUDF].[ValidationFunctions].[UFun_MTAS_Sen];
Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Please suggest
Supersent
Tuesday, July 9, 2019 7:41 AM -
Did you see the suggestion from SimpleSQL about using Powershell?
Doing this in T-SQL is not fun at all.You can avoid this problem by doing:
SELECT @sp_executesql = @db_name + '.sys.sp_executesql'
EXEC @sp_executesql N'CREATE FUNCTION ... 'But you still have to integrate the function definition in the script, which makes it difficult to maintain. Not the least if there are quotes in the function definition that needs to be double.
A PowerShell that reads the function definition from a file is a lot more easier.
Just because you have a hammer, does not mean that you use it for the screws as well.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, July 9, 2019 8:51 AM -
I used powershell and put the script into taskscheduler for automation.
Thanks
Supersent
Thursday, August 1, 2019 6:58 AM