Oscar Foley Oscar Foley - 7 months ago 11
SQL Question

How to execute this t-sql script that syntax checking does not allow me?

This script fails to execute from PowerShell (sqlcmd) and directly from SQL Management Studio:

IF db_id('BANANATEST') is not null
BEGIN
print 'DB BANANATEST already exists. NO ACTION TAKEN.'
END
ELSE
BEGIN
print 'DB BANANATEST does NOT exist.'
print 'Restoring a backup of BANANATEST'
RESTORE DATABASE BANANATEST
FROM DISK = 'BANANATEST.original.bak' ;

print 'Creating User BANANATEST'
USE master
If Exists (select loginname from master.dbo.syslogins where name ='BANANATEST')
Begin
DROP LOGIN [BANANATEST]
END
CREATE LOGIN [BANANATEST] WITH PASSWORD=N'BANANATEST', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
USE BANANATEST
ALTER USER [BANANATEST] WITH LOGIN = [BANANATEST]
END


The error is Syntax.


Msg 911, Level 16, State 1, Line 20 Database 'BANANATEST' does not
exist. Make sure that the name is entered correctly.


While it is accurate, BANANATEST DB doesn't exist in the moment of query "compilation"... SQL Server doesn't realise that database WILL exist when it reaches this line.

How can I make it work? Either by ignoring syntax checks or whatever it make it work...

NOTE: The backup comes with a BANANATEST usr and I want to cover two scenarios:


  1. LOGIN already exists in SQL Server

  2. LOGIN does not exist


Answer

You can get around the go with #temp tables

CREATE table #temp12 (restore1  bit)
IF db_id('BANANATEST') is not null
 BEGIN   
  print 'DB BANANATEST already exists. NO ACTION TAKEN.'
 END
ELSE
 BEGIN
  print 'DB BANANATEST does NOT exist.'
  print 'Restoring a backup of BANANATEST'
  RESTORE DATABASE BANANATEST
   FROM DISK = 'BANANATEST.original.bak' ;
  INSERT INTO #temp12 VALUES ('1')
  print 'Creating User BANANATEST'
 END 
 GO
  USE master
  IF EXISTS (SELECT 1 from #TEMP12)
      BEGIN
      If Exists (select loginname from master.dbo.syslogins where name ='BANANATEST')
      Begin
       DROP LOGIN [BANANATEST]
      END
      CREATE LOGIN [BANANATEST] WITH PASSWORD=N'BANANATEST', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
      USE BANANATEST
      ALTER USER [BANANATEST] WITH LOGIN = [BANANATEST]
  END
DROP TABLE #temp12
Comments