July 5, 2011 at 8:52 am
Hi !
I run the below query with SSIS on sybase DBMS but i was getting the error message stating that "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." and this query works fine in Sybase .So using the cursors will not work in SSIS ?.If so can some one guide me to make sure this query works so that i can loop with all the Sybase servers using the SSIS .Thanks in advance 🙂
Query :-
/* CREATE #AUDIT_PRIV_IDS TABLE */
use master
go
SET NOCOUNT ON
DECLARE @Temphost varchar (15)
DECLARE @Host varchar (15)
DECLARE @Instance varchar(32)
select @Temphost = address_info from master..syslisteners
select @Host = str_replace(@Temphost, '.' , ' ')
select @Instance =srvname from master..sysservers where srvid = 0
CREATE TABLE #AUDIT_PRIV_IDS
(
Host varchar(16)
,Instance varchar(32)
,DBName varchar(32)
,DBType varchar(8)
,LoginId varchar(20) NULL
,UserId varchar(20)
,Privilege varchar(20)
)
/* SELECT MEMBERS OF SERVER FIXED ROLES */
INSERT INTO #AUDIT_PRIV_IDS
SELECT @Host AS [Host]
,@Instance AS [Instance]
,'master' AS [DBName]
,'Sybase' AS [DBType]
,suser_name(suid) AS [LoginId]
,suser_name(suid) AS [UserId]
,role_name(srid) AS [Privilege]
FROM master..sysloginroles
go
/* SELECT MEMBERS OF EACH DATABASE ROLES */
declare get_db_names cursor for
select name from master..sysdatabases where name != 'master'
go
DECLARE @dbname varchar(32)
DECLARE @sqlstring varchar(512)
OPEN get_db_names
FETCH get_db_names into @dbname
while (@@sqlstatus=0)
begin
SET NOCOUNT ON
SELECT @sqlstring = 'INSERT INTO #AUDIT_PRIV_IDS
SELECT @Host AS Host,
@Instance AS Instance,
@dbname AS DBName,
@DBType AS DBType,
m.name AS LoginId,
u.name AS UserId,
g.name AS Privilege
FROM ' + @dbname + '..sysusers u, ' + @dbname + '..sysusers g, master..syslogins m
where u.suid *= m.suid
and u.gid *= g.uid
and ((u.uid < @@mingroupid and u.uid != 0)
or (u.uid > @@maxgroupid))'
DECLARE @Temphost varchar (15)
DECLARE @Host varchar (15)
DECLARE @Instance varchar(32)
DECLARE @DBType varchar(8)
select @Temphost = address_info from master..syslisteners
select @Host = str_replace(@Temphost, '.' , ' ')
select @Instance =srvname from master..sysservers where srvid = 0
set @DBType = 'Sybase'
execute (@sqlstring)
FETCH get_db_names into @dbname
end
CLOSE get_db_names
DEALLOCATE CURSOR get_db_names
go
select * from #AUDIT_PRIV_IDS
go
drop table #AUDIT_PRIV_IDS
go
July 11, 2011 at 3:07 am
Isn't there a way to rewrite the query so that a cursor isn't necessary?
You can use the For Loop and the For Each Loop in SSIS to create loops.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 9:00 am
I think Koen is pointing you in the right direction. You could use an Execute SQL Task to populate an ADO Recordset with your database names and then use a FOR EACH LOOP to iterate over the recordset to run your query. I think you would need a permanent table instead of a temporary table and then outside the loop do your select from the permanent table to return your results.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply