DECLARE @server as nvarchar(50), @sql as nvarchar(4000)
DECLARE SharedServers CURSOR FORWARD_ONLY FOR
SELECT server FROM [dyna-dell].ebis_prod.dbo.SharedServerList
OPEN SharedServers FETCH NEXT FROM SharedServers INTO @server BEGIN TRANSACTION SET @sql = ''
WHILE @@FETCH_STATUS = 0 BEGIN IF (@server) <> '' BEGIN
--One itteration of SQL goes HERE
SET @sql = @sql + '
INSERT INTO [' + @server + '].dyna.dbo.TestTable (val1, val2) VALUES (''mReschke'', ''hi'')
'
END FETCH NEXT FROM SharedServers INTO @server
END exec sp_executesql @sql COMMIT
CLOSE SharedServers DEALLOCATE SharedServers
CREATE PROCEDURE SetProductflag
AS
Declare @EbisCancel datetime,
@CarMailCancel datetime,
@DealerMenusCancel datetime,
@DealerClubsCancel datetime,
@LiveCallCancel datetime,
@DynaSurveysCancel datetime,
@EbisPlusCancel datetime,
@DealerID int,
@EbisSaleDate datetime,
@CarMailSaleDate datetime,
@DealerMenusSaleDate datetime,
@DealerClubsSaleDate datetime,
@LiveCallSaleDate datetime,
@DynaSurveysSaleDate datetime,
@EbisPlusSaleDate datetime,
@CarMailUserName nvarchar(255),
@Distributor nvarchar(200),
@DistributorID int
Declare ProductFlag CURSOR FORWARD_ONLY FOR SELECT DealerID, DistributorID, EbisCancel, CarMailCancel, DealerMenusCancel, DealerClubsCancel, LiveCallCancel, DynaSurveysCancel, EbisPlusCancel,
EbisSaleDate, CarMailSaleDate, DealerMenusSaleDate, DealerClubsSaleDate, LiveCallSaleDate, DynaSurveysSaleDate, EbisPlusSaleDate, CarMailUserName
FROM [dyna-sql2].DealerProfile.dbo.tblDPSummary
Open ProductFlag
FETCH NEXT FROM ProductFlag
INTO @DealerID, @DistributorID, @EbisCancel, @CarMailCancel, @DealerMenusCancel, @DealerClubsCancel, @LiveCallCancel, @DynaSurveysCancel, @EbisPlusCancel,
@EbisSaleDate, @CarMailSaleDate, @DealerMenusSaleDate, @DealerClubsSaleDate, @LiveCallSaleDate, @DynaSurveysSaleDate, @EbisPlusSaleDate, @CarMailUserName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Distributor = (Select distributor from [dyna-sql2].DealerProfile.dbo.tblDistributors where id = @DistributorID)
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary Set Distributor=@Distributor Where DealerID = @DealerID
if @EbisSaleDate is not null and @EbisCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set EbisProductActive= 1
Where DealerID = @DealerID
End
if @CarMailSaleDate is not null and @CarMailCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set CarMailProductActive = 1
Where DealerID = @DealerID
End
if @DealerMenusSaleDate is not null and @DealerMenusCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set DealerMenusProductActive = 1
Where DealerID = @DealerID
End
if @DealerClubsSaleDate is not null and @DealerClubsCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set DealerClubProductActive = 1
Where DealerID = @DealerID
End
if @LiveCallSaleDate is not null and @LiveCallCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set LiveCallProductActive = 1
Where DealerID = @DealerID
End
if @DynaSurveysSaleDate is not null and @DynaSurveysCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set DynaSurveysProductActive = 1
Where DealerID = @DealerID
End
if @EbisPlusSaleDate is not null and @EbisPlusCancel is null
Begin
Update [dyna-sql2].DealerProfile.dbo.tblDPSummary
Set EbisPlusProductActive = 1
Where DealerID = @DealerID
End
FETCH NEXT FROM ProductFlag
INTO @DealerID, @DistributorID, @EbisCancel, @CarMailCancel, @DealerMenusCancel, @DealerClubsCancel, @LiveCallCancel, @DynaSurveysCancel, @EbisPlusCancel,
@EbisSaleDate, @CarMailSaleDate, @DealerMenusSaleDate, @DealerClubsSaleDate, @LiveCallSaleDate, @DynaSurveysSaleDate, @EbisPlusSaleDate, @CarMailUserName
END
CLOSE ProductFlag
DEALLOCATE ProductFlag
exec RecapCarMailCalculation
GO