none
VBS - SQL Query RRS feed

  • Question

  • Hello and sorry for my bad English,

    I had a problem with a .VBS Script

    the function of this script is, that is create a "User" on the SQL Server and in the database


    Const adUseClient = 3
    Dim cn
    Set cn = CreateObject("ADODB.Connection")
    
    dim sql
    sql = "USE [master] " & _
    "GO " & _
    "CREATE LOGIN [Domain\m2t3_test] FROM WINDOWS WITH DEFAULT_DATABASE=[master] " & _
    "GO " & _
    "USE [SL_Daten]" & _
    "GO " & _
    "CREATE USER [Domain\m2t3_test] FOR LOGIN [Domain\m2t3_test] " & _
    "GO " & _
    "USE [SL_Daten] " & _
    "GO " & _
    "EXEC sp_addrolemember N'db_datareader', N'Domain\m2t3_test' " & _
    "GO " & _
    "USE [SL_Daten] " & _
    "GO " & _
    "EXEC sp_addrolemember N'db_datawriter', N'Domain\m2t3_test' " & _
    "GO " & _
    "USE [SL_Log_Daten] " & _
    "GO " & _
    "CREATE USER [Domain\m2t3_test] FOR LOGIN [Domain\m2t3_test] " & _
    "GO " & _
    "USE [SL_Log_Daten] " & _
    "GO " & _
    "EXEC sp_addrolemember N'db_datareader', N'Domain\m2t3_test' " & _
    "GO " & _
    "USE [SL_Log_Daten] " & _
    "GO " & _
    "EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\m2t3_test' " & _
    "GO"
    
    
    with cn 
        .CursorLocation = adUseClient
        .Open "Driver={SQL Server};Server=SQLSERVER\SLSQL; Database=master; UID=AA; PWD=AA"
        .CommandTimeout = 0
        .Execute(sql)
    end with
    
    cn.close
    set cn = Nothing
    
    

    If i start the Script, i get a Error on Line 40

    Synatax Error near the 'GO'.


    How i can converte the SQL Query to VBScript?


    thx for your Help!



    Freundliche Grüsse

    Wednesday, March 25, 2015 9:38 AM

Answers

  • GO is not usable here:

    sql = "CREATE LOGIN [Domain\m2t3_test] FROM WINDOWS WITH DEFAULT_DATABASE=[master]" _
    	& "EXEC sp_addrolemember N'db_datareader', N'Domain\m2t3_test'" _
    	& "USE [SL_Daten]" _
    	& "EXEC sp_addrolemember N'db_datawriter', N'Domain\m2t3_test' " _
    	& "CREATE USER [Domain\m2t3_test] FOR LOGIN [Domain\m2t3_test]" _
    	& "EXEC sp_addrolemember N'db_datareader', N'Domain\m2t3_test'" _
    	& "EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\m2t3_test'"

    You do not need multiple "USE" statements.


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, March 25, 2015 1:07 PM
    • Proposed as answer by Mike Laughlin Wednesday, March 25, 2015 1:56 PM
    • Marked as answer by Schmidi_ch Monday, April 13, 2015 8:37 AM
    Wednesday, March 25, 2015 1:06 PM

All replies

  • GO is not usable here:

    sql = "CREATE LOGIN [Domain\m2t3_test] FROM WINDOWS WITH DEFAULT_DATABASE=[master]" _
    	& "EXEC sp_addrolemember N'db_datareader', N'Domain\m2t3_test'" _
    	& "USE [SL_Daten]" _
    	& "EXEC sp_addrolemember N'db_datawriter', N'Domain\m2t3_test' " _
    	& "CREATE USER [Domain\m2t3_test] FOR LOGIN [Domain\m2t3_test]" _
    	& "EXEC sp_addrolemember N'db_datareader', N'Domain\m2t3_test'" _
    	& "EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\m2t3_test'"

    You do not need multiple "USE" statements.


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, March 25, 2015 1:07 PM
    • Proposed as answer by Mike Laughlin Wednesday, March 25, 2015 1:56 PM
    • Marked as answer by Schmidi_ch Monday, April 13, 2015 8:37 AM
    Wednesday, March 25, 2015 1:06 PM
  • Thank you ! it's woork :-)

    Freundliche Grüsse

    Wednesday, March 25, 2015 1:48 PM