July 1, 2011 at 10:14 am
Hi Sam!
I have a quick question to this topic...If i need to loop the query on different servers, is it possible? as we are keeping the retain same connection to true and the connection is not changed in run time if we need to loop other servers also , So is there any way to resolve the issue to loop to all servers list? for this topic solution . thanks in advance .
July 2, 2011 at 1:42 am
Hi Steve,
No i don't think tht option is feasible because the connection string for a OLEDB source is embedde within the component and u can't set it throught an expresion
Sam
July 5, 2011 at 8:46 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 5, 2011 at 8:59 am
Run that query from a query window. I think you'll find it returns more than one result set, and that's why your task is failing.
John
July 5, 2011 at 9:14 am
when i run the query i get only 1 result set .and i am still not able to figure out where i am going wrong .so we can use cursors right ?
Thanks in advance
July 6, 2011 at 1:20 am
OK, have you done a search on the error message you get? Here's one link that may help, but you'll find loads more.
John
July 6, 2011 at 7:14 am
In this link he has changed the result set to 0 but in my case i am bringing the data and assigning to some variable and i was using the OLEDB destination .I was able to split the query to 2 parts and defined the retain same connection to true and it works but when i need to loop to several servers the retain same connection will make to connection to be same for all the servers i loop
July 6, 2011 at 9:14 am
Chad E. Downey, CDMP - Certified Data Management Professional
Consultant - SQL Server, SSIS, SSRS, SSAS
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply