Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-20 : 07:56:16
|
| Chris writes "Hi,I was able to use:declare @SqlStr NVARCHAR(MAX) ,@Sql NVARCHAR(100) ,@TargetServer VARCHAR(100) ,@TargetDataBase VARCHAR(100)SET @TargetDataBase = 'abc'SET @TargetServer = 'Srever1'set @SqlStr = ' CREATE DATABASE ' + @TargetDataBase + 'ON( NAME = ' + @TargetDataBase + '_dat, FILENAME = ''H:\MSSQL\DATA\' + @TargetDataBase + 'dat.mdf'', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = ' + @TargetDataBase + '_log, FILENAME = ''O:\MSSQL\DATA\' + @TargetDataBase + '.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5 MB)'Execute Server1.master.dbo.sp_executesql @SqlStrto create a remote database successfully.However, I was not able to use dynamic T-SQL to create one:declare @SqlStr NVARCHAR(MAX) ,@Sql NVARCHAR(100) ,@TargetServer VARCHAR(100) ,@TargetDataBase VARCHAR(100)SET @TargetDataBase = 'abc'SET @TargetServer = 'Server1'set @SqlStr = @TargetServer + '.master.dbo.sp_executesql' + ' CREATE DATABASE ' + @TargetDataBase + 'ON( NAME = ' + @TargetDataBase + '_dat, FILENAME = ''H:\MSSQL\DATA\' + @TargetDataBase + 'dat.mdf'', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = ' + @TargetDataBase + '_log, FILENAME = ''O:\MSSQL\DATA\' + @TargetDataBase + '.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5 MB)'EXEC('EXEC sp_executesql '+ @SqlStr)I also tried other syntaxes but no luck. Any hint? Thanks.Chris" |
|
|
|
|
|
|
|
Dynamic T-SQL to create a remote database