Does CMDSQL comment out EXEC?

• CriticalRationalist

  Right there with Babe

  Points: 722

  More actions

  June 24, 2010 at 10:36 am

  #220350

  Hi,

  I have some unexpected behaviour in a CMDSQL script:

  USE [master]

  GO

  IF (SELECT COUNT(*) FROM sys.sql_logins WHERE name = '$(newUser)') < 1

  CREATE LOGIN [$(newUser)] WITH PASSWORD=N'$(newPassword)', DEFAULT_DATABASE=[$(newDefaultDatabase)], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

  GO

  USE $(newDefaultDatabase)

  GO

  DECLARE @sUser sysname

  SELECT @sUser = '$(newUser)'

  IF (SELECT COUNT(*) FROM sys.database_principals WHERE name = '$(newUser)') < 1

  CREATE USER [$(newUser)] FOR LOGIN [$(newUser)]

  ELSE

  BEGIN

  DECLARE @tblUsers TABLE (UserName sysname NOT NULL, UserId UniqueIdentifier NOT NULL)

  INSERT @tblUsers

  EXEC sp_change_users_login 'Report'

  IF (SELECT COUNT(*) FROM @tblUsers WHERE UserName = '$(newUser)') < 1

  EXEC sp_change_users_login 'Update_One', @sUser,@sUser

  END

  I use CMDSQL like

  sqlcmd -E -S localhost\sql2005 -d "mydatabase" -v newUser="testuser" -v newPassword="password" -v newDefaultDatabase="mydatabase" -i CreateSQLUser.sql

  and when I fire up a profiler trace, I get:

  DECLARE @sUser sysname

  SELECT @sUser = 'testuser'

  IF (SELECT COUNT(*) FROM sys.database_principals WHERE name = 'testuser') < 1

  CREATE USER [testuser] FOR LOGIN [testuser]

  ELSE

  BEGIN

  DECLARE @tblUsers TABLE (UserName sysname NOT NULL, UserId UniqueIdentifier NOT NULL)

  INSERT @tblUsers

  EXEC sp_change_users_login 'Report'

  IF (SELECT COUNT(*) FROM @tblUsers WHERE UserName = 'testuser') < 1

  --*sp_change_users_login------------------------------

  END

  So, it seems that EXEC can be used if the result is redirected, but otherwise it can't?

  So, if I create a dummy table, my code isn't commented out:

  DECLARE @sUser sysname

  SELECT @sUser = 'testuser'

  IF (SELECT COUNT(*) FROM sys.database_principals WHERE name = 'testuser') < 1

  CREATE USER [testuser] FOR LOGIN [testuser]

  ELSE

  BEGIN

  DECLARE @tblResult TABLE (test int NOT NULL)

  DECLARE @tblUsers TABLE (UserName sysname NOT NULL, UserId UniqueIdentifier NOT NULL)

  INSERT @tblUsers

  EXEC sp_change_users_login 'Report'

  IF (SELECT COUNT(*) FROM @tblUsers WHERE UserName = 'testuser') < 1

  INSERT @tblResult EXEC sp_change_users_login 'Update_One', @sUser,@sUser

  END

  Jan