locked
create function in for the database having string "sandy" RRS feed

  • 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


    • Edited by SimpleSQL Monday, July 8, 2019 11:26 AM
    • Marked as answer by supersent Thursday, August 1, 2019 6:57 AM
    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


    • Edited by SimpleSQL Monday, July 8, 2019 11:26 AM
    • Marked as answer by supersent Thursday, August 1, 2019 6:57 AM
    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
    

    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