Solved

MSforeachdb error.. Maximum length is 128.

Posted on 2011-09-19
19
1,125 Views
Last Modified: 2012-05-12
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
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +2
19 Comments
 
LVL 5

Author Comment

by:25112
ID: 36562404
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36562685
The query (or string) you are passing to sp_MSforeachdb is too large.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36562742
Yep this is a limitation of the sp_msforeachdb procedure.  I suggest you create a cursor or use another alternative to loop.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 50 total points
ID: 36562781
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36562816
Increase the length of data types in temp table to 200
0
 
LVL 5

Author Comment

by:25112
ID: 36563929
BrandonGalderisi - the seemingly double quotes are single quotes.. the other one is the escape character.. hope i understood what you said..
0
 
LVL 5

Author Comment

by:25112
ID: 36563935
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
 
LVL 5

Author Comment

by:25112
ID: 36563945

>>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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36564023
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36564180
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36564191
Ha. The IF was commented out anyway.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36564193
Nice catch acperkins. Or should I say, nice thought on putting this in a code block. Makes it easier to debug.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36564208
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36564215
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36564221
>>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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 50 total points
ID: 36564223
:) 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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 36564233
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36564238
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
 
LVL 5

Author Comment

by:25112
ID: 36577419
Brandon- I am sorry I did not understand you right.. thanks for helping acperkins..

mwvisa1 - appreciate your support.
0

Featured Post

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question