Solved

MSforeachdb error.. Maximum length is 128.

Posted on 2011-09-19
19
995 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
  • 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now