CODEWITHSUNDEEP
sql-servert-sql
Peeyush
Peeyush

Reputation: 95

How to read from a file which has SQL script which needs to be executed on multiple DBs on same server

Below script suppose to be executed on multiple DBs, however on parsing gives below error-

Msg 102, Level 15, State 1, Line 40 Incorrect syntax near '+'.

declare @list table (name varchar(128) not null);

fill the list with your custom subset
insert into @list
select name from sys.databases where name like '%connect%';

--select * from @list
--select min(name) from @list

--read the script
declare
    @DBServerName varchar(128),
    @FilePathName varchar(128),
    @DBName varchar(128)
;

set @DBServerName = 'peeyushp-w8';
set @FilePathName = 'D:\script.sql';
set @DBName = '';

--EXEC xp_cmdshell  'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName


declare
    @db varchar(128),
    @t varchar(max),
    @s varchar(max)
;

--set @t = 'use {db}; exec sp_spaceused';

set @db = (select min(name) from @list);

while @db is not null
begin
    --set @s = replace(@t, '{db}', ' -d' + @db);

    --exec (@s);
    EXEC xp_cmdshell  'sqlcmd -S ' + @DBServerName + @s + ' -i ' + replace(@FilePathName, '{db}', ' -d' + @db);

    set @db = (select min(name) from @list where name > @db);
end
GO

Error is coming on 'xp_cmdshell' exec statement. Am i missing something which to be included in my SQL script. Will appreciate any help on the same.

Upvotes: 0

Views: 38

Answers (1)

Mincong Huang
Mincong Huang

Reputation: 5552

According to PROCEDURE's Limitations and Restrictions in T-SQL :

You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. However, you can pass a function as a variable as shown in the following example.

So, it is impossible to use directly the string concatenation as procedure parameter. It must be declared before : 

DECLARE @cmd VARCHAR(400)
SET @cmd = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName

-- Now you can use it
EXEC xp_cmdshell @cmd

Upvotes: 1

Related Questions