Select query error

Question

Select query error

MickyD

SSCarpal Tunnel

Points: 4601
More actions
July 3, 2014 at 8:51 am

#309741

Hi everyone.
I have a query that does return results , but returns an error.
This is the SQL statement:
DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName
Works , but results return
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
(222 row(s) affected)
Any ideas on how to address the 102 error ?
Thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You have one or more databases on the instance with a - in their name?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

try to add

PRINT @dbuser_sql

Before executing it.

I guess you may need to change:

"LEFT OUTER JOIN ?." to "LEFT OUTER JOIN [?]."

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 3

Thanks everyone , changed and working without error.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

ALERT!!!! You should NEVER use sp_MSforeachdb!!! http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service