Below is what I have put into an execute sql task container. When I try to run it I have output open and have posted the output. The code runs fine when I have the configuration database set up to only check one server. However, when I add anymore to it, the package will get the security from the first db and then error out. Below is the error output and script. Any help with my problem is greatly appreciated.
**********Begin script*******************
declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @.mSql1
Execute (@.mSql1)
FETCH NEXT FROM DBName_Cursor INTO @.dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go
****************End Script***********************
****************Begin Error Output********************
[Execute SQL Task] Error: Executing the query "declare @.dbname varchar(200) declare @.mSql1 varchar(8000) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @.dbname WHILE @.@.FETCH_STATUS = 0 BEGIN Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @.dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @.dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @.mSql1 Execute (@.mSql1) FETCH NEXT FROM DBName_Cursor INTO @.dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor " failed with the following error: "Line 15: Incorrect syntax near '-'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
**************End Error Output**************
Not knowing your database names, I'd suspect that one or more of them have names that need to be bracketed in a SQL statement. The error indicates that one might have a hyphen in the name? Try putting brackets around the @.dbname variable. Ex: "from [' + @.dbName+'].dbo.sysmembers"|||I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.|||
 kschlap wrote: |
| |
| I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow. |
What you have would work fine for a database named FooBar, but it would fail with the error you posted if the database were named Foo-Bar.
|||
I"m sorry, I made a mistake in my last post. I meant to say it worked for one server, but won't work for any other server.
-Kyle
|||
I think I have figured out one of the problems. I need to change the OLE DB Connection Manager. However, it won't let me do this and hit ok because it says that the table in my FROM clause is not in the database. Well of course its not on the database because the table gets built dynamically and the servers are gathered dynamically. How can I change this?
-Kyle
|||SSIS tries to validate the tasks when you are editing them; so I guess the table have to exists while you are editing the package. At run time though you can set DelayValidation to true; so the package does not fail validation.|||
The problem with this is that the connection I am trying to use is generated dynamically too. Thus, there are no servers until run time that I could add the table to to get around this error.
-Kyle
|||I don't get that. Does this mean you don't have a development environment where you can create oll required stuff? remebember this is just for validation purposes.|||
No, I have the proper development environment. I just get an error that says that the table doesn't exist whenever I try to make a change to the OLE DB connection. Here is the message I sent to the template creator yesterday.
I am having trouble with changing an OLE DB source. The package is set to not validate until run time, but currently it won’t let me change one of the settings. I keep getting an error that the table it is referring to is not valid. However, I know that it isn’t created because it gets created at run-time. Is there a way to change OLE DB sources? As always any help is appreciated.
|||
I fixed the problem. The package needed me to make an advanced edit instead of a normal one? Weird, but I don't care why it worked just because it worked. If anybody else is building a sql dba configuration database, mine is from a template by sqlmag, please PM me and we can work together on them.
-Kyle
