Alter Login Problem

• Jon Chase-482546

  SSC Journeyman

  Points: 84

  More actions

  August 11, 2008 at 12:16 pm

  #122852

  We have had a stored procedure in place now for months that would allow the user change their password on the database without having to call up the DBA to have them change it for them. This is a very simple SP that uses the Alter Login command on SQL Server.

  This SP has been working perfectly until today. When they try to change their password it generates an error message: Message: SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

  So I went into the SP and added a line of code that SET ANSI_PADDING ON; after the begin and it works perfectly from SQL Server. The problem though is on the client end, we use Powerbuilder, it fails with the same exact error message no matter what, I have also took the ALTER LOGIN command and put this in a string and did an EXECUTE IMMEDIATE inside of Powerbuilder and it still came back with the same exact error message. We do not want ANSI_PADDING ON for our application as that would create too much blank space in columsn in the tables. I am at a loss here and don't know what to do to make this work on the client end, not understand why all of a sudden this would fail?

  Thanks,

  Jon

• Jon Chase-482546

  SSC Journeyman

  Points: 84

  More actions

  August 11, 2008 at 4:23 pm

  #856422

  I figured out a workaround to this problem .... inside of the client side language I am no longer calling out to the stored procedure but instead putting a dynamic call for the Alter Login and Execute Immediately but in order for this to work I also had to have the SETs on the same line as well ... so this is what I had to do ...

  string ls_string

  ls_string = "SET ANSI_PADDING ON ; SET CONCAT_NULL_YIELDS_NULL ON ; SET ANSI_WARNINGS ON ; ALTER LOGIN " + ls_userid + " WITH PASSWORD = '" + ls_new + "'"

  EXECUTE IMMEDIATE :ls_string USING SQLCA;