Error-Database connect using dynamic SQL

• SQLIndia

  Ten Centuries

  Points: 1073

  More actions

  May 11, 2005 at 3:40 am

  #88397

  Hi Folks

  I am try to do run following code and create a role..

  But do not create the role to the database I am connecting in the code rather to which I have connected to when i run this copde in Query Analyser. If I am using 'USE database' command in code then it should connect to the require database and create the role..

  Unfortunately.. not What I am missing here..

  script.....................................Start here

  declare @usedb varchar(50)

  declare @sqldb sysname

  set @sqldb = (select name from master.dbo.sysdatabases where name = 'samy')--db_name())print @sqldb

  set @usedb = 'use ' + @sqldb

  exec(@usedb)

  declare @adrole nvarchar(100)

  declare @role nvarchar(50)

  set @role2 = 'me'

  set @adrole =' '

  set @adrole = 'EXEC sp_addrole ' +char(39)+@role+char(39)

  execute sp_executesql @adrole

  Script ..........................................End here..

  Thanx in advance

  SqlIndia

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  May 11, 2005 at 5:29 am

  #558019

  If it needs to be dynamic sql, put everything in one string and execute this. Something along these lines:

  DECLARE @stmt NCHAR(1000)

  SET @stmt = 'USE Frank_3 '

   + CHAR(13) +

   'DECLARE @stmt NCHAR(1000)'

   + CHAR(13) +

   'SET @stmt = ''CREATE TABLE First (col_a int)'''

   + CHAR (13) +

   'PRINT (@stmt)'

  EXEC sp_executesql @stmt

  SET @stmt = 'USE Frank_3 '

   + CHAR(13) +

   'DECLARE @stmt NCHAR(1000)'

   + CHAR(13) +

   'SET @stmt = ''CREATE TRIGGER blahblah ON FIRST FOR INSERT AS SELECT * FROM FIRST'''

   + CHAR (13) +

   'EXEC (@stmt)'

  EXEC sp_executesql @stmt

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]