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)

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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, [e-mail]

Answer 7

I used powershell  and put the script into taskscheduler   for automation.

Thanks

Supersent