CODEWITHSUNDEEP
sql-serverpython-3.xpython-2.7azure-sql-databasepyodbc
Azar Maniyar
Azar Maniyar

Reputation: 23

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'abcdef'. (102) (SQLExecDirectW)")

ownerpass="abcdef"
owner="yrdy"
sql_login = (
"DECLARE @sql AS NVARCHAR(MAX);"
"DECLARE @Databaseowner sysname = ?;"
"DECLARE @Databaseownerpass sysname = ?;"
"SET @sql = N'if not exists (select * from sys.sql_logins where name = @Databaseowner )'  + N' CREATE LOGIN ' + QUOTENAME(@Databaseowner) + N''' WITH PASSWORD = ' + QUOTENAME(@Databaseownerpass, '''') + N';';"
"EXEC sp_executesql @sql,N'@Databaseowner sysname', N'@Databaseownerpass sysname', @Databaseowner = @Databaseowner, @Databaseownerpass = @Databaseownerpass;")
cursor.execute(sql_login, owner,ownerpass)
=============================================

Getting incorrect syntax error near password

Upvotes: 2

Views: 5140

Answers (3)

Zhorov
Zhorov

Reputation: 29993

First, generate valid T-SQL statement. Your have errors when you use CREATE LOGIN and sp_executesql.

ownerpass = "abcdef"
owner = "yrdy"
sql_login = (
    "DECLARE @sql AS NVARCHAR(MAX);"
    "DECLARE @Databaseowner sysname = ?;"
    "DECLARE @Databaseownerpass sysname = ?;"
    "SET @sql = 
        N'if not exists (select * from sys.sql_logins where name = @Databaseowner )'  + 
        N' CREATE LOGIN ' + QUOTENAME(@Databaseowner) + 
        N' WITH PASSWORD = ' + QUOTENAME(@Databaseownerpass, '''') + N';';"
    "EXEC sp_executesql @sql, N'@Databaseowner sysname, @Databaseownerpass sysname', @Databaseowner, @Databaseownerpass;"
)

Then, if you use ? as a placeholder, execute your script like this:

cursor.execute(sql_login, (owner, ownerpass))

Upvotes: 1

sumit salunke
sumit salunke

Reputation: 125

"SET @sql = N'if not exists (select * from sys.sql_logins where name = @Databaseowner )' + N' CREATE LOGIN ' + QUOTENAME(@Databaseowner) + N''' WITH PASSWORD = ' + QUOTENAME(@Databaseownerpass, '''') + N';';"

how can I add check_policy= off in above line ?

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16431

According what I found, if your sql statement has parameters, you should modify you code like this:

ownerpass="abcdef"
owner="yrdy"
sql_login = (
"DECLARE @sql AS NVARCHAR(MAX);"
"DECLARE @Databaseowner sysname = ?;"
"DECLARE @Databaseownerpass sysname = ?;"
"SET @sql = N'if not exists (select * from sys.sql_logins where name = @Databaseowner )'  + N' CREATE LOGIN ' + QUOTENAME(@Databaseowner) + N''' WITH PASSWORD = ' + QUOTENAME(@Databaseownerpass, '''') + N';';"
"EXEC sp_executesql @sql,N'@Databaseowner sysname', N'@Databaseownerpass sysname', @Databaseowner = @Databaseowner, @Databaseownerpass = @Databaseownerpass;")
cursor.execute(sql_login, (owner,ownerpass))

Reference: (Python) cursor.execute(sql)

Hope this helps.

Upvotes: 1

Related Questions