• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1278
  • Last Modified:

MSforeachdb error.. Maximum length is 128.

can you see what error causes the above..
create  table #TempTable (dbase_name varchar(100),
Name varchar(100), perm varchar(100),
description varchar(100), object_name varchar(100))
sp_MSforeachdb '--if "?" in (''DEC_LED'',
''DEC_HED'',''DEC_TPE'')
begin
use [?];
insert into #TempTable(dbase_name , Name ,
perm , description , object_name )
SELECT "?",p.name, permission_name, state_desc,
object_name_name(major_id)
FROM ?.sys.database_permissions dp JOIN
?.sys.database_principals p
ON dp.grantee_principal_id = p.principal_id
AND name = ''DECLA\DE-eDEC_Developers''
end
'
select * from #TempTable
drop table #TempTable
0
25112
Asked:
25112
  • 7
  • 5
  • 4
  • +2
3 Solutions
 
25112Author Commented:
exact error is
Msg 103, Level 15, State 17, Line 4
The identifier that starts with '--if "?" in ('DEC_LED',
'DEC_HED','DEC_TPE') 
begin
use [?];
insert into #TempTable(dbase_name , Name , 
' is too long. Maximum length is 128.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'sp_MSforeachdb'.

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
The query (or string) you are passing to sp_MSforeachdb is too large.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Yep this is a limitation of the sp_msforeachdb procedure.  I suggest you create a cursor or use another alternative to loop.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
BrandonGalderisiCommented:
I think the problem is with your mixed use of single and double quotes.  It's treating strings as identifiers.  Try switching to singles.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Increase the length of data types in temp table to 200
0
 
25112Author Commented:
BrandonGalderisi - the seemingly double quotes are single quotes.. the other one is the escape character.. hope i understood what you said..
0
 
25112Author Commented:
Racimo, increasing them all to 200 gives

The size (102000) given to the column 'Perm' exceeds the maximum allowed for any data type (8000). this is sql r2.. could we take advantage of it somehow?
0
 
25112Author Commented:

>>The query (or string) you are passing to sp_MSforeachdb is too large.

is it possible to put the sql string in multiple strings and pass it to MSforeachdb ? this is the first time i have hit a limitation with this proc.. did not know this before..
0
 
Kevin CrossChief Technology OfficerCommented:
You could consider rewriting this. Since you you have identified the three databases you want, why use sp_MSforeachdb is one thought?

Aside from that, you may be able to write this without the IF structure since you are storing the database name in the #temptable. You can just remove the rows that don't meet the IF later. I know that is probably less efficient, but an option.

A secondary option to this is to remove the IF, but add into the WHERE clause of the INSERT a WHERE ? NOT IN (...). Should do the same thing without the added character size of the IF structure.

Maybe this can be written into procedure that accepts database name.
0
 
Anthony PerkinsCommented:
Try it this way:
CREATE  TABLE #TempTable
    (dbase_name varchar(100),
     Name varchar(100),
     perm varchar(100),
     description varchar(100),
     [object_name] varchar(100))
EXEC sp_MSforeachdb '
--IF ''?'' IN (''DEC_LED'', ''DEC_HED'',''DEC_TPE'') 
BEGIN

USE [?];
INSERT INTO #TempTable(dbase_name, Name, perm,description, [object_name]) 
SELECT	''?'', p.name, dp.permission_name, dp.state_desc, object_name(dp.major_id) 
FROM	sys.database_permissions dp 
	JOIN sys.database_principals p ON dp.grantee_principal_id = p.principal_id 
WHERE   p.name = ''DECLA\DE-eDEC_Developers''

END
'

SELECT  *
FROM    #TempTable

DROP TABLE #TempTable

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Ha. The IF was commented out anyway.
0
 
Kevin CrossChief Technology OfficerCommented:
Nice catch acperkins. Or should I say, nice thought on putting this in a code block. Makes it easier to debug.
0
 
Kevin CrossChief Technology OfficerCommented:
Hmm. I swore the error had to do with the string as it is the same error you get with bcp utility when you pass too big a query. Anyway, this works.
CREATE  TABLE #TempTable(
   dbase_name varchar(100),
   Name varchar(100),
   perm varchar(100),
   description varchar(100),
   [object_name] varchar(100)
);

EXEC sp_MSforeachdb '
INSERT INTO #TempTable(dbase_name, Name, perm,description, [object_name]) 
SELECT ''?'', p.name, dp.permission_name, dp.state_desc, object_name(dp.major_id) 
FROM sys.database_permissions dp 
JOIN sys.database_principals p ON dp.grantee_principal_id = p.principal_id 
WHERE   p.name = ''DECLA\DE-eDEC_Developers''
AND ''?'' IN (''DEC_LED'', ''DEC_HED'',''DEC_TPE'')
'

SELECT  *
FROM #TempTable

DROP TABLE #TempTable

Open in new window

0
 
Anthony PerkinsCommented:
Also, the author does not know the difference between a double quote (as pointed out by Brandon) and a single quote:  SELECT "?"  has double quotes.
0
 
Anthony PerkinsCommented:
>>Anyway, this works. <<
Actually not quite.  You either need
USE [?]         -- This is what I used
or
FROM [?].sys.database_permissions dp
JOIN [?].sys.database_principals

Otherwise you are going to be selecting from the same database over and over again...
0
 
Kevin CrossChief Technology OfficerCommented:
:) I forgot the "USE [?];" part.
By the way, acperkins way works perfectly for me also. So as I said, I stand corrected on the length issue as I must have been thinking about bcp utility.
0
 
Anthony PerkinsCommented:
If in fact the query is only going against the three databases than all you have to do is uncomment the line with IF as in:
CREATE  TABLE #TempTable
    (dbase_name varchar(100),
     Name varchar(100),
     perm varchar(100),
     description varchar(100),
     [object_name] varchar(100))
EXEC sp_MSforeachdb '
IF ''?'' IN (''DEC_LED'', ''DEC_HED'',''DEC_TPE'') 
BEGIN

USE [?];
INSERT INTO #TempTable(dbase_name, Name, perm,description, [object_name]) 
SELECT	''?'', p.name, dp.permission_name, dp.state_desc, object_name(dp.major_id) 
FROM	sys.database_permissions dp 
	JOIN sys.database_principals p ON dp.grantee_principal_id = p.principal_id 
WHERE   p.name = ''DECLA\DE-eDEC_Developers''

END
'

SELECT  *
FROM    #TempTable

DROP TABLE #TempTable

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Exactly. You are 100% correct. I took the original query and cleaned up the bad quoting and type-o of object_name_name and all was well.
0
 
25112Author Commented:
Brandon- I am sorry I did not understand you right.. thanks for helping acperkins..

mwvisa1 - appreciate your support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now