Solved

MSforeachdb error.. Maximum length is 128.

Posted on 2011-09-19
19
1,056 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 59

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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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 59

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 59

Expert Comment

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

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 59

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 59

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 59

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 69
Display field if column exists 7 33
Convert time stamp to date 2 56
Trouble installing msi file with msiexe.exe 2 17
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

749 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