Variable Size Woes

• Stamey

  SSCertifiable

  Points: 5600

  More actions

  October 21, 2015 at 6:10 am

  #322076

  I have a script that checks for orphaned users in DBs and deletes them if there is no matching login on the server. The weirdest thing is happening though. It acts like my variable isn't big enough although it is declared at VarChar(8000) and the number of characters in the variable is about 2200 or less. I have tried declaring the variable at 4000 to begin with, but that did not help either.

  The reason I believe it's the variable size is because as it goes though each user ID I get different errors that seem to correspond to the length of the user ID, plus if I change the length of comments in my code, the error message changes.

  This script generates scripts dynamically to run in each DB and is run using sp_MSForEachDB.

  If I copy the code it generated a paste into a new window, it has no problem executing. If I count the generated script's characters, up to where it says the error is, I have 2000 bytes. Note that I have declared all of my variables well in excess of 2000 byes.

  I have edited this to removed all of the confusing code because I had an epiphany. It is a limitation of the sp_MSforEachDB store proc. That stored proac change whatever you send into it to NVarChar(2000), so if you have something bigger than that it will fail.

  Fortunately, it does allow you to send multiple variable into it as @Command1, @Command2, @Command3, so you can still pass something larger into it, up to 6000 characters. Unfortunately, it doesn't handle multiple command variables well. In order to make my code work I had to use Aaron Bertrand's version of sp_ForEachDB, which has more capacity and better control over the databases. My code now works perfectly.

  https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

  Some of the microsoft code:

  create proc sys.sp_MSforeachdb

  @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,

  @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null

  Thanks,

  Chris

  Learning something new on every visit to SSC. Hoping to pass it on to someone else.